aspose file tools*
The moose likes JDBC and the fly likes Using Boolean in Callable Statments Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using Boolean in Callable Statments" Watch "Using Boolean in Callable Statments" New topic
Author

Using Boolean in Callable Statments

Rajani Majety
Greenhorn

Joined: Jul 14, 2000
Posts: 3
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.

Rajani
Milind Kulkarni
Ranch Hand

Joined: Jun 01, 2000
Posts: 146
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

Joined: Jun 13, 2000
Posts: 3340
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...

I Hope This Helps
Carl Trusiak, SCJP2, SCWCD
Milind Kulkarni
Ranch Hand

Joined: Jun 01, 2000
Posts: 146
Hi Carl,
You are right. I have made necessary change in my previous post.
Regards,
Milind
Rajani Majety
Greenhorn

Joined: Jul 14, 2000
Posts: 3
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

Joined: Jun 01, 2000
Posts: 146
Hi Rajani,
SUN recommends Java mapping for the JDBC BIT type is as a Java boolean.
Regards,
Milind

charles mee
Greenhorn

Joined: Jun 20, 2001
Posts: 5
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using Boolean in Callable Statments