AppletTalk.com Forum Index AppletTalk.com
Java discussions newsgroups
 
Archives   FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Syntax of setting Ref Cursor in procedure from JDBC

 
Post new topic   Reply to topic    AppletTalk.com Forum Index -> Java and Databases
View previous topic :: View next topic  
Author Message
Atul
Guest
PostPosted: Sun Nov 28, 2004 1:22 pm    Post subject: Syntax of setting Ref Cursor in procedure from JDBC Reply with quote


Hi,

I am calling a SP from java. Now the problem is that the SP has 3
Parameters- :

2 IN String variables
1 IN OUT variable which is a REF CURSOR

Java Code

String cmrQuery="{call cust_db.get_cntr_num_sp(?,?,?)}"; <<--IS SYNTAX
CORRECT
cstmt = con.prepareCall(cmrQuery);
cstmt.setQueryTimeout(QUERY_TIMEOUT) ;
cstmt.setString(1,mcn_base);
cstmt.setString(2,mcn_sfx);
cstmt.setString(3,NULL); <<-- PROBLEM IS HERE
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
rs =cstmt.executeQuery();


SO now my question is -:

1. Is the calling syntax correct of the SP with these 3 parameters ?
2. While using setString() of IN OUT Ref Cursor what will be
parameter?? ( i.e . will it be null or we can leave it empty? since we
r not passing anything in the ref cursor and if we dont set it then it
will say a variable not bound error.!!)

Any help or clarification will be highly helpful.. I have been banging
my head on this one for some time now..

Thanks in advance
atul
Back to top
Chuck Simpson
Guest
PostPosted: Sun Nov 28, 2004 9:12 pm    Post subject: Re: Syntax of setting Ref Cursor in procedure from JDBC Reply with quote


On Sun, 28 Nov 2004 05:22:08 -0800, Atul wrote:

Quote:
Hi,

I am calling a SP from java. Now the problem is that the SP has 3
Parameters- :

2 IN String variables
1 IN OUT variable which is a REF CURSOR

Java Code

String cmrQuery="{call cust_db.get_cntr_num_sp(?,?,?)}"; <<--IS SYNTAX
CORRECT
cstmt = con.prepareCall(cmrQuery);
cstmt.setQueryTimeout(QUERY_TIMEOUT) ; cstmt.setString(1,mcn_base);
cstmt.setString(2,mcn_sfx);
cstmt.setString(3,NULL); <<-- PROBLEM IS HERE
cstmt.registerOutParameter(1, OracleTypes.CURSOR); rs
=cstmt.executeQuery();


SO now my question is -:

1. Is the calling syntax correct of the SP with these 3 parameters ? 2.
While using setString() of IN OUT Ref Cursor what will be parameter?? (
i.e . will it be null or we can leave it empty? since we r not passing
anything in the ref cursor and if we dont set it then it will say a
variable not bound error.!!)

Any help or clarification will be highly helpful.. I have been banging
my head on this one for some time now..

Thanks in advance
atul

Atul,

The procedure call syntax is good. However, when used this way the 3rd
parameter must be set using setNull(). Also you must register the "3rd"
parameter as an OUT parameter instead of the "1st".After the call executes
then read the out parameters. See modified example below:

cstmt = con.prepareCall("{call cust_db.get_cntr_num_sp(?,?,?)}");
cstmt.setQueryTimeout(QUERY_TIMEOUT);
cstmt.registerOutParameter(3, OracleTypes.CURSOR);
cstmt.setString(1,mcn_base);
cstmt.setString(2,mcn_sfx);
cstmt.setNull(3, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs = cstmt.getObject(3);
... read ResultSet contents ...
cstmt.close();

Chuck



Back to top
Atul
Guest
PostPosted: Tue Nov 30, 2004 9:58 am    Post subject: Re: Syntax of setting Ref Cursor in procedure from JDBC Reply with quote


Chuck,

Thanks a lot for this .. It worked..

I really appreciate your help.

Thanks
atul

Chuck Simpson <chuckls (AT) cox-internet (DOT) com> wrote

Quote:
On Sun, 28 Nov 2004 05:22:08 -0800, Atul wrote:

Hi,

I am calling a SP from java. Now the problem is that the SP has 3
Parameters- :

2 IN String variables
1 IN OUT variable which is a REF CURSOR

Java Code

String cmrQuery="{call cust_db.get_cntr_num_sp(?,?,?)}"; <<--IS SYNTAX
CORRECT
cstmt = con.prepareCall(cmrQuery);
cstmt.setQueryTimeout(QUERY_TIMEOUT) ; cstmt.setString(1,mcn_base);
cstmt.setString(2,mcn_sfx);
cstmt.setString(3,NULL); <<-- PROBLEM IS HERE
cstmt.registerOutParameter(1, OracleTypes.CURSOR); rs
=cstmt.executeQuery();


SO now my question is -:

1. Is the calling syntax correct of the SP with these 3 parameters ? 2.
While using setString() of IN OUT Ref Cursor what will be parameter?? (
i.e . will it be null or we can leave it empty? since we r not passing
anything in the ref cursor and if we dont set it then it will say a
variable not bound error.!!)

Any help or clarification will be highly helpful.. I have been banging
my head on this one for some time now..

Thanks in advance
atul

Atul,

The procedure call syntax is good. However, when used this way the 3rd
parameter must be set using setNull(). Also you must register the "3rd"
parameter as an OUT parameter instead of the "1st".After the call executes
then read the out parameters. See modified example below:

cstmt = con.prepareCall("{call cust_db.get_cntr_num_sp(?,?,?)}");
cstmt.setQueryTimeout(QUERY_TIMEOUT);
cstmt.registerOutParameter(3, OracleTypes.CURSOR);
cstmt.setString(1,mcn_base);
cstmt.setString(2,mcn_sfx);
cstmt.setNull(3, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs = cstmt.getObject(3);
... read ResultSet contents ...
cstmt.close();

Chuck

Back to top
Display posts from previous:   
Post new topic   Reply to topic    AppletTalk.com Forum Index -> Java and Databases All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2006 phpBB Group
SEO toolkit © 2004-2006 webmedic.