This chapter provides information on how to use SQL statements with OpenAPI to access a DBMS. It also details the syntax of those SQL statements for which the syntax differs from that of embedded SQL. Lastly, it instructs how to enter SQL statement parameters using IIapi_query(), IIapi_setDescriptor(), and IIapi_putParms().
|
|
|
Most SQL statements are invoked by calling IIapi_query() with a query type of IIAPI_QT_QUERY, iiapi_setDescriptor(), and Iapi_putParms() for query parameters (optional), followed by IIAPI_getQueryInfo() and IIapi_close(). Some SQL statements require additional OpenAPI functions to provide query parameters and retrieve result data. As a common practice, an application should call IIapi_getQueryInfo() after each query is completed to check if the data source reported any errors.
Note: For more information on issuing SQL statement parameters, see Queries, Parameters, and Query Data Correlation. For the syntax of SQL statements, see SQL Syntax and the SQL Reference Guide.
The following table shows the general SQL statement, as well as those SQL statements that are invoked differently:
SQL Statement |
OpenAPI Function |
Description |
Query Type |
|---|---|---|---|
<general SQL> |
IIapi_query() |
Invokes the SQL statement |
IIAPI_QT_QUERY |
|
[IIapi_setDescriptor()] |
Optional. Sends the information about the format of data to be sent with subsequent IIapi_putParms() calls |
|
|
[IIapi_putParms()] |
Optional. Sends data value for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
close |
IIapi_close() |
Closes a cursor along with the statement handle |
|
commit |
IIapi_commit() |
Commits a transaction |
|
connect |
IIapi_connect() |
Connects to a data source |
|
copy from |
IIapi_query() |
Copies data from a file into a table |
IIAPI_QT_QUERY |
|
IIapi_getCopyMap() |
Retrieves copy data descriptors |
|
|
IIapi_putColumns() |
Submits copy data for the copy from statement. This function is called repeatedly until all data is sent. |
|
|
IIapi_getQueryInfo() |
Retrieves query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
copy into |
IIapi_query() |
Copies data from a table into a file |
IIAPI_QT_QUERY |
|
IIapi_getCopyMap() |
Retrieves copy data descriptors |
|
|
IIapi_getColumns() |
Retrieves copy data for the copy into statement. This function is called repeatedly until all data is retrieved. |
|
|
IIapi_getQueryInfo() |
Retrieves query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
delete (cursor) |
IIapi_query() |
Deletes rows from a table using a cursor |
IIAPI_QT_CURSOR_ |
|
IIapi_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() calls |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves the row count |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
delete (repeat) |
IIapi_query() |
Defines a repeated delete |
First repeat: |
|
IIapi_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() calls |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves a repeat query ID |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
|
IIapi_query() |
Executes a repeated delete |
Subsequent repeats: |
|
IIapi_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves row count and query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
describe |
IIapi_query() |
Describes a previously prepared statement |
IIAPI_QT_QUERY |
|
IIapi_getDescriptor() |
Retrieves descriptors if a prepared statement is a select statement |
|
|
IIapi_getQueryInfo() |
Retrieves query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
disconnect |
IIapi_disconnect() |
Disconnects from a data source |
|
execute |
IIapi_query() |
Executes a previously-prepared statement |
IIAPI_QT_EXEC |
|
[IIapi_setDescriptor()] |
Optional. Sends information about the format of data to be sent with subsequent IIapi_putParms() calls |
|
|
[IIapi_putParms()] |
Optional. Sends data values for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
execute procedure |
IIapi_query() |
Invokes a database procedure |
IIAPI_QT_EXEC_ |
|
IIapi_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
[IIapi_getDescriptor] |
Retrieves descriptors of output parameters or columns of row-returning procedures |
|
|
[IIapi_getColumns] |
Retrieves output parameter values or column values of row-returning procedures |
|
|
IIapi_getQueryInfo() |
Retrieves query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
fetch |
IIapi_getColumns() |
Retrieves rows of data |
|
|
[IIapi_getQueryInfo()] |
Retrieves query results |
|
get dbevent |
IIapi_catchEvent() |
Retrieves a database event |
|
|
[IIapi_getEvent()] |
Wait for a database event to be received |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
insert (repeat) |
IIapi_query() |
Defines a repeated insert |
First repeat: |
|
IIapi_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves a repeat query ID |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
|
IIapi_query() |
Executes a repeated insert |
Subsequent repeats: |
|
IIapi_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() calls |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves row count and query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
open |
IIapi_query() |
Opens a cursor |
IIAPI_QT_OPEN |
|
[IIapi_setDescriptor()] |
Sends information about the format of cursor to be sent with subsequent IIapi_putParms() calls |
|
|
[IIapi_putParms()] |
Sends data values for the cursor parameter |
|
|
IIapi_getDescriptor() |
Retrieves descriptors for data |
|
|
[IIapi_getQueryInfo()] |
Retrieves query results |
|
prepare |
IIapi_query() |
Prepares a statement to be executed later |
IIAPI_QT_QUERY |
|
[IIapi_getDescriptor] |
Retrieves descriptor when "into sqlda" is used in prepare query text |
|
|
IIapi_getQueryInfo() |
Retrieves query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
prepare to commit |
IIapi_prepareCommit() |
Polls the server to determine the commit status of a local transaction associated with a specified distributed transaction, and secures the transaction |
|
rollback |
IIapi_rollback() |
Rolls back a transaction to its beginning or to a savepoint |
|
savepoint |
IIapi_savepoint() |
Declares a savepoint marker within a transaction |
|
select |
IIapi_query() |
Retrieves values from one or more tables |
IIAPI_QT_QUERY |
|
IIapi_getDescriptor() |
Retrieves descriptors for data |
|
|
IIapi_getColumns() |
Retrieves rows of data. This function is called repeatedly until all data is retrieved. |
|
|
IIapi_getQueryInfo() |
Retrieves query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
select (repeat) |
IIapi_query() |
Defines a repeated select |
First repeat: |
|
IIapi_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() calls |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves repeat query ID |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
|
IIapi_query() |
Executes a repeated select |
Subsequent repeats: |
|
IIapi_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() calls |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
IIapi_getDescriptor() |
Retrieves descriptors for one row of data |
|
|
IIapi_getColumns() |
Retrieves rows of data. This function is called repeatedly until all data is retrieved. |
|
|
IIapi_getQueryInfo() |
Retrieves query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
set autocommit on/off |
IIapi_autocommit() |
Enables or disables the autocommit state in the server |
|
update (cursor) |
IIapi_query() |
Updates column values in a table using a cursor |
API_QT_ |
|
II_api_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() calls |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves row count, logical key, and query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
update (repeat) |
IIapi_query() |
Defines a repeated update |
First repeat: |
|
IIapi_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() calls |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves a repeated query ID |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|
|
IIapi_query() |
Executes the repeated update |
Subsequent repeats: |
|
IIapi_setDescriptor() |
Sends information about the format of data to be sent with subsequent IIapi_putParms() calls |
|
|
IIapi_putParms() |
Sends data values for SQL statement parameters |
|
|
IIapi_getQueryInfo() |
Retrieves row count, logical key, and query results |
|
|
IIapi_close() |
Frees resources associated with the SQL statement |
|