Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #314 > unrolled thread

Jave stored procedure que

Back to article view | Back to comp.lang.java.databases

Contents

  Jave stored procedure que "Starbuck" <starbuck@THRWHITE.remove-dii-this> - 2011-04-27 15:23 +0000
    Re: Jave stored procedure "Starbuck" <starbuck@THRWHITE.remove-dii-this> - 2011-04-27 15:23 +0000
      Re: Jave stored procedure "=?ISO-8859-1?Q?Arne_Vajh=" <=?iso-8859-1?q?arne_vajh=@THRWHITE.remove-dii-this> - 2011-04-27 15:23 +0000
        Re: Jave stored procedure "Starbuck" <starbuck@THRWHITE.remove-dii-this> - 2011-04-27 15:23 +0000

#314 — Jave stored procedure que

  To: comp.lang.java.databases
Hi Guys

Can anyone see what is wrong here

CallableStatement cs = con.prepareCall("{ ? = call list_index () }");
cs.registerOutParameter(1, Types.OTHER);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1);
while (rs.next())
{
     //read the returned recordset	
}


When  - ResultSet rs = (ResultSet) cs.getObject(1);
is executed the error is - "[B cannot be cast to java.sql.ResultSet"

The stored procedure being called is as follows, it works fine in c#

CREATE PROCEDURE list_index
AS
BEGIN
   /* Procedure body */
   SELECT name FROM listtypes where ctype = 'C' order by ndx
END

Thanks in advance chaps.

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [next] | [standalone]

#316 — Re: Jave stored procedure

  To: comp.lang.java.databases
Starbuck wrote:
> Hi Guys
> 
> Can anyone see what is wrong here
> 
> CallableStatement cs = con.prepareCall("{ ? = call list_index () }");
> cs.registerOutParameter(1, Types.OTHER);
> cs.execute();
> ResultSet rs = (ResultSet) cs.getObject(1);
> while (rs.next())
> {
>     //read the returned recordset   
> }
> 
> 
> When  - ResultSet rs = (ResultSet) cs.getObject(1);
> is executed the error is - "[B cannot be cast to java.sql.ResultSet"
> 
> The stored procedure being called is as follows, it works fine in c#
> 
> CREATE PROCEDURE list_index
> AS
> BEGIN
>   /* Procedure body */
>   SELECT name FROM listtypes where ctype = 'C' order by ndx
> END
> 
> Thanks in advance chaps.

Its ok, silly me

//ResultSet rs = (ResultSet) cs.getObject(1);
ResultSet rs = cs.executeQuery();

Works fine now.
This Java is rather cool eh.

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#317 — Re: Jave stored procedure

  To: comp.lang.java.databases
Starbuck wrote:
> Starbuck wrote:
>> Can anyone see what is wrong here
>>
>> CallableStatement cs = con.prepareCall("{ ? = call list_index () }");
>> cs.registerOutParameter(1, Types.OTHER);
>> cs.execute();
>> ResultSet rs = (ResultSet) cs.getObject(1);
>> while (rs.next())
>> {
>>     //read the returned recordset   }
>>
>>
>> When  - ResultSet rs = (ResultSet) cs.getObject(1);
>> is executed the error is - "[B cannot be cast to java.sql.ResultSet"
>>
>> The stored procedure being called is as follows, it works fine in c#
>>
>> CREATE PROCEDURE list_index
>> AS
>> BEGIN
>>   /* Procedure body */
>>   SELECT name FROM listtypes where ctype = 'C' order by ndx
>> END
>>
>> Thanks in advance chaps.
> 
> Its ok, silly me
> 
> //ResultSet rs = (ResultSet) cs.getObject(1);
> ResultSet rs = cs.executeQuery();
> 
> Works fine now.

An SP can return:
- out parameters
- result sets
- return values

A long time ago I created the following to demo the
various options:

CREATE PROCEDURE TEST_MULTIOUT
@inarg INTEGER,
@outarg INTEGER OUTPUT
AS
SELECT @outarg = 123
SELECT * FROM T1
SELECT * FROM T1
RETURN -@inarg

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;

public class UseSP {
     public static void main(String[] args) throws Exception {
         Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
         Connection con = 
DriverManager.getConnection("jdbc:sybase:Tds:arnepc2:5000", "sa", "");
         con.setCatalog("Test");
         CallableStatement cstmt = con.prepareCall("{? = CALL 
TEST_MULTIOUT(?,?)}");
         cstmt.registerOutParameter(1, Types.INTEGER);
         cstmt.setInt(2, 5);
         cstmt.registerOutParameter(3, Types.INTEGER);
         cstmt.execute();
         while(cstmt.getMoreResults()) {
             ResultSet rs = cstmt.getResultSet();
             while(rs.next()) {
                 System.out.println(rs.getInt(1) + " " + rs.getString(2));
             }
         }
         System.out.println("return value = " + cstmt.getInt(1));
         System.out.println("out parameter = " + cstmt.getInt(3));
         cstmt.close();
         con.close();
     }
}

Arne

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#318 — Re: Jave stored procedure

  To: comp.lang.java.databases
Arne Vajhoj wrote:
> Starbuck wrote:
>> Starbuck wrote:
>>> Can anyone see what is wrong here
>>>
>>> CallableStatement cs = con.prepareCall("{ ? = call list_index () }");
>>> cs.registerOutParameter(1, Types.OTHER);
>>> cs.execute();
>>> ResultSet rs = (ResultSet) cs.getObject(1);
>>> while (rs.next())
>>> {
>>>     //read the returned recordset   }
>>>
>>>
>>> When  - ResultSet rs = (ResultSet) cs.getObject(1);
>>> is executed the error is - "[B cannot be cast to java.sql.ResultSet"
>>>
>>> The stored procedure being called is as follows, it works fine in c#
>>>
>>> CREATE PROCEDURE list_index
>>> AS
>>> BEGIN
>>>   /* Procedure body */
>>>   SELECT name FROM listtypes where ctype = 'C' order by ndx
>>> END
>>>
>>> Thanks in advance chaps.
>>
>> Its ok, silly me
>>
>> //ResultSet rs = (ResultSet) cs.getObject(1);
>> ResultSet rs = cs.executeQuery();
>>
>> Works fine now.
> 
> An SP can return:
> - out parameters
> - result sets
> - return values
> 
> A long time ago I created the following to demo the
> various options:
> 
> CREATE PROCEDURE TEST_MULTIOUT
> @inarg INTEGER,
> @outarg INTEGER OUTPUT
> AS
> SELECT @outarg = 123
> SELECT * FROM T1
> SELECT * FROM T1
> RETURN -@inarg
> 
> import java.sql.CallableStatement;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.Types;
> 
> public class UseSP {
>     public static void main(String[] args) throws Exception {
>         Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
>         Connection con = 
> DriverManager.getConnection("jdbc:sybase:Tds:arnepc2:5000", "sa", "");
>         con.setCatalog("Test");
>         CallableStatement cstmt = con.prepareCall("{? = CALL 
> TEST_MULTIOUT(?,?)}");
>         cstmt.registerOutParameter(1, Types.INTEGER);
>         cstmt.setInt(2, 5);
>         cstmt.registerOutParameter(3, Types.INTEGER);
>         cstmt.execute();
>         while(cstmt.getMoreResults()) {
>             ResultSet rs = cstmt.getResultSet();
>             while(rs.next()) {
>                 System.out.println(rs.getInt(1) + " " + rs.getString(2));
>             }
>         }
>         System.out.println("return value = " + cstmt.getInt(1));
>         System.out.println("out parameter = " + cstmt.getInt(3));
>         cstmt.close();
>         con.close();
>     }
> }
> 
> Arne
> 

Thanks Arne

That is very helpful

Kev

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.databases

csiph-web