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
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.
 
Don't get me started about those stupid light bulbs.
 
subject: Using Boolean in Callable Statments