INFORMIX-ESQL/C Programmer's Manual ESQL/C Function Library

The sqldetach() function detaches a process from the database server. You generally call this function when an application forks a new process to begin a new stream of execution.

mint sqldetach();

If an application spawns one or more processes after it initiates a connection to a database server, all the child processes inherit that database server connection from the parent process (the application process that spawned the child). However, the database server still assumes that this connection has only one process. If one database server connection tries to serve both the parent and child processes at the same time, problems can result. For example, if both processes send messages to do something, the database server has no way of knowing which messages belong to which process. The database server might not receive messages in an order that makes sense and might thereby generate an error (such as error -408).

In this situation, call the sqldetach() function from the child process. The sqldetach() function detaches the child process from the connection that the parent process establishes (which the child inherits). This action drops all database server connections in the child process. The child process can then establish its own connection to a database server.

Use the sqldetach() function with the fork() system call. When you spawn a child process from an application process with a database server connection, sequence the function calls as follows:

1. Call fork() from the parent process to create a copy of the parent process (the child process). Now both parent and child share the same connection to the database server.
2. Call sqldetach() from the child process to detach the child process from the database server. This call closes the connection in the child process.

Tip: You cannot use sqldetach() after a vfork() call because vfork() does not execute a true process fork until the exec() function is called. Do not use sqldetach() after the parent process uses an exec(); when exec() starts the child process, the child process does not inherit the connection that the parent process established.

A call to the sqldetach() function does not affect the database server sessions of the parent process. Therefore, after sqldetach() executes in the child process, the parent process retains any open cursors, transactions, or databases, and the child process has neither database server sessions nor database server connections.

When you call the sqlexit() function from the parent process, the function drops the connection in the parent process but does not affect the connections in the child process. Similarly, when you call sqlexit() from the child process, the function drops only the child connections; it does not affect the parent connections. The sqlexit() function rolls back any open transactions before it closes the connection.

If you execute the DISCONNECT statement from a child process, you disconnect the process from database server connections and terminate the database server sessions that correspond to those connections. The DISCONNECT fails if any transactions are open.

If the child process application has only one implicit connection before it calls sqldetach(), execution of the next SQL statement or of the sqlstart() library function reestablishes an implicit connection to the default database server. If the application has made one or more explicit connections, you must issue a CONNECT statement before you execute any other SQL statements.

The sqldetach demonstration program illustrates how to use the sqldetach() function.

The sqldetach.ec file in the demo directory contains this sample program.

/*
* sqldetach.ec *

This program demonstrates how to detach a child process from a
parent process using the ESQL/C sqldetach() library function.
*/

main()
{
EXEC SQL BEGIN DECLARE SECTION;
mint pa;
EXEC SQL END DECLARE SECTION;

printf("SQLDETACH Sample ESQL Program running.\n\n");

printf("Beginning execution of parent process.\n\n");
printf("Connecting to default server...\n");
EXEC SQL connect to default;
chk("CONNECT");
printf("\n");

printf("Creating database 'aa'...\n");
EXEC SQL create database aa;
chk("CREATE DATABASE");
printf("\n");

printf("Creating table 'tab1'...\n");
EXEC SQL create table tab1 (a integer);
chk("CREATE TABLE");
printf("\n");

printf("Inserting 4 rows into 'tab1'...\n");
EXEC SQL insert into tab1 values (1);
chk("INSERT #1");
EXEC SQL insert into tab1 values (2);
chk("INSERT #2");
EXEC SQL insert into tab1 values (3);
chk("INSERT #3");
EXEC SQL insert into tab1 values (4);
chk("INSERT #4");
printf("\n");

printf("Selecting rows from 'tab1' table...\n");
EXEC SQL declare c cursor for select * from tab1;
chk("DECLARE");

EXEC SQL open c;
chk("OPEN");

printf("\nForking child process...\n");
fork_child();

printf("\nFetching row from cursor 'c'...\n");
EXEC SQL fetch c into $pa;
chk("Parent FETCH");
if (sqlca.sqlcode == 0)
printf("Value selected from 'c' = %d.\n", pa);
printf("\n");

printf("Cleaning up...\n");
EXEC SQL close database;
chk("CLOSE DATABASE");
EXEC SQL drop database aa;
chk("DROP DATABASE");
EXEC SQL disconnect all;
chk("DISCONNECT");

printf("\nEnding execution of parent process.\n");
printf("\nSQLDETACH Sample Program over.\n\n");
}

fork_child()
{
mint rc, status, pid;

EXEC SQL BEGIN DECLARE SECTION;
mint cnt, ca;
EXEC SQL END DECLARE SECTION;

pid = fork();
if (pid < 0)
printf("can't fork child.\n");

else if (pid == 0)
{
printf("\n**********************************************\n");
printf("* Beginning execution of child process.\n");
rc = sqldetach();
printf("* sqldetach() call returns %d.\n", rc);

/* Verify that the child is not longer using the parent's
* connection and has not inherited the parent's connection
* environment.
*/
printf("* Trying to fetch row from cursor 'c'...\n");
EXEC SQL fetch c into $ca;
chk("* Child FETCH");
if (sqlca.sqlcode == 0)
printf("* Value from 'c' = %d.\n", ca);

/* startup a connection for the child, since
* it doesn't have one.
*/

printf("\n* Establish a connection, since child doesn't have one\n");
printf("* Connecting to database 'aa'...\n");
EXEC SQL connect to 'aa';
chk("* CONNECT");
printf("* \n");
printf("* Determining number of rows in 'tab1'...\n");
EXEC SQL select count(*) into $cnt from tab1;
chk("* SELECT");
if (sqlca.sqlcode == 0)
printf("* Number of entries in 'tab1' = %d.\n", cnt);
printf("* \n");

printf("* Disconnecting from 'aa' database...\n");
EXEC SQL disconnect current;
chk("* DISCONNECT");
printf("* \n");
printf("* Ending execution of child process.\n");
printf("**********************************************\n");

exit();
}

/* wait for child process to finish */
while ((rc = wait(&status)) != pid && rc != -1);

}

chk(s)
char *s;
{
mint msglen;
char buf1[200], buf2[200];

if (SQLCODE == 0)
{
printf("%s was successful\n", s);
return;
}
printf("\n%s:\n", s);
if (SQLCODE)
{
printf("\tSQLCODE = %6d: ", SQLCODE);
rgetlmsg(SQLCODE, buf1, sizeof(buf1), &msglen);
sprintf(buf2, buf1, sqlca.sqlerrm);
printf(buf2);
if (sqlca.sqlerrd[1])
{
printf("\tISAM Error = %6hd: ", sqlca.sqlerrd[1]);
rgetlmsg(sqlca.sqlerrd[1], buf1, sizeof(buf1), &msglen);
sprintf(buf2, buf1, sqlca.sqlerrm);
printf(buf2);
}
}
}

SQLDETACH Sample ESQL Program running.

Beginning execution of parent process.

Connecting to default server...
CONNECT was successful

Creating database 'aa'...
CREATE DATABASE was successful

Creating table 'tab1'...
CREATE TABLE was successful

Inserting 4 rows into 'tab1'...
INSERT #1 was successful
INSERT #2 was successful
INSERT #3 was successful
INSERT #4 was successful

Selecting rows from 'tab1' table...
DECLARE was successful
OPEN was successful

Forking child process...

**********************************************
* Beginning execution of child process.
* sqldetach() call returns 0.
* Trying to fetch row from cursor 'c'...

* Child FETCH:
SQLCODE = -404: The cursor or statement is not available.

* Establish a connection, since child doesn't have one
* Connecting to database 'aa'...
* CONNECT was successful
*
* Determining number of rows in 'tab1'...
* SELECT was successful
* Number of entries in 'tab1' = 4.
*
* Disconnecting from 'aa' database...
* DISCONNECT was successful
*
* Ending execution of child process.
**********************************************
SQLDETACH Sample ESQL Program running.

Beginning execution of parent process.

Connecting to default server...
CONNECT was successful

Creating database 'aa'...
CREATE DATABASE was successful

Creating table 'tab1'...

CREATE TABLE was successful

Inserting 4 rows into 'tab1'...
INSERT #1 was successful
INSERT #2 was successful
INSERT #3 was successful
INSERT #4 was successful

Selecting rows from 'tab1' table...
DECLARE was successful
OPEN was successful

Forking child process...

Fetching row from cursor 'c'...
Parent FETCH was successful
Value selected from 'c' = 1.

Cleaning up...
CLOSE DATABASE was successful
DROP DATABASE was successful
DISCONNECT was successful

Ending execution of parent process.

SQLDETACH Sample Program over.