Re: Passing SQL NULL to a stored procedure - throws an exception
Sébastien Sablé <[email protected]> Tue, 08 Sep 2009 19:04:42 +0200
| Newsgroups | gmane.comp.python.sybase |
|---|---|
| Message-ID | <[email protected]> |
Hi Jeffrey, this problem is specific to Sybase >=3D 15.0.2 and has been reported in a = previous bug tracker issue: http://sourceforge.net/tracker/?func=3Ddetail&aid=3D1771757&group_id=3D1840= 50&atid=3D907701 There is no easy solution at the module level. A workaround has been = proposed: """ The workaround to this is a bit problematic. Usually you just pass in a value and the module figures out the type from the value. But None has no value and no type. So you were defaulting to sending it as a CS_INT. Now the user has to construct and provide a DataBuf object as the parameter value and they have to explicitly construct a CS_DATAFMT for that DataBuf if they want to pass a NULL value. """ regards -- S=E9bastien Sabl=E9 Jeffrey Zelt a =E9crit : > Hi, > = > = > = > I need to pass SQL NULL for one of the input parameters to a stored = > procedure. This stored procedure is called via the usual: > = > = > = > cursor.callproc(=91storedprocname=92, parameterdictionary) > = > = > = > The usual way to pass SQL NULL using the Python DB-API is to pass None = > instead. However, this causes python-sybase to throw an exception, as = > shown here: > = > = > = > Traceback (most recent call last): > = > File = > "/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_data= _tests.py", = > line 100, in <module> > = > '@op_ClientNumber': Sybase.OUTPUT(1)}) > = > File "build/bdist.linux-i686/egg/Sybase.py", line 426, in callproc > = > File "build/bdist.linux-i686/egg/Sybase.py", line 703, in _start > = > File "build/bdist.linux-i686/egg/Sybase.py", line 717, in _mainloop > = > File "build/bdist.linux-i686/egg/Sybase.py", line 761, in _raise_er= ror > = > Sybase.DatabaseError: Msg 257, Level 16, State 1, Procedure = > qp_GUI_ClientInsert > = > Implicit conversion from datatype 'INT' to 'UNIVARCHAR' is not = > allowed. Use the CONVERT function to run this query. > = > = > = > For some reason, it triggers an =93Implicit conversion from datatype 'INT= ' = > to 'UNIVARCHAR' is not allowed=94 exception. For this particular case, = > the input parameter is of type UNIVARCHAR, but this problem is not = > particular to input parameters of only this type. For this particular = > case, SQL NULL is a legal value to pass to the stored procedure. > = > = > = > I have found one other reference to this issue here: > = > = > = > = > http://www.archivum.info/comp.databases.sybase/2007-08/00008/15.0.2_upgra= de_issue_wrt_passing_null_parameters_to_procedure > = > = > = > Is this a known problem? Is there a simple fix? Or am I doing = > something stupid? > = > = > = > Regards, > = > Jeffrey > = > = > = > = > ------------------------------------------------------------------------ > = > -------------------------------------------------------------------------= ----- > Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-D= ay = > trial. Simplify your report design, integration and deployment - and focu= s on = > what you do best, core application coding. Discover what's new with = > Crystal Reports now. http://p.sf.net/sfu/bobj-july > = > = > ------------------------------------------------------------------------ > = > _______________________________________________ > Python-sybase-misc mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/python-sybase-misc ---------------------------------------------------------------------------= --- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day = trial. Simplify your report design, integration and deployment - and focus = on = what you do best, core application coding. Discover what's new with = Crystal Reports now. http://p.sf.net/sfu/bobj-july