ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher.Mathru...@sybase.com
Subject Setting parameters on a CallableStatement
Date Thu, 01 Feb 2007 00:14:36 GMT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.5730.11" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial size=2><SPAN class=177070600-01022007>This is the
first 
time I've ever come across this and it's causing me a bit of a headache. This is 
not an iBatis issue but rather a Sybase JConnect issue. (As I see 
it)</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN 
class=177070600-01022007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2><SPAN class=177070600-01022007>I have a stored
proc 
that I am attempting to execute to perform an insert. (32 parameters) All of 
which allow null to be specified except the first which is the OUT parameter 
that returned the new records ID.&nbsp; I had coded up the statement to use 
inline parameters, specifying the IN and OUT modes. Not all parameters are 
supplied so rather than specifying properties on my object I simply set those 
parameters to NULL literal. </SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN 
class=177070600-01022007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2><SPAN class=177070600-01022007>So my call would

look like the following:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=177070600-01022007>&nbsp; &nbsp;{call

p_ins_con(<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

#id,jdbcType=INTEGER,javaType=java.lang.Integer,mode=INOUT#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#addressFreeze,jdbcType=CHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#address1,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#address2,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#address3,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#address4,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#city,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#language,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#companyName,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#country.isoCode,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#county,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#createLogin,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#email,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , 
#fax1No,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , 
NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#fax2No,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , 
NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, 
#name,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , 
NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , 
#phone,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , 
NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , 
#postalCode,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , 
#state,jdbcType=VARCHAR,javaType=java.lang.String,mode=IN#<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

, NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , 
NULL<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , 
#active,jdbcType=CHAR,javaType=java.lang.Boolean,mode=IN#)<BR>&nbsp; 
&nbsp;}<BR></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN 
class=177070600-01022007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2><SPAN class=177070600-01022007>Each time I
executed 
this I would see an odd message from the driver stating:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=177070600-01022007>JZ0SG: A 
CallableStatement did not return<BR>as many output parameters as the application 
had registered for it.<BR></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=177070600-01022007>Very informative.
So 
I finally found a bit of information in their documentation that says the 
following:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=177070600-01022007><EM><STRONG>If
you 
execute a stored procedure in a CallableStatement object that represents 
parameter values as question marks, you get better performance than if you use 
both question marks and literal values for parameters. Further, if you mix 
literals and question marks, you cannot use output parameters with a stored 
procedure.</STRONG></EM><BR></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN 
class=177070600-01022007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2><SPAN class=177070600-01022007>So now I am
forced 
to use placeholders to populate the positional parameters. The only solution I 
have come up with so far is to keep specifying a given field on my object that 
is known to be null, but that is extremely ugly. Is there a better way to ensure 
that a NULL will be set for each parameter other than the way I've listed 
above?</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN 
class=177070600-01022007></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2><SPAN class=177070600-01022007>Thanks for the
help 
guys...</DIV></SPAN></FONT>
<DIV>&nbsp;</DIV>
<DIV align=left><FONT face=Arial size=2>Chris Mathrusse</FONT></DIV>
<DIV align=left><FONT face=Arial size=2><A 
href="mailto:christopher.mathrusse@sybase.com">christopher.mathrusse@sybase.com</A></FONT></DIV>
<DIV align=left><FONT face=Arial size=2>(925) 236-5553</FONT></DIV>
<DIV>&nbsp;</DIV></BODY></HTML>


Mime
View raw message