Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36

    Post Unanswered: Java Stored Procedure with OUT parameter

    Hi,

    I'm trying to execute a Java stored proc that verifies whether a file exists or not but I'm having trouble with the OUT parameter when I try to call it from CLP:
    db2 => call testFileExists()
    SQL4306N Java stored procedure or user-defined function
    "DB2INST1.FILE_EXISTS", specific name "SQL110816190528900" could not call Java
    method "cHECKFILE", signature "(Ljava/lang/String;[". SQLSTATE=42724

    However, if I change the DB2 wrapper stored proc to "IN" on the second parameter it runs! Albeit not properally working because it need to be an out parameter.

    Are there any specific rules for OUT parameters concerning Java SPs?

    Any ideas would be fantastic

    Marc
    -- DB2 9.7.2 on a Linux VM (Ubuntu Server 10.04)


    CREATE PROCEDURE FILE_EXISTS (IN input CHAR(200), OUT isExists INT)
    DYNAMIC RESULT SETS 0
    DETERMINISTIC
    LANGUAGE JAVA
    PARAMETER STYLE JAVA
    NO SQL
    FENCED
    THREADSAFE
    PROGRAM TYPE SUB
    EXTERNAL NAME 'FILE_EXISTS!cHECKFILE'


    //FILE_EXISTS.java
    import java.io.*;

    public class FILE_EXISTS
    {
    public static void cHECKFILE (String input, int isExists) throws Exception
    {
    String errorMessage;

    try
    {
    // Check file exists and pass the value back thru the OUT paramenter

    File f = new File(input);

    if (f.isFile()) isExists = 1;
    else
    isExists = 0;

    }
    catch (SecurityException ioe)
    {
    errorMessage = ioe.getMessage();
    throw new IOException( errorMessage + " FAILED" );
    }
    }
    }



    -- testFileExistsSP.sql
    CREATE PROCEDURE testFileExists()
    BEGIN
    DECLARE ISEXISTS INT DEFAULT 0;

    CALL FILE_EXISTS ('/home/db2inst1/test_file.txt',ISEXISTS);

    IF ISEXISTS = 1 THEN
    CALL runSHCommand('echo exists > /home/db2inst1/testFileExists.txt');
    ELSE
    CALL runSHCommand('echo notexists > /home/db2inst1/testFileExists.txt');
    END IF;
    END
    @

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check examples in the manual. The out parameter must be declared as an array in Java.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The reason for that is that Java uses call-by-value semantics on parameters - unless you have an array, in which case call-by-reference is applied. But if the method shall return some values, call-by-value wouldn't work, obviously. That's why the array is needed so that DB2 will actually see the modification made inside the method to the passed-in parameter.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    Thanks for the replies!

    So if I understand this correctly I should use:
    public static void cHECKFILE (String input, int[] isExists) throws Exception
    in the method call and
    isExists[0] = 1;

    Setting the 0th element of the of the int array which will cause the value to be passed by ref back to the DB2 calling proc?

    Funny thing is I get the OUT param always equals zero when passed back to the calling stored proc no matter what I set it to in the Java prog.

    Is there something else I need to do?

  5. #5
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36

    Cool

    Ignore me! That will teach me to code late at night

    I've done a fresh test and it works fine.

    Thanks for all the help -- this is a great forum


  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I just wanted to tell you to have a look at sample sqllib/samples/java/SpServer.java, method "outParameter". :-)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36

    Cool

    That's a good example - thanks for the tip.

    I'll test this out -- as it looks like it's got examples of VARCHAR -> String mappings (which I was having trouble with earlier).

    btw loved your article: Making Operating System Calls from SQL
    Last edited by marc_; 08-17-11 at 07:29. Reason: typo

  8. #8
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    Are java stored procs cached somewhere?

    It seems that :
    1. Copying the class file to ~sqllib/function/
    2. DROPping and recreating the corresponding wrapper Strored Proc.
    Is not enough ..


    I seem to find that DB2 is hanging on to a previous param list as it's failing at runtime. However, if I merely rename the offending class and the wrapper SP it works fine.
    Last edited by marc_; 08-17-11 at 09:08. Reason: typo

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Use NO for dev/test environments.

    IIRC, there used to be command to 'refresh' the environment for java routines. I'll try to find if one is available in 9.5
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  11. #11
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    Thanks!

    I thought that:
    CALL SQLJ.REFRESH_CLASSES()

    ONLY related to SQLJ routines rather than JDBC Java SPs.

    The things you learn *here*
    Last edited by marc_; 08-17-11 at 09:51. Reason: typo

Tags for this Thread

Posting Permissions

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