Clob as parameter in Java UDF

[gimme_this_gimme_that]

I have the following Java code :

package com.rhi.bb.udf.utils;

import java.sql.Clob;
import java.sql.SQLException;

import java.util.regex.Pattern;
import java.util.regex.Matcher;

// import COM.ibm.db2.app.UDF;

public class RegExp {

public static int MatchClob(String pattern, java.sql.Clob[] clob)
throws SQLException
{
// I tried making clob be both a Clob[] and a Clob but the error
message was the same
if ( 0 == clob.length ) {
return 2;
}
try {
Pattern p = Pattern.compile(pattern);
Matcher m = p.matcher(
clob[0].getSubString(Long.parseLong("1"), (int)clob[0].length()) );
if ( m.matches() ) {
return 1;
}
return 0;
} catch (Exception ex) {
throw new SQLException("RegExp.MatchClob failed " + ex);
}
}
}

I know DB2 finds the class file because I've placed other UDFs in the
RegExp class and they work.
Then:

drop function MATCH_CLOB;
call sqlj.refresh_classes();

CREATE FUNCTION MATCH_CLOB(pattern VARCHAR(4000), input_clob CLOB(1M))
RETURNS INTEGER
FENCED
VARIANT
NO SQL
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'com.rhi.bb.udf.utils.RegExp!MatchClob';
This is the resulting error message :
[IBM][CLI Driver][DB2/NT] SQL4302N Procedure or user-defined function
"EMMMCL01.MATCH_CLOB", specific name "SQL060815120135200" aborted with
an exception "class COM.ibm.db2.app.ClobWithD". SQLSTATE=38501\ d\ a"

Any suggestions?

Thanks.
Here is the full stack trace.
aqt d88-e04 EXIT SQLErrorW with return code 0
(SQL_SUCCESS)
HENV 020A12A0
HDBC 020A13A0
HSTMT 020A1D20
WCHAR * 0x0012EB80 (NYI)
SDWORD * 0x0012ED48 (-4302)
WCHAR * 0x0012E780 [ 207] "[IBM][CLI Driver][DB2/NT]
SQL4302N Procedure or user-defined function "EMMMCL01.MATCH_CLOB",
specific name "SQL060815120135200" aborted with an exception "class
COM.ibm.db2.app.ClobWithD". SQLSTATE=38501\ d\ a"
SWORD 512
SWORD * 0x0012ED44 (207)

aqt d88-e04 ENTER SQLErrorW
HENV 020A12A0
HDBC 020A13A0
HSTMT 020A1D20
WCHAR * 0x0012EB80 (NYI)
SDWORD * 0x0012ED48
WCHAR * 0x0012E780
SWORD 512
SWORD * 0x0012ED44

aqt d88-e04 EXIT SQLErrorW with return code 100
(SQL_NO_DATA_FOUND)
HENV 020A12A0
HDBC 020A13A0
HSTMT 020A1D20
WCHAR * 0x0012EB80 (NYI)
SDWORD * 0x0012ED48
WCHAR * 0x0012E780
SWORD 512
SWORD * 0x0012ED44

[Knut Stolze]

gi*******************@yahoo.com wrote:

I have the following Java code :

package com.rhi.bb.udf.utils;

import java.sql.Clob;
import java.sql.SQLException;

import java.util.regex.Pattern;
import java.util.regex.Matcher;

// import COM.ibm.db2.app.UDF;

public class RegExp {

public static int MatchClob(String pattern, java.sql.Clob[] clob)
throws SQLException
{
// I tried making clob be both a Clob[] and a Clob but the error
message was the same
if ( 0 == clob.length ) {
return 2;
}
try {
Pattern p = Pattern.compile(pattern);
Matcher m = p.matcher(
clob[0].getSubString(Long.parseLong("1"), (int)clob[0].length()) );
if ( m.matches() ) {
return 1;
}
return 0;
} catch (Exception ex) {
throw new SQLException("RegExp.MatchClob failed " + ex);
}
}
}

I know DB2 finds the class file because I've placed other UDFs in the
RegExp class and they work.
Then:

drop function MATCH_CLOB;
call sqlj.refresh_classes();

CREATE FUNCTION MATCH_CLOB(pattern VARCHAR(4000), input_clob CLOB(1M))
RETURNS INTEGER
FENCED
VARIANT
NO SQL
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'com.rhi.bb.udf.utils.RegExp!MatchClob';
This is the resulting error message :
[IBM][CLI Driver][DB2/NT] SQL4302N Procedure or user-defined function
"EMMMCL01.MATCH_CLOB", specific name "SQL060815120135200" aborted with
an exception "class COM.ibm.db2.app.ClobWithD". SQLSTATE=38501\ d\ a"

Any suggestions?

Have a look at the db2diag.log file (or just run the "db2diag" executable).
You will find the full stack trace of the exception there, including the
specific exception information. That should help us along in figuring out
what's going on.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[gimme_this_gimme_that]

Thanks Knut,

I get that it's not finding the MatchClob method.

But I have a MatchVarchar method in the same RegEx class and DB2 finds
that OK.

I've also stopped and restarted the server.
Here it is:

M
2006-08-15-12.37.01.804000-420 I364795H467 LEVEL: Warning^M
PID : 604 TID : 3064 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll,
probe:130^M
MESSAGE : JNI GetMethodID failed. class:
DATA #1 : Hexdump, 28 bytes^M
0x018AD230 : 636F 6D2F 7268 692F 626F 622F 7564 662F
com/rhi/bb/udf/^M
0x018AD240 : 7574 696C 732F 5265 6745 7870 utils/RegExp^M
^M
2006-08-15-12.37.01.804000-420 I365264H390 LEVEL: Warning^M
PID : 604 TID : 3064 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll,
probe:140^M
MESSAGE : JNI GetMethodID failed. method:
DATA #1 : Hexdump, 9 bytes^M
0x018AD24D : 4D61 7463 6843 6C6F 62 MatchClob^M
^M
2006-08-15-12.37.01.804000-420 I365656H537 LEVEL: Warning^M
PID : 604 TID : 3064 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll,
probe:150^M
MESSAGE : JNI GetMethodID failed. signature:
DATA #1 : Hexdump, 36 bytes^M
0x018AD290 : 284C 6A61 7661 2F6C 616E 672F 5374 7269
(Ljava/lang/Stri^M
0x018AD2A0 : 6E67 3B4C 6A61 7661 2F73 716C 2F43 6C6F
ng;Ljava/sql/Clo^M
0x018AD2B0 : 623B 2949 b;)I^M
^M
2006-08-15-12.37.01.804000-420 E366195H375 LEVEL: Warning^M
PID : 604 TID : 3064 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10^M
MESSAGE : ADM10000W A Java exception has been caught. The Java stack
^M
traceback has been written to the db2diag.log.^M
^M
2006-08-15-12.37.01.804000-420 I366572H384 LEVEL: Warning^M
PID : 604 TID : 3064 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10^M
MESSAGE : java.lang.NoSuchMethodError: MatchClob^M
DATA #1 : Hexdump, 4 bytes^M
0x01AAF430 : 0000 0000 ....^M
^M
2006-08-15-12.37.01.804000-420 I366958H384 LEVEL: Warning^M
PID : 604 TID : 3064 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, routine_infrastructure, sqlerJavaCallRoutine,
probe:30^M
MESSAGE : Error from DB2ER CallUDF. RC:
DATA #1 : Hexdump, 4 bytes^M
0x01AAF988 : 2EEF FFFF ....^M

[Knut Stolze]

gi*******************@yahoo.com wrote:

Thanks Knut,

I get that it's not finding the MatchClob method.

But I have a MatchVarchar method in the same RegEx class and DB2 finds
that OK.

Have you removed the [] in the method signature? IIRC, this is not needed.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[gimme_this_gimme_that]

Hi Knut,

Here is the result of db2diag.exe when the Clob is not an array:

2006-08-15-15.58.28.570000-420 I376819H299 LEVEL: Warning^M
PID : 628 TID : 2196 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll,
probe:370^M
MESSAGE : Java routine exited with exception.^M
^M
2006-08-15-15.58.28.570000-420 E377120H375 LEVEL: Warning^M
PID : 628 TID : 2196 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10^M
MESSAGE : ADM10000W A Java exception has been caught. The Java stack
^M
traceback has been written to the db2diag.log.^M
^M
2006-08-15-15.58.28.570000-420 I377497H547 LEVEL: Warning^M
PID : 628 TID : 2196 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10^M
DATA #1 : String, 184 bytes^M
java.lang.IncompatibleClassChangeError: class
COM.ibm.db2.app.ClobWithDB2Buffer does not implement interface
java.sql.Clob^M
at com.rhi.bob.udf.utils.RegExp.MatchClob(Unknown Source)^M
DATA #2 : Hexdump, 4 bytes^M
0x01AAF414 : 0000 0000 ....^M
^M
2006-08-15-15.58.28.570000-420 E378046H375 LEVEL: Warning^M
PID : 628 TID : 2196 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10^M
MESSAGE : ADM10000W A Java exception has been caught. The Java stack
^M
traceback has been written to the db2diag.log.^M
^M
2006-08-15-15.58.28.570000-420 I378423H547 LEVEL: Warning^M
PID : 628 TID : 2196 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10^M
DATA #1 : String, 184 bytes^M
java.lang.IncompatibleClassChangeError: class
COM.ibm.db2.app.ClobWithDB2Buffer does not implement interface
java.sql.Clob^M
at com.rhi.bob.udf.utils.RegExp.MatchClob(Unknown Source)^M
DATA #2 : Hexdump, 4 bytes^M
0x01AAF430 : 0000 0000 ....^M
^M
2006-08-15-15.58.28.570000-420 I378972H384 LEVEL: Warning^M
PID : 628 TID : 2196 PROC : db2fmp.exe^M
INSTANCE: DB2 NODE : 000^M
FUNCTION: DB2 UDB, routine_infrastructure, sqlerJavaCallRoutine,
probe:30^M
MESSAGE : Error from DB2ER CallUDF. RC:
DATA #1 : Hexdump, 4 bytes^M
0x01AAF988 : 32EF FFFF

[Knut Stolze]

gi*******************@yahoo.com wrote:

Here is the result of db2diag.exe when the Clob is not an array:

2006-08-15-15.58.28.570000-420 I377497H547 LEVEL: Warning
PID : 628 TID : 2196 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
DATA #1 : String, 184 bytes
java.lang.IncompatibleClassChangeError: class
COM.ibm.db2.app.ClobWithDB2Buffer does not implement interface
java.sql.Clob
at com.rhi.bob.udf.utils.RegExp.MatchClob(Unknown Source)
DATA #2 : Hexdump, 4 bytes
0x01AAF414 : 0000 0000 ....

That rings a bell. ;-)

Try to use COM.IBM.db2.app.Clob instead of java.sql.Clob.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[gimme_this_gimme_that]

I didn't find COM.IBM.db2.app.Clob in db2jcc.jar I found
COM.ibm.db2.app.Clob and used that.

Here's the result of db2diag.exe from executing

select MATCH_CLOB('\w{3}\d{2}',delme.lob) from delme;
2006-08-15-17.56.00.117000-420 I399029H467 LEVEL: Warning
PID : 3668 TID : 2676 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll,
probe:130
MESSAGE : JNI GetMethodID failed. class:
DATA #1 : Hexdump, 28 bytes
0x018AD230 : 636F 6D2F 7268 692F 626F 622F 7564 662F com/rhi/bb/udf/
0x018AD240 : 7574 696C 732F 5265 6745 7870 utils/RegExp

2006-08-15-17.56.00.117000-420 I399498H390 LEVEL: Warning
PID : 3668 TID : 2676 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll,
probe:140
MESSAGE : JNI GetMethodID failed. method:
DATA #1 : Hexdump, 9 bytes
0x018AD24D : 4D61 7463 6843 6C6F 62 MatchClob

2006-08-15-17.56.00.117000-420 I399890H537 LEVEL: Warning
PID : 3668 TID : 2676 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll,
probe:150
MESSAGE : JNI GetMethodID failed. signature:
DATA #1 : Hexdump, 36 bytes
0x018AD290 : 284C 6A61 7661 2F6C 616E 672F 5374 7269
(Ljava/lang/Stri
0x018AD2A0 : 6E67 3B4C 6A61 7661 2F73 716C 2F43 6C6F
ng;Ljava/sql/Clo
0x018AD2B0 : 623B 2949 b;)I

2006-08-15-17.56.00.132000-420 E400429H375 LEVEL: Warning
PID : 3668 TID : 2676 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
MESSAGE : ADM10000W A Java exception has been caught. The Java stack
traceback has been written to the db2diag.log.

2006-08-15-17.56.00.132000-420 I400806H384 LEVEL: Warning
PID : 3668 TID : 2676 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
MESSAGE : java.lang.NoSuchMethodError: MatchClob
DATA #1 : Hexdump, 4 bytes
0x01AAF430 : 0000 0000 ....

2006-08-15-17.56.00.132000-420 I401192H384 LEVEL: Warning
PID : 3668 TID : 2676 PROC : db2fmp.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, routine_infrastructure, sqlerJavaCallRoutine,
probe:30
MESSAGE : Error from DB2ER CallUDF. RC:
DATA #1 : Hexdump, 4 bytes
0x01AAF988 : 2EEF FFFF ....

Also:

package com.rhi.bb.udf.utils;

import java.sql.Clob;
import java.sql.SQLException;

import java.util.regex.Pattern;
import java.util.regex.Matcher;

public class RegExp {

public static int MatchClob(String pattern, COM.ibm.db2.app.Clob
clob) throws SQLException
{
if ( null == clob) {
return 2;
}
try {
Pattern p = Pattern.compile(pattern);
Long size = new Long(clob.size());
char[] cbuff = new char[size.intValue()];
clob.getReader().read(cbuff);
Matcher m = p.matcher( new String(cbuff) );
if ( m.matches() ) {
return 1;
}
return 0;
} catch (Exception ex) {
throw new SQLException("RegExp.MatchClob failed " + ex);
}
}

}
call sqlj.refresh_classes();
CREATE FUNCTION MATCH_CLOB(pattern VARCHAR(4000), input_clob CLOB(1M))
RETURNS INTEGER
FENCED
VARIANT
NO SQL
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'com.rhi.bb.udf.utils.RegExp!MatchClob';

[gimme_this_gimme_that]

Yey!!

To use COM.ibm.db2.app.Clob my java class had to implement UDF and I
had to use parameter style DB2GENERAL and not parameter style Java.

This worked when placed in a jar in the Windows XP CLASSPATH:

package com.rhi.bb.utils.udf;

import COM.ibm.db2.app.Clob;
import COM.ibm.db2.app.UDF;

import java.sql.SQLException;

import java.util.regex.Pattern;
import java.util.regex.Matcher;

public class RegExpUdf extends UDF {

public void MatchClob(String pattern, COM.ibm.db2.app.Clob clob,
int result) throws Exception
{
if ( null == clob) {
set(3,2);
return;
}
Pattern p = Pattern.compile(pattern);
Long size = new Long(clob.size());
char[] cbuff = new char[size.intValue()];
clob.getReader().read(cbuff);
Matcher m = p.matcher( new String(cbuff) );
if ( m.matches() ) {
set(3,1);
return;
}
set(3,0);
}

}
drop function MATCH_CLOB;
call sqlj.refresh_classes();

CREATE FUNCTION MATCH_CLOB(pattern VARCHAR(4000), clob CLOB(1048576))
RETURNS INTEGER
FENCED
NO SQL
LANGUAGE JAVA
DETERMINISTIC
PARAMETER STYLE DB2GENERAL
EXTERNAL NAME 'com.rhi.bb.utils.udf.RegExpUdf!MatchClob';

select MATCH_CLOB('\w{3}\d{2}',lob) from delme;
0
1

[gimme_this_gimme_that]

For those of you who might be interested ...

Here is the Java UDF for regular expression matching over varchars:

package com.rhi.bb.utils.udf;

import java.sql.SQLException;

import java.util.regex.Pattern;
import java.util.regex.Matcher;

public class RegExp {

public static int MatchVarchar(String pattern, String varchar) throws
SQLException
{
try {
Pattern p = Pattern.compile(pattern);
Matcher m = p.matcher( varchar );
if ( m.matches() ) {
return 1;
}
return 0;
} catch (Exception ex) {
throw new SQLException("RegExp.MatchVarchar failed " +
ex.getCause());
}
}
}
drop function MATCH_VARCHAR;

call sqlj.refresh_classes();
CREATE FUNCTION MATCH_VARCHAR(pattern VARCHAR(4000), string
VARCHAR(4000))
RETURNS INTEGER
FENCED
VARIANT
NO SQL
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME 'com.rhi.bob.utils.udf.RegExp!MatchVarchar';

select MATCH_VARCHAR('\w{3}\d{2}','abc33') from sysibm.sysdummy1;