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 stored procedure "Incorrect syntax near the keyword"
Date Tue, 19 Sep 2006 15:03:23 GMT
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META charset=iso-8859-1 plain; text Content-Type: quoted-printable 
Content-Transfer-Encoding:>
<META content="MSHTML 6.00.2900.2963" name=GENERATOR></HEAD>
<BODY>
<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 <CYRIL_JADE@YAHOO.FR><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><RESULTMAP 
class=com.calyon.ccc.ibatis.pojo.DeliveryUtilizationLineFromDatabase 
id=deliveryDetailResult><BR><RESULT column="Id" property="id" /><BR><RESULT

column="Utilisation" property="amount" /><BR><RESULT column="KeyName" 
property="keyName" /><BR><RESULT column="KeyValue" property="authorizationId"

/><BR><RESULT column="Date" property="utilizationDate" 
/><BR></RESULTMAP><BR><BR><PROCEDURE id=getDetailedDeliveryUtilizations2

resultMap="deliveryDetailResult"><BR><![CDATA[
<BR>{call CCCGetDeliveryUtilization("TODAY", "Profile", "Client", "1234567890", 
"All", "N")} <BR>]]&gt; <BR></PROCEDURE><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></BODY></HTML>


Mime
View raw message