Getting result set using callablestatement??

[Jignesh Gohel]

Hi,

In my procedure i am doing some SELECT queries.I want to obtain the result set which i would get when executing that procedure.So which method i should use on CallableSttement object to obtain that .
I tries to use executeQuery but it's not working??

Thanks,
Jignesh
[ April 17, 2006: Message edited by: Jignesh Gohel ]

[Shailesh Chandra]

you can write code similar to this

CallableStatement cstmt = con.prepareCall("{call my_proc(?, ?)}");
 
cstmt.registerOutParameter(1, java.sql.Types.YOUR_TYPE);
cstmt.registerOutParameter(2, java.sql.Types.YOUR_TYPE);
ResultSet rs = cstmt.executeQuery();
// use result set values with rs.getXXX methods
 

or

CallableStatement cstmt = connection.prepareCall("{call my_proc(?, ?)}");
cstmt.registerOutParameter(1, java.sql.Types.YOUR_TYPE);
cstmt.registerOutParameter(2, java.sql.Types.YOUR_TYPE);
 
cstmt.execute();
 
ResultSet rs = (ResultSet)cstmt.getObject(index)

Shailesh

[Jignesh Gohel]

Thanks Sailesh,


But i dont have any out parameters to be registered.In that case how i should write this code:

CallableStatement cstmt = connection.prepareCall("{call my_proc(?, ?)}");cstmt.registerOutParameter(1, java.sql.Types.YOUR_TYPE);cstmt.registerOutParameter(2, java.sql.Types.YOUR_TYPE); cstmt.execute(); ResultSet rs = (ResultSet)cstmt.getObject(index)

or the other one ???

Jignesh

[Shailesh Chandra]

could you show us your stored procedure ?

Shailesh

[Pablo Ganga]

I have a similar problem mi stored procedure look like this in sqlserver 2000
I can't get any result...

     
 CREATE PROCEDURE list_employees
 AS
   BEGIN
     SELECT * FROM employees
   END 

[Suresh Ks]

Hi All,

Here in the above code it is mentioned that java.sql.Types.YOUR_TYPE. I would like to know ,what I will write in the YOUR_TYPE?

Please can you elaborate on this....


Subu

[Shailesh Chandra]

Originally posted by Pablo Ganga:
I have a similar problem mi stored procedure look like this in sqlserver 2000
I can't get any result...

     
 CREATE PROCEDURE list_employees
 AS
   BEGIN
     SELECT * FROM employees
   END 

You can use stored procedure in java IFF it works on your DataBase on the first place.

Not sure about SQL Server but if it were oracle the your procedure wouldn't have compiled. It would have given error like INTO clause is expected in this SELECT statement

your procedure shpuld be like

 
CREATE PROCEDURE list_employees
 AS
   BEGIN
     SELECT COl1,COL2 INTO VAR1,VAR2 FROM employees
   END 
 

here VAR1,VAR2 are variable defined as COl1 and COl2 type. Also in order to retrun some output from procedure us should have some out arguement to hold output.

If you are planning to return multiple rows from procedure then your out type would be a 'REF CURSOR'


Shailesh

[Shailesh Chandra]

Originally posted by Suresh Ks:
Hi All,

Here in the above code it is mentioned that java.sql.Types.YOUR_TYPE. I would like to know ,what I will write in the YOUR_TYPE?

Please can you elaborate on this....


Subu

It would be data type of your argument which you are setting

for example if your arguement is varchar in procedure then you will put

Types.VARCHAR at the place of YOUR_TYPE


Shailesh

[Pablo Ganga]

Originally posted by Shailesh Chandra:

     
 CREATE PROCEDURE list_employees
 AS
   BEGIN
     SELECT * FROM employees
   END 

<hr></blockquote>


You can use stored procedure in java IFF it works on your DataBase on the first place.

Not sure about SQL Server but if it were oracle the your procedure wouldn't have compiled. It would have given error like INTO clause is expected in this SELECT statement

your procedure shpuld be like

 
CREATE PROCEDURE list_employees
 AS
   BEGIN
     SELECT COl1,COL2 INTO VAR1,VAR2 FROM employees
   END 
 

here VAR1,VAR2 are variable defined as COl1 and COl2 type. Also in order to retrun some output from procedure us should have some out arguement to hold output.

If you are planning to return multiple rows from procedure then your out type would be a 'REF CURSOR'


Shailesh[/QB]

that's right the procedure works fine in sql server 2000 but in oracle didn't work.

can you put an example with ref_cursor.
I'm a little green using jdbc.

[Shailesh Chandra]

Originally posted by Pablo Ganga:

can you put an example with ref_cursor.
I'm a little green using jdbc.

here is one example for you.

Shailesh

[Pablo Ganga]

Thanks , it was really helpful