Passing a Clob argument to a stored procedure from Java using JDBC

[Martin Conboy]

Hi all,

I am writing code in Java that is to call a stored procedure on a DB2
database that updates a table, and one of the columns in the table is
of type CLOB. Therefore the SP must be passed a CLOB as an argument.
However I can find no way to do this, can someone help?

The sample code below illustrates the problem.

Firstly, create the table and the stored procedure. (This piece of SQL
script can be run using CommandCenter if you first set CC's SQL
delimiter character (Tools->Tool Settings menu) to be the char '/').

--------------------------------------------------------------------
-- Create test table, sequence for IDs, and stored procedure. MWC
--------------------------------------------------------------------
DROP TABLE TESTTABLE/
DROP SEQUENCE SEQTESTID RESTRICT/
DROP PROCEDURE SPINSTESTTABLE/

CREATE TABLE TESTTABLE (
TESTID NUMERIC(18) NOT NULL,
COL1 CLOB(65535) ,
CONSTRAINT PKTESTTABLE PRIMARY KEY (TESTID)
)/

CREATE SEQUENCE SEQTESTID START WITH 1 INCREMENT BY 1 NOMAXVALUE
NOCYCLE ORDER/

CREATE PROCEDURE SPINSTESTTABLE(
IN XCOL1 CLOB(65535) )
LANGUAGE SQL
P1: BEGIN
DECLARE XTESTID NUMERIC(18);
SELECT NEXTVAL FOR SEQTESTID INTO XTESTID FROM sysibm.sysdummy1;
INSERT INTO TESTTABLE VALUES (XTESTID, XCOL1);
END P1
/
------------------------=:{ Ends }:=--------------------------------

This creates the table TESTTABLE, and the stored procedure to update
it,
SPINSTESTTABLE that takes the Clob argument.

The java code to call the stored procedure looks like this:
//... starts
System.out.println("About to register DB2 driver");
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
System.out.println("About to connect to DB2 database");
Connection connection =
DriverManager.getConnection("jdbc:db2:MYDBINSTANCE", "myaccnt",
"mypasswd");

// Create a callable statement and execute the sql to call
the stored procedure.
CallableStatement cstmt = connection.prepareCall("{call
SPINSTESTTABLE (?)}");
String data = new String("This is going to be the clob
parameter contents, think of it as a huge great string");
cstmt.setCharacterStream(1, new
java.io.StringReader(data), data.length());
System.out.println("About to execute");
cstmt.execute();
System.out.println("Execute worked Ok.");
cstmt.close();
//... ends

And this code, when executed, results in:
...
About to register DB2 driver
About to connect to DB2 database
About to execute
SQLException: [IBM][CLI Driver][DB2/NT] SQL0171N The data type,
length or value
of argument "0" of routine "SPINSTESTTABLE" is incorrect.
SQLSTATE=42815
...

All suggestions greatfully received!

MWC

[Martin Conboy]

Hi all,

And in addition to passing a clob from a Java app to a DB2 stored
procedure, I also want to write a DB2 stored procedure in Java that
takes a clob as an argument.

The sample code below illustrates the problem:
Firstly, create the table.
DROP TABLE TESTTABLE/

CREATE TABLE TESTTABLE (
TESTID NUMERIC(18) NOT NULL,
COL1 CLOB(65535) ,
CONSTRAINT PKTESTTABLE PRIMARY KEY (TESTID)
)/

Then create this Java file:

/** DB2 stored procedure CLOBIN */
import java.sql.*;
import java.math.*;
public class SPJVACLOBIN
{
public static void SPJVACLOBIN( Clob CLOBCOL )
{
long newID = 0;
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try
{
con = DriverManager.getConnection("jdbc:default:connection");

stmt = con.prepareStatement( "INSERT INTO TESTTABLE
(TESTID,COL1) VALUES (?,?)" );
stmt.setBigDecimal (1, BigDecimal.valueOf(1000));
stmt.setClob (2, CLOBCOL);

int updateCount = stmt.executeUpdate();
stmt.close();
con.close();
}
catch (Exception ex)
{
throw new RuntimeException("SPJVACLOBIN error
"+ex.getMessage());
}
}
}
/* Ends */

And trying to install this procedure the Stored Procedure Builder
generates the message "Parameter name CLOBCOL, Java type Clob is not
supported for a stored procedure parameter." (I have also tried type
COM.ibm.db2.app.Clob, with same result.)

I need to pass large objects into the database, VARCHAR is not big
enough. For security reasons my customer won't allow a C compiler on
his machine, so I cannot use an SQL sp.

help!!!

MartinC

mar...@lineone.net (Martin Conboy) wrote in message news:<f910c083.03041...@posting.google.com>...