You got me wrong. I did not mean using iBATIS but rather plain JDBC. By the way, try Koka's solution. It will work.
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 09, 2005 7:37 PM To: user-java@ibatis.apache.org Subject: RE: SQLMaps - Sybase and stored procedures 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> <[EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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> <[EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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> <[EMAIL PROTECTED] 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > 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 = [EMAIL PROTECTED] > 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. > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > >
