Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 13.5 ECPGPlus Statements

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

13.5 ECPGPlus Statements

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

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.

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 see the PostgreSQL Core Documentation, available from the EnterpriseDB website at:

13.5.1 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 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):

      EXEC SQL FOR :emp_array ALLOCATE DESCRIPTOR emp_query;

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

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

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

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

Where:

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:

      EXEC SQL CLOSE emp_cursor;

A cursor is automatically closed when an application terminates.

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

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

      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 that a client application should 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

13.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 Oracle-compatible, the other is not.

The first form is Oracle-compatible:

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

      EXEC SQL CONNECT :user IDENTIFIED BY :password;

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

      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.

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

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

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 is stored in the emp table.

13.5.8 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 demonstrates declaring an identifier for the acctg database:

      EXEC SQL DECLARE acctg 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.

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

13.5.9 DECLARE STATEMENT

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;

      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 might be:

      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.

13.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:

      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 ]

Where:

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:

DELETE FROM emp WHERE sal > :max_sal;

For more information about using the DELETE statement, please see the PostgreSQL Core documentation, available from EnterpriseDB at:

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

      or

      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 alternate Pro*C compatible syntax for the DESCRIBE DESCRIPTOR statement.

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

      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.

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

Where:

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:

      /* client.pgc*/

      int main()

      {

      EXEC SQL CONNECT TO hr AS connection_name;

      EXEC SQL DISCONNECT connection_name;

      return(0);

      }

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

Where:

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.

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;

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

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

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

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;

Please Note: the EXECUTEEND EXEC statement is supported only by Postgres Plus Advanced Server.

13.5.17 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 will be executed 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 (ie., when writing a client application). For example, a client application may 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 may not contain references to host variables. If the statement may contain parameter markers or returns one or more values, you must 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;

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

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

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;

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

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:

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

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

    TYPE

    LENGTH

    OCTET_LENGTH

    RETURNED_LENGTH

    RETURNED_OCTET_LENGTH

    PRECISION

    SCALE

    NULLABLE

    INDICATOR

    DATA

    NAME

The following code fragment demonstrates 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 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.

13.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:

      INSERT INTO table [(column [, ...])]

      {DEFAULT VALUES |

      VALUES ({expression | DEFAULT} [, ...])[, ...] | query}

      [RETURNING * | output_expression [[ AS ] output_name] [, ...]]

Where:

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:

      INSERT INTO emp (empno, ename, job, hiredate)

      VALUES ('8400', :ename, 'CLERK', '2011-10-31');

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 from EnterpriseDB at:

13.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, … ]

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

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.

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

13.5.24 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, saving repetitive processing time.

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;

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

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

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.

13.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 ]

Where:

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.

Use the following statement to rollback a complete transaction:

      EXEC SQL ROLLBACK;

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

      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 will be erased.

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

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

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

Savepoints can only be established within a transaction block. A transaction block may contain multiple savepoints.

To create a savepoint named my_savepoint, include the statement:

      EXEC SQL SAVEPOINT my_savepoint;

13.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]

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 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 from EnterpriseDB 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:

      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 would be permitted. 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 run-time for the dept_no parameter marker.

13.5.28 SET CONNECTION

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

    ● You may want different privileges for different statements;

    ● You may need to interact with multiple databases within the same client.

    ● Multiple threads of execution (within a client application) cannot 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, 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 DELETE FROM emp;

This statement will not 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, a client application that creates and maintains multiple connections (such as):

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

and

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

Can change between the connections with the SET CONNECTION statement:

      SET CONNECTION acctg_conn;

or

      SET CONNECTION hr_conn;

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.

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

    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, we'll assume that the user has 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 (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.

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.

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

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 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 from EnterpriseDB at:

A host variable can be used in any clause that specifies a value. To use a host variable, simply 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, and 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 would be 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 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.

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

Condition

Description

NOT FOUND

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.

SQLERROR

The server returns an SQLERROR condition when it encounters a serious error returned by an SQL statement.

SQLWARNING

The server returns an SQLWARNING condition when it encounters a non-fatal warning returned by an SQL statement.

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

Action

Description

CALL function[([args])]

Instructs the client application to call the named function.

CONTINUE

Instructs the client application to proceed to the next statement.

DO BREAK

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

DO CONTINUE

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

Instructs the client application to call the named function.

GOTO label or
GO TO label

Instructs the client application to proceed to the statement that contains the label.

SQLPRINT

Instructs the client application to print a message to standard error.

STOP

Instructs the client application to stop execution.

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 that a client should 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__);

Previous PageTable Of ContentsNext Page