[DB-SIG] mxODBC: Function sequence error

[M.-A. Lemburg]

Thomas Waechter wrote:
> Hallo,
> I am trying out concurrent  read access on an Access ODBC datasource using mxODBC. Basically I am only trying to execute "select * from table where id = 1" statements using several threads. Everything is working fine with just one thread, but  using multiple threads curses the following error in MS ODBC Driver Manager .
> 
> DB driver message:
> ('S1010', 0, '[Microsoft][ODBC Driver Manager] Function sequence error', 3326)(){}

Are you using one connection per thread ?

Please also make sure that you're using the latest available
ODBC drivers for MS Access, since earlier versions are not
thread safe.

> For me it seems that the sequential order of SQLPrepare and SQLExecute calls gets changed in some way. I checked the C++ sources of mxODBC and found out  that in the Windows/mxODBC.cpp threads a generally allowed, see as follows:
> 
> Py_BEGIN_ALLOW_THREADS;
> rc = SQLPrepare(dbcs->hstmt,
> 		    (SQLCHAR*)sqlcmd,
> 		    sqlcmd_len);
> Py_END_ALLOW_THREADS;
> 
>       ...
> 
> Py_BEGIN_ALLOW_THREADS;
>    if (!direct)
>       rc = SQLExecute(dbcs->hstmt);
>    else
>       rc = SQLExecDirect(dbcs->hstmt,
> 				   (SQLCHAR*)PyString_AS_STRING(sqlcmd),
> 				   (SQLINTEGER)PyString_GET_SIZE(sqlcmd));
> Py_END_ALLOW_THREADS;
> 
> I couldn't find a connection between these two calls. How is it possible to guarantee the right order?
> My question is now, if it would be sufficient to recompile mxODBC with direct=1 to use SQLExecDirect instead of SQLExecute/Prepare to get rid of this problem or maybe there is another reason for it. 
> Any information regarding this would be greatly appreciated. 

No need to recompile: direct is set to 1 if you execute
an SQL statement without parameters or you switch to
.executedirect().

> Thanks for your help in advance.
> Thomas Waechter
> 
> --
> Thomas Waechter
> Software Developer
> Cenix BioScience
> BioInnovations Zentrum
> Tatzberg 46
> D-01307
> Dresden
> Germany
> 
> Tel : +49 (0)351 4173 0155
> e-mail : waechter at cenix-bioscience.com
> Cenix Website : http://www.cenix-bioscience.com
> 
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 18 2004)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
EuroPython 2004, Göteborg, Sweden                           19 days left

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::