Connections across threads

The simplest way to manage a connection that several threads must use is to put the SET CONNECTION statement in a loop. The following code fragment shows a simple SET CONNECTION loop.

/* wait for connection: error -1802 indicates that the connection 
   is in use 
 */
do {
   EXEC SQL set connection  :con_name;
   } while (SQLCODE == -1802);

The following code fragment uses the CONNECT statement to establish connections and SET CONNECTION statements to make dormant connections active within threads. It also uses SET CONNECTION...DORMANT to make active connections dormant. This code fragment establishes the connections that Concurrent connections in a thread-safe ESQL/C application illustrates. It does not show DCE-related calls and code for the start_threads() function.

main()
{   EXEC SQL BEGIN DECLARE SECTION;
      int a;
   EXEC SQL END DECLARE SECTION;

   start_threads(); /* start 2 threads */
   wait for the threads to finish work.

   /* Use con1 to update table t1; Con1 is dormant at this point.*/
   EXEC SQL set connection 'con1';
   EXEC SQL update table t1 set a = 40 where a = 10;

   /* disconnect all connections */
   EXEC SQL disconnect all;
}
thread_1()
{
   EXEC SQL connect to 'db1' as 'con1’;
   EXEC SQL insert into table t1 values (10); /* table t1 is in db1*/

   /* make con1 available to other threads */
   EXEC SQL set connection 'con1' dormant;

   /* Wait for con2 to become available and then update t2 */
   do { 
      EXEC SQL set connection 'con2';
      } while ((SQLCODE == -1802) );
   if (SQLCODE != 0)
      return;
   EXEC SQL update t2 set a = 12 where a = 10; /* table t2 is in db1 */
   EXEC SQL set connection 'con2' dormant;
}

thread_2()
{   /* Make con2 an active connection */
   EXEC SQL connect to 'db2' as 'con2';
   EXEC SQL insert into table t2 values(10); /* table t2 is in db2*/
   /* Make con2 available to other threads */
   EXEC SQL set connection'con2' dormant;
}