4. Accessing a DBMS Using SQL
 
Share this page                  
Accessing a DBMS Using SQL
Mapping of SQL to OpenAPI
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_
DELETE
 
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_QT_DEF_
REPEAT_QUERY
 
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_QT_EXEC_
REPEAT_QUERY
 
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 non-select 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_
PROCEDURE
 
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_QT_DEF_
REPEAT_QUERY
 
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_QT_EXEC_
REPEAT-QUERY
 
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 for a select statement or a previously-prepared select statement
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 non-select statement to be executed later, or a select statement for opening a cursor
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
or
IIAPI_QT_
SELECT_
SINGLETON
 
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_QT_DEF_
REPEAT_QUERY
 
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_QT_EXEC_
REPEAT_QUERY
 
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_
CURSOR_UPDATE
 
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_QT_DEF_
REPEAT_QUERY
 
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_QT_EXEC_
REPEAT_QUERY
 
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