[DB-SIG] ODBC parameters and T-SQL functions

[Andy Todd]

Afternoon all. I am writing a script in Python 2.2 using mxODBC talking to SQL Server 2000.

I'm getting errors when I pass an mxDateTime object as a parameter to a SQL statement. But its only when the parameter is subsequently used in a T-SQL function, specifically DATEDIFF. Elsewhere (for instance in a where clause) I can pass this same value in without a hitch.

Has anyone seen this before? The reason I ask is because this works with the win32all odbc package but not with mxODBC. Here is an echo of my interactive session;

>>> from mx import ODBC
>>> from mx import DateTime
>>> db=ODBC.Windows.connect('Wine')
>>> cursor=db.cursor()
>>> myDate=DateTime.DateFrom('31-DEC-2002')
>>> stmt = "SELECT share_number ,datediff(Month, date_of_election, ?) FROM member_details"
>>> cursor.execute(stmt, (myDate,))
ceback (most recent call last):
  File "<interactive input>", line 1, in ?
ProgrammingError: ('37000', 0, '[Microsoft][ODBC SQL Server Driver]Syntax error or access violation', 4498)
>>>
>>> db.close()
>>> import odbc
>>> db=odbc.odbc('Wine')
>>> cursor=db.cursor()
>>> cursor.execute(stmt, (myDate,))
0
>>> results=cursor.fetchone()
>>> results
(1, 338)
>>> 

What am I doing wrong?

Regards,
Andy
-- 
--------------------------------------------------------------------------------
>From the desk of Andrew J Todd esq - http://www.halfcooked.com/