Determining an Unknown Statement Type at Runtime

Go Up to Working with Dynamic SQL

An application can use isc_dsql_sql_info() to determine the statement type of an unknown prepared statement, for example, a statement entered by the user at runtime.

Requested information can include:

• Statement type.
• Number of input parameters required by the statement.
• Number of output values returned by the statement.
• Detailed information regarding each input parameter or output value, including its data type, scale, and length.

To use isc_dsql_sql_info(), allocate an item-list buffer that describes the type of information requested, and allocate a result buffer, where the function can return the desired information. For example, to determine the statement type of an unknown, but prepared statement, you would allocate a one-element item-list buffer, and fill it with the macro constant, isc_info_sql_stmt_type, defined in ibase.h:

char type_item[];
type_item[] = {isc_info_sql_stmt_type};

Note:
Additional information item macros for requested items can be found in ibase.h under the comment, “SQL information items.”

The result buffer must be large enough to contain any data returned by the call. The proper size for this buffer depends on the information requested. If not enough space is allocated, then isc_dsql_sql_info() puts the predefined value, isc_info_truncated, in the last byte of the result buffer. Generally, when requesting statement type information, 8 bytes is a sufficient buffer size. Declaring a larger than necessary buffer is also safe. A request to identify a statement type returns the following information in the result buffer:

1. One byte containing isc_info_sql_stmt_type.
2. Two bytes containing a number, <n>, telling how many bytes compose the subsequent <value>.
3. One or two bytes specifying the statement type. The following table lists the statement types that can be returned:

   Type	Numeric value
   isc_info_sql_stmt_select	1
   isc_info_sql_stmt_insert	2
   isc_info_sql_stmt_update	3
   isc_info_sql_stmt_delete	4
   isc_info_sql_stmt_ddl	5
   isc_info_sql_stmt_get_segment	6
   isc_info_sql_stmt_put_segment	7
   isc_info_sql_stmt_exec_procedure	8
   isc_info_sql_stmt_start_trans	9
   isc_info_sql_stmt_commit	10
   isc_info_sql_stmt_rollback	11
   isc_info_sql_stmt_select_for_upd	12

4. A final byte containing the value isc_info_end (0). The values placed in the result buffer are not aligned. Furthermore, all numbers are represented in a generic format, with the least significant byte first, and the most significant byte last. Signed numbers have the sign in the last byte. Convert the numbers to a data type native to your system before interpreting them.

Note:
All information about a statement except its type can be more easily determined by calling functions other than isc_dsql_sql_info(). For example, to determine the information to fill in an input XSQLDA, call ­isc_dsql_describe_bind(). To fill in an output XSQLDA, call ­isc_dsql_prepare() or isc_dsql_describe().

Advance To:

• API Guide