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"/>
</parameterMap>
<procedure id="insert" parameterMap="insert_customer_param" >
{call p_customers_ins(?,?,?,?,?,?,?,?,?)}
</procedure>
<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"/>
</parameterMap>
<procedure id="insert" parameterMap="insert_customer_param" >
{call p_customers_ins(?,?,?,?,?,?,?,?,?)}
</procedure>
From: Cyril <[EMAIL PROTECTED]> [mailto:Cyril <[EMAIL PROTECTED]>]
Sent: Wednesday, September 20, 2006 7:24 AM
To: [EMAIL PROTECTED]
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.
Cyril
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.
Cyril
-----
Message d'origine ----
De : [EMAIL PROTECTED]
À : [EMAIL PROTECTED]; 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"
From: Cyril <[EMAIL PROTECTED]> [mailto:Cyril <[EMAIL PROTECTED]>]
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 !
Cyril
----- 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"
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:
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
De : [EMAIL PROTECTED]
À : [EMAIL PROTECTED]; 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:
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: user-java@ibatis.apache.org
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
À : user-java@ibatis.apache.org
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:
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
