ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Cyril <cyril_j...@yahoo.fr>
Subject Re : Problem when executing multi result Sybase stored procedure
Date Thu, 21 Sep 2006 08:23:51 GMT
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"
    <parameter property="customerClass.id"  jdbcType="CHAR" javaType="java.lang.String"
     <parameter property="homeCompany.id" jdbcType="VARCHAR"  javaType="java.lang.String"
    <parameter  property="customerCode" jdbcType="VARCHAR" javaType="java.lang.String"
    <parameter property="sicIndustryCode"  jdbcType="VARCHAR" javaType="java.lang.String"
     <parameter property="totalCreditLimit" jdbcType="DECIMAL"  javaType="java.math.BigDecimal"
     <parameter property="creLimitExpires" jdbcType="TIMESTAMP"  javaType="java.util.Date"
    <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

 DIV { MARGIN:0px;}    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.
 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  

View raw message