SQL Server DB backend: Rev 940 test results

[jdunck@…]

This is the output of tests/runtests.py against MS SQL 2000 SP 3 on Win2K SP4.

[jdunck@…]

3rd attempt at attaching test results

[Adrian Holovaty]

Thanks very much for these test results, Jeremy. It seems that database parameter-quoting isn't being done correctly.

A couple of questions --

• What's the "Strategy" stuff? Is that something SQL Server does?
• Can you run the following code in the Python interactive prompt and let us know what happens?

from django.core.db import db
cursor = db.cursor()

cursor.execute("SELECT 1")
print cursor.fetchall()

cursor.execute("SELECT 1 WHERE 1=%s", [1])
print cursor.fetchall()

cursor.execute("SELECT 1 WHERE 1=%s", (1,))
print cursor.fetchall()

cursor.execute("SELECT 1 WHERE 1=%s AND 2=%s", (1, 2))
print cursor.fetchall()

[jdunck@…]

To use database port:

# TODO: Handle DATABASE_PORT.
conn_string = "PROVIDER=SQLOLEDB;DATA SOURCE=%s,%s;UID=%s;PWD=%s;DATABASE=%s" % (DATABASE_HOST, DATABASE_PORT, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME)

(That is, "Data Source=hostname,port;")

Also, 1433 is the default port, and almost noone changes it. It'd be nice to default to 1433 if not explicitly set.

[jdunck@…]

It appears strategy is specific to adodbapi; I've never seen it in context of SQL Server before.

In adodbapi code, there's this:
"

strategy=1 # The ways of different OLE-DB providers are unpredictable, had to use two

# strategies in order to make both Access and SQL Server work.

"

I dunno if you're familiar, but ADO is a wrapper over different DB drivers, like ODBC, but with native performance. It appears that te various ADO providers are, uh, inconsistent, and adodbapi makes various attempts to get something to work before giving up.

So it tries something, and if it fails, it goes to the next strategy.

It only gives up when it runs out of ideas.

I'm sure this is fantastic for performance. :-/

Skeleton of logic:

def executeHelper(self,operation,isStoredProcedureCall,parameters=None):

strategy=1 # The ways of different OLE-DB providers are unpredictable, had to use two

# strategies in order to make both Access and SQL Server work.

tryAgain=1
while tryAgain:

try:

#branching ugliness

...

tryAgain=0

except (Exception), e:

tblist=traceback.format_exception(sys.exc_type,

sys.exc_value,
sys.exc_traceback,
8)

tb=string.join(tblist)
tracebackhistory+='\n-----------\nStrategy %i: Traceback:%s\n' %(strategy,tb)
strategy+=1
if strategy > 4:

tracebackhistory+='--- ADODBAPI on command:%s with parameters: %s' %(operation,parameters)
self._raiseCursorError(DatabaseError,tracebackhistory)
return

[jdunck@…]

Jeez, sorry for mangling.

It appears strategy is specific to adodbapi; I've never seen it in context of SQL Server before.

I dunno if you're familiar, but ADO is a wrapper over different DB drivers, like ODBC, but with native performance. It appears that te various ADO providers are, uh, inconsistent, and adodbapi makes various attempts to get something to work before giving up.

So it tries something, and if it fails, it goes to the next strategy.

It only gives up when it runs out of ideas.

I'm sure this is fantastic for performance. :-/

Skeleton logic:

    def executeHelper(self,operation,isStoredProcedureCall,parameters=None):        
        strategy=1 # The ways of different OLE-DB providers are unpredictable, had to use two
                   # strategies in order to make both Access and SQL Server work.
        tryAgain=1
        while tryAgain:
            try:                
                #various branching on strategy
                ...
                tryAgain=0
            except (Exception), e:
                tblist=traceback.format_exception(sys.exc_type,
                                                  sys.exc_value,
                                                  sys.exc_traceback,
                                                  8)
                tb=string.join(tblist)
                tracebackhistory+='\n-----------\nStrategy %i: Traceback:%s\n' %(strategy,tb)
                strategy+=1
                if strategy > 4:
                    tracebackhistory+='--- ADODBAPI on command:%s with parameters: %s' %(operation,parameters)
                    self._raiseCursorError(DatabaseError,tracebackhistory)
                    return

[jdunck@…]

And results of running requested code:

C:\temp\django>python
ActivePython 2.4.1 Build 247 (ActiveState Corp.) based on
Python 2.4.1 (#65, Jun 20 2005, 17:01:55) [MSC v.1310 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from django.core.db import db
>>> cursor = db.cursor()
>>>
>>> cursor.execute("SELECT 1")
>>> print cursor.fetchall()
((1,),)
>>>
>>> cursor.execute("SELECT 1 WHERE 1=%s", [1])
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "C:\Python24\lib\site-packages\django\core\db\base.py", line 10, in execu
te
    result = self.cursor.execute(sql, params)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 592, in execut
e
    self.executeHelper(operation,0,parameters)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 552, in execut
eHelper
    self._raiseCursorError(DatabaseError,tracebackhistory)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 407, in _raise
CursorError
    eh(self.conn,self,errorclass,errorvalue)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 38, in standar
dErrorHandler
    raise errorclass(errorvalue)
adodbapi.adodbapi.DatabaseError:
-----------
Strategy 1: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 539, in execu
teHelper
    raise DatabaseError(e)
 DatabaseError: (-2147352567, 'Exception occurred.', (0, 'ADODB.Parameters', 'It
em cannot be found in the collection corresponding to the requested name or ordi
nal.', 'C:\\WINNT\\HELP\\ADO270.CHM', 1240649, -2146825023), None)


-----------
Strategy 2: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 540, in execu
teHelper
    adoRetVal=self.cmd.Execute()
   File "<COMObject ADODB.Command>", line 3, in Execute
   File "C:\Python24\lib\site-packages\win32com\client\dynamic.py", line 251, in
 _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes
) + args)
 com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider
for SQL Server', "Line 1: Incorrect syntax near 's'.", None, 0, -2147217900), No
ne)


-----------
Strategy 3: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 539, in execu
teHelper
    raise DatabaseError(e)
 DatabaseError: (-2147352567, 'Exception occurred.', (0, 'ADODB.Parameters', 'It
em cannot be found in the collection corresponding to the requested name or ordi
nal.', 'C:\\WINNT\\HELP\\ADO270.CHM', 1240649, -2146825023), None)


-----------
Strategy 4: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 540, in execu
teHelper
    adoRetVal=self.cmd.Execute()
   File "<COMObject ADODB.Command>", line 3, in Execute
   File "C:\Python24\lib\site-packages\win32com\client\dynamic.py", line 251, in
 _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes
) + args)
 com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider
for SQL Server', "Line 1: Incorrect syntax near 's'.", None, 0, -2147217900), No
ne)

--- ADODBAPI on command:SELECT 1 WHERE 1=%s with parameters: [1]
>>> print cursor.fetchall()
[]
>>>
>>> cursor.execute("SELECT 1 WHERE 1=%s", (1,))
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "C:\Python24\lib\site-packages\django\core\db\base.py", line 10, in execu
te
    result = self.cursor.execute(sql, params)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 592, in execut
e
    self.executeHelper(operation,0,parameters)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 552, in execut
eHelper
    self._raiseCursorError(DatabaseError,tracebackhistory)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 407, in _raise
CursorError
    eh(self.conn,self,errorclass,errorvalue)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 38, in standar
dErrorHandler
    raise errorclass(errorvalue)
adodbapi.adodbapi.DatabaseError:
-----------
Strategy 1: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 539, in execu
teHelper
    raise DatabaseError(e)
 DatabaseError: (-2147352567, 'Exception occurred.', (0, 'ADODB.Parameters', 'It
em cannot be found in the collection corresponding to the requested name or ordi
nal.', 'C:\\WINNT\\HELP\\ADO270.CHM', 1240649, -2146825023), None)


-----------
Strategy 2: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 540, in execu
teHelper
    adoRetVal=self.cmd.Execute()
   File "<COMObject ADODB.Command>", line 3, in Execute
   File "C:\Python24\lib\site-packages\win32com\client\dynamic.py", line 251, in
 _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes
) + args)
 com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider
for SQL Server', "Line 1: Incorrect syntax near 's'.", None, 0, -2147217900), No
ne)


-----------
Strategy 3: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 539, in execu
teHelper
    raise DatabaseError(e)
 DatabaseError: (-2147352567, 'Exception occurred.', (0, 'ADODB.Parameters', 'It
em cannot be found in the collection corresponding to the requested name or ordi
nal.', 'C:\\WINNT\\HELP\\ADO270.CHM', 1240649, -2146825023), None)


-----------
Strategy 4: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 540, in execu
teHelper
    adoRetVal=self.cmd.Execute()
   File "<COMObject ADODB.Command>", line 3, in Execute
   File "C:\Python24\lib\site-packages\win32com\client\dynamic.py", line 251, in
 _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes
) + args)
 com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider
for SQL Server', "Line 1: Incorrect syntax near 's'.", None, 0, -2147217900), No
ne)

--- ADODBAPI on command:SELECT 1 WHERE 1=%s with parameters: (1,)
>>> print cursor.fetchall()
[]
>>>
>>> cursor.execute("SELECT 1 WHERE 1=%s AND 2=%s", (1, 2))
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "C:\Python24\lib\site-packages\django\core\db\base.py", line 10, in execu
te
    result = self.cursor.execute(sql, params)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 592, in execut
e
    self.executeHelper(operation,0,parameters)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 552, in execut
eHelper
    self._raiseCursorError(DatabaseError,tracebackhistory)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 407, in _raise
CursorError
    eh(self.conn,self,errorclass,errorvalue)
  File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 38, in standar
dErrorHandler
    raise errorclass(errorvalue)
adodbapi.adodbapi.DatabaseError:
-----------
Strategy 1: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 539, in execu
teHelper
    raise DatabaseError(e)
 DatabaseError: (-2147352567, 'Exception occurred.', (0, 'ADODB.Parameters', 'It
em cannot be found in the collection corresponding to the requested name or ordi
nal.', 'C:\\WINNT\\HELP\\ADO270.CHM', 1240649, -2146825023), None)


-----------
Strategy 2: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 540, in execu
teHelper
    adoRetVal=self.cmd.Execute()
   File "<COMObject ADODB.Command>", line 3, in Execute
   File "C:\Python24\lib\site-packages\win32com\client\dynamic.py", line 251, in
 _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes
) + args)
 com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider
for SQL Server', "Line 1: Incorrect syntax near 's'.", None, 0, -2147217900), No
ne)


-----------
Strategy 3: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 539, in execu
teHelper
    raise DatabaseError(e)
 DatabaseError: (-2147352567, 'Exception occurred.', (0, 'ADODB.Parameters', 'It
em cannot be found in the collection corresponding to the requested name or ordi
nal.', 'C:\\WINNT\\HELP\\ADO270.CHM', 1240649, -2146825023), None)


-----------
Strategy 4: Traceback:Traceback (most recent call last):
   File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 540, in execu
teHelper
    adoRetVal=self.cmd.Execute()
   File "<COMObject ADODB.Command>", line 3, in Execute
   File "C:\Python24\lib\site-packages\win32com\client\dynamic.py", line 251, in
 _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes
) + args)
 com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider
for SQL Server', "Line 1: Incorrect syntax near 's'.", None, 0, -2147217900), No
ne)

--- ADODBAPI on command:SELECT 1 WHERE 1=%s AND 2=%s with parameters: (1, 2)
>>> print cursor.fetchall()
[]
>>>
            

[Adrian Holovaty]

OK, with the debugging cap on, here's what's next to do --

• Verify the custom Cursor class in backends/ado_mssql.py is actually being used (probably using print statements).
• Verify the query in Cursor.executeHelper is being executed and is using correct parameter quoting.

[jdunck@…]

OK, Got it.

>>> import adodbapi
>>> adodbapi.paramstyle
'qmark'
>>> conn = adodbapi.connect(..nunya..)
>>> cur = conn.cursor()
>>> cur.execute('select 1 where 1=?', [1])
name, type = p0, 3
>>> r = cur.fetchone()
>>> r
(1,)

So, core.db needs to respect the backend's paramstyle.

I don't have time to make a patch right now...

[jdunck@…]

Arg, spoke too soon.

ado_mssql.py already has this in it:

# We need to use a special Cursor class because adodbapi expects question-mark
# param style, but Django expects "%s". This cursor converts question marks to
# format-string style.
class Cursor(Database.Cursor):
    def executeHelper(self, operation, isStoredProcedureCall, parameters=None):
        if parameters is not None and "%s" in operation:
            operation = operation.replace("%s", "?")
        Database.Cursor.executeHelper(self, operation, isStoredProcedureCall, parameters)

So that was a red herring.

Lemme poke some more.

[Adrian Holovaty]

Yeah, we need to verify that the custom executeHelper is actually getting called.

[jdunck@…]

Well, it's definitely not, but I'm not sure how it was intended to. :/

ado_mssql does:

import adodbapi as Database

class Connection(Database.Connection):
  ...
Database.Connection = Connection
...
  self.connection = Database.connect(conn_string)

wherein adodbapi does:

def connect(connstr):
...
    return Connection(conn)

class Connection:
  ...

That is, it looks like ado_mssql is trying to replace adodbapi's Connection with it's own, inherited class, but it's not actually working.

Any ideas how to fix it? I'm scratching my head.

[Adrian Holovaty]

Try this (in ado_mssql):

• Remove class Cursor, class Connection and Database.Connection = Connection.
• Add this:

old_executeHelper = Database.Cursor.executeHelper
def executeHelper(self, operation, isStoredProcedureCall, parameters=None):
    if parameters is not None and "%s" in operation:
        operation = operation.replace("%s", "?")
    old_executeHelperself, operation, isStoredProcedureCall, parameters)
Database.Cursor.executeHelper = executeHelper

[Adrian Holovaty]

My last comment has a typo. Should be this:

old_executeHelper = Database.Cursor.executeHelper
def executeHelper(self, operation, isStoredProcedureCall, parameters=None):
    if parameters is not None and "%s" in operation:
        operation = operation.replace("%s", "?")
    old_executeHelper(self, operation, isStoredProcedureCall, parameters)
Database.Cursor.executeHelper = executeHelper

[jdunck@…]

Yeah, I figured the typo out.

I guess it's not possible to reach into another module and change it's classes out? Probly better that way...

Latest test results attached.

[Adrian Holovaty]

Jeremy (and anybody else): What's the latest?

[Adrian Holovaty]

Closing this because somebody's working on SQL Server support.