Language element reference v17

An embedded SQL statement allows your client application to interact with the server. An embedded directive is an instruction to the ECPGPlus compiler.

You can embed any EDB Postgres Advanced Server SQL statement in a C program. Each statement must begin with the keywords EXEC SQL and must be terminated with a semi-colon (;). In 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. For more information about substituting host variables for value expressions, see Declaring host variables.

ECPGPlus extends the PostgreSQL server-side syntax for some statements. Syntax differences are noted in the reference information that follows. For a complete reference to the supported syntax of other SQL commands, see the PostgreSQL core documentation.

ALLOCATE DESCRIPTOR

Use the ALLOCATE DESCRIPTOR statement to allocate an SQL descriptor area:

EXEC SQL [FOR <array_size>] ALLOCATE DESCRIPTOR <descriptor_name>
    [WITH MAX <variable_count>];

Where:

• array_size is a variable that specifies the number of array elements to allocate for the descriptor. array_size can 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 can 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 can be processed as an array (emp_array):

EXEC SQL FOR :emp_array ALLOCATE DESCRIPTOR emp_query;

CALL

Use the CALL statement to invoke a procedure or function on the server. The CALL statement works only on EDB Postgres 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>]];

The second form is used to call a procedure:

EXEC SQL CALL <program_name> '('[<actual_arguments>]')';

Where:

• program_name is the name of the stored procedure or function that the CALL statement invokes. The program name can be schema qualified, package qualified, or both. If you don't specify the schema or package in which the program resides, ECPGPlus uses the value of search_path to locate the program.
• actual_arguments specifies a comma-separated list of arguments required by the program. Each actual_argument corresponds to a formal argument expected by the program. Each formal argument can be an IN parameter, an OUT parameter, or an INOUT parameter.
• :ret_variable specifies a host variable that receives the value returned if the program is a function.
• :ret_indicator specifies a host variable that receives 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:

EXEC SQL CALL get_job_desc(:ename)
  INTO :job;

CLOSE

Use the CLOSE statement to close a cursor and free any resources currently in use by the cursor. A client application can't fetch rows from a closed cursor. The syntax of the CLOSE statement is:

EXEC SQL CLOSE [<cursor_name>];

Where cursor_name is the name of the cursor closed by the statement. The cursor name can take the form of an identifier or of a host variable.

The OPEN statement initializes a cursor. Once initialized, a cursor result set remains unchanged unless the cursor is reopened. You don't need to CLOSE a cursor before reopening it.

To manually close a cursor named emp_cursor, use the command:

EXEC SQL CLOSE emp_cursor;

A cursor is automatically closed when an application terminates.

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];

Where 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 can take the form of an unquoted string literal or of a host variable.

For compatibility, ECPGPlus accepts the COMMENT clause without error but doesn't 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:

EXEC SQL AT dept COMMIT RELEASE;

By default, statements are committed only when a client application performs a COMMIT statement. Include the -t option when invoking ECPGPlus to specify for a client application to invoke AUTOCOMMIT functionality. You can also control AUTOCOMMIT functionality in a client application with the following statements:

EXEC SQL SET AUTOCOMMIT TO ON

and

EXEC SQL SET AUTOCOMMIT TO OFF

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, and the other is not.

The first form is compatible with Oracle databases:

EXEC SQL CONNECT
  {{:<user_name> IDENTIFIED BY :<password>}  | :<connection_id>}
  [AT <database_name>]
  [USING :database_string]
  [ALTER AUTHORIZATION :new_password];

Where:

• user_name is a host variable that contains the role that the client application uses 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 to establish the connection.

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:

EXEC SQL CONNECT :user IDENTIFIED BY :password;

A client application can 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 :connection_id;

The syntax of the second form of the CONNECT statement is:

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

In the second form:

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]

Where:

• 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, don't include a connection_name or credentials.

• connection_name is the name of the connection to the database. connection_name takes 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 don't 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 don't 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 EDB Postgres Advanced Server database. The privileges of the specified role are 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:

EXEC SQL CONNECT TO edb AS acctg_conn
  USER 'alice' IDENTIFIED BY '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.

DEALLOCATE DESCRIPTOR

Use the DEALLOCATE DESCRIPTOR statement to free memory in use by an allocated descriptor. The syntax of the statement is:

EXEC SQL DEALLOCATE DESCRIPTOR <descriptor_name>

Where descriptor_name is the name of the descriptor. This value can take the form of a quoted string literal or of a host variable.

The following example deallocates a descriptor named emp_query:

EXEC SQL DEALLOCATE DESCRIPTOR emp_query;

DECLARE CURSOR

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>);

Where:

• database_name is the name of the database on which the cursor operates. This value can take the form of an identifier or of a host variable. If you don't 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 can't contain an INTO clause.
• statement_name is the name of a SQL statement or block that defines the cursor result set.

The following example declares a cursor named employees:

EXEC SQL DECLARE employees CURSOR FOR
  SELECT
    empno, ename, sal, comm
  FROM
    emp;

The cursor generates a result set that contains the employee number, employee name, salary, and commission for each employee record that's stored in the emp table.

DECLARE DATABASE

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;

Where database_name specifies the name of the database.

The following example shows declaring an identifier for the acctg database:

EXEC SQL DECLARE acctg DATABASE;

After invoking the command declaring acctg as a database identifier, you can reference the acctg database by name when establishing a connection or in AT clauses.

This statement has no effect and is provided for Pro*C compatibility only.

DECLARE STATEMENT

Use the DECLARE STATEMENT directive to declare an identifier for an SQL statement. EDB Postgres Advanced Server supports two versions of the DECLARE STATEMENT directive:

EXEC SQL [<database_name>] DECLARE <statement_name> STATEMENT;

and

EXEC SQL DECLARE STATEMENT <statement_name>;

Where:

• 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 is:

EXEC SQL DECLARE give_raise STATEMENT;      // give_raise is now a statement
handle (not prepared)
EXEC SQL PREPARE give_raise FROM :stmtText; // give_raise is now associated
with a statement
EXEC SQL EXECUTE give_raise;

This statement has no effect and is provided for Pro*C compatibility only.

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 executes. 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 don't 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 the rows to delete. If you don't include a WHERE clause in the statement, DELETE deletes 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 are deleted.
• cursor_name is the name of the cursor to use in the WHERE CURRENT OF clause. The row to be deleted is the one most recently fetched from this cursor. The cursor must be a nongrouping query on the DELETE statements target table. You can't 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's 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:

DELETE FROM emp WHERE sal > :max_sal;

For more information about using the DELETE statement, see the PostgreSQL core documentation.

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>;

Where:

• 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:

SQLDA *query_values_in;
SQLDA *query_values_out;

You might prepare a query that returns information from the emp table:

EXEC SQL PREPARE get_emp FROM
  "SELECT ename, empno, sal FROM emp WHERE empno = ?";

The command requires one input variable for the parameter marker (?).

EXEC SQL DESCRIBE BIND VARIABLES
  FOR get_emp INTO query_values_in;

After describing the bind variables for this statement, you can examine the descriptor to find the number of variables required and the type of each variable.

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:

EXEC SQL DESCRIBE SELECT LIST
  FOR get_emp INTO query_values_out;

After describing the select list for this statement, you can examine the descriptor to find the number of returned values and the name and type of each value.

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.

This is alternative Pro*C-compatible syntax for the DESCRIBE DESCRIPTOR statement.

DESCRIBE DESCRIPTOR

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>;

Where:

• 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.

For example, given two descriptors:

EXEC SQL ALLOCATE DESCRIPTOR query_values_in;
EXEC SQL ALLOCATE DESCRIPTOR query_values_out;

You might prepare a query that returns information from the emp table:

EXEC SQL PREPARE get_emp FROM
  "SELECT ename, empno, sal FROM emp WHERE empno = ?";

The command requires one input variable for the parameter marker (?).

EXEC SQL DESCRIBE INPUT get_emp USING 'query_values_in';

After describing the bind variables for this statement, you can examine the descriptor to find the number of variables required and the type of each variable.

If you don't 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.

For example, the following statement returns three values:

EXEC SQL DESCRIBE OUTPUT FOR get_emp USING 'query_values_out';

After describing the select list for this statement, you can examine the descriptor to find the number of returned values and the name and type of each value.

DISCONNECT

Use the DISCONNECT statement to close the connection to the server. The syntax is:

EXEC SQL DISCONNECT [<connection_name>][CURRENT][DEFAULT][ALL];

Where connection_name is the connection name specified in the CONNECT statement used to establish the connection. If you don't specify a connection name, the current connection is closed.

Include the CURRENT keyword to specify for ECPGPlus to close the connection used most recently.

Include the DEFAULT keyword to specify for ECPGPlus to close the connection named DEFAULT. If you don't specify a name when opening a connection, ECPGPlus assigns the name DEFAULT to the connection.

Include the ALL keyword 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:

/* client.pgc*/
int main()
{
    EXEC SQL CONNECT TO hr AS connection_name;
    EXEC SQL DISCONNECT connection_name;
    return(0);
}

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>]}];

Where:

• array_size is an integer value or a host variable that contains an integer value that specifies the number of rows to process. 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 in 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.

The following example creates a prepared statement that inserts a record into the emp table:

EXEC SQL PREPARE add_emp (numeric, text, text, numeric) AS
    INSERT INTO emp VALUES($1, $2, $3, $4);

Each time you invoke the prepared statement, provide fresh parameter values for the statement:

EXEC SQL EXECUTE add_emp USING 8000, 'DAWSON', 'CLERK', 7788;
EXEC SQL EXECUTE add_emp USING 8001, 'EDWARDS', 'ANALYST', 7698;

EXECUTE DESCRIPTOR

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>];

Where:

• array_size is an integer value or a host variable that contains an integer value that specifies the number of rows to process. 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.
• 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 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 writes the results returned by the prepared statement.

The following example executes the prepared statement, give_raise, using the values contained in the descriptor stmtText:

EXEC SQL PREPARE give_raise FROM :stmtText;
EXEC SQL EXECUTE give_raise USING DESCRIPTOR :stmtText;

EXECUTE...END EXEC

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;

Where:

• database_name is the database identifier or a host variable that contains the database identifier. If you omit the AT clause, the statement executes on the current default database.
• anonymous_block is an inline sequence of PL/pgSQL or SPL statements and declarations. You can include host variables and optional indicator variables in the block. Each such variable is treated as an IN/OUT value.

The following example executes an anonymous block:

EXEC SQL EXECUTE
  BEGIN
    IF (current_user = :admin_user_name) THEN
      DBMS_OUTPUT.PUT_LINE('You are an administrator');
    END IF;
END-EXEC;

EXECUTE IMMEDIATE

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>;

Where:

• database_name is the database identifier or a host variable that contains the database identifier. If you omit the AT clause, the statement executes on the current default database.
• command_text is the command executed by the EXECUTE IMMEDIATE statement.

This dynamic SQL statement is useful when you don't know the text of an SQL statement when writing a client application. For example, a client application might prompt a trusted user for a statement to execute. After the user provides the text of the statement as a string value, the statement is then executed with an EXECUTE IMMEDIATE command.

The statement text can't contain references to host variables. If the statement might contain parameter markers or returns one or more values, use the PREPARE and DESCRIBE statements.

The following example executes the command contained in the :command_text host variable:

EXEC SQL EXECUTE IMMEDIATE :command_text;

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> };

Where:

• 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 populates the specified SQLDA descriptor with the values returned by the server.

If you include an INTO clause, the FETCH statement populates 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:

EXEC SQL DECLARE employees CURSOR FOR
    SELECT empno, ename, esal FROM emp;
EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH emp_cursor INTO :emp_no, :emp_name, :emp_sal;

FETCH DESCRIPTOR

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>;

Where:

• 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.
• 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.

Include the INTO clause to specify a SQL descriptor into which the EXECUTE statement writes the results returned by the prepared statement.

The following example allocates a descriptor named row_desc that holds 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:

EXEC SQL ALLOCATE DESCRIPTOR 'row_desc';
EXEC SQL DECLARE my_cursor CURSOR FOR query;
EXEC SQL OPEN my_cursor;

for( row = 0; ; row++ )
{
  EXEC SQL BEGIN DECLARE SECTION;
    int col;
EXEC SQL END DECLARE SECTION;
EXEC SQL FETCH my_cursor INTO SQL DESCRIPTOR 'row_desc';

GET 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;

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> {,…}};

Where:

• array_size is an integer value or a host variable that contains an integer value that specifies the number of rows to process. 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 in the descriptor.
• host_variable specifies the name of the host variable that receives the value of the item.
• descriptor_item specifies the type of the retrieved descriptor item.

ECPGPlus implements the following descriptor_item types:

• TYPE
• LENGTH
• OCTET_LENGTH
• RETURNED_LENGTH
• RETURNED_OCTET_LENGTH
• PRECISION
• SCALE
• NULLABLE
• INDICATOR
• DATA
• NAME

The following code fragment shows using a GET DESCRIPTOR statement to obtain the number of columns entered in a user-provided string:

EXEC SQL ALLOCATE DESCRIPTOR parse_desc;
EXEC SQL PREPARE query FROM :stmt;
EXEC SQL DESCRIBE query INTO SQL DESCRIPTOR parse_desc;
EXEC SQL GET DESCRIPTOR parse_desc :col_count = COUNT;

The example allocates an SQL descriptor named parse_desc before using a PREPARE statement to check the syntax of 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.

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 executes. 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 can 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's assigned to the corresponding column. Specify DEFAULT to fill the corresponding column with its default value.
• query specifies a SELECT statement that supplies the rows to insert.
• output_expression is an expression that's computed and returned by the INSERT command after each row is inserted. The expression can refer to any column in the table. Specify * to return all columns of the inserted rows.
• output_name specifies a name to use for a returned column.

The following example adds a row to the employees table:

INSERT INTO emp (empno, ename, job, hiredate)
    VALUES ('8400', :ename, 'CLERK', '2011-10-31');

For more information about using the INSERT statement, see the PostgreSQL core documentation.

OPEN

Use the OPEN statement to open a cursor. The syntax is:

EXEC SQL [FOR <array_size>] OPEN <cursor> [USING <parameters>];

parameters is one of the following:

DESCRIPTOR <SQLDA_descriptor>

or

<host_variable> [ [ INDICATOR ] <indicator_variable>, … ]

Where:

• 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 remains unchanged unless the cursor is closed and reopened. A cursor is automatically closed when an application terminates.

The following example declares a cursor named employees that queries the emp table. It returns the employee number, name, salary, and commission of an employee whose name matches a user-supplied value stored in the host variable :emp_name.

EXEC SQL DECLARE employees CURSOR FOR
  SELECT
    empno, ename, sal, comm 
  FROM 
    emp
  WHERE ename = :emp_name;
EXEC SQL OPEN employees;
...

After declaring the cursor, the example uses an OPEN statement to make the contents of the cursor available to a client application.

OPEN DESCRIPTOR

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>];

Where:

• 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:

EXEC SQL OPEN emp_cursor USING DESCRIPTOR :employees;

PREPARE

Prepared statements are useful when a client application must perform a task multiple times. The statement is parsed, written, and planned only once rather than each time the statement is executed. This approach saves repetitive processing time.

Use the PREPARE statement to prepare a 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>;

The second form is:

EXEC SQL [AT <database_name>] PREPARE <statement_name>
  AS <sql_statement>;

Where:

• database_name is the database identifier or a host variable that contains the database identifier against which the statement executes. If you omit the AT clause, the statement executes against the current default database.
• statement_name is the identifier associated with a prepared SQL statement or PL/SQL block.
• sql_statement can take the form of a SELECT statement, a single-quoted string literal, or a host variable that contains the text of an SQL statement.

To include variables in a prepared statement, substitute placeholders ($1, $2, $3, and so on) for statement values that might change when you PREPARE the statement. When you EXECUTE the statement, provide a value for each parameter. Provide the values in the order in which they replace placeholders.

The following example creates a prepared statement named add_emp that inserts a record into the emp table:

EXEC SQL PREPARE add_emp (int, text, text, numeric) AS
    INSERT INTO emp VALUES($1, $2, $3, $4);

Each time you invoke the statement, provide fresh parameter values for the statement:

EXEC SQL EXECUTE add_emp(8003, 'Davis', 'CLERK', 2000.00);
EXEC SQL EXECUTE add_emp(8004, 'Myer', 'CLERK', 2000.00);

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 ]

Where database_name is the database identifier or a host variable that contains the database identifier against which the statement executes. If you omit the AT clause, the statement executes against the current default database.

Include the TO clause to abort any commands that executed after the specified savepoint. Use the SAVEPOINT statement to define the savepoint. If you omit the TO clause, the ROLLBACK statement aborts the transaction, discarding all updates.

Include the RELEASE clause to cause the application to execute an EXEC SQL COMMIT RELEASE and close the connection.

Use the following statement to roll back a complete transaction:

EXEC SQL ROLLBACK;

Invoking this statement aborts the transaction, undoing all changes, erasing any savepoints, and releasing all transaction locks. Suppose you include a savepoint (my_savepoint in the following example):

EXEC SQL ROLLBACK TO SAVEPOINT my_savepoint;

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 are erased.

Rolling back to a specified savepoint releases all locks acquired after the savepoint.

SAVEPOINT

Use the SAVEPOINT statement to define a savepoint. A savepoint is a marker in 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>

Where:

• 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 executes against the current default database.
• savepoint_name is the name of the savepoint. If you reuse a savepoint_name, the original savepoint is discarded.

You can establish savepoints only in a transaction block. A transaction block can contain multiple savepoints.

To create a savepoint named my_savepoint, include the statement:

EXEC SQL SAVEPOINT my_savepoint;

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 EDB Postgres Advanced Server database into a host variable. The syntax for the SELECT statement is:

EXEC SQL [AT <database_name>]
SELECT
  [ <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 ]
  [ { UNION [ ALL ] | INTERSECT | MINUS } (subquery) ]
  [ ORDER BY expression [order_by_options]]
  [ LIMIT { count | ALL }]
  [ OFFSET start [ ROW | ROWS ] ]
  [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
  [ FOR { UPDATE | SHARE } [OF table_name [, ...]][NOWAIT ][...]]

Where:

• 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 can take the form of an unquoted string literal or of a host variable.
• host_variables is a list of host variables 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.

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:

EXEC SQL SELECT ename, sal
  INTO :emp_name, :emp_sal
  FROM emp
  WHERE empno = 7988;

The enhanced SELECT statement also allows you to include parameter markers (question marks) in any clause where a value is allowed. For example, the following query contains a parameter marker in the WHERE clause:

SELECT * FROM emp WHERE dept_no = ?;

This SELECT statement allows you to provide a value at runtime for the dept_no parameter marker.

SET CONNECTION

There are at least three reasons you might need more than one connection in a given client application:

• You might want different privileges for different statements.
• You might need to interact with multiple databases in the same client.
• Multiple threads of execution in a client application can't share a connection concurrently.

The syntax for the SET CONNECTION statement is:

EXEC SQL SET CONNECTION <connection_name>;

Where connection_name is the name of the connection to the database.

To use the SET CONNECTION statement, 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 used only when you execute an EXEC SQL statement that doesn't explicitly specify a connection name. For example, the following statement uses the default connection because it doesn't include an AT connection_name clause:

EXEC SQL DELETE FROM emp;

This statement doesn't use the default connection because it specifies a connection name using the AT connection_name clause:

EXEC SQL AT acctg_conn DELETE FROM emp;

For example, suppose a client application creates and maintains multiple connections using either of the following approaches:

EXEC SQL CONNECT TO edb AS acctg_conn
  USER 'alice' IDENTIFIED BY 'acctpwd';

EXEC SQL CONNECT TO edb AS hr_conn
  USER 'bob' IDENTIFIED BY 'hrpwd';

It can change between the connections with the SET CONNECTION statement:

SET CONNECTION acctg_conn;

or

SET CONNECTION hr_conn;

The server uses the privileges associated with the connection when determining the privileges available to the connecting client. When using the acctg_conn connection, the client has the privileges associated with the role alice. When connected using hr_conn, the client has the privileges associated with bob.

SET DESCRIPTOR

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>;

The second form is:

EXEC SQL [FOR <array_size>] SET DESCRIPTOR <descriptor_name>
  COUNT = integer;

Where:

• 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 executes 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:

• TYPE
• LENGTH
• [REF] INDICATOR
• [REF] DATA
• [REF] RETURNED LENGTH

For example, a client application might prompt a user for a dynamically created query:

query_text = promptUser("Enter a 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.

EXEC SQL ALLOCATE DESCRIPTOR query_params;
EXEC SQL PREPARE emp_query FROM :query_text;

EXEC SQL DESCRIBE INPUT emp_query
  USING SQL DESCRIPTOR 'query_params';

After describing the query, the query_params descriptor contains information about each parameter required by the query.

For this example, assume that the user entered:

SELECT ename FROM emp WHERE sal > ? AND job = ?;,

In this case, the descriptor describes two parameters, one for sal > ? and one for job = ?.

To discover the number of parameter markers (question marks) in the query and therefore the number of values you must provide before executing the query, use:

EXEC SQL GET DESCRIPTOR … :host_variable = COUNT;

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 from the descriptor, along with the number of parameters. Or you can supply each value in the form of a character string and ECPG converts 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.

Next, loop through each parameter, prompting the user for a value, and store those values in host variables. You can use GET DESCRIPTOR … COUNT to find the number of parameters in the query.

EXEC SQL GET DESCRIPTOR 'query_params'
  :param_count = COUNT;

for(param_number = 1;
    param_number <= param_count;
    param_number++)
{

Use GET DESCRIPTOR to copy the name of the parameter into the param_name host variable:

EXEC SQL GET DESCRIPTOR 'query_params'
  VALUE :param_number :param_name = NAME;

reply = promptUser(param_name);
if (reply == NULL)
 reply_ind = 1; /* NULL */
else
 reply_ind = 0; /* NOT NULL */

To associate a value with each parameter, you use the EXEC SQL SET DESCRIPTOR statement. For example:

EXEC SQL SET DESCRIPTOR 'query_params'
  VALUE :param_number DATA = :reply;
EXEC SQL SET DESCRIPTOR 'query_params'
  VALUE :param_number INDICATOR = :reply_ind;
}

Now, you can use the EXEC SQL EXECUTE DESCRIPTOR statement to execute the prepared statement on the server.

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] [, ...] ]

Where 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 can 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 executes. 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.

You can use a host variable in any clause that specifies a value. To use a host variable, substitute a defined variable for any value associated with any of the documented UPDATE clauses.

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. It increases the employees salary by multiplying the current salary by the value in the :increase host variable:

EXEC SQL UPDATE emp
  SET job = :new_job, sal = sal * :increase
  WHERE ename = :ename;

The enhanced UPDATE statement also allows you to include parameter markers (question marks) in any clause where an input value is permitted. For example, we can write the same update statement with a parameter marker in the WHERE clause:

EXEC SQL UPDATE emp
  SET job = ?, sal = sal * ?
  WHERE ename = :ename;

This UPDATE statement allows 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.

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 following table describes the different conditions that might trigger an action.

The following table describes the actions that result from a client encountering a condition.

The following code fragment prints a message if the client application encounters a warning and aborts the application if it encounters an error:

EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR STOP;

Include the following code to specify for a client to continue processing after warning a user of a problem:

EXEC SQL WHENEVER SQLWARNING SQLPRINT;

Include the following code to call a function if a query returns no rows or when a cursor reaches the end of a result set:

EXEC SQL WHENEVER NOT FOUND CALL error_handler(__LINE__);