[prev in list] [next in list] [prev in thread] [next in thread] 

List:       hsqldb-user
Subject:    [Hsqldb-user]  Error with Clob
From:       Tmo_hsqldb_q <tmoy () bgcpartners ! com>
Date:       2011-06-22 22:25:28
Message-ID: 31907330.post () talk ! nabble ! com
[Download RAW message or body]


In HSQLDB 2.2.3 I have the following table:

CREATE TABLE testtable
(
   clobcolumn CLOB(1000)     NOT NULL 
);



I have created the trigger associated with that table:

CREATE TRIGGER testtable_TRIG AFTER INSERT ON testtable
referencing NEW ROW AS newrow
FOR EACH ROW WHEN (newrow.clobcolumn IS NOT NULL)
CALL testtableupdate(newrow.clobcolumn);



But not before creating the procedure linked to that table:

CREATE PROCEDURE testtableupdate(testmessage clob(1000))
MODIFIES SQL DATA
LANGUAGE JAVA
EXTERNAL NAME 'CLASSPATH:TestTableTrigger.testTableUpdate'



And the Java class associated with that procedure:

import java.sql.*;

public class TestTableTrigger
{

	public static void testTableUpdate(Connection triggerConn, String
testmessage) throws Exception
	{

			System.out.println("TestTableTrigger - testmessage = " + testmessage);
	}

}



I have a stored procedure that inserts into the testtable:

CREATE PROCEDURE testclob(testmessage clob(1000))
MODIFIES SQL DATA
BEGIN ATOMIC

     INSERT INTO testtable(clobcolumn) 
     VALUES(testmessage);

END



As well as the tester java class that calls the stored procedure passing in
a string:

import java.sql.*;

public class TestHSQL
{

	public void runTest()
	{
		ResultSet rs = null;
    	try {
    	    Class.forName("org.hsqldb.jdbc.JDBCDriver" );

    		Connection hsqlConn =
DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:9105/ads;ifexists=true",
"SA", "");

			System.out.println("Got connection");

			CallableStatement cstmtTestClob = hsqlConn.prepareCall("call
testclob(?)");
//			cstmtTestClob.clearParameters();
			cstmtTestClob.setString(1, "teststring");
			cstmtTestClob.execute();

			cstmtTestClob.close();

			hsqlConn.close();

		} catch (Exception e) {
    	    e.printStackTrace();
    	    return;
    	}

	}

	public static void main(String args[])
	{
       TestHSQL th = new TestHSQL();
       th.runTest();
	}

}



I get the following exception when running the Tester:

java.sql.SQLException: Java execution: TESTTABLEUPDATE
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.JDBCCallableStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)
        at TestHSQL.runTest(TestHSQL.java:19)
        at TestHSQL.main(TestHSQL.java:35)




This error doesn't occur when I switch usage of clob(1000) to varchar(1000). 
In the project I'm working on I need usage of a clob(10000)
or equivalent.


regards,
Tony
-- 
View this message in context: http://old.nabble.com/Error-with-Clob-tp31907330p31907330.html
Sent from the HSQLDB - User mailing list archive at Nabble.com.


------------------------------------------------------------------------------
Simplify data backup and recovery for your virtual environment with vRanger.
Installation's a snap, and flexible recovery options mean your data is safe,
secure and there when you need it. Data protection magic?
Nope - It's vRanger. Get your free trial download today.
http://p.sf.net/sfu/quest-sfdev2dev
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
[prev in list] [next in list] [prev in thread] [next in thread]