Passing SQL NULL to a stored procedure - throws an exception
"Jeffrey Zelt" <[email protected]> Tue, 8 Sep 2009 12:46:00 +0200
| Newsgroups | gmane.comp.python.sybase |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format.
--===============7025915361863756398==
Content-class: urn:content-classes:message
Content-Type: multipart/alternative;
boundary="----_=_NextPart_001_01CA3071.8E715F2D"
This is a multi-part message in MIME format.
------_=_NextPart_001_01CA3071.8E715F2D
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi,
=20
I need to pass SQL NULL for one of the input parameters to a stored
procedure. This stored procedure is called via the usual:
=20
cursor.callproc('storedprocname', parameterdictionary)
=20
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:
=20
Traceback (most recent call last):
File
"/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate_dat
a_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.
=20
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.
=20
I have found one other reference to this issue here:
=20
=20
http://www.archivum.info/comp.databases.sybase/2007-08/00008/15.0.2_upgr
ade_issue_wrt_passing_null_parameters_to_procedure
=20
Is this a known problem? Is there a simple fix? Or am I doing
something stupid?
=20
Regards,
Jeffrey
=20
------_=_NextPart_001_01CA3071.8E715F2D
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3DEN-GB link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal>Hi,<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>I need to pass SQL NULL for one of the input =
parameters to a
stored procedure. This stored procedure is called via the =
usual:<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal> =
cursor.callproc(‘storedprocname’,
parameterdictionary)<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>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:<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal> Traceback (most recent call =
last):<o:p></o:p></p>
<p class=3DMsoNormal> File
"/home/jeffreyz/workspace-3.5/generate-client-test-data/src/generate=
_data_tests.py",
line 100, in <module><o:p></o:p></p>
<p class=3DMsoNormal> =
'@op_ClientNumber':
Sybase.OUTPUT(1)})<o:p></o:p></p>
<p class=3DMsoNormal> File
"build/bdist.linux-i686/egg/Sybase.py", line 426, in =
callproc<o:p></o:p></p>
<p class=3DMsoNormal> File
"build/bdist.linux-i686/egg/Sybase.py", line 703, in =
_start<o:p></o:p></p>
<p class=3DMsoNormal> File
"build/bdist.linux-i686/egg/Sybase.py", line 717, in =
_mainloop<o:p></o:p></p>
<p class=3DMsoNormal> File
"build/bdist.linux-i686/egg/Sybase.py", line 761, in =
_raise_error<o:p></o:p></p>
<p class=3DMsoNormal> Sybase.DatabaseError: Msg 257, =
Level 16,
State 1, Procedure qp_GUI_ClientInsert<o:p></o:p></p>
<p class=3DMsoNormal> Implicit conversion from =
datatype 'INT'
to 'UNIVARCHAR' is not allowed. Use the CONVERT function to run =
this
query.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>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.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>I have found one other reference to this issue =
here:<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal> <a
href=3D"http://www.archivum.info/comp.databases.sybase/2007-08/00008/15.0=
.2_upgrade_issue_wrt_passing_null_parameters_to_procedure">http://www.arc=
hivum.info/comp.databases.sybase/2007-08/00008/15.0.2_upgrade_issue_wrt_p=
assing_null_parameters_to_procedure</a><o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Is this a known problem? Is there a simple =
fix?
Or am I doing something stupid?<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Regards,<o:p></o:p></p>
<p class=3DMsoNormal>Jeffrey<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
</div>
</body>
</html>
------_=_NextPart_001_01CA3071.8E715F2D--
--===============7025915361863756398==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
------------------------------------------------------------------------------
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
--===============7025915361863756398==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
Python-sybase-misc mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/python-sybase-misc
--===============7025915361863756398==--