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

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

Post by Martin Conb » Wed, 16 Apr 2003 00:31:29



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

 
 
 

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

Post by Martin Conb » Sat, 19 Apr 2003 02:07:22


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());
        }
    }

Quote:}

/* 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


> 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