Opened 10 years ago

Closed 9 years ago

Last modified 9 years ago

#644 closed defect (duplicate)

SQL Server DB backend: Rev 940 test results

Reported by: jdunck@… Owned by: adrian
Component: Database layer (models, ORM) Version:
Severity: normal Keywords: db ado sqlserver core test
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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

Attachments (2)

ado_mssql_test_results.zip (9.2 KB) - added by jdunck@… 10 years ago.
3rd attempt at attaching test results
ado_mssql_test_results_2.zip (4.4 KB) - added by jdunck@… 10 years ago.

Download all attachments as: .zip

Change History (17)

Changed 10 years ago by jdunck@…

3rd attempt at attaching test results

comment:1 Changed 10 years ago by adrian

  • Status changed from new to assigned

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()

comment:2 Changed 10 years ago by 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.

comment:3 Changed 10 years ago by 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

comment:4 Changed 10 years ago by 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

comment:5 Changed 10 years ago by 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()
[]
>>>
            

comment:6 Changed 10 years ago by adrian

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.

comment:7 Changed 10 years ago by 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...

comment:8 Changed 10 years ago by 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.

comment:9 Changed 10 years ago by adrian

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

comment:10 Changed 10 years ago by 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.

comment:11 Changed 10 years ago by adrian

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

comment:12 Changed 10 years ago by adrian

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

Changed 10 years ago by jdunck@…

comment:13 Changed 10 years ago by 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.

comment:14 Changed 10 years ago by adrian

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

comment:15 Changed 9 years ago by adrian

  • Resolution set to duplicate
  • Status changed from assigned to closed

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

Note: See TracTickets for help on using tickets.
Back to Top