• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Using Boolean in Callable Statments

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 146
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).]
 
Sheriff
Posts: 3341
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Rajani,
SUN recommends Java mapping for the JDBC BIT type is as a Java boolean.
Regards,
Milind

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic