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. These are called
"done-in-proc" 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?)
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)
You can call this on the driver
directly:
setIGNORE_DONE_IN_PROC(true)
or you can pass it in on the URL :
jdbc:sybase:Tds:MyDBServer:4801/myDb?IGNORE_DONE_IN_PROC=true
or you can set it in the Properties object that is
passed into the driver when you request a
Connection:
DriverManager.getConnection(url, properties)
DriverManager.getConnection(url, properties)
There is also a global property that you can set
on ASE so that the done-in-proc result sets are always ignored,
but that might have an impact on existing applications.
By setting this value to true the database will only
return the last result set, ignoring all the done-in-proc
result sets. This should get you past your problem.I hope this
helps....
From: Cyril <[EMAIL PROTECTED]> [mailto:Cyril <[EMAIL PROTECTED]>]
Sent: Tuesday, September 19, 2006 1:13 AM
To: [email protected]
Subject: Re : Problem when executing stored procedure "Incorrect syntax near the keyword"
Time to update our version of iBatis !
Cyril
----- Message d'origine ----
De : Cyril <[EMAIL PROTECTED]>
À : [email protected]
Envoyé le : Lundi, 18 Septembre 2006, 6h02mn 31s
Objet : Problem when executing stored procedure "Incorrect syntax near the keyword"
Hi!
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.
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:
com.sybase.jdbc2.jdbc.SybSQLException: Incorrect syntax near the keyword 'from'.
(my data base is Sybase)
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.
My iBatis sql map file contains:
{call CCCGetDeliveryUtilization("TODAY", "Profile", "Client", "1234567890", "All", "N")}
]]>
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").
My DAO looks like:
List result = null;
try {
result = sqlMap.queryForList("getDetailedDeliveryUtilizations2", new ArrayList());
} catch (SQLException sqle) {
LOGGER.error("SQLException " + sqle.getMessage(), sqle);
}
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?
Thanks,
Cyril Gambis
