Re: [Python-sybase-misc] Passing SQL NULL to a stored procedure - throws an exception

[Sébastien S. <sa...@us...>]

Hi Jeffrey,

this problem is specific to Sybase >= 15.0.2 and has been reported in a 
previous bug tracker issue:

http://sourceforge.net/tracker/?func=detail&aid=1771757&group_id=184050&atid=907701

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ébastien Sablé

Jeffrey Zelt a écrit :
> 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(‘storedprocname’, 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_error
> 
>     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 “Implicit conversion from datatype 'INT' 
> to 'UNIVARCHAR' is not allowed” 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_upgrade_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-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
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Python-sybase-misc mailing list
> Pyt...@li...
> https://lists.sourceforge.net/lists/listinfo/python-sybase-misc