[egenix-users] mxODBC and Microsoft SQL Server 2000 : An other case!

Sébastien Walgraffe walgraffe at dupuis.com
Tue Oct 21 18:22:48 CEST 2003


Hello,
after seeing the same case described in the mailing list archives from
september, I decided to post my problem too, because it seems really similar

Reminder :
nwingfield at che-llp.com wrote :
"We are having trouble with mxODBC and Microsoft SQL Server 2000, when two
or more people run the same query at the same time.  This trouble is easily
reproduced by opening two browser windows and loading a page that executes
a lengthy SQL query in both windows simultaneously.  Exhibit A shown below
is the most common, but Exhibit B is not hard to reproduce under the same
circumstances.  Exhibits C and D are also fairly common, and though not
directly SQL-related, suggest incomplete or corrupted recordsets.
Sometimes one window will successfully load the page; sometimes neither
will.  Never do both windows successfully load the page simultaneously.  I
would appreciate any suggestions.[...]"

Here is our problem :

We the are working with Zope (mxODBCZopeDA 1.00 for connection) mxODBC and
SQL Server 2000 too.
We use ZSQL methods to define sql.
We have an application showing some complex informations (retrieving from
several databases), but sometimes the following errors occur:

1: Fuction Sequence Error :
here is the log :
"Traceback (innermost last):
  Module ZPublisher.Publish, line 98, in publish
  Module ZPublisher.mapply, line 88, in mapply
  Module ZPublisher.Publish, line 39, in: call_object
  Module Shared.DC.Scripts.Bindings, line 252, in __call__
  Module Shared.DC.Scripts.Bindings, line 283, in _bindAndExec
  Module Products.PythonScripts.PythonScript, line 315, in _exec
  Module Script (Python), line 13, in choixserie
   -
   - Line 13
  Module Dupuis.GestionCatalogue.DomaineCatalogue.SerieCommerciale, line 54,
in getListeTitreByNum
  Module Dupuis.GestionCatalogue.DomaineCatalogue.SerieCommerciale, line 84,
in _initTitres
  Module Dupuis.GestionCatalogue.DomaineCatalogue.ArticlesOuvragesFactory,
line 36, in getArticleWithCorrectType
  Module Dupuis.GestionCatalogue.ZDomaineCatalogue.ZArticlesOuvragesFactory,
line 38, in createTitre
  Module Dupuis.GestionCatalogue.ZDomaineCatalogue.ZTitre, line 50, in
__init__
  Module Dupuis.GestionCatalogue.DomaineCatalogue.Titre, line 34, in
__init__
  Module Dupuis.GestionCatalogue.DomaineCatalogue.Article, line 39, in
__init__
  Module Dupuis.GestionCatalogue.ZDBAccessCatalogue.ZArticlesFinder, line
20, in getNumeroArticleByNum
  Module Shared.DC.ZRDB.DA, line 428, in __call__
  Module Products.mxODBCZopeDA.ZopeDA, line 1059, in query
  Module Products.mxODBCZopeDA.ZopeDA, line 1000, in run_cursor_callback
  Module Products.mxODBCZopeDA.ZopeDA, line 707, in errorhandler
InterfaceError: ('HY010', 0, '[Microsoft][ODBC SQL Server Driver]Function
sequence error', 4198)"
(getNumeroArticleByNum call a ZSQL method doing a simple select in a
database)

This happen when Zope has to retrieve severall informations in a database,
but a connection is already open on this database.

2 : Connection Busy

We also had:
"Error Type: ProgrammingError
Error Value: ('HY000', 0, '[Microsoft][ODBC SQL Server Driver]Connection is
busy with results for another hstmt', 5911)"

That happen in the same circumstances.

 3 : Wrong resultset - Empty resultset, no commit :

We call a ZSQL to get information from some article (identified with a known
number)
This raise some exception (we verify number exists before to fetch
information)

"Traceback (innermost last):
  Module ZPublisher.Publish, line 98, in publish
  Module ZPublisher.mapply, line 88, in mapply
  Module ZPublisher.Publish, line 39, in call_object
  Module Shared.DC.Scripts.Bindings, line 252, in __call__
  Module Shared.DC.Scripts.Bindings, line 283, in _bindAndExec
  Module Products.PythonScripts.PythonScript, line 315, in _exec
  Module Script (Python), line 13, in choixserie
   -
   - Line 13
  Module Dupuis.GestionCatalogue.DomaineCatalogue.SerieCommerciale, line 54,
in getListeTitreByNum
  Module Dupuis.GestionCatalogue.DomaineCatalogue.SerieCommerciale, line 84,
in _initTitres
  Module Dupuis.GestionCatalogue.DomaineCatalogue.ArticlesOuvragesFactory,
line 35, in getArticleWithCorrectType
  Module Dupuis.GestionCatalogue.DomaineCatalogue.Article, line 282, in
isUnTitre
  Module Dupuis.GestionCatalogue.AbstractDBAccessCatalogue.ArticleDBGateway,
line 604, in isUnTitre
  Module Dupuis.GestionCatalogue.AbstractDBAccessCatalogue.ArticleDBGateway,
line 712, in _getParams
AbstractCatalogueDBException: Article1118320:Article1118320Inexistant"

Wich means "no article with number 1118320", but this article exists!!
We also have no error message, but query return an empty resultset (should
not be)
and finally, most important, when inserting some data in a database, it
doesn t show any error, but the data isn't inserted (like if transaction
didn t ends by a commit)...

What we did :

With all those problems, we had no other choice than supress ZSQL
methods....

So we created a internal product for zope doing the following things :
- open a new connection
- writing sql with given parameters
- getting results
- closing connection
With this, we can avoid all problems we got.

But with this solution, we don t have transaction and rollback
possibilities. It s the only thing we found.
Obviously, this his solution can be temporary, but not definitive.

Other solution?

Is there something else we could do? Like change something in the mxODBC
driver to avoid all those problems?


Best regards
Sebastien Walgraffe



More information about the egenix-users mailing list