3.1.2 Declaring Host Variables
7.3 Type Codes
7.5.1 ALLOCATE DESCRIPTOR
7.5.2 CALL
7.5.3 CLOSE
7.5.4 COMMIT
7.5.5 CONNECT
7.5.6 DEALLOCATE DESCRIPTOR
7.5.7 DECLARE CURSOR
7.5.8 DECLARE DATABASE
7.5.9 DECLARE STATEMENT
7.5.10 DELETE
7.5.11 DESCRIBE
7.5.12 DESCRIBE DESCRIPTOR
7.5.13 DISCONNECT
7.5.14 EXECUTE
7.5.15 EXECUTE DESCRIPTOR
7.5.16 EXECUTE...END EXEC
7.5.17 EXECUTE IMMEDIATE
7.5.18 FETCH
7.5.19 FETCH DESCRIPTOR
7.5.20 GET DESCRIPTOR
7.5.21 INSERT
7.5.22 OPEN
7.5.23 OPEN DESCRIPTOR
7.5.24 PREPARE
7.5.25 ROLLBACK
7.5.26 SAVEPOINT
7.5.27 SELECT
7.5.28 SET CONNECTION
7.5.29 SET DESCRIPTOR
7.5.30 UPDATE
7.5.31 WHENEVER
In the following descriptions a term refers to any word or group of words that are language keywords, user-supplied values, literals, etc. A term’s exact meaning depends upon the context in which it is used.
To produce an executable from a C program that contains embedded SQL statements, pass the program (my_program.pgc in the diagram above) to the ECPGPlus pre-compiler. ECPGPlus translates each SQL statement in my_program.pgc into C code that calls the ecpglib API, and produces a C program (my_program.c). Then, pass the C program to a C compiler; the C compiler generates an object file (my_program.o). Finally, pass the object file (my_program.o), as well as the ecpglib library file, and any other required libraries to the linker, which in turn produces the executable (my_program).While the ECPGPlus preprocessor validates the syntax of each SQL statement, it cannot validate the semantics. For example, the preprocessor will confirm that an INSERT statement is syntactically correct, but it cannot confirm that the table mentioned in the INSERT statement actually exists.C preprocessor directives may be interpreted or ignored; the option is controlled by a command line option (-C PROC) entered when you invoke ECPGPlus. In either case, ECPGPlus copies each C preprocessor directive to the output file (4) without change; any C preprocessor directive found in the source file will appear in the output file.C declarations are copied to the output file without change, except that each VARCHAR declaration is translated into an equivalent struct declaration.
C variables that you refer to within SQL code are known as host variables. If you invoke the ECPGPlus preprocessor in Pro*C mode (-C PROC), you may refer to any C variable within a SQL statement; otherwise you must declare each host variable within a BEGIN/END DECLARATION SECTION pair.Any SQL statement must be prefixed with EXEC SQL and extends to the next (unquoted) semicolon. For example:When the preprocessor encounters the code fragment shown above, it passes the C code (the first line and the last line) to the output file without translation and converts each EXEC SQL statement into a call to an ecpglib function. The result would appear similar to the following:
On Windows, ECPGPlus is installed by the Advanced Server installation wizard as part of the Database Server component. On Linux, install with the edb-asxx-server-devel RPM package where xx is the Advanced Server version number. By default, on a Linux installation, the executable is located in:When invoking the ECPGPlus compiler, the executable must be in your search path (%PATH% on Windows, $PATH on Linux). For example, the following commands set the search path to include the directory that holds the ECPGPlus executable file ecpg.
A makefile contains a set of instructions that tell the make utility how to transform a program written in C (that contains embedded SQL) into a C program. To try the examples in this guide, you will need:
In the sample makefile shown above, make includes the -C option when invoking ECPGPlus to specify that ECPGPlus should be invoked in Pro*C compatible mode.If you include the -C PROC keywords on the command line, in addition to the ECPG syntax, you may use Pro*C command line syntax; for example:
The first code sample demonstrates how to execute a SELECT statement (which returns a single row), storing the results in a group of host variables. After declaring host variables, it connects to the edb sample database using a hard-coded role name and the associated password, and queries the emp table. The query returns the values into the declared host variables; after checking the value of the NULL indicator variable, it prints a simple result set onscreen and closes the connection.The code sample begins by including the prototypes and type definitions for the C stdio library, and then declares the main function:Please note that if you plan to pre-compile the code in PROC mode, you may omit the BEGIN DECLARE…END DECLARE section. For more information about declaring host variables, refer to Section 3.1.2, Declaring Host Variables.The data type associated with each variable within the declaration section is a C data type. Data passed between the server and the client application must share a compatible data type; for more information about data types, see Section 7.2, Supported C Data Types.If the client application encounters an error in the SQL code, the server will print an error message to stderr (standard error), using the sqlprint() function supplied with ecpglib. The next EXEC SQL statement establishes a connection with Advanced Server:In our example, the client application connects to the edb database, using a role named alice with a password of 1safepwd.The SELECT statement uses an INTO clause to assign the retrieved values (from the empno, ename, sal and comm columns) into the :v_empno, :v_ename, :v_sal and :v_comm host variables (and the :v_comm_ind null indicator). The first value retrieved is assigned to the first variable listed in the INTO clause, the second value is assigned to the second variable, and so on.The comm column contains the commission values earned by an employee, and could potentially contain a NULL value. The statement includes the INDICATOR keyword, and a host variable to hold a null indicator.If the null indicator is 0 (that is, false), the comm column contains a meaningful value, and the printf function displays the commission. If the null indicator contains a non-zero value, comm is NULL, and printf displays a value of NULL. Please note that a host variable (other than a null indicator) contains no meaningful value if you fetch a NULL into that host variable; you must use null indicators to identify any value which may be NULL.
The previous example included an indicator variable that identifies any row in which the value of the comm column (when returned by the server) was NULL. An indicator variable is an extra host variable that denotes if the content of the preceding variable is NULL or truncated. The indicator variable is populated when the contents of a row are stored. An indicator variable may contain the following values:
3.1.2 Declaring Host VariablesYou can use a host variable in a SQL statement at any point that a value may appear within that statement. A host variable is a C variable that you can use to pass data values from the client application to the server, and return data from the server to the client application. A host variable can be:
Dynamic SQL allows a client application to execute SQL statements that are composed at runtime. This is useful when you don't know the content or form a statement will take when you are writing a client application. ECPGPlus does not allow you to use a host variable in place of an identifier (such as a table name, column name or index name); instead, you should use dynamic SQL statements to build a string that includes the information, and then execute that string. The string is passed between the client and the server in the form of a descriptor. A descriptor is a data structure that contains both the data and the information about the shape of the data.A client application must use a GET DESCRIPTOR statement to retrieve information from a descriptor. The following steps describe the basic flow of a client application using dynamic SQL:
The following example demonstrates how to use the EXECUTE IMMEDIATE command to execute a SQL statement where the text of the statement is not known until you run the application. You cannot use EXECUTE IMMEDIATE to execute a statement that returns a result set. You cannot use EXECUTE IMMEDIATE to execute a statement that contains parameter placeholders.The EXECUTE IMMEDIATE statement parses and plans the SQL statement each time it executes, which can have a negative impact on the performance of your application. If you plan to execute the same statement repeatedly, consider using the PREPARE/EXECUTE technique described in the next example.The code sample begins by including the prototypes and type definitions for the C stdio, string, and stdlib libraries, and providing basic infrastructure for the program:The example then sets up an error handler; ECPGPlus calls the handle_error() function whenever a SQL error occurs:
EXEC SQL CONNECT :argv[1];
Next, the program uses an EXECUTE IMMEDIATE statement to execute a SQL statement, adding a row to the dept table:If the EXECUTE IMMEDIATE command fails for any reason, ECPGPlus will invoke the handle_error() function (which terminates the application after displaying an error message to the user). If the EXECUTE IMMEDIATE command succeeds, the application displays a message (ok) to the user, commits the changes, disconnects from the server, and terminates the application.ECPGPlus calls the handle_error() function whenever it encounters a SQL error. The handle_error() function prints the content of the error message, resets the error handler, rolls back any changes, disconnects from the database, and terminates the application.
To execute a non-query command that includes a known number of parameter placeholders, you must first PREPARE the statement (providing a statement handle), and then EXECUTE the statement using the statement handle. When the application executes the statement, it must provide a value for each placeholder found in the statement.When an application uses the PREPARE/EXECUTE mechanism, each SQL statement is parsed and planned once, but may execute many times (providing different values each time).The code sample begins by including the prototypes and type definitions for the C stdio, string, stdlib, and sqlca libraries, and providing basic infrastructure for the program:The example then sets up an error handler; ECPGPlus calls the handle_error() function whenever a SQL error occurs:Next, the program uses a PREPARE statement to parse and plan a statement that includes three parameter markers - if the PREPARE statement succeeds, it will create a statement handle that you can use to execute the statement (in this example, the statement handle is named stmtHandle). You can execute a given statement multiple times using the same statement handle.After parsing and planning the statement, the application uses the EXECUTE statement to execute the statement associated with the statement handle, substituting user-provided values for the parameter markers:If the EXECUTE command fails for any reason, ECPGPlus will invoke the handle_error() function (which terminates the application after displaying an error message to the user). If the EXECUTE command succeeds, the application displays a message (ok) to the user, commits the changes, disconnects from the server, and terminates the application.ECPGPlus calls the handle_error() function whenever it encounters a SQL error. The handle_error() function prints the content of the error message, resets the error handler, rolls back any changes, disconnects from the database, and terminates the application.
This example demonstrates how to execute a query with a known number of input parameters, and with a known number of columns in the result set. This method uses the PREPARE statement to parse and plan a query, before opening a cursor and iterating through the result set.The code sample begins by including the prototypes and type definitions for the C stdio, string, stdlib, stdbool, and sqlca libraries, and providing basic infrastructure for the program:The example then sets up an error handler; ECPGPlus calls the handle_error() function whenever a SQL error occurs:Next, the program uses a PREPARE statement to parse and plan a query that includes a single parameter marker - if the PREPARE statement succeeds, it will create a statement handle that you can use to execute the statement (in this example, the statement handle is named stmtHandle). You can execute a given statement multiple times using the same statement handle.The program then declares and opens the cursor, empCursor, substituting a user-provided value for the parameter marker in the prepared SELECT statement. Notice that the OPEN statement includes a USING clause: the USING clause must provide a value for each placeholder found in the query:The application calls the handle_error() function whenever it encounters a SQL error. The handle_error() function prints the content of the error message, resets the error handler, rolls back any changes, disconnects from the database, and terminates the application.
The next example demonstrates executing a query with an unknown number of input parameters and/or columns in the result set. This type of query may occur when you prompt the user for the text of the query, or when a query is assembled from a form on which the user chooses from a number of conditions (i.e., a filter).The code sample begins by including the prototypes and type definitions for the C stdio and stdlib libraries. In addition, the program includes the sqlda.h and sqlcpr.h header files. sqlda.h defines the SQLDA structure used throughout this example. sqlcpr.h defines a small set of functions used to interrogate the metadata found in an SQLDA structure.Next, the program declares pointers to two SQLDA structures. The first SQLDA structure (params) will be used to describe the metadata for any parameter markers found in the dynamic query text. The second SQLDA structure (results) will contain both the metadata and the result set obtained by executing the dynamic query.Next, the program declares three host variables; the first two (username and password) are used to connect to the database server; the third host variable (stmtTxt) is a NULL-terminated C string containing the text of the query to execute. Notice that the values for these three host variables are derived from the command-line arguments. When the program begins execution, it sets up an error handler and then connects to the database server:Next, the program calls the sqlald()function to allocate the memory required for each descriptor. Each descriptor contains (among other things):When you allocate an SQLDA descriptor, you specify the maximum number of columns you expect to find in the result set (for SELECT-list descriptors) or the maximum number of parameters you expect to find the dynamic query text (for bind-variable descriptors) - in this case, we specify that we expect no more than 20 columns and 20 parameters. You must also specify a maximum length for each column (or parameter) name and each indicator variable name - in this case, we expect names to be no more than 64 bytes long.After allocating the SELECT-list and bind descriptors, the program prepares the dynamic statement and declares a cursor over the result set.Next, the program calls the bindParams() function. The bindParams() function examines the bind descriptor (params) and prompt the user for a value to substitute in place of each parameter marker found in the dynamic query.Finally, the program opens the cursor (using the parameter values supplied by the user, if any) and calls the displayResultSet() function to print the result set produced by the query.The bindParams() function determines whether the dynamic query contains any parameter markers, and, if so, prompts the user for a value for each parameter and then binds that value to the corresponding marker. The DESCRIBE BIND VARIABLE statement populates the params SQLDA structure with information describing each parameter marker.If the statement contains no parameter markers, params->F will contain 0. If the statement contains more parameters than will fit into the descriptor, params->F will contain a negative number (in this case, the absolute value of params->F indicates the number of parameter markers found in the statement). If params->F contains a positive number, that number indicates how many parameter markers were found in the statement.After prompting the user for a value for a given parameter, the program binds that value to the parameter by setting params->T[i] to indicate the data type of the value (see Section 7.3 for a list of type codes), params->L[i] to the length of the value (we subtract one to trim off the trailing new-line character added by fgets()), and params->V[i] to point to a copy of the NULL-terminated string provided by the user.The displayResultSet() function loops through each row in the result set and prints the value found in each column. displayResultSet() starts by executing a DESCRIBE SELECT LIST statement - this statement populates an SQLDA descriptor (results) with a description of each column in the result set.If the dynamic statement returns no columns (that is, the dynamic statement is not a SELECT statement), results->F will contain 0. If the statement returns more columns than will fit into the descriptor, results->F will contain a negative number (in this case, the absolute value of results->F indicates the number of columns returned by the statement). If results->F contains a positive number, that number indicates how many columns where returned by the query.To decode the type code found in results->T, the program invokes the sqlnul() function (see the description of the T member of the SQLDA structure in Section 7.4). This call to sqlnul() modifies results->T[col] to contain only the type code (the nullability flag is copied to null_permitted). This step is necessary because the DESCRIBE SELECT LIST statement encodes the type of each column and the nullability of each column into the T array.After decoding the actual data type of the column, the program modifies the results descriptor to tell ECPGPlus to return each value in the form of a NULL-terminated string. Before modifying the descriptor, the program must compute the amount of space required to hold each value. To make this computation, the program examines the maximum length of each column (results->V[col]) and the data type of each column (results->T[col]).For numeric values (where results->T[col] = 2), the program calls the sqlprc() function to extract the precision and scale from the column length. To compute the number of bytes required to hold a numeric value in string form, displayResultSet() starts with the precision (that is, the maximum number of digits) and adds three bytes for a sign character, a decimal point, and a NULL terminator.For a value of any type other than date or numeric, displayResultSet() starts with the maximum column width reported by DESCRIBE SELECT LIST and adds one extra byte for the NULL terminator. Again, in a real-world application you may want to include more careful calculations for other data types.After computing the amount of space required to hold a given column, the program allocates enough memory to hold the value, sets results->L[col] to indicate the number of bytes found at results->V[col], and set the type code for the column (results->T[col]) to 1 to instruct the upcoming FETCH statement to return the value in the form of a NULL-terminated string.The program executes a FETCH statement to fetch the next row in the cursor into the results descriptor. If the FETCH statement fails (because the cursor is exhausted), control transfers to the end of the loop because of the EXEC SQL WHENEVER directive found before the top of the loop.The FETCH statement will populate the following members of the results descriptor:
sqlca (SQL communications area) is a global variable used by ecpglib to communicate information from the server to the client application. After executing a SQL statement (for example, an INSERT or SELECT statement) you can inspect the contents of sqlca to determine if the statement has completed successfully or if the statement has failed.sqlca has the following structure:Use the following directive to implement sqlca functionality:If you include the ecpg directive, you do not need to #include the sqlca.h file in the client application's header declaration.The Advanced Server sqlca structure contains the following members:sqlcaid contains the string: "SQLCA".sqlabc contains the size of the sqlca structure.The sqlcode member has been deprecated with SQL 92; Advanced Server supports sqlcode for backward compatibility, but you should use the sqlstate member when writing new code.sqlcode is an integer value; a positive sqlcode value indicates that the client application has encountered a harmless processing condition, while a negative value indicates a warning or error.If a statement processes without error, sqlcode will contain a value of 0. If the client application encounters an error (or warning) during a statement's execution, sqlcode will contain the last code returned.The SQL standard defines only a positive value of 100, which indicates that he most recent SQL statement processed returned/affected no rows. Since the SQL standard does not define other sqlcode values, please be aware that the values assigned to each condition may vary from database to database.sqlerrm is a structure embedded within sqlca, composed of two members:sqlerrml contains the length of the error message currently stored in sqlerrmc.sqlerrmc contains the null-terminated message text associated with the code stored in sqlstate. If a message exceeds 149 characters in length, ecpglib will truncate the error message.sqlerrp contains the string "NOT SET".sqlerrd is an array that contains six elements:sqlerrd[1] contains the OID of the processed row (if applicable).sqlerrd[2] contains the number of processed or returned rows.sqlwarn is an array that contains 8 characters:sqlwarn[1] contains a value of 'W' if a data value was truncated when it was stored in a host variable.sqlwarn[2] contains a value of 'W' if the client application encounters a non-fatal warning.sqlstate is a 5 character array that contains a SQL-compliant status code after the execution of a statement from the client application. If a statement processes without error, sqlstate will contain a value of 00000. Please note that sqlstate is not a null-terminated string.sqlstate codes are assigned in a hierarchical scheme:
Use the EXEC SQL WHENEVER directive to implement simple error handling for client applications compiled with ECPGPlus. The syntax of the directive is:The code instructs the client application that it should perform a specified action if the client application detects a given condition. The condition may be one of the following:You can specify that the client application perform one of the following actions if it encounters one of the previous conditions:Specify CONTINUE to instruct the client application to continue processing, ignoring the current condition. CONTINUE is the default action.DO CONTINUEAn action of DO CONTINUE will generate a CONTINUE statement in the emitted C code that if it encounters the condition, skips the rest of the code in the loop and continues with the next iteration. You can only use it within a loop.Print an error message to stderr (standard error), using the sqlprint() function. The sqlprint() function prints sql error, followed by the contents of sqlca.sqlerrm.sqlerrmc.Call exit(1) to signal an error, and terminate the program.Invoke the C function specified by the name parameter, using the parameters specified in the args parameter.Please Note: The ECPGPlus compiler processes your program from top to bottom, even though the client application may not execute from top to bottom. The compiler directive is applied to each line in order, and remains in effect until the compiler encounters another directive.
An ECPGPlus application must deal with two sets of data types: SQL data types (such as SMALLINT, DOUBLE PRECISION and CHARACTER VARYING) and C data types (like short, double and varchar[n]). When an application fetches data from the server, ECPGPlus will map each SQL data type to the type of the C variable into which the data is returned.ECPGPlus can convert any SQL type into C character values (char[n] or varchar[n]). Although it is safe to convert any SQL type to/from char[n] or varchar[n], it is often convenient to use more natural C types such as int, double, or float.
7.3 Type CodesThe following table contains the type codes for external data types. An external data type is used to indicate the type of a C host variable. When an application binds a value to a parameter or binds a buffer to a SELECT-list item, the type code in the corresponding SQLDA descriptor (descriptor->T[column]) should be set to one of the following values:
N - maximum number of entriesThe N structure member contains the maximum number of entries that the SQLDA may describe. This member is populated by the sqlald() function when you allocate the SQLDA structure. Before using a descriptor in an OPEN or FETCH statement, you must set N to the actual number of values described.V - data valuesThe V structure member is a pointer to an array of data values.For a SELECT-list descriptor, V points to an array of values returned by a FETCH statement (each member in the array corresponds to a column in the result set).For a bind descriptor, V points to an array of parameter values (you must populate the values in this array before opening a cursor that uses the descriptor).Your application must allocate the space required to hold each value. See the displayResultSet() function for an example of how to allocate space for SELECT-list values (Section 5.4, Executing a Query with an Unknown Number of Variables).L - length of each data valueThe L structure member is a pointer to an array of lengths. Each member of this array must indicate the amount of memory available in the corresponding member of the V array. For example, if V[5] points to a buffer large enough to hold a 20-byte NULL-terminated string, L[5] should contain the value 21 (20 bytes for the characters in the string plus 1 byte for the NULL-terminator). Your application must set each member of the L array.T - data typesThe T structure member points to an array of data types, one for each column (or parameter) described by the descriptor.For a bind descriptor, you must set each member of the T array to tell ECPGPlus the data type of each parameter.For a SELECT-list descriptor, the DESCRIBE SELECT LIST statement sets each member of the T array to reflect the type of data found in the corresponding column.You may change any member of the T array before executing a FETCH statement to force ECPGPlus to convert the corresponding value to a specific data type. For example, if the DESCRIBE SELECT LIST statement indicates that a given column is of type DATE, you may change the corresponding T member to request that the next FETCH statement return that value in the form of a NULL-terminated string. Each member of the T array is a numeric type code (see Section 7.3 for a list of type codes). The type codes returned by a DESCRIBE SELECT LIST statement differ from those expected by a FETCH statement. After executing a DESCRIBE SELECT LIST statement, each member of T encodes a data type and a flag indicating whether the corresponding column is nullable. You can use the sqlnul() function to extract the type code and nullable flag from a member of the T array. The signature of the sqlnul() function is as follows:For example, to find the type code and nullable flag for the third column of a descriptor named results, you would invoke sqlnul() as follows:I - indicator variablesThe I structure member points to an array of indicator variables. This array is allocated for you when your application calls the sqlald() function to allocate the descriptor.For a SELECT-list descriptor, each member of the I array indicates whether the corresponding column contains a NULL (non-zero) or non-NULL (zero) value.For a bind parameter, your application must set each member of the I array to indicate whether the corresponding parameter value is NULL.F - number of entriesThe F structure member indicates how many values are described by the descriptor (the N structure member indicates the maximum number of values which may be described by the descriptor; F indicates the actual number of values). The value of the F member is set by ECPGPlus when you execute a DESCRIBE statement. F may be positive, negative, or zero.For a SELECT-list descriptor, F will contain a positive value if the number of columns in the result set is equal to or less than the maximum number of values permitted by the descriptor (as determined by the N structure member); 0 if the statement is not a SELECT statement, or a negative value if the query returns more columns than allowed by the N structure member.For a bind descriptor, F will contain a positive number if the number of parameters found in the statement is less than or equal to the maximum number of values permitted by the descriptor (as determined by the N structure member); 0 if the statement contains no parameters markers, or a negative value if the statement contains more parameter markers than allowed by the N structure member.If F contains a positive number (after executing a DESCRIBE statement), that number reflects the count of columns in the result set (for a SELECT-list descriptor) or the number of parameter markers found in the statement (for a bind descriptor). If F contains a negative value, you may compute the absolute value of F to discover how many values (or parameter markers) are required. For example, if F contains -24 after describing a SELECT list, you know that the query returns 24 columns.S - column/parameter namesThe S structure member points to an array of NULL-terminated strings.For a SELECT-list descriptor, the DESCRIBE SELECT LIST statement sets each member of this array to the name of the corresponding column in the result set.For a bind descriptor, the DESCRIBE BIND VARIABLES statement sets each member of this array to the name of the corresponding bind variable.In this release, the name of each bind variable is determined by the left-to-right order of the parameter marker within the query - for example, the name of the first parameter is always ?0, the name of the second parameter is always ?1, and so on.M - maximum column/parameter name lengthThe M structure member points to an array of lengths. Each member in this array specifies the maximum length of the corresponding member of the S array (that is, M[0] specifies the maximum length of the column/parameter name found at S[0]). This array is populated by the sqlald() function.C - actual column/parameter name lengthThe C structure member points to an array of lengths. Each member in this array specifies the actual length of the corresponding member of the S array (that is, C[0] specifies the actual length of the column/parameter name found at S[0]).This array is populated by the DESCRIBE statement.X - indicator variable namesThe X structure member points to an array of NULL-terminated strings - each string represents the name of a NULL indicator for the corresponding value.Y - maximum indicator name lengthThe Y structure member points to an array of lengths. Each member in this array specifies the maximum length of the corresponding member of the X array (that is, Y[0] specifies the maximum length of the indicator name found at X[0]).Z - actual indicator name lengthThe Z structure member points to an array of lengths. Each member in this array specifies the actual length of the corresponding member of the X array (that is, Z[0] specifies the actual length of the indicator name found at X[0]).
You can embed any Advanced Server SQL statement in a C program. Each statement should begin with the keywords EXEC SQL, and must be terminated with a semi-colon (;). Within the C program, a SQL statement takes the form:Where sql_command_body represents a standard SQL statement. You can use a host variable anywhere that the SQL statement expects a value expression. For more information about substituting host variables for value expressions, please see Section 3.1.2, Declaring Host Variables.ECPGPlus extends the PostgreSQL server-side syntax for some statements; for those statements, syntax differences are outlined in the following reference sections. For a complete reference to the supported syntax of other SQL commands, please refer to the PostgreSQL Core Documentation available at:
7.5.1 ALLOCATE DESCRIPTORarray_size is a variable that specifies the number of array elements to allocate for the descriptor. array_size may be an INTEGER value or a host variable.descriptor_name is the host variable that contains the name of the descriptor, or the name of the descriptor. This value may take the form of an identifier, a quoted string literal, or of a host variable.variable_count specifies the maximum number of host variables in the descriptor. The default value of variable_count is 100.The following code fragment allocates a descriptor named emp_query that may be processed as an array (emp_array):
7.5.2 CALLUse the CALL statement to invoke a procedure or function on the server. The CALL statement works only on Advanced Server. The CALL statement comes in two forms; the first form is used to call a function:The second form is used to call a procedure:program_name is the name of the stored procedure or function that the CALL statement invokes. The program name may be schema-qualified or package-qualified (or both); if you do not specify the schema or package in which the program resides, ECPGPlus will use the value of search_path to locate the program.actual_arguments specifies a comma-separated list of arguments required by the program. Note that each actual_argument corresponds to a formal argument expected by the program. Each formal argument may be an IN parameter, an OUT parameter, or an INOUT parameter.:ret_variable specifies a host variable that will receive the value returned if the program is a function.:ret_indicator specifies a host variable that will receive the indicator value returned, if the program is a function.For example, the following statement invokes the get_job_desc function with the value contained in the :ename host variable, and captures the value returned by that function in the :job host variable:
7.5.3 CLOSEUse the CLOSE statement to close a cursor, and free any resources currently in use by the cursor. A client application cannot fetch rows from a closed cursor. The syntax of the CLOSE statement is:cursor_name is the name of the cursor closed by the statement. The cursor name may take the form of an identifier or of a host variable.The OPEN statement initializes a cursor. Once initialized, a cursor result set will remain unchanged unless the cursor is re-opened. You do not need to CLOSE a cursor before re-opening it.To manually close a cursor named emp_cursor, use the command:
7.5.4 COMMITUse the COMMIT statement to complete the current transaction, making all changes permanent and visible to other users. The syntax is:database_name is the name of the database (or host variable that contains the name of the database) in which the work resides. This value may take the form of an unquoted string literal, or of a host variable.For compatibility, ECPGPlus accepts the COMMENT clause without error but does not store any text included with the COMMENT clause.Include the RELEASE clause to close the current connection after performing the commit.For example, the following command commits all work performed on the dept database and closes the current connection:By default, statements are committed only when a client application performs a COMMIT statement. Include the -t option when invoking ECPGPlus to specify that a client application should invoke AUTOCOMMIT functionality. You can also control AUTOCOMMIT functionality in a client application with the following statements:
7.5.5 CONNECTUse the CONNECT statement to establish a connection to a database. The CONNECT statement is available in two forms - one form is compatible with Oracle databases, the other is not.EXEC SQL CONNECT
{{:user_name IDENTIFIED BY :password} | :connection_id}
[AT database_name]
[USING :database_string]
[ALTER AUTHORIZATION :new_password];user_name is a host variable that contains the role that the client application will use to connect to the server.password is a host variable that contains the password associated with that role.connection_id is a host variable that contains a slash-delimited user name and password used to connect to the database.Include the AT clause to specify the database to which the connection is established. database_name is the name of the database to which the client is connecting; specify the value in the form of a variable, or as a string literal.Include the USING clause to specify a host variable that contains a null-terminated string identifying the database to which the connection will be established.The ALTER AUTHORIZATION clause is supported for syntax compatibility only; ECPGPlus parses the ALTER AUTHORIZATION clause, and reports a warning.Using the first form of the CONNECT statement, a client application might establish a connection with a host variable named user that contains the identity of the connecting role, and a host variable named password that contains the associated password using the following command:A client application could also use the first form of the CONNECT statement to establish a connection using a single host variable named :connection_id. In the following example, connection_id contains the slash-delimited role name and associated password for the user:The syntax of the second form of the CONNECT statement is:Where credentials is one of the following:database_name is the name or identity of the database to which the client is connecting. Specify database_name as a variable, or as a string literal, in one of the following forms:hostname is the name or IP address of the server on which the database resides.port is the port on which the server listens.You can also specify a value of DEFAULT to establish a connection with the default database, using the default role name. If you specify DEFAULT as the target database, do not include a connection_name or credentials.connection_name is the name of the connection to the database. connection_name should take the form of an identifier (that is, not a string literal or a variable). You can open multiple connections, by providing a unique connection_name for each connection.If you do not specify a name for a connection, ecpglib assigns a name of DEFAULT to the connection. You can refer to the connection by name (DEFAULT) in any EXEC SQL statement.CURRENT is the most recently opened or the connection mentioned in the most-recent SET CONNECTION TO statement. If you do not refer to a connection by name in an EXEC SQL statement, ECPG assumes the name of the connection to be CURRENT.user_name is the role used to establish the connection with the Advanced Server database. The privileges of the specified role will be applied to all commands performed through the connection.password is the password associated with the specified user_name.The following code fragment uses the second form of the CONNECT statement to establish a connection to a database named edb, using the role alice and the password associated with that role, 1safepwd:The name of the connection is acctg_conn; you can use the connection name when changing the connection name using the SET CONNECTION statement.
7.5.6 DEALLOCATE DESCRIPTORUse the DEALLOCATE DESCRIPTOR statement to free memory in use by an allocated descriptor. The syntax of the statement is:descriptor_name is the name of the descriptor. This value may take the form of a quoted string literal, or of a host variable.
7.5.7 DECLARE CURSORdatabase_name is the name of the database on which the cursor operates. This value may take the form of an identifier or of a host variable. If you do not specify a database name, the default value of database_name is the default database.cursor_name is the name of the cursor.select_statement is the text of the SELECT statement that defines the cursor result set; the SELECT statement cannot contain an INTO clause.statement_name is the name of a SQL statement or block that defines the cursor result set.
7.5.8 DECLARE DATABASEUse the DECLARE DATABASE statement to declare a database identifier for use in subsequent SQL statements (for example, in a CONNECT statement). The syntax is:database_name specifies the name of the database.After invoking the command declaring acctg as a database identifier, the acctg database can be referenced by name when establishing a connection or in AT clauses.
7.5.9 DECLARE STATEMENTUse the DECLARE STATEMENT directive to declare an identifier for an SQL statement. Advanced Server supports two versions of the DECLARE STATEMENT directive:statement_name specifies the identifier associated with the statement.database_name specifies the name of the database. This value may take the form of an identifier or of a host variable that contains the identifier.
7.5.10 DELETEUse the DELETE statement to delete one or more rows from a table. The syntax for the ECPGPlus DELETE statement is the same as the syntax for the SQL statement, but you can use parameter markers and host variables any place that an expression is allowed. The syntax is:[WHERE condition | WHERE CURRENT OF cursor_name]
[{RETURNING|RETURN} * | output_expression [[ AS] output_name] [, ...] INTO host_variable_list ]Include the FOR exec_count clause to specify the number of times the statement will execute; this clause is valid only if the VALUES clause references an array or a pointer to an array.table is the name (optionally schema-qualified) of an existing table. Include the ONLY clause to limit processing to the specified table; if you do not include the ONLY clause, any tables inheriting from the named table are also processed.alias is a substitute name for the target table.using_list is a list of table expressions, allowing columns from other tables to appear in the WHERE condition.Include the WHERE clause to specify which rows should be deleted. If you do not include a WHERE clause in the statement, DELETE will delete all rows from the table, leaving the table definition intact.condition is an expression, host variable or parameter marker that returns a value of type BOOLEAN. Those rows for which condition returns true will be deleted.cursor_name is the name of the cursor to use in the WHERE CURRENT OF clause; the row to be deleted will be the one most recently fetched from this cursor. The cursor must be a non-grouping query on the DELETE statements target table. You cannot specify WHERE CURRENT OF in a DELETE statement that includes a Boolean condition.The RETURN/RETURNING clause specifies an output_expression or host_variable_list that is returned by the DELETE command after each row is deleted:output_expression is an expression to be computed and returned by the DELETE command after each row is deleted. output_name is the name of the returned column; include * to return all columns.host_variable_list is a comma-separated list of host variables and optional indicator variables. Each host variable receives a corresponding value from the RETURNING clause.For example, the following statement deletes all rows from the emp table where the sal column contains a value greater than the value specified in the host variable, :max_sal:For more information about using the DELETE statement, please see the PostgreSQL Core documentation available at:
7.5.11 DESCRIBEUse the DESCRIBE statement to find the number of input values required by a prepared statement or the number of output values returned by a prepared statement. The DESCRIBE statement is used to analyze a SQL statement whose shape is unknown at the time you write your application.The DESCRIBE statement populates an SQLDA descriptor; to populate a SQL descriptor, use the ALLOCATE DESCRIPTOR and DESCRIBE…DESCRIPTOR statements.statement_name is the identifier associated with a prepared SQL statement or PL/SQL block.descriptor is the name of C variable of type SQLDA*. You must allocate the space for the descriptor by calling sqlald() (and initialize the descriptor) before executing the DESCRIBE statement.When you execute the first form of the DESCRIBE statement, ECPG populates the given descriptor with a description of each input variable required by the statement. For example, given two descriptors:When you execute the second form, ECPG populates the given descriptor with a description of each value returned by the statement. For example, the following statement returns three values:Before executing the statement, you must bind a variable for each input value and a variable for each output value. The variables that you bind for the input values specify the actual values used by the statement. The variables that you bind for the output values tell ECPGPlus where to put the values when you execute the statement.
7.5.12 DESCRIBE DESCRIPTORUse the DESCRIBE DESCRIPTOR statement to retrieve information about a SQL statement, and store that information in a SQL descriptor. Before using DESCRIBE DESCRIPTOR, you must allocate the descriptor with the ALLOCATE DESCRIPTOR statement. The syntax is:statement_name is the name of a prepared SQL statement.descriptor_name is the name of the descriptor. descriptor_name can be a quoted string value or a host variable that contains the name of the descriptor.If you include the INPUT clause, ECPGPlus populates the given descriptor with a description of each input variable required by the statement.If you do not specify the INPUT clause, DESCRIBE DESCRIPTOR populates the specified descriptor with the values returned by the statement.If you include the OUTPUT clause, ECPGPlus populates the given descriptor with a description of each value returned by the statement.
7.5.13 DISCONNECTUse the DISCONNECT statement to close the connection to the server. The syntax is:connection_name is the connection name specified in the CONNECT statement used to establish the connection. If you do not specify a connection name, the current connection is closed.Include the CURRENT keyword to specify that ECPGPlus should close the most-recently used connection.Include the DEFAULT keyword to specify that ECPGPlus should close the connection named DEFAULT. If you do not specify a name when opening a connection, ECPGPlus assigns the name, DEFAULT, to the connection.Include the ALL keyword to instruct ECPGPlus to close all active connections.The following example creates a connection (named hr_connection) that connects to the hr database, and then disconnects from the connection:
7.5.14 EXECUTEUse the EXECUTE statement to execute a statement previously prepared using an EXEC SQL PREPARE statement. The syntax is:EXEC SQL [FOR array_size] EXECUTE statement_name
[USING {DESCRIPTOR SQLDA_descriptor
|:host_variable [[INDICATOR] :indicator_variable]}];array_size is an integer value or a host variable that contains an integer value that specifies the number of rows to be processed. If you omit the FOR clause, the statement is executed once for each member of the array.statement_name specifies the name assigned to the statement when the statement was created (using the EXEC SQL PREPARE statement).Include the USING clause to supply values for parameters within the prepared statement:Include the DESCRIPTOR SQLDA_descriptor clause to provide an SQLDA descriptor value for a parameter.Use a host_variable (and an optional indicator_variable) to provide a user-specified value for a parameter.
7.5.15 EXECUTE DESCRIPTORUse the EXECUTE statement to execute a statement previously prepared by an EXEC SQL PREPARE statement, using an SQL descriptor. The syntax is:EXEC SQL [FOR array_size] EXECUTE statement_identifier
[USING [SQL] DESCRIPTOR descriptor_name]
[INTO [SQL] DESCRIPTOR descriptor_name];array_size is an integer value or a host variable that contains an integer value that specifies the number of rows to be processed. If you omit the FOR clause, the statement is executed once for each member of the array.statement_identifier specifies the identifier assigned to the statement with the EXEC SQL PREPARE statement.Include the USING clause to specify values for any input parameters required by the prepared statement.Include the INTO clause to specify a descriptor into which the EXECUTE statement will write the results returned by the prepared statement.descriptor_name specifies the name of a descriptor (as a single-quoted string literal), or a host variable that contains the name of a descriptor.The following example executes the prepared statement, give_raise, using the values contained in the descriptor stmtText:
7.5.16 EXECUTE...END EXECUse the EXECUTE…END-EXEC statement to embed an anonymous block into a client application. The syntax is:database_name is the database identifier or a host variable that contains the database identifier. If you omit the AT clause, the statement will be executed on the current default database.anonymous_block is an inline sequence of PL/pgSQL or SPL statements and declarations. You may include host variables and optional indicator variables within the block; each such variable is treated as an IN/OUT value.
7.5.17 EXECUTE IMMEDIATEdatabase_name is the database identifier or a host variable that contains the database identifier. If you omit the AT clause, the statement will be executed on the current default database.
7.5.18 FETCHUse the FETCH statement to return rows from a cursor into an SQLDA descriptor or a target list of host variables. Before using a FETCH statement to retrieve information from a cursor, you must prepare the cursor using DECLARE and OPEN statements. The statement syntax is:array_size is an integer value or a host variable that contains an integer value specifying the number of rows to fetch. If you omit the FOR clause, the statement is executed once for each member of the array.cursor is the name of the cursor from which rows are being fetched, or a host variable that contains the name of the cursor.If you include a USING clause, the FETCH statement will populate the specified SQLDA descriptor with the values returned by the server.If you include an INTO clause, the FETCH statement will populate the host variables (and optional indicator variables) specified in the target_list.The following code fragment declares a cursor named employees that retrieves the employee number, name and salary from the emp table:
7.5.19 FETCH DESCRIPTORUse the FETCH DESCRIPTOR statement to retrieve rows from a cursor into an SQL descriptor. The syntax is:array_size is an integer value or a host variable that contains an integer value specifying the number of rows to fetch. If you omit the FOR clause, the statement is executed once for each member of the array.cursor is the name of the cursor from which rows are fetched, or a host variable that contains the name of the cursor. The client must DECLARE and OPEN the cursor before calling the FETCH DESCRIPTOR statement.Include the INTO clause to specify an SQL descriptor into which the EXECUTE statement will write the results returned by the prepared statement. descriptor_name specifies the name of a descriptor (as a single-quoted string literal), or a host variable that contains the name of a descriptor. Prior to use, the descriptor must be allocated using an ALLOCATE DESCRIPTOR statement.The following example allocates a descriptor named row_desc that will hold the description and the values of a specific row in the result set. It then declares and opens a cursor for a prepared statement (my_cursor), before looping through the rows in result set, using a FETCH to retrieve the next row from the cursor into the descriptor:
7.5.20 GET DESCRIPTORUse the GET DESCRIPTOR statement to retrieve information from a descriptor. The GET DESCRIPTOR statement comes in two forms. The first form returns the number of values (or columns) in the descriptor.The second form returns information about a specific value (specified by the VALUE column_number clause).EXEC SQL [FOR array_size] GET DESCRIPTOR descriptor_name
VALUE column_number {:host_variable = descriptor_item {,…}};array_size is an integer value or a host variable that contains an integer value that specifies the number of rows to be processed. If you specify an array_size, the host_variable must be an array of that size; for example, if array_size is 10, :host_variable must be a 10-member array of host_variables. If you omit the FOR clause, the statement is executed once for each member of the array.descriptor_name specifies the name of a descriptor (as a single-quoted string literal), or a host variable that contains the name of a descriptor.Include the VALUE clause to specify the information retrieved from the descriptor.column_number identifies the position of the variable within the descriptor.host_variable specifies the name of the host variable that will receive the value of the item.descriptor_item specifies the type of the retrieved descriptor item.ECPGPlus implements the following descriptor_item types:
7.5.21 INSERTUse the INSERT statement to add one or more rows to a table. The syntax for the ECPGPlus INSERT statement is the same as the syntax for the SQL statement, but you can use parameter markers and host variables any place that a value is allowed. The syntax is:Include the FOR exec_count clause to specify the number of times the statement will execute; this clause is valid only if the VALUES clause references an array or a pointer to an array.table specifies the (optionally schema-qualified) name of an existing table.column is the name of a column in the table. The column name may be qualified with a subfield name or array subscript. Specify the DEFAULT VALUES clause to use default values for all columns.expression is the expression, value, host variable or parameter marker that will be assigned to the corresponding column. Specify DEFAULT to fill the corresponding column with its default value.query specifies a SELECT statement that supplies the row(s) to be inserted.output_expression is an expression that will be computed and returned by the INSERT command after each row is inserted. The expression can refer to any column within the table. Specify * to return all columns of the inserted row(s).output_name specifies a name to use for a returned column.The following example adds a row to the employees table:Note that the INSERT statement uses a host variable (:ename) to specify the value of the ename column.For more information about using the INSERT statement, please see the PostgreSQL Core documentation available at:
7.5.22 OPENUse the OPEN statement to open a cursor. The syntax is:Where parameters is one of the following:array_size is an integer value or a host variable that contains an integer value specifying the number of rows to fetch. If you omit the FOR clause, the statement is executed once for each member of the array.cursor is the name of the cursor being opened.parameters is either DESCRIPTOR SQLDA_descriptor or a comma-separated list of host variables (and optional indicator variables) that initialize the cursor. If specifying an SQLDA_descriptor, the descriptor must be initialized with a DESCRIBE statement.The OPEN statement initializes a cursor using the values provided in parameters. Once initialized, the cursor result set will remain unchanged unless the cursor is closed and re-opened. A cursor is automatically closed when an application terminates.The following example declares a cursor named employees, that queries the emp table, returning the employee number, name, salary and commission of an employee whose name matches a user-supplied value (stored in the host variable, :emp_name).After declaring the cursor, the example uses an OPEN statement to make the contents of the cursor available to a client application.
7.5.23 OPEN DESCRIPTOREXEC SQL [FOR array_size] OPEN cursor
[USING [SQL] DESCRIPTOR descriptor_name]
[INTO [SQL] DESCRIPTOR descriptor_name];array_size is an integer value or a host variable that contains an integer value specifying the number of rows to fetch. If you omit the FOR clause, the statement is executed once for each member of the array.cursor is the name of the cursor being opened.descriptor_name specifies the name of an SQL descriptor (in the form of a single-quoted string literal) or a host variable that contains the name of an SQL descriptor that contains the query that initializes the cursor.For example, the following statement opens a cursor (named emp_cursor), using the host variable, :employees:
7.5.24 PREPAREUse the PREPARE statement to prepare an SQL statement or PL/pgSQL block for execution. The statement is available in two forms; the first form is:database_name is the database identifier or a host variable that contains the database identifier against which the statement will execute. If you omit the AT clause, the statement will execute against the current default database.statement_name is the identifier associated with a prepared SQL statement or PL/SQL block.sql_statement may take the form of a SELECT statement, a single-quoted string literal or host variable that contains the text of an SQL statement.To include variables within a prepared statement, substitute placeholders ($1, $2, $3, etc.) for statement values that might change when you PREPARE the statement. When you EXECUTE the statement, provide a value for each parameter. The values must be provided in the order in which they will replace placeholders.The following example creates a prepared statement (named add_emp) that inserts a record into the emp table:Please note: A client application must issue a PREPARE statement within each session in which a statement will be executed; prepared statements persist only for the duration of the current session.
7.5.25 ROLLBACKUse the ROLLBACK statement to abort the current transaction, and discard any updates made by the transaction. The syntax is:database_name is the database identifier or a host variable that contains the database identifier against which the statement will execute. If you omit the AT clause, the statement will execute against the current default database.Include the TO clause to abort any commands that were executed after the specified savepoint; use the SAVEPOINT statement to define the savepoint. If you omit the TO clause, the ROLLBACK statement will abort the transaction, discarding all updates.Include the RELEASE clause to cause the application to execute an EXEC SQL COMMIT RELEASE and close the connection.Invoking this statement will abort the transaction, undoing all changes, erasing any savepoints, and releasing all transaction locks. If you include a savepoint (my_savepoint in the following example):Only the portion of the transaction that occurred after the my_savepoint is rolled back; my_savepoint is retained, but any savepoints created after my_savepoint will be erased.
7.5.26 SAVEPOINTUse the SAVEPOINT statement to define a savepoint; a savepoint is a marker within a transaction. You can use a ROLLBACK statement to abort the current transaction, returning the state of the server to its condition prior to the specified savepoint. The syntax of a SAVEPOINT statement is:database_name is the database identifier or a host variable that contains the database identifier against which the savepoint resides. If you omit the AT clause, the statement will execute against the current default database.savepoint_name is the name of the savepoint. If you re-use a savepoint_name, the original savepoint is discarded.To create a savepoint named my_savepoint, include the statement:
7.5.27 SELECTECPGPlus extends support of the SQL SELECT statement by providing the INTO host_variables clause. The clause allows you to select specified information from an Advanced Server database into a host variable. The syntax for the SELECT statement is:[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]database_name is the name of the database (or host variable that contains the name of the database) in which the table resides. This value may take the form of an unquoted string literal, or of a host variable.host_variables is a list of host variables that will be populated by the SELECT statement. If the SELECT statement returns more than a single row, host_variables must be an array.ECPGPlus provides support for the additional clauses of the SQL SELECT statement as documented in the PostgreSQL Core documentation available at:To use the INTO host_variables clause, include the names of defined host variables when specifying the SELECT statement. For example, the following SELECT statement populates the :emp_name and :emp_sal host variables with a list of employee names and salaries:The enhanced SELECT statement also allows you to include parameter markers (question marks) in any clause where a value would be permitted. For example, the following query contains a parameter marker in the WHERE clause:
7.5.28 SET CONNECTION
7.5.29 SET DESCRIPTORUse the SET DESCRIPTOR statement to assign a value to a descriptor area using information provided by the client application in the form of a host variable or an integer value. The statement comes in two forms; the first form is:EXEC SQL [FOR array_size] SET DESCRIPTOR descriptor_name
VALUE column_number descriptor_item = host_variable;array_size is an integer value or a host variable that contains an integer value specifying the number of rows to fetch. If you omit the FOR clause, the statement is executed once for each member of the array.descriptor_name specifies the name of a descriptor (as a single-quoted string literal), or a host variable that contains the name of a descriptor.Include the VALUE clause to describe the information stored in the descriptor.column_number identifies the position of the variable within the descriptor.descriptor_item specifies the type of the descriptor item.host_variable specifies the name of the host variable that contains the value of the item.ECPGPlus implements the following descriptor_item types:
7.5.30 UPDATEUse an UPDATE statement to modify the data stored in a table. The syntax is:[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [[ AS ] output_name] [, ...] ]database_name is the name of the database (or host variable that contains the name of the database) in which the table resides. This value may take the form of an unquoted string literal, or of a host variable.Include the FOR exec_count clause to specify the number of times the statement will execute; this clause is valid only if the SET or WHERE clause contains an array.ECPGPlus provides support for the additional clauses of the SQL UPDATE statement as documented in the PostgreSQL Core documentation available at:The following UPDATE statement changes the job description of an employee (identified by the :ename host variable) to the value contained in the :new_job host variable, and increases the employees salary, by multiplying the current salary by the value in the :increase host variable:The enhanced UPDATE statement also allows you to include parameter markers (question marks) in any clause where an input value would be permitted. For example, we can write the same update statement with a parameter marker in the WHERE clause:This UPDATE statement could allow you to prompt the user for a new value for the job column and provide the amount by which the sal column is incremented for the employee specified by :ename.
7.5.31 WHENEVERUse the WHENEVER statement to specify the action taken by a client application when it encounters an SQL error or warning. The syntax is: