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

List:       hsqldb-user
Subject:    Re: [Hsqldb-user] Error with Clob
From:       "Fred Toussi" <fredt () users ! sourceforge ! net>
Date:       2011-06-22 22:50:42
Message-ID: 1308783042.25081.1466201333 () webmail ! messagingengine ! com
[Download RAW message or body]

Use CLOB in your tables, but use VARCHAR in the procedure parameter
definition and arguments.

Use a cast here:

CALL testtableupdate(CAST(newrow.clobcolumn AS VARCHAR(10000));

In general, BLOB and CLOB cannot be used as parameters to Java routines,
but they should work in SQL routines.

Fred

On Wed, 22 Jun 2011 15:25 -0700, "Tmo_hsqldb_q" <tmoy@bgcpartners.com>
wrote:
>
> 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
>

------------------------------------------------------------------------------
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]