ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From andrew.sc...@jpmchase.com
Subject Re: SQLMaps - Sybase and stored procedures
Date Tue, 09 Aug 2005 14:57:17 GMT
Yes, I have tried that one too :)

Do you think this may be down to the jdbc driver I am using, as I assume
that you all are getting this working without
the same issues that I have.
Is anyone else using Sybase ASE?

thanks,
andy





                                                                                         
                                                  
                      Ed Griebel                                                         
                                                  
                      <edgriebel@gmail.        To:       user-java@ibatis.apache.org  
                                                     
                      com>                     cc:                                    
                                                     
                                               Subject:  Re: SQLMaps - Sybase and stored procedures
                                        
                      09/08/2005 15:52                                                   
                                                  
                      Please respond to                                                  
                                                  
                      user-java                                                          
                                                  
                                                                                         
                                                  
                                                                                         
                                                  




We use something like this where we return a value:

<parameterMap id="myProc" class="java.util.HashMap">
  <parameter property="result" javaType="string" jdbcType="STRING"
mode="OUT"/>
 <... rest of parameters...>
</parameterMap>
<procedure id="myProc" parameterMap="myProc">
  {? = call myProc(?, ?); }
</procedure>

HTH,
-ed

On 8/9/05, andrew.scott@jpmchase.com <andrew.scott@jpmchase.com> wrote:
> Hi there.
> I can only see queryForList() and queryForObject() in the API docs.
> How do you force an executeQuery() instead of the
execute()/getResultset()
> option?
>
> andy
>
>
>
>
>
>
>                       "Priyesh
>                       Mashelkar"               To:
<user-java@ibatis.apache.org>
>                       <priyeshm@mastek.        cc:
>                       com>                     Subject:  RE: SQLMaps -
Sybase and stored procedures
>
>                       09/08/2005 15:01
>                       Please respond to
>                       user-java
>
>
>
>
>
>
> Voila, You are doing a executeQuery(), while iBATIS uses execute() and
> getResultSet().
> Try with the later combination.
>
>
> -----Original Message-----
> From: andrew.scott@jpmchase.com [mailto:andrew.scott@jpmchase.com]
> Sent: Tuesday, August 09, 2005 7:20 PM
> To: user-java@ibatis.apache.org
> Subject: RE: SQLMaps - Sybase and stored procedures
>
>
> If you mean just use regular jdbc and callable statements, I have tried
> that and it runs fine:
>
> (from original post)
>
> CallableStatement cstmt = conn.prepareCall("{call language_lookup
> @from_text = 'Computers', @language = 'FRE'}");
> ResultSet rs = cstmt.executeQuery();
>
> rs.next();
> String output = (String) rs.getString(1);
> System.out.println("Output = " + output);
>
>
>
> The output is ----->
> Output = Ordinateurs
>
>
>
> thanks,
> andy
>
>
>
>
>
>
>
>
>                       "Priyesh
>
>                       Mashelkar"               To:
> <user-java@ibatis.apache.org>
>
>                       <priyeshm@mastek.        cc:
>
>                       com>                     Subject:  RE: SQLMaps -
> Sybase and stored procedures
>
>                       09/08/2005 14:45
>
>                       Please respond to
>
>                       user-java
>
>
>
>
>
>
>
> Have you tried using a plain JDBC connection and running the procedure
> using it.
> Use execute and get resultset from the callablestatement used for
executing
> the procedure.
> If the resultset returned is null, I am afraid we are sailing in  the
same
> boat. Only difference is i am selecting data from a temporary table in
the
> stored procedure.
>
> If you find any solution, do let me know.
>
> -----Original Message-----
> From: andrew.scott@jpmchase.com [mailto:andrew.scott@jpmchase.com]
> Sent: Tuesday, August 09, 2005 7:08 PM
> To: user-java@ibatis.apache.org
> Subject: RE: SQLMaps - Sybase and stored procedures
>
>
> I'm afraid that neither
>
>       {call language_lookup (?, ?)}
> nor
>       {call language_lookup @from_text, @language}
>
> seems to make any difference.
>
> As long as I add the 'resultClass' or 'resultMap' attribute to the
> <procedure> tag I get a Null Pointer Exception.
>
> If I miss out these attributes from the tag, the code runs but the result
> is null :(
>
> I wondered whether these sql calls would run in an ordinary jdbc client,
so
> I downloaded Aqua Data Studio and the sql is fine from Java.
>
> Unfortunately, I am only allowed to use stored procedures, as the DBA
> prevents me from using standard 'Selects' et al.
> The theory is that by forcing the developers to use stored procs, this
> prevents any accidental deleting of data.
>
> andy
>
>
>
>
>
>
>
>
>
>                       "Priyesh
>
>                       Mashelkar"               To:
> <user-java@ibatis.apache.org>
>
>                       <priyeshm@mastek.        cc:
>
>                       com>                     Subject:  RE: SQLMaps -
> Sybase and stored procedures
>
>                       09/08/2005 14:25
>
>                       Please respond to
>
>                       user-java
>
>
>
>
>
>
>
> Sorry, I gave you wrong info.
> Use              {call language_lookup (?, ?)} .
>
>
> -----Original Message-----
> From: andrew.scott@jpmchase.com [mailto:andrew.scott@jpmchase.com]
> Sent: Tuesday, August 09, 2005 6:25 PM
> To: user-java@ibatis.apache.org
> Subject: Re: SQLMaps - Sybase and stored procedures
>
>
> Thanks for the info guys, but it doesnt seem to have solved the problem.
>
> I updated my procedure to :
>
>       <procedure id="languageLookup"  parameterMap="languageLookupMap"
> resultClass="java.lang.String">
>              {call language_lookup @from_text = ?, @language = ?}
>       </procedure>
>
>
> Now I get the following stack trace:
>
>
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in sqlmap/TestSqlMap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the TestNameSpace.languageLookupMap.
> --- Check the results (failed to retrieve results).
> --- Cause: java.lang.NullPointerException
> Caused by: java.lang.NullPointerException
>       at
>
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)

>
>
>       at
>
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)

>
>
>
>       at
>
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)

>
>
>
>       at
>
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)

>
>
>
>       at
>
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:101)

>
>
>
>       at
>
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:78)

>
>
>
>       at test.TestStoredProcCall.main(TestStoredProcCall.java:23)
>
>
>
> Any ideas?
>
> andy
>
>
>
>
>
>
>                       Larry Meadors
>
>                       <larry.meadors@gm        To:
> user-java@ibatis.apache.org
>
>                       ail.com>                 cc:
>
>                                                Subject:  Re: SQLMaps -
> Sybase and stored procedures
>                       09/08/2005 12:01
>
>                       Please respond to
>
>                       user-java
>
>
>
>
>
>
>
> It's true: Without a resultMap or resultClass, your results will be
> silently discarded.
>
> Larry
>
>
> On 8/9/05, Priyesh Mashelkar <priyeshm@mastek.com> wrote:
> > Use a resultMap or resultClass as you would do for a normal query.
> > They have missed mention of procedures returning values in their
> documentation.
> >
> > Your mapping would look like:
> >
> > <parameterMap id="languageLookupMap" class="java.util.HashMap">
> >       <parameter javaType="java.lang.String" mode="IN"
jdbcType="VARCHAR"
> > property="from_text"/>
> >       <parameter javaType="java.lang.String" mode="IN"
jdbcType="VARCHAR"
> > property="language"/>
> > </parameterMap>
> >
> > <procedure id="languageLookup"  parameterMap="languageLookupMap"
> resultClass="java.lang.String">
> >       {call language_lookup @from_text = ?, @language = ?}
> > </procedure>
> >
> > The call remains the same. i.e.,       {call language_lookup @from_text
=
> ?, @language = ?}.
> >
> >
> > -----Original Message-----
> > From: andrew.scott@jpmchase.com [mailto:andrew.scott@jpmchase.com]
> > Sent: Tuesday, August 09, 2005 4:21 PM
> > To: user-java@ibatis.apache.org
> > Subject: SQLMaps - Sybase and stored procedures
> >
> >
> > Hi,
> >
> > I have a question regarding the use of stored procedures (in particular
> > Sybase) with SQLMaps.
> > Having looked at the documentation I am still unsure as the
> documentation,
> > unless I have missed it, doesnt deal with getting results back from
> > the proc.
> >
> > I wish to call a Sybase stored procedure, passing into two parameters
and
> > retrieving a String value in a resultset.
> > The proc, which provides a language translation, takes two String
> > parameters (the text you wish translated, and the language
> > you wish translated to) and then returns a String value of the
translated
> > text.
> >
> >
> >
> > My sqlmap is as follows:
> >
> > <parameterMap id="languageLookupMap" class="java.util.HashMap">
> >       <parameter javaType="java.lang.String" mode="IN"
jdbcType="VARCHAR"
> > property="from_text"/>
> >       <parameter javaType="java.lang.String" mode="IN"
jdbcType="VARCHAR"
> > property="language"/>
> > </parameterMap>
> >
> > <procedure id="languageLookup"  parameterMap="languageLookupMap">
> >       {call language_lookup @from_text = ?, @language = ?}
> > </procedure>
> >
> >
> >
> > My code is as follows:
> >
> > HashMap map = new HashMap();
> > map.put("from_text", "Computers");
> > map.put("language", "FRE");
> >
> > ArrayList results = (ArrayList)
sqlMap.queryForList("testLanguageLookup",
> > map);
> > iter = results.iterator();
> > System.out.println("results = " + results.toString());
> >
> >
> > When I run the code, the output is always "result = []".
> >
> >
> >
> >
> >
> > However, if I run the following regular jdbc code, I get a valid
result:
> >
> > CallableStatement cstmt = conn.prepareCall("{call language_lookup
> > @from_text = 'Computers', @language = 'FRE'}");
> > ResultSet rs = cstmt.executeQuery();
> >
> > System.out.println("Resultset = " + rs.toString());
> > rs.next();
> > String output = (String) rs.getString(1);
> > System.out.println("Output = " + output);
> >
> >
> >
> > The output is ----->
> >
> > Resultset = com.sybase.jdbc2.jdbc.SybResultSet@5fc75b3
> > Output = Ordinateurs
> >
> >
> >
> >
> > Can anyone give me some tips to get the same eresults with SQLMaps as I
> get
> > with regular jdbc code?
> >
> > Thanks in advance,
> > andy
> >
> >
> >
> >
> > MASTEK
> > "Making a valuable difference"
> > Mastek in NASSCOM's 'India Top 20' Software Service Exporters List.
> > In the US, we're called MAJESCO
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>
>
>
>
> > Opinions expressed in this e-mail are those of the individual and not
> that of Mastek Limited, unless specifically indicated to that effect.
> Mastek Limited does not accept any responsibility or liability for it.
This
> e-mail and attachments (if any) transmitted with it are confidential
and/or
> privileged and solely for the use of the intended person or entity to
which
> it is addressed. Any review, re-transmission, dissemination or other use
of
> or taking of any action in reliance upon this information by persons or
> entities other than the intended recipient is prohibited. This e-mail and
> its attachments have been scanned for the presence of computer viruses.
It
> is the responsibility of the recipient to run the virus check on e-mails
> and attachments before opening them. If you have received this e-mail in
> error, kindly delete this e-mail from all computers.
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>
>
>
>
> >
> >
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




Mime
View raw message