• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using Boolean in Callable Statments

 
Rajani Majety
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can somebody give me a working example of a callable statement in JDBC.The callable statement will execute an Oracle procedure having 1 IN parameter and 1 OUT paramater. The IN parameter should be a boolean type and the OUT parameter can be any type.
 
Milind Kulkarni
Ranch Hand
Posts: 146
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rajani,
Consider the following CallableStatement:
String mil = "{call getRes(?)}";
CallableStatement cstmt = con.prepareCall(mil);
Passing IN Parameters
IN parameter can be passed using setXXX methods inherited from PreparedStatement. A boolean value can be passed as an IN parameter using setBoolean method.
cstmt.setBoolean(1, false);
Passing OUT Parameters
If the stored procedure returns OUT parameters, the JDBC type of each out parameter must be registered before the CallableStatement object can be executed.
String mil = "{call getRes(?)}";
CallableStatement cstmt = con.prepareCall(mil);
cstmt.registerOutParameter(1, java.sql.Types.BIT);
ResultSet rs = cstmt.executeQuery();
boolean x = cstmt.getBoolean(1);
Similarly CallableStatement object can have parameters that supplies input as well as accept output parameter (INOUT parameter).
Regards,
Milind

[This message has been edited by Milind Kulkarni (edited July 15, 2000).]
 
Carl Trusiak
Sheriff
Posts: 3340
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Milind Kulkarni:
Hi Rajani,
Consider the following CallableStatement:
String mil = "{call getRes(?)}";
CallableStatement cstmt = con.prepareCall(mil);
[b] Passing IN Parameters

IN parameter can be passed using setXXX methods inherited from PreparedStatement. A boolean value can be passed as an IN parameter using setBoolean method.
cstmt.setBoolean(1, false);
Passing OUT Parameters
If the stored procedure returns OUT parameters, the JDBC type of each out parameter must be registered before the CallableStatement object can be executed.
String mil = "{call getRes(?)}";
CallableStatement cstmt = con.prepareCall(mil);
cstmt.registerOutParameter(1, java.sql.Types.Boolean);
ResultSet rs = cstmt.executeQuery();
boolean x = cstmt.getBoolean(1);
Similarly CallableStatement object can have parameters that supplies input as well as accept output parameter (INOUT parameter).
Regards,
Milind
[/B]

I searched Sun's API documentation for jdk 1.1, 1.2 and 1.3 and none had the type java.sql.Types.Boolean. When I researched this all the documentation maps the getBoolean method to java.sql.Types.BIT. I haven't had any opertunity to test this so...
 
Milind Kulkarni
Ranch Hand
Posts: 146
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Carl,
You are right. I have made necessary change in my previous post.
Regards,
Milind
 
Rajani Majety
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I tried the way you suggested to pass the IN boolean parameter using setXXX method. I found that the java boolean type does not match with the PL/SQl boolean type. Can anyone tell me how can a java boolean type be matched to Pl/SQL boolean type.
 
Milind Kulkarni
Ranch Hand
Posts: 146
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rajani,
SUN recommends Java mapping for the JDBC BIT type is as a Java boolean.
Regards,
Milind

 
charles mee
Greenhorn
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is from Oracle:
Restrictions (all versions)
Features Not Implemented
We do not support arguments of type BOOLEAN to PL/SQL stored procedures. This is a restriction of the OCI.
Workaround: define a second PL/SQL stored procedure that accepts the BOOLEAN argument as a CHAR or NUMBER and passes it as a BOOLEAN to the first stored procedure.
ie: create a wrapper function that calls your package. Have the wrapper function convert the boolean to an integer and send the integer back.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic