Prepared statements across threads

The PREPARE statements are scoped at the connection level. That is, they are associated with a connection. When a thread makes a connection active, it can access any of the prepared statements that are associated with this connection. If your thread-safe HCL OneDB™ ESQL/C application uses prepared statements, you might want to isolate compilation of PREPARE statements so that they are compiled only once in a program.

One possible way to structure your application is to execute the statements that initialize the connection context as a group. The connection context includes the name of the current user and the information that the database environment associates with this name (including prepared statements).

For each connection, the application would perform the following steps:
  1. Use the CONNECT statement to establish the connection that the thread requires.
  2. Use the PREPARE statement to compile any SQL statements that are associated with the connection.
  3. Use the SET CONNECTION...DORMANT statement to put the connection in the dormant state.

When the connection is dormant, any thread can access the dormant connection through the SET CONNECTION statement. When the thread makes this connection active, it can send the corresponding prepared statement or statements to the database server for execution.

In the following figure , the code fragment prepares SQL statements during the connection initialization and executes them later in the program.
Figure 1: Using prepared SQL statements across threads


The code fragment in Using prepared SQL statements across threads performs the following actions:
  1. The main thread calls start_con_threads(), which calls start_con_thread() to start two threads:
    • For Thread 1, the start_con_thread() function establishes connection con1, prepares a statement that is called s1, and makes connection con1 dormant.
    • For Thread 2, the start_con_thread() function establishes connection con2, prepares a statement that is called s2, and makes connection con2 dormant.
  2. The main thread calls start_execute_threads(), which calls start_execute_thread() to execute the prepared statements for each of the two threads:
    • For Thread 1, the start_execute_thread() function makes connection con1 active, executes the s1 prepared statement associated with con1, and makes connection con1 dormant.
    • For Thread 2, the start_execute_thread() function makes connection con2 active, executes the s2 prepared statement associated with con2, and makes connection con2 dormant.
  3. The main thread disconnects all connections.