Home

	Expand Search

Chapter 2: The Embedded SQL Interface

The SQL Communication Area

Using multiple SQLCAs

Using multiple SQLCAs

1. You must use the command line switch on the SQL preprocessor that generates reentrant code ( -r ). The reentrant code is a little larger and a little slower because statically initialized global variables cannot be used. However, these effects are minimal.

2. Each SQLCA used in your program must be initialized with a call to db_init and cleaned up at the end with a call to db_fini .

   WARNING!

   Caution

   Failure to call db_fini for each db_init on NetWare can cause the database server to fail, and the NetWare file server to fail.

3. The Embedded SQL statement SET SQLCA (SET SQLCA statement ) is used to tell the SQL preprocessor to use a different SQLCA for database requests. Usually, a statement such as: EXEC SQL SET SQLCA 'task_data->sqlca'; is used at the top of your program or in a header file to set the SQLCA reference to point at task specific data. This statement does not generate any code and thus has no performance impact. It changes the state within the preprocessor so that any reference to the SQLCA will use the given string.

For information about creating SQLCAs, see SET SQLCA statement .

When to use multiple SQLCAs

You can use the multiple SQLCA support in any of the supported Embedded SQL environments, but it is only required in reentrant code.

The following list details the environments where multiple SQLCAs must be used:

• Multi-threaded applications

  If more than one thread uses the same SQLCA, a context switch can cause more than one thread to be using the SQLCA at the same time. Each thread must have its own SQLCA. This can also happen when you have a DLL that uses Embedded SQL and is called by more than one thread in your application.

• Dynamic link libraries and shared libraries

  A DLL has only one data segment. While the database server is processing a request from one application, it may yield to another application that makes a request to the database server. If your DLL uses the global SQLCA, both applications are using it at the same time. Each Windows application must have its own SQLCA.

• A DLL with one data segment

  A DLL can be created with only one data segment or one data segment for each application. If your DLL has only one data segment, you cannot use the global SQLCA for the same reason, that a DLL cannot use the global SQLCA. Each application must have its own SQLCA.

Connection management with multiple SQLCAs

You do not need to use multiple SQLCAs to connect to more than one database or have more than one connection to a single database.

Each SQLCA can have one unnamed connection. Each SQLCA has an active or current connection (see SET CONNECTION statement ). All operations on a given database connection must use the same SQLCA that was used when the connection was established.

Note:

Record locking

Operations on different connections are subject to the normal record locking mechanisms and may cause each other to block and possibly to deadlock. For information on locking, see the chapter Using Transactions and Locks .