I have written an ESQL application using C++. This program has a main
thread, which will create a "client thread" and a database connection
with an unique connection name when it receives a client connection.
i.e. One dedicated database connection for one thread. The thread will
handle some requests from a client and perform some databse operations
using the dedicated database connection. My aplication can receive
many client connections.
However, i got the same error every time I execute the sql, say,
execute immediate. The error is
sqlcode = -33620271
sqlerrmc is "cs_objects: cslib user api layer: external error: 08003
connection exception -- connection does not exist"
I think the connection/ sqlca are somehow binded to the information of
the thread which creates them and can only be used by the thread.
Therefore my program doesn't work because the connection is created by
a main thread, but it is used by a "client thread".
I have two experiment
1) After the connection is established, i use execute immediate to
update a record in the database and it works! And so i come up with
this conclusion.
2) If i don't use threads, everything works.
I have used the -r precompiler option but it doesn't help. In fact, my
application also needs to support dynamic sql method 4 and i wonder if
it more problems will arise.
Could anyone please give me some hints to solve this problem?

Hello,

General rules for using ESQL with threads.
===========================================
(1) Have a global mutex and use the mutex when acquiring connections;
(2) Although you should be okay with a golbal SQLCA structure, it is
always safer to have variables local to a thread. Make the SQLCA
structure local to the thread;
(3) Minimize the use of dynamic sql (if you use dynamic sql, then understand that
the name space is global to the entire process) so do something like making
the prepared statement identifiers unique. For example:
sprintf (hostvar, "name%05d", pthread_self()) ;
(4) Cursors and connections are scoped by thread so if you have to use a
connection on a thread other than the one that acquired it, use the
#define CONNECTIONS_ARE_SHARED_ACROSS_THREADS 1, in your .cp file(s).

The cs_objects structure is an internal Sybase structure used to store context and
connection information. When an 'EXEC SQL CONNECT ...' statement is issued by
ESQL, keep in mind that it translates to ctlib calls, wherein a context
structure is allocated and populated with the users environment information,
such as locale info and a connection structure as well with the login info and a
command structure(s) for executing sql commands.