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
@

Check examples in the manual. The out parameter must be declared as an array in Java.

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.

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?

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

I just wanted to tell you to have a look at sample sqllib/samples/java/SpServer.java, method "outParameter". :-)

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

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.

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

IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

Thanks!

I thought that:
CALL SQLJ.REFRESH_CLASSES()

ONLY related to SQLJ routines rather than JDBC Java SPs.

The things you learn *here*