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

[M.-A. Lemburg]

Henrik Ekelund wrote:
> I tried the statement using adodbapi and the Microsoft OLE-DB Driver and got
> the same error,
> so it probably is not ODBC related. 

Well, I guess that the ADO or OLE drivers use the ODBC driver
under the covers (the error messages hint at that), so it
is related to the MS SQL ODBC driver.

BTW, the reason you don't get the error using the simplistic
odbc module is that the latter binds *all* parameters as
strings and doesn't use the SQLDescribeParam() API which triggers
the error at all.

I wonder whether passing a string instead of a DateTime value
will give you the same error message ?!

> The error code in OLE-DB is 80004005, so you can try to search for that. 
> 
> You can take a look at http://www.aspfaq.com/show.asp?id=2009
> 
> /Henrik Ekelund
> 
> 
> 
>>-----Original Message-----
>>From: Andy Todd [mailto:andy47@halfcooked.com]
>>Sent: den 10 mars 2003 17:47
>>To: db-sig
>>Subject: [DB-SIG] ODBC parameters and T-SQL functions
>>
>>
>>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/
>>
>>_______________________________________________
>>DB-SIG maillist  -  DB-SIG@python.org
>>http://mail.python.org/mailman/listinfo/db-sig
>>
> 
> 
> 
> **********************************************************************
> This email message has been swept by
> MIMEsweeper for the presence of computer viruses.
> 
> www.mimesweeper.com
> **********************************************************************
> 
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Mar 10 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
Python UK 2003, Oxford:                                     22 days left
EuroPython 2003, Charleroi, Belgium:                       106 days left