Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    Unanswered: Problem calling stored proc from a java application

    I am trying to access a stored procedure located in a DB2 server on a different m/c using the following syntax.

    //signature of stored proc for ur reference
    //CREATE PROCEDURE SP_TEST(IN P_SELECT_FIELDS VARCHAR
    //(50),IN CreateTable char(2))

    CallableStatement cstmt = con.prepareCall("{call admininstrator.SP_Test('name,effectivedate','Y'}") ;
    System.out.println("Time before create " + System.currentTimeMillis());
    ResultSet rs = cstmt.executeQuery();

    But I am getting the following exception.

    com.ibm.db2.jcc.b.SQLException: Routine "SP_TEST" (specific name "SQL03011717013
    3460") is implemented with code in library or path "...STRATOR.SP_TEST", functio
    n "ADMININSTRATOR.SP_TEST" which cannot be accessed. Reason code: "4".
    at com.ibm.db2.jcc.b.ce.d(ce.java:975)
    at com.ibm.db2.jcc.a.bd.k(bd.java:312)
    at com.ibm.db2.jcc.a.bd.e(bd.java:89)
    at com.ibm.db2.jcc.a.r.e(r.java:108)
    at com.ibm.db2.jcc.a.bp.i(bp.java:181)
    at com.ibm.db2.jcc.b.ce.k(ce.java:953)
    at com.ibm.db2.jcc.b.cf.M(cf.java:1649)
    at com.ibm.db2.jcc.b.cf.a(cf.java:1572)
    at com.ibm.db2.jcc.b.cg.executeQuery(cg.java:124)
    at Document3.main(test_sp.java:26)

    What is going wrong in the call?

    Do I need to set any configuration parameter either at server side or client side?

    However, I am able to issue simple jdbc statements from my client to the DB2 server.

    Thanking in advance for your advice..

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I'm pretty new to Java but I think you have to set the data types that are passed in the CallableStatement object.

    CallableStatement cstmt = con.prepareCall("call admininstrator.SP_Test (?,?)");
    cstmt.setString(1,"name,effectivedate");
    cstmt.setString(2,"Y");

    HTH

  3. #3
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    unable to access stored procedures(DB2) from Java client

    I am trying to call a stored procedure with no-args located in a DB2 server (OS WinNT) on a diffrent m/c using the following syntax.

    con = DriverManager.getConnection( url,"Administrator","adminpwd" );
    CallableStatement cstmt = con.prepareCall("{call admininstrator.SP_Test1(}");

    boolean i = cstmt.execute();

    But I am getting the following exception.

    com.ibm.db2.jcc.b.SQLException: Routine "SP_TEST1" (specific name "SQL0301191930
    31560") is implemented with code in library or path "...ATOR.SP_TEST1", function
    "ADMININSTRATOR.SP_TEST1" which cannot be accessed. Reason code: "4".
    at com.ibm.db2.jcc.b.ce.d(ce.java:975)
    at com.ibm.db2.jcc.a.bd.k(bd.java:312)
    at com.ibm.db2.jcc.a.bd.e(bd.java:89)
    at com.ibm.db2.jcc.a.r.e(r.java:108)
    at com.ibm.db2.jcc.a.bp.i(bp.java:181)
    at com.ibm.db2.jcc.b.ce.k(ce.java:953)
    at com.ibm.db2.jcc.b.cf.M(cf.java:1649)
    at com.ibm.db2.jcc.b.cf.a(cf.java:1572)
    at com.ibm.db2.jcc.b.cg.execute(cg.java:106)
    at Document3.main(Document3.java:28)

    what's wrong in my code?

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    try this...

    con = DriverManager.getConnection( url,"Administrator","adminpwd" );
    CallableStatement cstmt = con.prepareCall("call admininstrator.SP_Test1()");

    boolean i = cstmt.execute();

  5. #5
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    Stored proc still not working

    I tried all the suggestions given by you.
    But nothing is working.
    I found in DB2 documentation -
    Calls to uncataloged stored procedures are not supported in type 4 driver.
    Probably that's the reason I am unable to access it.

    The DLL created for the particular procedure is present in the \\<DB2 root>\sqllib\function\routine\sqlproc\dbname\admin istrator folder. still it is not accessible.
    What's going wrong?

  6. #6
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    Screen output using SQL scripting

    After lot of trial and error finally my no-argument procedure worked.
    I simply did not use the username before the <.procedurename> and it worked.

    But procedure which takes argument is still giving error. So i need to debug it.
    Please let me know how to use screen logging, screen printing using SQL scripting.
    for example, static printing looks like

    !echo inserting values....@

    but i need dynamic printing like below,

    SET DYNA_SQL = --something.

    !echo the dynamic SQL || DYNA_SQL ...@

    I need the correct syntax for the above line where I can append a variable to a static text.

    Thanks a lot for all the advice you have given so far.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •