Bobspadger / pyodbc

Automatically exported from code.google.com/p/pyodbc
MIT No Attribution
0 stars 0 forks source link

07009 null object in store procedure #208

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago
What steps will reproduce the problem?
1. On server create store procedure which as a one parameter might takes Null 
(None python)
2. On client side execute this orocedure as: cursor.execute (proc_name 
,params_tuple)

one or more elements in patams_tupe is None

What is the expected output? What do you see instead?
Resoults of working proceudre 

What version of the product are you using? On what operating system?
I was trying to do this on pyOdbc 2.1.7 and 2.1.8
python 2.6.4

Please provide any additional information below.
On Linux machines works OK, on Windows Server 2008 not.

Error: ('07009', '[07009] [Microsoft][ODBC SQL Server Driver]Invalid Descriptor 
Index (0) (SQLDescribeParam); [07009] [Microsoft][ODBC SQL Server 
Driver]Invalid parameter number (0)')

Tracing from ODBC in attachments.

Original issue reported on code.google.com by chandij....@gmail.com on 6 Sep 2011 at 5:58

Attachments:

GoogleCodeExporter commented 9 years ago
Not a very good error message, but it is because you are passing more parameter 
values than parameters in the SQL.  You need '?' markers in your SQL for where 
you want the parameters to go.  Cursor.execute is generic and is not just for 
stored procedures.

You can see an example of this in the sqlservertests.py test_sp_with_none.  You 
want something like this:
  cursor.execute("{call proc_name(?)", params)
or 
  cursor.execute("{call proc_name(?)", value1, value2, ...)

This is the ODBC escape syntax that should work for any database.  (The driver 
is responsible for converting it to the DB's specific syntax.): 
http://msdn.microsoft.com/en-us/library/ms710100(v=vs.85).aspx

You can also use your DB's syntax directly.  For SQL Server you can use:
  cursor.execute("exec proc_name ?", params)

Original comment by mkleehammer on 13 Sep 2011 at 10:36