|
|
|
This section introduces the concepts that you should be familiar with before using OpenAPI. It also discusses how OpenAPI functions are used to establish connections and perform server operations.
|
|
|
The parameter block is a C structure that is used for passing information back and forth between an application and OpenAPI. All OpenAPI functions require a parameter block.
The application creates the parameter block and passes it as an argument to the OpenAPI function.
Each parameter block contains input and output parameters:
Contain information sent by the application and needed by OpenAPI to carry out the request.
Are returned from OpenAPI to the application and contain the results needed by the application for status reporting or for making subsequent function calls. Output parameters can be immediate output or delayed output:
Contain meaningful values as soon as the OpenAPI function returns.
Contain no meaningful values until all tasks associated with the OpenAPI function are completed.
Most parameter blocks have a common substructure containing generic parameters. The generic parameters are used to handle asynchronous processing and to communicate the return status of the function.
Example—parameter block
The following parameter block is allocated before the OpenAPI function IIapi_query() is invoked:
typedef struct _IIAPI_QUERYPARM
{
Generic Parameters (containing input and output parameters):
IIAPI_GENPARM qy_genParm;
Input Parameters:
II_PTR qy_connHandle;
IIAPI_QUERYTYPE qy_queryType;
II_CHAR *qy_queryText;
II_BOOL qy_parameters;
Input and Immediate Output Parameters:
II_PTR qy_tranHandle;
Immediate Output Parameters:
II_PTR qy_stmtHandle;
} IIAPI_QUERYPARM;
The resources associated with the parameter block must not be freed until the OpenAPI function completes.
|
|
|
When all tasks associated with an OpenAPI function are completed, OpenAPI notifies the application by means of a callback function. When the callback function is invoked, OpenAPI sends return status and other information to the application in the delayed output parameters. This "other" information is needed by the application to make subsequent function calls.
Closure is the means for an application to pass any information it wishes to the callback function.
The application creates the closure parameter and passes it as an input parameter to the parameter block. OpenAPI does not care about the contents of the closure parameter; it simply passes it to the callback function when the function completes.
An asynchronous OpenAPI function always requests a callback function to notify the application when the OpenAPI function tasks are completed. If the application does not provide a callback function, OpenAPI assumes that the application is polling for the function completion.
|
|
|
A client/server operation normally involves a request and a response. The client issues the request to a server to start a database operation; in response, the server reports success or failure to the client and returns any relevant data.
When an application submits a request to a remote database, several seconds may elapse before the application receives a response. Under traditional synchronous processing, the application cannot perform any functions while it is waiting for the response. However, under asynchronous processing, the application can issue a request and then perform functions that do not require knowledge about the response. For example, it can update a window in OpenROAD or update a log file. This enables the application to maximize efficiency during the period between a request and response.
When the application completes its non-database operations, it returns control to OpenAPI, using the IIapi_wait() function to complete the database request.
|
|
|
OpenAPI provides a function for synchronous processing when it is needed. The IIapi_wait() function takes control away from the application and gives it to OpenAPI until an outstanding database operation completes or until a user-defined timeout expires. This is useful when the application does not have any functions to perform while the response is being generated; it simply calls IIapi_wait() and waits for the response information before proceeding to the next task.
|
|
|
The handles to these storage areas for OpenAPI object information are returned to the application. The application then uses them in subsequent calls to OpenAPI functions to identify the objects.
An API function may allocate a handle even if the requested action fails (for example, a connect request may fail with an invalid password and still return a connection handle). In these cases, the associated OpenAPI function that releases the handle must still be called to release the resources associated with the handle and the failed request.
|
|
|
There are several main types of handles:
Identifies storage for information about user configuration settings. An application requests an environment handle with the IIapi_initialize() function by setting the in_version parameter to IIAPI_VERSION_2 (or higher). The application can then make various user configuration settings using the IIapi_setEnvParam() function.
The environment handle configuration settings are used when formatting data using the IIapi_formatData() function. The settings are also inherited by connections opened in the context of the environment by providing the environment handle as an input parameter to the IIapi_connect() or IIapi_setConnectParam() functions. Environment handle resources are released with the IIapi_releaseEnv() function.
Identifies storage for information about a specific connection to a database. An application requests a connection handle with the IIapi_connect() or the IIapi_setConnectParam() functions. The application then specifies the handle whenever it issues requests within the context of the connection. When the application no longer needs the connection, it releases the handle with the IIapi_disconnect() function.
Identifies storage for information about a specific transaction. An application requests a transaction handle with the IIapi_query() or the IIapi_autoCommit() function. The application then specifies the handle whenever it issues requests within the context of the transaction. When the application needs to end the transaction, it releases the handle with the IIapi_commit(), IIapi_rollback(), or IIapi_autoCommit() functions.
Identifies storage for information about a specific query statement. An application requests a statement handle with the IIapi_query() function. The application then specifies the handle whenever it issues requests within the context of the statement. When the application no longer needs the statement handle, it releases it with the IIapi_close() function.
Identifies storage for information about a specific database event retrieval. An application requests an event handle with the IIapi_catchEvent() function. The application then specifies this handle whenever it issues requests within the context of the database event registration. When the application no longer needs the event handle, it releases it with the IIapi_close() function.
|
|
|
Before an application can request data from a database, it must establish a dialog, or connection, with a data source—either a relational DBMS Server or the Name Server. It does this by using the IIapi_connect() function. The connection handle is the identifier of this connection.
If the application needs to establish connection characteristics, it does so by using IIapi_setConnectParam() prior to IIapi_connect().
All activity between the application and the server must be within the context of a connection. Normally, the application controls the duration of the connection. When an application no longer needs to communicate with a server, it severs the connection by using the IIapi_disconnect() function. In some error conditions, however, the server severs the connection (IIapi_disconnect() must still be called to release the OpenAPI resources associated with the connection). II api_abort() can also be used to release the resources associated with a connection, but is only intended for use in recovering from error conditions.
|
|
|
A transaction is one or more query statements that make up a logical unit of work. This unit of work is either executed in its entirety, or it is totally or partially rolled back.
With OpenAPI, three types of transactions can occur. They are:
This type of transaction only affects a single database through a single connection. A transaction is started by IIapi_query() when the qy_tranHandle parameter is NULL. The transaction is committed using IIapi_commit() or rolled back using IIapi_rollback().
This type of transaction uses the two-phase commit mechanism to ensure that committal of a distributed transaction occurs in all participating databases through multiple connections.
Distributed transactions are identified by a transaction ID handle returned by IIapi_registerXID(). This handle is used to start transactions on each participating connection by being passed as the qy_tranHandle value when calling IIapi_query(). The transaction ends by calling IIapi_prepareCommit() for each connection followed by IIapi_commit() or IIapi_rollback(). The transaction ID handle is freed by calling IIapi_releaseXID().
This type of transaction causes each individual query to be automatically committed when complete. If a cursor is opened, the commit occurs when the cursor is closed.
Autocommit transactions are started by IIapi_autoCommit() using the connection handle as input. An autocommit transaction ends by calling IIapi_autoCommit() with the autocommit transaction handle as input.
|
|
|
The following sections detail how an application begins and ends a transaction, how distributed transactions are used, and how savepoints are used.
An application specifies the beginning of a new transaction by calling IIapi_query() with a input parameter qy_tranHandle. If the parameter is a NULL pointer or is a transaction ID handle created by IIapi_registerXID(), a new transaction is begun and a transaction handle is allocated and returned in qy_tranHandle. If the qy_tranHandle input value is a transaction handle returned by a previous call to IIapi_query(), the query is performed as part of the already-opened transaction. If the qy_tranHandle input value is an autocommit transaction handle returned by IIapi_autoCommit(), the query is executed and the results are immediately committed by the server.
At the end of a transaction, the application calls IIapi_commit() or IIapi_rollback() before starting another transaction within the connection. IIapi_commit() ends the transaction by committing all SQL statements upon completion, thereby guaranteeing that changes to the database are permanent. IIapi_rollback() ends the transaction by aborting all query statements being executed within the transaction unless a savepoint handle is specified.
If the transaction is distributed, IIapi_prepareCommit() must be called for each connection participating in the transaction prior to calling IIapi_commit() or IIapi_rollback(). For distributed transactions, the resources allocated by IIapi_registerXID() are freed by calling IIapi_releaseXID() once the transaction has been fully committed or rolled back.
In a multi-statement transaction, savepoints can be defined using the IIapi_savePoint() function. IIapi_savePoint() allocates a savepoint handle to identify each savepoint, which can be used to perform a partial rollback when calling IIapi_rollback(). If a savepoint is specified with IIapi_rollback(), only the query statements executed following the savepoint are aborted and the transaction remains active. When a transaction is committed or fully rolled back, all associated savepoint handles are automatically released.
The underlying GCA protocol accepts only one transaction at a time within a connection. Once a transaction is started, the application must use the same transaction within that connection for all query statements until the transaction is committed or rolled back.
|
|
|
An application invokes query statements by calling the IIapi_query() function and providing the statement type and statement text in input parameters. Normally, the application obtains the results of the query by calling the IIapi_getQueryInfo() function and closes the statement by calling the IIapi_close() function.
Note: Most of the information in this guide pertains to connecting to and operating on a DBMS, but you can work with the Name Server also. The term server is used generically, and query statement is used generically for either an SQL statement or Name Server query statement.
Data exchange between the application and a server requires two sets of information: data descriptors and data values. Query parameters are passed in calls to the IIapi_setDescriptor() and IIapi_putParms() functions. Result data returned by query statements is retrieved by calls to the IIapi_getDescriptor() and IIapi_getColumns() functions.
|
|
|
Depending on the query operation, the application may call additional OpenAPI functions. For statements that return data (the SQL select statement, Name Server show statement, database procedures with BYREF, INOUT, or OUT parameters, and row-returning database procedure), and for the SQL copy statement, the order in which an application invokes OpenAPI functions is shown in the figure that follows.
|
|
|
The left side of the diagram shows the sequence of functions for statements that return data. The right side shows the sequence of functions for copying data from a database table to the program (copy into) and copying data from the program into a database table (copy from).
|
|
|
An application can cancel a query statement started with IIapi_query() before the statement is fully executed. To cancel a query statement, the application issues IIapi_cancel(), specifying the statement handle returned by IIapi_query(). IIapi_cancel() can be called anytime after IIapi_query() has returned with a status of "success." If the query has already been completed, an error is returned to the application stating so.
|
|
|
The following SQL statements are used to retrieve data from a DBMS Server:
Retrieves one row from the database. Used when only one result row for the select statement is desirable. If the singleton select tries to retrieve more than one row, an error occurs. This type of select does not use a cursor.
The application issues the following singleton select statement with IIapi_query():
IIAPI_QT_SELECT_SINGLETON
Retrieves an unlimited number of result rows. Used when the number of rows in the result set is unknown.
This style of select statement is useful for applications that need to read data to load program data sets or to generate reports. This type of select does not use a cursor.
The application issues the following select loop statement with IIapi_query():
IIAPI_QT_SELECT
Opens a cursor to retrieve rows. Used when it is desirable to use a cursor.
Updateable cursors retrieve one row at a time, permitting updates or deletion of the row addressed by the cursor. Read-only cursors may retrieve more rows at a time, similar to select loops, but permit database operations between retrieval operations.
The application issues the following open cursor statement with IIapi_query():
IIAPI_QT_OPEN
Regardless of the statement used for retrieving data, the application calls IIapi_getDescriptor() after calling IIapi_query() to obtain information about the format of the data being returned from the server. It then calls IIapi_getColumns() one or more times to retrieve the data. If no long varchar or long byte data types exist in the result set, and it is not an updateable cursor, multiple rows can be returned for each call. IIapi_getColumns() returns the "no more data" status once all rows have been returned. When all available data has been retrieved, the application calls IIapi_close() to end the process.
|
|
|
Cursors enable an application to process, one at a time, the result rows returned by a select statement. The following SQL statements are used in processing data with a cursor:
Updates the current row.
Deletes the current row.
If a cursor is opened as updateable (the default), the application can update or delete the row referenced by the cursor. If the cursor is opened as read-only, the application can read the data but cannot update or delete it.
When an application calls IIapi_query() to open a cursor, it provides the name of the cursor as a parameter in subsequent calls to IIapi_setDescriptor() and IIapi_putParms(). Cursor name is the character string, unique within the application, that represents the cursor.
When the application calls IIapi_query() to update or delete data with a cursor, it provides the cursor ID as a parameter in subsequent calls to IIapi_setDescriptor() and IIapi_putParms(). Cursor ID is the statement handle returned by IIapi_query() when the cursor is opened.
|
|
|
To use a cursor, the application:
The application may call IIapi_getQueryInfo() to obtain the status of the open cursor request.
The application may call IIapi_getQueryInfo() after each call to IIapi_getColumns() to obtain the status of the fetch request.
If the application is deleting or updating information with a cursor, it specifies the cursor delete or update statement for the row where the cursor is positioned. The statement handle returned from the open statement should be used as a cursor ID value for the delete or update statement.
|
|
|
A database event is a notification to an application that a specific condition has occurred.
An application processes database events with the following SQL statements:
Registers to receive event notifications. After register dbevent is executed, the application retrieves events with IIapi_catchEvent().
Removes an event for which an application has previously registered.
|
|
|
|
|
|
The application calls IIapi_getQueryInfo() and IIapi_close() after each call to IIapi_query(), shown in the illustration. Additional SQL statements are used to create, drop, and raise database events.
Note: For a description and usage of these statements, see the SQL Reference Guide.
IIapi_catchEvent() registers a callback function to be called when a database event notification is received. IIapi_catchEvent() operates similarly to the embedded SQL statement SET_SQL(DBEVENTHANDLER = dbevent_handler). IIapi_catchEvent() cannot be used in synchronous mode; it is inherently asynchronous.
For each call to IIapi_catchEvent(), only one database event notification will be returned. To receive multiple database events, IIapi_catchEvent() must be called every time OpenAPI returns a database event notification to the application. Rather than closing and repeatedly re-allocating event handles, an event handle passed to the application callback function can be re-activated by providing it as input to IIapi_catchEvent().
The server can send database event notifications with other query results. When OpenAPI receives a database event notification, OpenAPI processes the event and calls the the application callback function for any event handles matching the database event.
Database event notifications can also be sent by the server between client queries. OpenAPI provides a function, IIapi_getEvent(), which can be used to check for database events between queries or if the application desires only to wait for database event notification without performing other database operations.
IIapi_getEvent() waits for a database event notification to be sent by the server on a particular connection. No other request may be made on the connection until IIapi_getEvent() completes. A timeout value can be specified when calling IIapi_getEvent() so that the application can poll for database event notifications. IIapi_getEvent() operates similarly to the embedded SQL statement GET DBEVENT.
IIapi_getEvent() does not return database event notifications directly. To receive database events, the application must still issue an IIapi_catchEvent() request prior to calling IIapi_getEvent(). When received by IIapi_getEvent(), OpenAPI processes a database event notification and calls the application callback function for any event handles matching the database event.
Matching criteria specified when calling IIapi_catchEvent() permits an application to filter database events by name and owner. Database event notifications are compared to all active event handles and may result in callbacks for none, some, or all handles depending on the filtering information.
Since it is possible for a database event notification to be received but not matched to any active event handle, an additional callback function can be registered on the environment handle using IIapi_setEnvParm(). This callback function is called for each database event notification that fails to match an active event handle. An event handle is not passed to the callback function, but the output information available from IIapi_catchEvent() is passed to the callback function as a structure parameter.
|
|
|
In general, OpenAPI supports SQL syntax identical to that supported by embedded SQL and the Ingres terminal monitors. OpenAPI does not support some embedded SQL statements and supports some SQL statements through OpenAPI functions rather than through an SQL statement. OpenAPI does not support any of the Ingres forms or 4GL statements.
|
|
|
OpenAPI provides several types of statements that allow you to access and operate on the Name Server. The create, destroy, and show statements allow you to create, destroy, or show Name Server entities such as login, connection, or attribute definitions.
|
|
|
OpenAPI does not support host variables, as does embedded SQL. There are several mechanisms by which an application can handle queries for which the parameter values are not known until runtime.
First, the application can use C library string formatting routines, such as sprintf(), to build the SQL query text at runtime. Parameter values are formatted as literals in the query text prior to query execution.
Second, the application can use dynamic SQL and provide the parameter values as parameters to the query when the statement is executed or a cursor is opened. (Dynamic SQL is not supported by the Name Server.)
A third method, which uses parameter markers but does not require the statement to be prepared, is also available. This method is the same mechanism used by embedded SQL to handle host variables. It is also the only method that handles runtime parameter values for repeat queries. Since this mechanism is usually hidden to applications, it is not described further here.
Note: Parameter strings in queries should be sent as varchar type, rather than chars type, due to the pattern matching method used by the DBMS Server.
|
|
|
Long varchar and long byte data types are binary large objects (sometimes called BLOBs) that can store up to 2 GB of data. Since it is often impossible to allocate a storage buffer of this size, special handling is required to segment the unformatted data across the OpenAPI interface. This is done with the IIapi_getColumns(), IIapi_putColumns(), and IIapi_putParms() functions.
Each of these functions contain three common parameters:
Contain the number of parameters being sent or columns being retrieved in an SQL statement.
Contain the buffers of data being sent or retrieved.
Indicates if there are more data segments to be sent or retrieved for a column of long varchar or long byte data type.
Data passes between the application and OpenAPI in a row. Normally, all data in a row or all parameters in an SQL statement are passed with one call to IIapi_getColumns(), IIapi_putColumns(), or IIapi_putParms(). If one of the columns is a long varchar or long byte, however, each segment of the long varchar or long byte must be passed with a single call to the above functions. The moreSegments parameter (which is set to TRUE or FALSE) indicates whether the long varchar or long byte data is completely retrieved or sent. After it is retrieved or sent, the rest of the data, up to the next long varchar or long byte, is passed with one function call.
Example—passing unformatted data
An application calls IIapi_getColumns() to retrieve a row of ten columns in a table. The fifth column is a long varchar data type spanning multiple segments. To retrieve the data, the application does the following:
The same logic is used when an application is sending data to a server with IIapi_putParms() or IIapi_putColumns().
|
|
|
Most Ingres data types have a corresponding C language data type. Data values passed between the application and a server use the C language data formats. For data types that do not have corresponding C language data types—namely money, decimal, and date—OpenAPI provides two functions, IIapi_convertData() and IIapi_formatData(), which convert these values to a native C language format. The introduction of new data types is controlled by an OpenAPI level negotiated by the IIapi_connect() function.
|
|
|
This section describes how the application checks for errors.
|
|
|
An application checks for OpenAPI function errors when the function has completed its tasks, as indicated by the completion flag, gp_completed, in the generic parameter block.
When the function successfully or unsuccessfully completes its tasks, the completion flag is set to TRUE and the callback function is invoked if the callback address is provided in the parameter block. The value of the generic parameter gp_status indicates the success or failure of the function.
A failed task may have additional error information attached to it. To find out if such information exists, an application examines the value of the generic parameter gp_errorHandle. If the handle is non-NULL, the application calls IIapi_getErrorInfo() to retrieve the additional error information.
|
|
|
When an OpenAPI function detects an error, an error code and text describing the error are generated. This information is available through IIapi_getErrorInfo() and the error handle returned in the generic parameters of the function parameter block. When IIapi_getErrorInfo() returns with ge_serverInfoAvail set to FALSE, the error information generated by the OpenAPI and ge_errorCode is set to a particular value.