Reference¶

The sections that follow describe ecpgPlus language elements:

C-Preprocessor Directives
Supported C Data Types
Type Codes
The SQLDA Structure
ECPGPlus Statements

C-preprocessor Directives¶

The ECPGPlus C-preprocessor enforces two behaviors that are dependent on the mode in which you invoke ECPGPlus:

PROC mode
non-PROC mode

Compiling in PROC Mode

In PROC mode, ECPGPlus allows you to:

Declare host variables outside of an EXEC SQL BEGIN/END DECLARE SECTION.
Use any C variable as a host variable as long as it is of a data type compatible with ECPG.

When you invoke ECPGPlus in PROC mode (by including the -C PROC keywords), the ECPG compiler honors the following C-preprocessor directives:

#include
#if expression
#ifdef symbolName
#ifndef symbolName
#else
#elif expression
#endif
#define symbolName expansion
#define symbolName([macro arguments]) expansion
#undef symbolName
#defined(symbolName)

Pre-processor directives are used to effect or direct the code that is received by the compiler. For example, using the following code sample:

#if HAVE_LONG_LONG == 1
#define BALANCE_TYPE long long
#else
#define BALANCE_TYPE double
#endif
...
BALANCE_TYPE customerBalance;

If you invoke ECPGPlus with the following command-line arguments:

ecpg –C PROC –DHAVE_LONG_LONG=1

ECPGPlus will copy the entire fragment (without change) to the output file, but will only send the following tokens to the ECPG parser:

long long customerBalance;

On the other hand, if you invoke ECPGPlus with the following command-line arguments:

ecpg –C PROC –DHAVE_LONG_LONG=0

The ECPG parser will receive the following tokens:

double customerBalance;

If your code uses preprocessor directives to filter the code that is sent to the compiler, the complete code is retained in the original code, while the ECPG parser sees only the processed token stream.

You can also use compatible syntax when executing the following preprocessor directives with an EXEC directive:

EXEC ORACLE DEFINE
EXEC ORACLE UNDEF
EXEC ORACLE INCLUDE
EXEC ORACLE IFDEF
EXEC ORACLE IFNDEF
EXEC ORACLE ELIF
EXEC ORACLE ELSE
EXEC ORACLE ENDIF
EXEC ORACLE OPTION

For example, if your code includes the following:

EXEC ORACLE IFDEF HAVE_LONG_LONG;
#define BALANCE_TYPE long long
EXEC ORACLE ENDIF;
BALANCE_TYPE customerBalance;

If you invoke ECPGPlus with the following command-line arguments:

ecpg –C PROC DEFINE=HAVE_LONG_LONG=1

ECPGPlus will send the following tokens to the output file, and the ECPG parser:

long long customerBalance;

Note

The EXEC ORACLE pre-processor directives only work if you specify -C PROC on the ECPG command line.

Using the SELECT_ERROR Precompiler Option

When using ECPGPlus in compatible mode, you can use the SELECT_ERROR precompiler option to instruct your program how to handle result sets that contain more rows than the host variable can accommodate. The syntax is:

SELECT_ERROR={YES|NO}

The default value is YES; a SELECT statement will return an error message if the result set exceeds the capacity of the host variable. Specify NO to instruct the program to suppress error messages when a SELECT statement returns more rows than a host variable can accommodate.

Use SELECT_ERROR with the EXEC ORACLE OPTION directive.

Compiling in non-PROC Mode

If you do not include the -C PROC command-line option:

C preprocessor directives are copied to the output file without change.
You must declare the type and name of each C variable that you intend to use as a host variable within an EXEC SQL BEGIN/END DECLARE section.

When invoked in non-PROC mode, ECPG implements the behavior described in the PostgreSQL Core documentation.

Supported C Data Types¶

An ECPGPlus application must deal with two sets of data types: SQL data types (such as SMALLINT, DOUBLE PRECISION and CHARACTER VARYING) and C data types (like short, double and varchar[n]). When an application fetches data from the server, ECPGPlus will map each SQL data type to the type of the C variable into which the data is returned.

In general, ECPGPlus can convert most SQL server types into similar C types, but not all combinations are valid. For example, ECPGPlus will try to convert a SQL character value into a C integer value, but the conversion may fail (at execution time) if the SQL character value contains non-numeric characters. The reverse is also true; when an application sends a value to the server, ECPGPlus will try to convert the C data type into the required SQL type. Again, the conversion may fail (at execution time) if the C value cannot be converted into the required SQL type.

ECPGPlus can convert any SQL type into C character values (char[n] or varchar[n]). Although it is safe to convert any SQL type to/from char[n] or varchar[n], it is often convenient to use more natural C types such as int, double, or float.

The supported C data types are:

short
int
unsigned int
long long int
float
double
char[n+1]
varchar[n+1]
bool
and any equivalent created by a typedef

In addition to the numeric and character types supported by C, the pgtypeslib run-time library offers custom data types (and functions to operate on those types) for dealing with date/time and exact numeric values:

timestamp
interval
date
decimal
numeric

To use a data type supplied by pgtypeslib, you must #include the proper header file.

Type Codes¶

The following table contains the type codes for external data types. An external data type is used to indicate the type of a C host variable. When an application binds a value to a parameter or binds a buffer to a SELECT-list item, the type code in the corresponding SQLDA descriptor (descriptor->T[column]) should be set to one of the following values:

Type Code	Host Variable Type (C Data Type)
1, 2, 8, 11, 12, 15, 23, 24, 91, 94, 95, 96, 97	char[]
3	int
4, 7, 21	float
5, 6	null-terminated string (char[length+1])
9	varchar
22	double
68	unsigned int

The following table contains the type codes for internal data types. An internal type code is used to indicate the type of a value as it resides in the database. The DESCRIBE SELECT LIST statement populates the data type array (descriptor->T[column]) using the following values.

Internal Type Code	Server Type
1	VARCHAR2
2	NUMBER
8	LONG
11	ROWID
12	DATE
23	RAW
24	LONG RAW
96	CHAR
100	BINARY FLOAT
101	BINARY DOUBLE
104	UROWID
187	TIMESTAMP
188	TIMESTAMP W/TIMEZONE
189	INTERVAL YEAR TO MONTH
190	INTERVAL DAY TO SECOND
232	TIMESTAMP LOCAL_TZ

The SQLDA Structure¶

Oracle Dynamic SQL method 4 uses the SQLDA data structure to hold the data and metadata for a dynamic SQL statement. A SQLDA structure can describe a set of input parameters corresponding to the parameter markers found in the text of a dynamic statement or the result set of a dynamic statement. The layout of the SQLDA structure is:

struct SQLDA
{
  int     N; /* Number of entries                     */
  char  **V; /* Variables                             */
  int    *L; /* Variable lengths                      */
  short  *T; /* Variable types                        */
  short **I; /* Indicators                            */
  int     F; /* Count of variables discovered by DESCRIBE */
  char  **S; /* Variable names                        */
  short  *M; /* Variable name maximum lengths         */
  short  *C; /* Variable name actual lengths  */
  char  **X; /* Indicator names                       */
  short  *Y; /* Indicator name maximum lengths */
  short  *Z; /* Indicator name actual lengths         */
};

Parameters

N - maximum number of entries

The N structure member contains the maximum number of entries that the SQLDA may describe. This member is populated by the sqlald() function when you allocate the SQLDA structure. Before using a descriptor in an OPEN or FETCH statement, you must set N to the actual number of values described.

V - data values

The V structure member is a pointer to an array of data values.

For a SELECT-list descriptor, V points to an array of values returned by a FETCH statement (each member in the array corresponds to a column in the result set).

For a bind descriptor, V points to an array of parameter values (you must populate the values in this array before opening a cursor that uses the descriptor).

Your application must allocate the space required to hold each value. Refer to displayResultSet function for an example of how to allocate space for SELECT-list values.

L - length of each data value

The L structure member is a pointer to an array of lengths. Each member of this array must indicate the amount of memory available in the corresponding member of the V array. For example, if V[5] points to a buffer large enough to hold a 20-byte NULL-terminated string, L[5] should contain the value 21 (20 bytes for the characters in the string plus 1 byte for the NULL-terminator). Your application must set each member of the L array.

T - data types

The T structure member points to an array of data types, one for each column (or parameter) described by the descriptor.

For a bind descriptor, you must set each member of the T array to tell ECPGPlus the data type of each parameter.

For a SELECT-list descriptor, the DESCRIBE SELECT LIST statement sets each member of the T array to reflect the type of data found in the corresponding column.

You may change any member of the T array before executing a FETCH statement to force ECPGPlus to convert the corresponding value to a specific data type. For example, if the DESCRIBE SELECT LIST statement indicates that a given column is of type DATE, you may change the corresponding T member to request that the next FETCH statement return that value in the form of a NULL-terminated string. Each member of the T array is a numeric type code (see Type Codes for a list of type codes). The type codes returned by a DESCRIBE SELECT LIST statement differ from those expected by a FETCH statement. After executing a DESCRIBE SELECT LIST statement, each member of T encodes a data type and a flag indicating whether the corresponding column is nullable. You can use the sqlnul() function to extract the type code and nullable flag from a member of the T array. The signature of the sqlnul() function is as follows:
void sqlnul(unsigned short *valType,
            unsigned short *typeCode,
            int            *isNull)
For example, to find the type code and nullable flag for the third column of a descriptor named results, you would invoke sqlnul() as follows:
sqlnul(&results->T[2], &typeCode, &isNull);

I - indicator variables

The I structure member points to an array of indicator variables. This array is allocated for you when your application calls the sqlald() function to allocate the descriptor.

For a SELECT-list descriptor, each member of the I array indicates whether the corresponding column contains a NULL (non-zero) or non-NULL (zero) value.

For a bind parameter, your application must set each member of the I array to indicate whether the corresponding parameter value is NULL.

F - number of entries

The F structure member indicates how many values are described by the descriptor (the N structure member indicates the maximum number of values which may be described by the descriptor; F indicates the actual number of values). The value of the F member is set by ECPGPlus when you execute a DESCRIBE statement. F may be positive, negative, or zero.

For a SELECT-list descriptor, F will contain a positive value if the number of columns in the result set is equal to or less than the maximum number of values permitted by the descriptor (as determined by the N structure member); 0 if the statement is not a SELECT statement, or a negative value if the query returns more columns than allowed by the N structure member.

For a bind descriptor, F will contain a positive number if the number of parameters found in the statement is less than or equal to the maximum number of values permitted by the descriptor (as determined by the N structure member); 0 if the statement contains no parameters markers, or a negative value if the statement contains more parameter markers than allowed by the N structure member.

If F contains a positive number (after executing a DESCRIBE statement), that number reflects the count of columns in the result set (for a SELECT-list descriptor) or the number of parameter markers found in the statement (for a bind descriptor). If F contains a negative value, you may compute the absolute value of F to discover how many values (or parameter markers) are required. For example, if F contains -24 after describing a SELECT list, you know that the query returns 24 columns.

S - column/parameter names

The S structure member points to an array of NULL-terminated strings.

For a SELECT-list descriptor, the DESCRIBE SELECT LIST statement sets each member of this array to the name of the corresponding column in the result set.

For a bind descriptor, the DESCRIBE BIND VARIABLES statement sets each member of this array to the name of the corresponding bind variable.

In this release, the name of each bind variable is determined by the left-to-right order of the parameter marker within the query - for example, the name of the first parameter is always ?0, the name of the second parameter is always ?1, and so on.

M - maximum column/parameter name length

The M structure member points to an array of lengths. Each member in this array specifies the maximum length of the corresponding member of the S array (that is, M[0] specifies the maximum length of the column/parameter name found at S[0]). This array is populated by the sqlald() function.

C - actual column/parameter name length

The C structure member points to an array of lengths. Each member in this array specifies the actual length of the corresponding member of the S array (that is, C[0] specifies the actual length of the column/parameter name found at S[0]).

This array is populated by the DESCRIBE statement.

X - indicator variable names

The X structure member points to an array of NULL-terminated strings - each string represents the name of a NULL indicator for the corresponding value.

This array is not used by ECPGPlus, but is provided for compatibility with Pro*C applications.

Y - maximum indicator name length

The Y structure member points to an array of lengths. Each member in this array specifies the maximum length of the corresponding member of the X array (that is, Y[0] specifies the maximum length of the indicator name found at X[0]).

This array is not used by ECPGPlus, but is provided for compatibility with Pro*C applications.

Z - actual indicator name length

The Z structure member points to an array of lengths. Each member in this array specifies the actual length of the corresponding member of the X array (that is, Z[0] specifies the actual length of the indicator name found at X[0]).

This array is not used by ECPGPlus, but is provided for compatibility with Pro*C applications.

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. For more information about substituting host variables for value expressions, refer to Declaring Host Variables.

ECPGPlus extends the PostgreSQL server-side syntax for some statements; for those statements, syntax differences are outlined in the following reference sections. For a complete reference to the supported syntax of other SQL commands, refer to the PostgreSQL Core Documentation available at:

https://www.postgresql.org/docs/current/static/sql-commands.html

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;

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;

CLOSE¶

Use the CLOSE statement to close a cursor, and free any resources scurrently 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.

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

CONNECT¶

Use the CONNECT statement to establish a connection to a database. The CONNECT statement is available in two forms - one form is compatible with Oracle databases, the other is not.

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

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;

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.

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.

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.

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

Where:

Include the FOR exec_count clause to specify the number of times the statement will execute; this clause is valid only if the VALUES clause references an array or a pointer to an array.

table is the name (optionally schema-qualified) of an existing table. Include the ONLY clause to limit processing to the specified table; if you do not include the ONLY clause, any tables inheriting from the named table are also processed.

alias is a substitute name for the target table.

using_list is a list of table expressions, allowing columns from other tables to appear in the WHERE condition.

Include the WHERE clause to specify which rows should be deleted. If you do not include a WHERE clause in the statement, DELETE will delete all rows from the table, leaving the table definition intact.

condition is an expression, host variable or parameter marker that returns a value of type BOOLEAN. Those rows for which condition returns true will be deleted.

cursor_name is the name of the cursor to use in the WHERE CURRENT OF clause; the row to be deleted will be the one most recently fetched from this cursor. The cursor must be a non-grouping query on the DELETE statements target table. You cannot specify WHERE CURRENT OF in a DELETE statement that includes a Boolean condition.

The RETURN/RETURNING clause specifies an output_expression or host_variable_list that is returned by the DELETE command after each row is deleted:

output_expression is an expression to be computed and returned by the DELETE command after each row is deleted. output_name is the name of the returned column; include * to return all columns.

host_variable_list is a comma-separated list of host variables and optional indicator variables. Each host variable receives a corresponding value from the RETURNING clause.

For example, the following statement deletes all rows from the emp table where the sal column contains a value greater than the value specified in the host variable, :max_sal:

DELETE FROM emp WHERE sal > :max_sal;

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

https://www.postgresql.org/docs/current/static/sql-delete.html

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.

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.

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

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;

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;

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;

Note

The EXECUTE…END EXEC statement is supported only by Advanced Server.

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;

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

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

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.

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

Where:

Include the FOR exec_count clause to specify the number of times the statement will execute; this clause is valid only if the VALUES clause references an array or a pointer to an array.

table specifies the (optionally schema-qualified) name of an existing table.

column is the name of a column in the table. The column name may be qualified with a subfield name or array subscript. Specify the DEFAULT VALUES clause to use default values for all columns.

expression is the expression, value, host variable or parameter marker that will be assigned to the corresponding column. Specify DEFAULT to fill the corresponding column with its default value.

query specifies a SELECT statement that supplies the row(s) to be inserted.

output_expression is an expression that will be computed and returned by the INSERT command after each row is inserted. The expression can refer to any column within the table. Specify * to return all columns of the inserted row(s).

output_name specifies a name to use for a returned column.

The following example adds a row to the employees table:

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

Note

The INSERT statement uses a host variable (:ename) to specify the value of the ename column.

For more information about using the INSERT statement, see the PostgreSQL Core documentation available at:

https://www.postgresql.org/docs/current/static/sql-insert.html

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.

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

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.

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.

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;

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

https://www.postgresql.org/docs/current/static/sql-select.html

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.

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.

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.

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

https://www.postgresql.org/docs/current/static/sql-update.html

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.

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

Reference¶

C-preprocessor Directives¶

Supported C Data Types¶

Type Codes¶

The SQLDA Structure¶

ECPGPlus Statements¶

ALLOCATE DESCRIPTOR¶

CALL¶

CLOSE¶

COMMIT¶

CONNECT¶

DEALLOCATE DESCRIPTOR¶

DECLARE CURSOR¶

DECLARE DATABASE¶

DECLARE STATEMENT¶

DELETE¶

DESCRIBE¶

DESCRIBE DESCRIPTOR¶

DISCONNECT¶

EXECUTE¶

EXECUTE DESCRIPTOR¶

EXECUTE…END EXEC¶

EXECUTE IMMEDIATE¶

FETCH¶

FETCH DESCRIPTOR¶

GET DESCRIPTOR¶

INSERT¶

OPEN¶

OPEN DESCRIPTOR¶

PREPARE¶

ROLLBACK¶

SAVEPOINT¶

SELECT¶

SET CONNECTION¶

SET DESCRIPTOR¶

UPDATE¶

WHENEVER¶

Previous topic

Next topic

Table of Contents