调用Java存储过程遇到SQL4306N时怎么办？

     本文讲解了在开发Java存储过程时经常会碰到的一个问题及其解决办法。
ing inStr, int inStart, int inNu
m)
throws Exception
{
if (inStart < 1 || inStart > inStr.length() || inNum <= 0 ) outStr="";
if ((inStart - 1 + inNum) > inStr.length()) inNum = inStr.length() + 1 - inS
tart;
outStr = inStr.substring(inStart - 1, inStart + inNum - 1);
}
}

2. 创建存储过程的DDL语句(SpTest.db2)：

DROP SPECIFIC PROCEDURE TESTSUB@
CREATE PROCEDURE subString(OUT OUTSTRING VARCHAR(500),IN INSTRING VARCHAR(500),I
N INSTART INT,IN INNUM INT)
SPECIFIC TESTSUB
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE

3. 编译并定义Java存储过程：
$ javac SpTest.java
$ cp SpTest.class ~/sqllib/function
$ db2 connect to sample

Database Connection Information

Database server = DB2/6000 8.2.0
SQL authorization ID = XXXX
Local database alias = SAMPLE

$ db2 -td@ -vf SpTest.db2
DROP SPECIFIC PROCEDURE TESTSUB
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "XXXX.TESTSUB" is an undefined name. SQLSTATE=42704

CREATE PROCEDURE subString(OUT OUTSTRING VARCHAR(500),IN INSTRING VARCHAR(500),I
N INSTART INT,IN INNUM INT)
SPECIFIC TESTSUB
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
EXTERNAL NAME 'SpTest.subString'
DB20000I The SQL command completed successfully.

$ db2 "call subString(?,'sdafatewfdsa',2,5)"
SQL4306N Java stored procedure or user-defined function "XXXX.SUBSTRING",
specific name "TESTSUB" could not call Java method "subString", signature
"([Ljava/lang/String;Ljava/lang/Strin". SQLSTATE=42724

    我们经过检查，发现类名正确、类库也在目录$HOME/sqllib/function下。这时我们集中精力检查程序的签名（Signature）。因为在错误信息中，签名不全，我们要查看$DB2DIAGPATH/db2diag.log文件（其中$DB2DIAGPATH 代表数据库管理器配置参数中的DIAGPATH参数）。我们看到完整的签名为：

2005-08-17-14.47.19.569936+480 I126072C540 LEVEL: Warning
PID : 1810588 TID : 1287 PROC : db2fmp (Java) 0
INSTANCE: XXXX NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:150
MESSAGE : JNI GetMethodID failed. signature:
DATA #1 : Hexdump, 42 bytes
0x3007A950 : 285B 4C6A 6176 612F 6C61 6E67 2F53 7472 ([Ljava/lang/Str
0x3007A960 : 696E 673B 4C6A 6176 612F 6C61 6E67 2F53 ing;Ljava/lang/S
0x3007A970 : 7472 696E 673B 4949 2956 tring;II)V

     这是DB2用来查找Java存储过程对应的方法时用的签名（Signature），此时我们再检查一下类文件中SubString方法的签名：
$ cd ~/sqllib/function
$ javap -s SpTest

This utility can be used to reverse assemble code. Many program license
agreements do not permit reverse assembly. If you are not the copyright
owner of the code which you want to reverse assemble, please check the
license agreement under which you acquired such code to confirm whether
you are permitted to perform such reverse assembly.

Compiled from SpTest.java
public class SpTest extends java.lang.Object {
public SpTest();
/* ()V */
public static void subString(java.lang.String, java.lang.String, int, int) t
hrows java.lang.Exception;
/* (Ljava/lang/String;Ljava/lang/String;II)V */

    我们看到在Java类中的函数签名与DB2查找的函数签名不一致。这就是SQL4306N产生的原因。

    第一个参数不一样。根据JNI规范， [Ljava/lang/String 是一个String数组。而Ljava/lang/String则是字符串String类型，也就是我们程序源文件中定义的类型。那为什么DB2要查找一个String数组类型的参数呢？ 通过查找DB2文档，我们发现如下解释（http://publib.boulder.ibm.com/infocenter/db2help/topic
/com.ibm.db2.udb.doc/admin/r0008328.htm）：

PARAMETER TYPE JAVA
This means that the procedure will use a parameter passing convention that conforms to the Java language and SQLJ Routines specification. IN/OUT and OUT parameters will be passed as single entry arrays to facilitate returning values. This can only be specified when LANGUAGE JAVA is used.

    我们看到如果创建存储过程时使用了IN/OUT以及OUT参数，DB2会将其解释为一个单项数组，并通过单项数组传递返回值。 因此，我们同样需要在Java程序中使用字符串数组来返回我们的结果。

    修改后的java源程序如下所示：
import java.lang.*;
import java.io.*;

public class SpTest
{
public static void subString(String[] outStr,String inStr, int inStart, int in
Num)
throws Exception
{
if (inStart < 1 || inStart > inStr.length() || inNum <= 0 ) outStr[0]="";
if ((inStart - 1 + inNum) > inStr.length()) inNum = inStr.length() + 1 - inS
tart;
outStr[0] = inStr.substring(inStart - 1, inStart + inNum - 1);
}
}

     此时我们重新编译并创建存储过程，可以看到，它可以正确执行了。

$ javac SpTest.java
$ cp SpTest.class ~/sqllib/function
$ db2 -td@ -vf SpTest.db2
DROP SPECIFIC PROCEDURE TESTSUB
DB20000I The SQL command completed successfully.

CREATE PROCEDURE subString(OUT OUTSTRING VARCHAR(500),IN INSTRING VARCHAR(500),I
N INSTART INT,IN INNUM INT)
SPECIFIC TESTSUB
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
EXTERNAL NAME 'SpTest.subString'
DB20000I The SQL command completed successfully.

$ db2 "call substring(?,'sadfdsafdsaf',2,5)"

Value of output parameters
--------------------------
Parameter Name : OUTSTRING
Parameter Value : adfds

Return Status = 0

    关于SQL4306N的错误，基本上通过上面的步骤就可以解决了。如果您的问题通过上面的检查方法还没有解决，请联系IBM技术支持人员。

参考资料:
1、DB2信息中心：http://publib.boulder.ibm.com/infocenter/db2help/index.jsp
2、Java Native Interface Specification： http://java.sun.com/j2se/1.3/docs/guide/jni/spec/types.doc.html