ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher.Mathru...@sybase.com
Subject RE: Problem when executing multi result Sybase stored procedure
Date Wed, 20 Sep 2006 15:28:35 GMT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META boundary="0-1321285905-1158762238=:16410" alternative; multipart 
Content-Type:>
<META charset=iso-8859-1 Content-Type: --0-1321285905-1158762238=":16410" html; 
text quoted-printable Content-Transfer-Encoding:>
<META content="MSHTML 6.00.2900.2963" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=136231915-20092006><FONT color=#0000ff

size=2>One thing I don't understand, looking at your SQL Map, why do you have 
the call to the sp in CDATA tags? I'm not using CDATA tags and the call to the 
sp works well for me.&nbsp;Also, remember that all SQL enclosed within 
&lt;procedure&gt;, &lt;insert&gt;, &lt;update&gt; and 
&lt;delete&gt;&nbsp;elements, is treated as SQL, literally. So your String 
literal parameters that you are passing to the sp should be in single quotes, 
not double quotes. I don't know if this will help, but it is definitely worth a 
try.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=136231915-20092006><FONT color=#0000ff

size=2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=136231915-20092006><FONT color=#0000ff

size=2>Below is my SQL Map that functions correctly. I hope this 
helps.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=136231915-20092006><FONT color=#0000ff

size=2></FONT></SPAN>&nbsp;</DIV>
<DIV dir=ltr align=left><SPAN class=136231915-20092006><FONT color=#0000ff

size=2>&nbsp; &lt;parameterMap id="insert_customer_param" 
class="Customer"&gt;&nbsp; &nbsp;&nbsp;&nbsp;<BR>&nbsp; 
&nbsp;&nbsp;&lt;parameter property="name" jdbcType="VARCHAR" 
javaType="java.lang.String" mode="IN"/&gt;<BR>&nbsp; &nbsp;&nbsp;&lt;parameter

property="parentCustomer.id" jdbcType="INTEGER" javaType="java.lang.String" 
mode="IN"/&gt;<BR>&nbsp; &nbsp;&nbsp;&lt;parameter property="customerClass.id"

jdbcType="CHAR" javaType="java.lang.String" mode="IN"/&gt;<BR>&nbsp; 
&nbsp;&nbsp;&lt;parameter property="homeCompany.id" jdbcType="VARCHAR" 
javaType="java.lang.String" mode="IN"/&gt;<BR>&nbsp; &nbsp;&nbsp;&lt;parameter

property="customerCode" jdbcType="VARCHAR" javaType="java.lang.String" 
mode="IN"/&gt;<BR>&nbsp; &nbsp;&nbsp;&lt;parameter property="sicIndustryCode"

jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/&gt;<BR>&nbsp; 
&nbsp;&nbsp;&lt;parameter property="totalCreditLimit" jdbcType="DECIMAL" 
javaType="java.math.BigDecimal" mode="IN"/&gt;<BR>&nbsp; 
&nbsp;&nbsp;&lt;parameter property="creLimitExpires" jdbcType="TIMESTAMP" 
javaType="java.util.Date" mode="IN"/&gt;<BR>&nbsp; &nbsp;&nbsp;&lt;parameter

property="id" jdbcType="VARCHAR" javaType="java.lang.String" 
mode="OUT"/&gt;<BR>&nbsp; &lt;/parameterMap&gt;<BR>&nbsp;<BR>&nbsp;

&lt;procedure id="insert" parameterMap="insert_customer_param" &gt;<BR>&nbsp;

&nbsp;{call p_customers_ins(?,?,?,?,?,?,?,?,?)}<BR>&nbsp; 
&lt;/procedure&gt;</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> Cyril &lt;cyril_jade@yahoo.fr&gt;

[mailto:Cyril &lt;cyril_jade@yahoo.fr&gt;] <BR><B>Sent:</B> Wednesday,
September 
20, 2006 7:24 AM<BR><B>To:</B> 
Christopher.Mathrusse@sybase.com<BR><B>Subject:</B> Re : Problem when executing

multi result Sybase stored procedure<BR></FONT><BR></DIV>
<DIV></DIV>
<STYLE type=text/css>DIV {
	MARGIN: 0px
}
</STYLE>

<DIV 
style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman, new york, times, serif">
<DIV 
style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman,new york,times,serif">Thanks 
for your help, Christopher.<BR><BR>Unfortunetely, your proposition doesn't solve

my problem. I have still the same strange exception. It's now throwned at the 
instruction "cs.execute();" (execute of callable statement ), in the 
SqlExecutor.<BR><BR>Since I can't spend more time on this problem, I will call

this procedure using plain JDBC (with Spring). When I will be able to change the 
stored procedure itself, everything should be alright and I'll change my JDBC 
implementation with the iBatis one.<BR><BR>Cyril<BR><BR>
<DIV 
style="FONT-SIZE: 12pt; FONT-FAMILY: times new roman,new york,times,serif">----- 
Message d'origine ----<BR>De : Christopher.Mathrusse@sybase.com<BR>À : 
cyril_jade@yahoo.fr; user-java@ibatis.apache.org<BR>Envoyé le : Mardi, 19 
Septembre 2006, 5h03mn 23s<BR>Objet&nbsp;: RE: Problem when executing stored 
procedure "Incorrect syntax near the keyword"<BR><BR>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006>At first sight this appears to be an ASE problem but it 
actually isn't. By definition ASE can return multiple result sets from an sp, or 
trigger for that matter. So even if you are expecting a single result set from 
an insert or update statement, there can be multiple.&nbsp;These are called 
"<STRONG>done-in-proc</STRONG>" result sets. If you execute a sp, that sp can
in 
turn execute another sp, and so on, and so on.... There can be any number of 
done-in-proc result sets. Even worse, if you execute a sp that executes another, 
and another and an someone down the chain raises an error, when it returns in 
your Java code you will not see the exception. That is because the raise error 
resides within the last result set. Only if you process all result sets will you 
see the exception. (Nice, huh?) </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006></SPAN></FONT>&nbsp;</DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006>I feel your pain as I had to spend the time researching 
this when I started work with ASE. The documentation is hard to find unless 
someone points you in the right direction. So here is the little secret that you 
need to know. You need to set a variable in your JDBC driver. 
(IGNORE_DONE_IN_PROC=true) </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006></SPAN></FONT>&nbsp;</DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006>You can call this on the driver 
directly:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006><STRONG>setIGNORE_DONE_IN_PROC(true)</STRONG> 
</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006></SPAN></FONT>&nbsp;</DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006>or you can pass it in on the URL :</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006><STRONG>jdbc:sybase:Tds:MyDBServer:4801/myDb?IGNORE_DONE_IN_PROC=true</STRONG>

</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006></SPAN></FONT>&nbsp;</DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006>or you can set it in the Properties object that is 
passed into the driver when you request a 
Connection:<BR><STRONG>DriverManager.getConnection(url, 
properties)</STRONG></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006><STRONG></STRONG></SPAN></FONT>&nbsp;</DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006>There&nbsp;is also a global property that you can set 
on ASE so that the <STRONG>done-in-proc </STRONG>result sets are always ignored,

but that might have an impact on existing applications. </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006></SPAN></FONT>&nbsp;</DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2><SPAN 
class=941504914-19092006></SPAN></FONT><FONT color=#0000ff><FONT size=2>B<SPAN

class=941504914-19092006>y setting this value to true the database will only 
return the last result set, ignoring all the <STRONG>done-in-proc</STRONG> 
result sets. This should get you past your problem.I hope this 
helps....</SPAN></FONT></FONT><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> Cyril &lt;cyril_jade@yahoo.fr&gt;

[mailto:Cyril &lt;cyril_jade@yahoo.fr&gt;] <BR><B>Sent:</B> Tuesday,
September 
19, 2006 1:13 AM<BR><B>To:</B> user-java@ibatis.apache.org<BR><B>Subject:</B>
Re 
: Problem when executing stored procedure "Incorrect syntax near the 
keyword"<BR></FONT><BR></DIV>
<DIV></DIV>It seems that this stored procedure has a multi-result set result.

The version of iBatis we are currently using doesn't work with such procedure, 
but I see the latest release supports this functionnality. <BR><BR>Time to 
update our version of iBatis ! <BR><BR>Cyril <BR><BR>----- Message
d'origine 
---- <BR>De : Cyril <BR>À : user-java@ibatis.apache.org <BR>Envoyé le :
Lundi, 
18 Septembre 2006, 6h02mn 31s <BR>Objet : Problem when executing stored 
procedure "Incorrect syntax near the keyword" <BR><BR>Hi! <BR><BR>I'm
migrating 
an application from an internal JDBC framework to iBatis. Currently, a lot of 
the business work is done in stored procedure, so I just need to call the stored 
procedures from DAO with iBatis. <BR><BR>Until now, I hadn't any major issue,

but when calling a particular stored procedure, I was stucked with an 
SQLException with my sqlMap.queryForList. The exception is: 
<BR><BR>com.sybase.jdbc2.jdbc.SybSQLException: Incorrect syntax near the keyword

'from'. <BR><BR>(my data base is Sybase) <BR><BR>It doesn't look an
iBatis 
problem, but when I call the same procedure with the same parameters from JDBC 
or from my SQL client, everything is ok. <BR><BR>My iBatis sql map file 
contains: <BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR><BR>It's
just 
a procedure call, there is no "from" in it. Since I can call the stored 
procedure with these parameters from JDBC or SQL client, I assume there is also 
no problem in the stored procedure (which contains "from"). <BR><BR>My DAO looks

like: <BR>List result = null; <BR>try { <BR>result = 
sqlMap.queryForList("getDetailedDeliveryUtilizations2", new ArrayList()); <BR>} 
catch (SQLException sqle) { <BR>LOGGER.error("SQLException " + 
sqle.getMessage(), sqle); <BR>} <BR><BR>Since I traced with the debugger
quite 
deep in iBatis, I can't understand the problem. It should not come from iBatis, 
but does somebody have an idea? <BR><BR>Thanks, <BR>Cyril Gambis 
<BR><BR><BR><BR><BR><BR><BR><BR></DIV><BR></DIV></DIV></BODY></HTML>


Mime
View raw message