Interacting with the Database Server
Within your ESQL/C program, you can interact with the database server in the following ways:
Determining Features of the Database Server
You can check on features of the database server after you execute one of the following SQL statements.
When the database server establishes a connection with one of these statements, it can obtain the following information about the database server:
The following table summarizes the values that the SQLSTATE variable and the sqlca structure take to indicate these conditions.
The SQLSTATE variable might return multiple exceptions after these connection statements. For more information about the SQLSTATE variable and the sqlca structure, see "Exception Handling."
Switching Between Multiple Database Connections
An ESQL/C application can make a number of simultaneous database connections with a CONNECT statement. These connections can be to several database environments or can be multiple connections to the same database environment. To switch between connections, the ESQL/C application must follow these steps:
1. Establish a connection with the CONNECT STATEMENT
2. Handle any active transactions
If the current connection has an active transaction, you can switch connections only if the CONNECT statement with the CONCURRENT TRANSACTION clause establishes the current connection.
3. Make a connection current with the SET CONNECTION or CONNECT statement
Making a Connection Current
When multiple connections exist, the application can only communicate with one connection at a time. This connection is the current connection. All other established connections are dormant. Your application can make another connection current with either of the following connection statements:
When you make a connection dormant and then current again, you perform an action similar to when you disconnect and then reconnect to the database environment. However, if you make a connection dormant you can typically avoid the need for the database server to perform authentication again, and thereby save the cost and use of resources that are associated with the connection.
For more information, see the entries for CONNECT and SET CONNECTION in the Informix Guide to SQL: Syntax.
Handling Transactions
If the CONNECT statement with the WITH CONCURRENT TRANSACTION clause has established the connection, the application can switch to another connection even if the current connection contains an active transaction.
For connections that are not established with the CONNECT...WITH CONCURRENT TRANSACTION statement, the application must end the active transaction before it switches to another connection. Any attempt to switch while a transaction is active causes the CONNECT or SET CONNECTION statement to fail (error number -1801). The transaction in the current connection remains active.
To maintain the integrity of database information, explicitly end the active transaction in one of the following ways:
The COMMIT WORK or ROLLBACK WORK statement applies only to the transaction that is within the current connection, not to transactions that are in any dormant connection. For more information on how to handle transactions, see the COMMIT WORK and ROLLBACK WORK statements. For more information on the WITH CONCURRENT TRANSACTION clause of CONNECT, see the CONNECT and SET CONNECTION statements. Entries for these SQL statements can be found in the Informix Guide to SQL: Syntax.
Identifying an Explicit Connection
From within an ESQL/C application, you can obtain the name of the database server and the name of the explicit connection with the GET DIAGNOSTICS statement. When you use GET DIAGNOSTICS after an SQL connection statement (CONNECT, SET CONNECTION, and DISCONNECT), GET DIAGNOSTICS puts this database server information in the diagnostics area in the SERVER_NAME and CONNECTION_NAME fields, respectively.
Figure 12-9 shows a code fragment that saves connection information in the srvrname and cnctname host variables.
For more information, see the entry for GET DIAGNOSTICS in the Informix Guide to SQL: Syntax.
From within an ESQL/C application, you can obtain the name of the current connection with the ifx_getcur_conn_name() function. This function returns the name of the current connection into a user-defined character buffer. The function is useful to determine the current connection among a group of active connections in a ESQL/C application that has multiple threads.
For example, the following code consists of a callback function, cb(), that is used by two sqlbreakcallback() calls in two different threads:
The cb() callback function uses the ifx_getcur_conn_name() to check which connection is current. For the syntax of ifx_getcur_conn_name(), see page 12-48. For more information on current and active connections, see "Concurrent Active Connections".
Obtaining Available Databases
From within an ESQL/C application, you can obtain the name of the databases that are available from a specified database server with the sqgetdbs() function. This function returns the names of the databases that are available in the database server of the current connection. For more information on sqgetdbs(), see page 12-49.
Checking the Status of the Database Server
Some interactions with the database server cannot execute unless the database server is idle. Other actions assume that the database server is busy processing a request. You can check whether the database server is currently processing an SQL request with the sqldone() function. This function returns zero (0) if the database server is idle and a negative value if it is busy. For more information on sqldone(), see page 12-64.
Detaching from a Connection
When your application forks a process, the child process inherits the database connections of the parent. If you leave these connections open, both parent and child processes use the same connection to communicate with the same database server. Therefore, the child process needs to establish a separate database connection.
To establish a separate database connection for the child process
1. Call sqldetach() to detach the child process from the database server connection in the parent process.
2. Establish a new connection in the child process (if one is needed).
For more information on the sqldetach() library function, see page 12-57.
Interrupting an SQL Request
Sometimes you might need to cancel an SQL request. If, for example, you inadvertently provide the wrong search criteria for a long query, you want to cancel the SELECT statement rather than wait for unneeded data. While the database server executes an SQL request, the ESQL/C application is blocked. To regain control, the application must interrupt the SQL request.
To interrupt the database server, you can use the sqlbreak() library function. You might want to interrupt an SQL request for some of the following reasons:
The following sections summarize how to handle each of these types of interrupt. For more information on the sqlbreak() function, see page 12-52.
Interruptible SQL Statements
You cannot cancel all SQL statements. Some types of database operations are not interruptible and others cannot be interrupted at certain points. An ESQL/C application can interrupt the following SQL statements.
In addition to the preceding statements, you can also cancel the operation of a loop as it executes within a SPL routine.
The ESQL/C application and the database server communicate through message requests. A message request is the full round trip of the message that initiates an SQL task. It can consist of the message that the application sends to the database server as well as the message that the database server sends back in reply. Alternatively, a message request can consist of the message that the database server sends to the application as well as the message that the application sends in acknowledgment.
Most SQL statements require only one message request to execute. The application sends the SQL statement to the database server and the database server executes it. However, an SQL statement that transfers large amounts of data (such as a SELECT, an INSERT, or a PUT), can require more than one message request to execute, as follows:
In addition, the OPEN statement always requires two message requests.
The database server decides when to check for an interrupt request. Therefore, the database server might not immediately terminate execution of an SQL statement and your application might not regain control as soon as it sends the interrupt request.
Allowing a User to Interrupt
When the database server processes a large query, you might want to allow the user to interrupt the query request with the Interrupt key (usually CTRL-C). To do this, you must set up a signal-handler function. The signal-handler function is a user-defined function that the application process calls when it receives a specific signal.
To allow the user to interrupt an SQL request, you define a signal-handler function for the SIGINT signal. This function must have the following declaration:
The user-defined signal-handler function can contain the ESQL/C control functions sqlbreak() and sqldone(). For more information on the sqlbreak() and sqldone() functions, refer to page 12-52 and page 12-64, respectively. If you use any other ESQL/C control function or any SQL statement in the signal handler while the database server is processing, ESQL/C generates an error (-439).
The ESQL/C application must determine how to continue execution after the signal handler completes. One possible method is to set up a nonlocal go to with the setjmp() and longjmp() system functions. These functions work together to support low-level interrupts, as follows:
See your UNIX operating system documentation for more information on the setjmp() and longjmp() system functions.
To associate the user-defined signal handler with a system signal, use the signal() system function, as follows:
When the ESQL/C application receives the SIGINT signal, it calls the function that sigfunc_ptr indicates. For more information on the signal() system function, see your UNIX operating system documentation.
To disassociate the signal-handler function from the SIGINT signal, call signal() with SIG_DFL as the function pointer, as follows:
SIG_DFL is the default signal-handling action. For the SIGINT signal, the default action is to stop the process and to generate a core dump. You might instead want to specify the SIG_IGN action to cause the application to ignore the signal.
Setting Up a Time-Out Interval
When the database server processes a large query, you might want to prompt the user periodically to determine whether to continue the request. To do this, you can use the sqlbreakcallback() function to provide the following information:
The Time-Out Interval
With the sqlbreakcallback() function, you specify a time-out interval. A time-out interval is the amount of time (in milliseconds) for which the database server can process an SQL request before the application regains control. The application then calls the callback function that you specify and executes it to completion.
Once the callback function completes, the application resumes its wait until one of the following actions take place:
The application calls the callback function each time the time-out interval elapses until the database server completes the request or is interrupted.
The Callback Function
With the sqlbreakcallback() function, you also specify a callback function to be called at several points in the execution of an SQL request. A callback function is a user-defined ESQL/C function that specifies actions to take during execution of an SQL request. This function must have the following declaration:
The integer status variable identifies at what point in the execution of the SQL request the callback function has been called. Within the callback function, you can check this status variable to determine at which point the function has been called. Figure 12-10 summarizes the valid status values.
Figure 12-10
Status Values of a Callback Function
Within the callback function, you might want to check the value of the status argument to determine what actions the function takes.
The callback function, and any of its subroutines, can contain only the following ESQL/C control functions:
If sqldone() returns error -439, the database server is still busy and you can proceed with the interrupt. For more information on the sqldone() function, refer to page 12-64.
Call sqlbreakcallback() with the following arguments:
sqlbreakcallback(-1L, (void *)NULL);
This step is not necessary if you want the callback function to remain for the duration of the current connection. When you close the current connection, you also disassociate the callback function.
If you use any ESQL/C control function other than those in the preceding list, or if you use any SQL statement while the database server is processing, ESQL/C generates an error (-439).
If the application calls a callback function because a time-out interval has elapsed, the function can prompt the user for whether to continue or cancel the SQL request, as follows:
While the callback function executes, the database server continues processing its SQL request. Once the callback function completes, the application waits for another time-out interval before it calls the callback function again. During this interval, the database server continues execution of the SQL request.
Execution of the callback function continues immediately after sqlbreak() sends the request. The application does not wait for the database server to respond until it completes execution of the callback function.
When the database server receives the interrupt request signal, it determines if the current SQL request is interruptible (see page 12-34). If so, the database server discontinues processing and returns control to the application. The application is responsible for the graceful termination of the program; it must release resources and roll back the current transaction. For more information on how the database server responds to an interrupt request, see the description of sqlbreak() on page 12-52.
Use the sqlbreakcallback() function to set the time-out interval (in milliseconds) and to register a callback function, as follows:
This callbackfunc_ptr must point to a callback function that you already defined (see page 12-38). Within the calling program, you must also declare this function, as follows:
For information on the sqlbreakcallback() function, see page 12-54. The timeout demonstration program, which page 12-70 describes, uses the sqlbreakcallback() function to establish a time-out interval for a database query.
Terminating a Connection
An ESQL/C program can use the following statements and functions to close a connection:
For more information on the CLOSE DATABASE and DISCONNECT statements, see of the Informix Guide to SQL: Syntax. For information on the sqldetach() and sqlexit() library functions, see page 12-57 and page 12-65, respectively.
|
