Hi again,

The CDATA tags are not useful here, it's true. But it's a habit I took after spending some time debugging a query containing a "<" (less than). I did try without CDATA and the effects are the same in my problem.

I use other stored procedures also, and they work. The problem arise just for this one, and since I suspect that this query is some kind of multi result (but it's not so clear... there is a exec(@strQuery1+@strQuery2), but strQuery1 and 2 are VERY complicated, and my knowledge of Sybase ASE stored procedures is very limited), I think I'll rewrite it, simplifying and putting the major part of the algorithmics in JAVA.


----- Message d'origine ----
De : Christopher.Mathrusse@sybase.com
À : cyril_jade@yahoo.fr
Cc : user-java@ibatis.apache.org
Envoyé le : Mercredi, 20 Septembre 2006, 5h28mn 35s
Objet : RE: Problem when executing multi result Sybase stored procedure

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. Also, remember that all SQL enclosed within <procedure>, <insert>, <update> and <delete> 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.
Below is my SQL Map that functions correctly. I hope this helps.
  <parameterMap id="insert_customer_param" class="Customer">     
    <parameter property="name" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
    <parameter property="parentCustomer.id" jdbcType="INTEGER" javaType="java.lang.String" mode="IN"/>
    <parameter property="customerClass.id" jdbcType="CHAR" javaType="java.lang.String" mode="IN"/>
    <parameter property="homeCompany.id" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
    <parameter property="customerCode" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
    <parameter property="sicIndustryCode" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
    <parameter property="totalCreditLimit" jdbcType="DECIMAL" javaType="java.math.BigDecimal" mode="IN"/>
    <parameter property="creLimitExpires" jdbcType="TIMESTAMP" javaType="java.util.Date" mode="IN"/>
    <parameter property="id" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
  <procedure id="insert" parameterMap="insert_customer_param" >
   {call p_customers_ins(?,?,?,?,?,?,?,?,?)}

From: Cyril <cyril_jade@yahoo.fr> [mailto:Cyril <cyril_jade@yahoo.fr>]
Sent: Wednesday, September 20, 2006 7:24 AM
To: Christopher.Mathrusse@sybase.com
Subject: Re : Problem when executing multi result Sybase stored procedure

Thanks for your help, Christopher.

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.

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.


----- Message d'origine ----
De : Christopher.Mathrusse@sybase.com
À : cyril_jade@yahoo.fr; user-java@ibatis.apache.org
Envoyé le : Mardi, 19 Septembre 2006, 5h03mn 23s
Objet : RE: Problem when executing stored procedure "Incorrect syntax near the keyword"

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:
or you can pass it in on the URL :
or you can set it in the Properties object that is passed into the driver when you request a Connection:
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 <cyril_jade@yahoo.fr> [mailto:Cyril <cyril_jade@yahoo.fr>]
Sent: Tuesday, September 19, 2006 1:13 AM
To: user-java@ibatis.apache.org
Subject: Re : Problem when executing stored procedure "Incorrect syntax near the keyword"

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.

Time to update our version of iBatis !


----- Message d'origine ----
De : Cyril
À : user-java@ibatis.apache.org
Envoyé le : Lundi, 18 Septembre 2006, 6h02mn 31s
Objet : Problem when executing stored procedure "Incorrect syntax near the keyword"


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:

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?

Cyril Gambis