Re: ODBC and multi-threading: basics

I work with ODBC from Delphi. Similar environment, different syntax. The
comments below are not specific to Postgresql, but I have done some threaded
programming with Postgresql specifically.

1) Thread-safety depends on the environment. I've done some work with
Postgresql, and it appears to be thread-safe, but others know that better
than I. There's nothing in the ODBC spec that is un-thread-safe.

2) If you need to you should be able to share henv and hdbc. Database
connections are computationally expensive. Definitely create new hstmt's and
don't share them. I have found it a good practice to destroy the connection
and re-establish every 1000-5000 connections. It shouldn't be necessary, but
I've had problems when I didn't, so I just put that as a standard feature in
my libraries and don't think about it any more.

3) Think about the architecture:
- Once, I request an environment handle. (SQLAllocEnv)
- With the environment handle, I get a handle for a database
connection.(SQLAllocConnect
- With this connection, I set the properties and connect.
(SQLSetConnectOption; SQLConnect)
- Once I've connected, I get a handle for a statement.( SQLAllocStm)
- With this statement, I set the properties and execute. (SQLPrepare,
SQLExecute)
- If appropriate, I get the results(SQLFetch, SQLGetData).
- When done, I destroy the statement handle (SQLFreeStmt)
- When I want to execute another statement, I should get another handle. I'm
not saying you have to, but I've had better luck when I do.
- When I'm done, I drop the connection and environment
handles.(SQLDisconnect; SQLFreeConnect; SQLFreeEnv)

Ed

----- Original Message -----
From: "zzzmito" <bernardofhoppe(at)yahoo(dot)com>
To: <pgsql-odbc(at)postgresql(dot)org>
Sent: Tuesday, June 29, 2004 3:35 PM
Subject: [ODBC] ODBC and multi-threading: basics

Hi,
could anyone tell me some basics about working with ODBC in a
multithreaded application?
Next are some of the questions I have, please, help me by answering
any or all of them. Your opinion would be best considered, and also
any link would be most appreciated.

Situation: I am using the ODBC API from Microsoft Visual C++ with
Windows NT 4.0. I use a single application with multiple threads that
connect all to the same database, but that might query it multiple
times (before disconnecting from it) and at different moments and with
different SQL querys from the other threads. Of course simultaneity is
a must.

Questions:
1) is ODBC a thread safe environment?
a) with any DB driver?
b) what about Access (mdb databases)?
I've heard that the Microsoft Jet Access driver version Jet 4.0
or + is thread safe. If that was to be true, how could I use the Jet
driver when I use the ODBC API in my application? Is there a way to
configure the Datasource to do so? Do you know about any link talking
about this?

2) which of these variables should I keep global so that they can be
shared among the threads? (remember they all access to the same DB)
- henv (environment handle)
- hdbc (connection handle, with which I connect to the same )
- hstmt (statement handle)

3) so, continuing question 2), which actions should be made only 1
time (for all the threads), and which should be made each time a
thread wants to access the database?
- SQLAllocEnv(&henv);
- SQLAllocConnect(henv, &hdbc);
- SQLSetConnectOption(hdbc, SQL_LOGIN_TIMEOUT, 20);
- SQLConnect(hdbc, (UCHAR*)szDataSource, SQL_NTS, (UCHAR*)szLoginName,
SQL_NTS, (UCHAR*)szPassword, SQL_NTS);
- SQLAllocStmt(hdbc, &hstmt);
- SQLPrepare(hstmt, (UCHAR*) szInst, SQL_NTS);
- SQLExecute(hstmt);
- SQLFreeStmt(hstmt,SQL_DROP);
- SQLDisconnect(hdbc);
- SQLFreeConnect(hdbc);
- SQLFreeEnv(henv);

Thanks indeed in advance and best regards.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match