14.5 ECPGPlus Statements

Table of Contents Previous Next


14 ECPGPlus : 14.5 ECPGPlus Statements

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:
EXEC SQL sql_command_body;
Where sql_command_body represents a standard SQL statement. You can use a host variable anywhere that the SQL statement expects a value expression.
Use the ALLOCATE DESCRIPTOR statement to allocate an SQL descriptor area:
EXEC SQL [FOR array_size] ALLOCATE DESCRIPTOR descriptor_name
[WITH MAX
variable_count];
array_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):
14.5.2 CALL
Use 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:
EXEC SQL CALL program_name '('[actual_arguments]')'
INTO [[:
ret_variable][:ret_indicator]];
EXEC SQL CALL program_name '('[actual_arguments]')';
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:
14.5.3 CLOSE
Use 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:
EXEC SQL CLOSE [cursor_name];
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:
14.5.4 COMMIT
Use the COMMIT statement to complete the current transaction, making all changes permanent and visible to other users. The syntax is:
EXEC SQL [AT database_name] COMMIT [WORK]
[COMMENT
'text'] [COMMENT 'text' RELEASE];
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:
14.5.5 CONNECT
Use 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:
EXEC SQL CONNECT TO database_name
[AS
connection_name] [credentials];
Where credentials is one of the following:
USER user_name password
USER
user_name IDENTIFIED BY password
USER
user_name USING password
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:
database_name[@hostname][:port]
tcp:postgresql://hostname[:port][/database_name][options]
unix:postgresql://hostname[:port][/database_name][options]
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.
Use 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.
Use the DECLARE CURSOR statement to define a cursor. The syntax of the statement is:
EXEC SQL [AT database_name] DECLARE cursor_name CURSOR FOR (select_statement | statement_name);
database_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.
Use the DECLARE DATABASE statement to declare a database identifier for use in subsequent SQL statements (for example, in a CONNECT statement). The syntax is:
EXEC SQL DECLARE database_name DATABASE;
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.
Use the DECLARE STATEMENT directive to declare an identifier for an SQL statement. Advanced Server supports two versions of the DECLARE STATEMENT directive:
EXEC SQL [database_name] DECLARE statement_name STATEMENT;
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.
A typical usage sequence that includes the DECLARE STATEMENT directive might be:
14.5.10 DELETE
Use 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:
[FOR exec_count] DELETE FROM [ONLY] table [[AS] alias]
[USING using_list]
[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:
14.5.11 DESCRIBE
Use 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.
EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name INTO descriptor;
EXEC SQL DESCRIBE SELECT LIST FOR statement_name INTO descriptor;
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.
Use 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:
EXEC SQL DESCRIBE [INPUT | OUTPUT] statement_identifier
USING
[SQL] DESCRIPTOR descriptor_name;
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.
EXEC SQL DESCRIBE OUTPUT FOR get_emp USING 'query_values_out';
14.5.13 DISCONNECT
Use the DISCONNECT statement to close the connection to the server. The syntax is:
EXEC SQL DISCONNECT [connection_name][CURRENT][DEFAULT][ALL];
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:
14.5.14 EXECUTE
Use 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.
Use 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:
Use the EXECUTE…END-EXEC statement to embed an anonymous block into a client application. The syntax is:
EXEC SQL [AT database_name] EXECUTE anonymous_block END-EXEC;
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.
Please Note: the EXECUTEEND EXEC statement is supported only by Advanced Server.
Use the EXECUTE IMMEDIATE statement to execute a string that contains a SQL command. The syntax is:
EXEC SQL [AT database_name] EXECUTE IMMEDIATE command_text;
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.
command_text is the command executed by the EXECUTE IMMEDIATE statement.
The following example executes the command contained in the :command_text host variable:
14.5.18 FETCH
Use 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:
EXEC SQL [FOR array_size] FETCH cursor
{ USING
DESCRIPTOR SQLDA_descriptor }|{ INTO target_list };
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:
Use the FETCH DESCRIPTOR statement to retrieve rows from a cursor into an SQL descriptor. The syntax is:
EXEC SQL [FOR array_size] FETCH cursor
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 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:
Use 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.
EXEC SQL GET DESCRIPTOR descriptor_name
:
host_variable = COUNT;
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.
The following code fragment demonstrates using a GET DESCRIPTOR statement to obtain the number of columns entered in a user-provided string:
The example allocates an SQL descriptor (named parse_desc), before using a PREPARE statement to syntax check the string provided by the user (:stmt). A DESCRIBE statement moves the user-provided string into the descriptor, parse_desc. The call to EXEC SQL GET DESCRIPTOR interrogates the descriptor to discover the number of columns (:col_count) in the result set.
14.5.21 INSERT
Use 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:
[FOR exec_count] INSERT INTO table [(column [, ...])]
{DEFAULT VALUES |
VALUES ({expression | DEFAULT} [, ...])[, ...] | query}
[RETURNING * | output_expression [[ AS ] output_name] [, ...]]
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.
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:
14.5.22 OPEN
Use the OPEN statement to open a cursor. The syntax is:
EXEC SQL [FOR array_size] OPEN cursor [USING parameters];
Where parameters is one of the following:
DESCRIPTOR SQLDA_descriptor
or
host_variable [ [ INDICATOR ] indicator_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.
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.
Use the OPEN DESCRIPTOR statement to open a cursor with a SQL descriptor. The syntax is:
EXEC 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:
14.5.24 PREPARE
Use 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:
EXEC SQL [AT database_name] PREPARE statement_name
FROM
sql_statement;
EXEC SQL [AT database_name] PREPARE statement_name
AS
sql_statement;
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.
14.5.25 ROLLBACK
Use the ROLLBACK statement to abort the current transaction, and discard any updates made by the transaction. The syntax is:
EXEC SQL [AT database_name] ROLLBACK [WORK]
[ { TO [SAVEPOINT]
savepoint } | RELEASE ]
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.
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.
14.5.26 SAVEPOINT
Use 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:
EXEC SQL [AT database_name] SAVEPOINT savepoint_name
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:
14.5.27 SELECT
ECPGPlus 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:
EXEC SQL [AT database_name]
[ hint ]
[ ALL | DISTINCT [ ON(expression, ...) ]]
select_list INTO host_variables
[ FROM from_item [, from_item ]...]
[ WHERE condition ]
[ hierarchical_query_clause ]
[ GROUP BY expression [, ...]]
[ HAVING condition ]
[ ORDER BY expression [order_by_options]]
[ LIMIT { count | ALL }]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | SHARE } [OF table_name [, ...]][NOWAIT ][...]]
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:
This SELECT statement allows you to provide a value at run-time for the dept_no parameter marker.
The syntax for the SET CONNECTION statement is:
EXEC SQL SET CONNECTION connection_name;
connection_name is the name of the connection to the database.
To use the SET CONNECTION statement, you should open the connection to the database using the second form of the CONNECT statement; include the AS clause to specify a connection_name.
By default, the current thread uses the current connection; use the SET CONNECTION statement to specify a default connection for the current thread to use. The default connection is only used when you execute an EXEC SQL statement that does not explicitly specify a connection name. For example, the following statement will use the default connection because it does not include an AT connection_name clause. :
EXEC SQL AT acctg_conn DELETE FROM emp;
The server will use the privileges associated with the connection when determining the privileges available to the connecting client. When using the acctg_conn connection, the client will have the privileges associated with the role, alice; when connected using hr_conn, the client will have the privileges associated with bob.
Use 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;
EXEC SQL [FOR array_size] SET DESCRIPTOR descriptor_name
COUNT = integer;
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.
For example, a client application might prompt a user for a dynamically created query:
To execute a dynamically created query, you must first prepare the query (parsing and validating the syntax of the query), and then describe the input parameters found in the query using the EXEC SQL DESCRIBE INPUT statement.
After describing the query, the query_params descriptor contains information about each parameter required by the query.
one for sal > ?
one for job = ?
Then, you can use EXEC SQL GET DESCRIPTOR to retrieve the name of each parameter. You can also use EXEC SQL GET DESCRIPTOR to retrieve the type of each parameter (along with the number of parameters) from the descriptor, or you can supply each value in the form of a character string and ECPG will convert that string into the required data type.
The data type of the first parameter is numeric; the type of the second parameter is varchar. The name of the first parameter is sal; the name of the second parameter is job.
Use GET DESCRIPTOR to copy the name of the parameter into the param_name host variable:
To associate a value with each parameter, you use the EXEC SQL SET DESCRIPTOR statement. For example:
Now, you can use the EXEC SQL EXECUTE DESCRIPTOR statement to execute the prepared statement on the server.
14.5.30 UPDATE
Use an UPDATE statement to modify the data stored in a table. The syntax is:
EXEC SQL [AT database_name][FOR exec_count]
UPDATE [ ONLY ]
table [ [ AS ] alias ]
SET {column = { expression | DEFAULT } |
(column [, ...]) = ({ expression|DEFAULT } [, ...])} [, ...]
[ FROM from_list ]
[ 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.
14.5.31 WHENEVER
Use the WHENEVER statement to specify the action taken by a client application when it encounters an SQL error or warning. The syntax is:
EXEC SQL WHENEVER condition action;
The server returns a NOT FOUND condition when it encounters a SELECT that returns no rows, or when a FETCH reaches the end of a result set.
The server returns an SQLERROR condition when it encounters a serious error returned by an SQL statement.
The server returns an SQLWARNING condition when it encounters a non-fatal warning returned by an SQL statement.
CALL function([args])
Instructs the client application to a C break statement. A break statement may appear in a loop or a switch statement. If executed, the break statement terminate the loop or the switch statement..
Instructs the client application to emit a C continue statement. A continue statement may only exist within a loop, and if executed, will cause the flow of control to return to the top of the loop.
DO function([args])
GOTO label or
GO TO label

14 ECPGPlus : 14.5 ECPGPlus Statements

Table of Contents Previous Next