Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...

Java Forum / Databases / August 2005

Tip: Looking for answers? Try searching our database.

jdbc + CallableStatement +Incorrectly registered parameters

Thread view: 
piernik - 28 Aug 2005 15:19 GMT
Hello I'm trying to learn how to work with stored procedures in Oracle and
java. I deal fine with ResultSets and PreparedStatement now I want to use
functions and CallableStatement.
I wrote my code looking at this sites:

http://andrej.racchvs.com/archives/2003/10/29/using-oracle-ref-cursors-in-java/
http://www.samspublishing.com/articles/article.asp?p=26251&seqNum=7
http://www.cs.bris.ac.uk/maintain/OracleDocs/java.816/a81354/samapp2.htm
http://www.enterprisedt.com/publications/oracle/result_set.html

I've created a package with a function to find buss connection between 2 cities
CREATE OR REPLACE PACKAGE cursors_pkg
AS
type dire_city_curs is record (
bus_id          Bus_Schedule.bus_id%TYPE,
station_name    Station.station_name%TYPE,
arrival_tim     Bus_Schedule.arrival_time%TYPE,
departure_time  Bus_Schedule.departure_time%TYPE,
station_nr      Bus_Schedule.station_nr%TYPE);
TYPE refcursortype IS REF CURSOR return dire_city_curs;
FUNCTION getdirect(from_city varchar2, to_city varchar2) RETURN refcursortype;
END cursors_pkg;

CREATE OR REPLACE PACKAGE BODY cursors_pkg IS
FUNCTION getdirect(from_city varchar2, to_city varchar2) RETURN refcursortype
IS
mycursor refcursortype;
BEGIN
OPEN kurszor FOR
select **bla bla**  
RETURN mycursor;
END;
END cursors_pkg;

now java

String usersSql = "{ call ? = cursors_pkg.getdirect(?,?) }";
CallableStatement stmt = db.prepareCall(usersSql);
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.setString(new String("from_city"),"London");
stmt.setString(new String("to_city"),"Paris");

   stmt.execute();
   ResultSet rset = (ResultSet) stmt.getObject(1);
//or ResultSet rset  = ((OracleCallableStatement)stmt).getCursor (1);
   while (rset.next()){
    System.out.println( rset.getDouble(1) +"   "+ rset.getString(2));
   }
and the effect of my efforts:
SQLException: Incorrectly set or registered parameters.:null  
I'm puzzled. I don't understand what goes wrong. Can you give me any hints?
I was sent to
http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm
but I find there nothing interesting.

thank you for help
Joe Weinstein - 28 Aug 2005 18:22 GMT
Here's code that works. It uses
a procedure rather than a function.
Joe Weinstein at BEA

      Statement st = c.createStatement();

      st.executeUpdate (
        "   CREATE OR REPLACE PROCEDURE joeproc(    "
        + "                  data_list IN OUT REF CURSOR)"
        + " IS                                          "
        + " BEGIN                                       "
        + "   OPEN data_list FOR                        "
        + "   SELECT * FROM DUAL;                       "
        + " END  joeproc;                               "
      );

      CallableStatement cs = c.prepareCall("{ call joeproc(?)}");

      cs.registerOutParameter(1,OracleTypes.CURSOR);
      cs.execute();
      ResultSet rs = (ResultSet)(cs.getObject(4));

> Hello I'm trying to learn how to work with stored procedures in Oracle and
> java. I deal fine with ResultSets and PreparedStatement now I want to use
[quoted text clipped - 52 lines]
>
> thank you for help
Virgil Green - 30 Aug 2005 22:54 GMT
> Hello I'm trying to learn how to work with stored procedures in
> Oracle and java. I deal fine with ResultSets and PreparedStatement
> now I want to use functions and CallableStatement.
> I wrote my code looking at this sites:

http://andrej.racchvs.com/archives/2003/10/29/using-oracle-ref-cursors-in-java/
> http://www.samspublishing.com/articles/article.asp?p=26251&seqNum=7
> http://www.cs.bris.ac.uk/maintain/OracleDocs/java.816/a81354/samapp2.htm
[quoted text clipped - 47 lines]
>
> thank you for help

I'm just thinking on-line here... try

 String usersSql = "{ call ? = cursors_pkg.getdirect( from_city ?, to_city
?) }";
 CallableStatement stmt = db.prepareCall(usersSql);
 stmt.registerOutParameter(1,OracleTypes.CURSOR);
 stmt.setString(2,"London");
 stmt.setString(3,"Paris");

    stmt.execute();
    ResultSet rset = (ResultSet) stmt.getObject(1);
//or ResultSet rset  = ((OracleCallableStatement)stmt).getCursor (1);
    while (rset.next()){
     System.out.println( rset.getDouble(1) +"   "+ rset.getString(2));
    }

based on the premise that you only want to substitute the data via the
parameters, not the parameter names.

A little further looking on the net tells me that the use of named
parameters is part of JDBC 3.0 and older drivers may not support the use of
named parameters.

Signature

Virgil



Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.