[Zope] Accessing MsSQL from Zope blocks

[robert rottermann]

Hi there,
this is not really an Zope Questions, I just  hope somebody of you cold
give me a hint.
I already asked egenix (as we are using mxODBC) they could not think of
a good reason. (but said to try a comercial odbc bridge).

thanks for any pointers.
robert

using mxOdbc our database application embeded in zope often blocks when executing
a simple statement as the following:

query = "select fieldValue from tblATicketDetailDynamicForm \
        where ticketDetailNoFk in (1683)"


calling sql_q_nil(query) (from the following class)
sometimes just never returns.


The problem seems to be, that after a certain amount of queries MsSQL is in a
state, that it does not allow any access to the querried database.

what we do in our application is:
- get a RedDbQuery object (class at the end of the posting)
- execute the statement with RedDbQuery.sql_q_nil(query)
- release the connection.

this approach worked fine using MySQL. However we did not cross any
OS bounderies and did not use ODBC.

we are using:

mxODBC Zope DA 1.0.8 License Information:
1 Zope Instance License for redcor <robert at redcor.ch> [6522021#1]
SuSE 9.3
UnixOdbc 2.8.8
freetds 0.64



class RedDbQuery:
  "All queries are routed through this class."

  def __init__( self ):
    self._connection = getConnection()
    #print 'getConnection() returns', _connection
    self._cursor = self._connection.cursor()
    #print 'cursor() returns', _cursor

  def dispose( self ):
    releaseConnection( self._connection, self._cursor )

  def cursor( self ):
    return self._cursor

  #
  # query level zero -- the one and only query execution method:
  #
  def sql_q_nil( self, query, verbose = None ):
    "Generic SQL query, expecting no return."
    if verbose: print( query )
    r = self._cursor.execute( query )
    #print 'execute() returns', r
    return r

  more methods ..


	



-------------- next part --------------
A non-text attachment was scrubbed...
Name: robert.vcf
Type: text/x-vcard
Size: 200 bytes
Desc: not available
Url : http://mail.zope.org/pipermail/zope/attachments/20060831/25bda288/robert.vcf