Error with Clob

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Error with Clob

Tmo_hsqldb_q
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Fred Toussi-2
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" <[hidden email]>
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 [hidden email]
> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Tmo_hsqldb_q
In reply to this post by Tmo_hsqldb_q
Hi Fred,

We now have requirements to handle payloads larger than what varchar(1000) hold.  1 Megabyte to be exact.  I tried using longvarchar specifying no character size limit and it works in handling large payloads.  My only concern is the 16Megabyte allocation that the HSQLDB Documentation says it will assign.  Does it assign 16 megabytes for every row or a smaller amount initially?  We handle on avg smaller payloads 200,000 characters in size, but it can go up to 1Megabyte.

many thanks,
Tony
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Fred Toussi-2
If your data is 200 000 characters or larger per item, then you should
use CLOB. There is also a database option to translate LONGVARCHAR to
CLOB, but it's easier to specify clob directly. For example:

CREATE TABLE T (ID INT PRIMARY KEY, DATA CLOB(16M)

The above means a clob up to 16 MB can be stored. It allocates space
only according to the size of the actual data. For example if a data
item is 128 000 characters, it assigns 256 KB.

You could use LONGVARCHAR for your data and it also allocates space as
needed , but it will slow down your app.

The latest snapshot jars for version 2.3.0 also support clob compression
for storage. The final version will be released next month.

Fred


On Thu, Mar 21, 2013, at 20:43, Tmo_hsqldb_q wrote:

> Hi Fred,
>
> We now have requirements to handle payloads larger than what
> varchar(1000)
> hold.  1 Megabyte to be exact.  I tried using longvarchar specifying no
> character size limit and it works in handling large payloads.  My only
> concern is the 16Megabyte allocation that the HSQLDB Documentation says
> it
> will assign.  Does it assign 16 megabytes for every row or a smaller
> amount
> initially?  We handle on avg smaller payloads 200,000 characters in size,
> but it can go up to 1Megabyte.
>
> many thanks,
> Tony
>
>
>
> --
> View this message in context:
> http://hsqldb.10974.n7.nabble.com/Error-with-Clob-tp151p3856.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@lists">[hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user">hsqldb-user

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_mar
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Tmo_hsqldb_q
In reply to this post by Tmo_hsqldb_q
Adding my test code incase you need it:



CREATE TABLE testtable
(
   longvarcharcolumn LONGVARCHAR     NOT NULL
);



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




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




import java.sql.*;

public class TestTableTrigger
{

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

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

}






CREATE PROCEDURE testlongvarchar(testmessage longvarchar)
MODIFIES SQL DATA
BEGIN ATOMIC

     INSERT INTO testtable(longvarcharcolumn)
     VALUES(testmessage);

END



















import java.sql.*;
import java.io.*;

public class TestHSQL
{

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

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

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

                        BufferedReader br = new BufferedReader(new FileReader("C:\\TonyProject\\test\\testhsqldblongvarchar\\verylongstring.txt"));

                        String verylongString = br.readLine();

                        System.out.println("Got verylongString = " + verylongString);


                        CallableStatement cstmtLongVarchar = hsqlConn.prepareCall("call testlongvarchar(?)");

                        cstmtLongVarchar.setString(1, verylongString);

                        cstmtLongVarchar.execute();

                        cstmtLongVarchar.close();

                        hsqlConn.close();

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

        }

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

}
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Fred Toussi-2
I don't know what this test is supposed to prove. Why not just insert
the data and perform a SELECT afterwards to see if your data is there.
Use an ID primary key column for your table to be able to select
individual rows from it.

Note you are also using a lot of memroy with LONGVARCHAR.

Fred

On Thu, Mar 21, 2013, at 21:07, Tmo_hsqldb_q wrote:

> Adding my test code incase you need it:
>
>
>
> CREATE TABLE testtable
> (
>    longvarcharcolumn LONGVARCHAR     NOT NULL
> );
>
>
>
> CREATE TRIGGER testtable_TRIG AFTER INSERT ON testtable
> referencing NEW ROW AS newrow
> FOR EACH ROW WHEN (newrow.longvarcharcolumn IS NOT NULL)
> CALL testtableupdate(newrow.longvarcharcolumn);
>
>
>
>
> CREATE PROCEDURE testtableupdate(testmessage longvarchar)
> MODIFIES SQL DATA
> LANGUAGE JAVA
> EXTERNAL NAME 'CLASSPATH:TestTableTrigger.testTableUpdate'
>
>
>
>
> import java.sql.*;
>
> public class TestTableTrigger
> {
>
>         public static void testTableUpdate(Connection triggerConn, String
> testmessage) throws Exception
>         {
>
>                         System.out.println("TestTableTrigger -
>                         testmessage =
> " + testmessage);
>         }
>
> }
>
>
>
>
>
>
> CREATE PROCEDURE testlongvarchar(testmessage longvarchar)
> MODIFIES SQL DATA
> BEGIN ATOMIC
>
>      INSERT INTO testtable(longvarcharcolumn)
>      VALUES(testmessage);
>
> END
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> import java.sql.*;
> import java.io.*;
>
> public class TestHSQL
> {
>
> public void runTest()
> {
> ResultSet rs = null;
>     try {
>        Class.forName("org.hsqldb.jdbc.JDBCDriver" );
>
>     Connection hsqlConn =
> DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:9107/facadestore;ifexists=true",
> "SA", "");
>
> System.out.println("Got connection");
>
> BufferedReader br = new BufferedReader(new
> FileReader("C:\\TonyProject\\test\\testhsqldblongvarchar\\verylongstring.txt"));
>
> String verylongString = br.readLine();
>
> System.out.println("Got verylongString = " + verylongString);
>
>
> CallableStatement cstmtLongVarchar = hsqlConn.prepareCall("call
> testlongvarchar(?)");
>
> cstmtLongVarchar.setString(1, verylongString);
>
> cstmtLongVarchar.execute();
>
> cstmtLongVarchar.close();
>
> hsqlConn.close();
>
> } catch (Exception e) {
>        e.printStackTrace();
>        return;
>     }
>
> }
>
> public static void main(String args[])
> {
>        TestHSQL th = new TestHSQL();
>        th.runTest();
> }
>
> }
>
>
>
>
> --
> View this message in context:
> http://hsqldb.10974.n7.nabble.com/Error-with-Clob-tp151p3858.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_mar
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Tmo_hsqldb_q
Understood.  The compression will also be very useful!
Correct me if i'm wrong.  In the below example I'll have to cast the clobcolumn to longvarchar in the Trigger declaration, because as you described earlier in the chain:
"In general, BLOB and CLOB cannot be used as parameters to Java routines, "





CREATE TABLE testtable3
(
   clobcolumn CLOB(1M)     NOT NULL
);



CREATE TRIGGER testtable3_TRIG AFTER INSERT ON testtable3
referencing NEW ROW AS newrow
FOR EACH ROW WHEN (newrow.clobcolumn IS NOT NULL)
CALL testtableupdate3(CAST(newrow.clobcolumn AS LONGVARCHAR));




CREATE PROCEDURE testtableupdate3(testmessage LONGVARCHAR)
MODIFIES SQL DATA
LANGUAGE JAVA
EXTERNAL NAME 'CLASSPATH:TestTableTrigger.testTableUpdate'




import java.sql.*;

public class TestTableTrigger
{

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

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

}


CREATE PROCEDURE testclob(testmessage CLOB(1M))
MODIFIES SQL DATA
BEGIN ATOMIC

     INSERT INTO testtable3(clobcolumn)
     VALUES(testmessage);

END
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Tmo_hsqldb_q
Fred,
I forgot to mention: The test code looks very simplistic but it represents what my production system is doing.  In my production system the Trigger does much more than just print out the value.

regards,
Tony
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Fred Toussi-2
In reply to this post by Tmo_hsqldb_q
Yes, it looks right. You cannot pass a CLOB parameter to Java routines.

As LONGVARCHAR is simply an alias for VARCHAR(16M) or CLOB(16M)
depending on the setting, it would be better to specify VARCHAR with a
size. If you set a 1M size for your clob, larger data items will be
rejected.

Fred

On Thu, Mar 21, 2013, at 21:39, Tmo_hsqldb_q wrote:

> Understood.  The compression will also be very useful!
> Correct me if i'm wrong.  In the below example I'll have to cast the
> clobcolumn to longvarchar in the Trigger declaration, because as you
> described earlier in the chain:
> "In general, BLOB and CLOB cannot be used as parameters to Java routines,
> "
>
>
>
>
>
> CREATE TABLE testtable3
> (
>    clobcolumn CLOB(1M)     NOT NULL
> );
>
>
>
> CREATE TRIGGER testtable3_TRIG AFTER INSERT ON testtable3
> referencing NEW ROW AS newrow
> FOR EACH ROW WHEN (newrow.clobcolumn IS NOT NULL)
> CALL testtableupdate3(CAST(newrow.clobcolumn AS LONGVARCHAR));
>
>
>
>
> CREATE PROCEDURE testtableupdate3(testmessage LONGVARCHAR)
> MODIFIES SQL DATA
> LANGUAGE JAVA
> EXTERNAL NAME 'CLASSPATH:TestTableTrigger.testTableUpdate'
>
>
>
>
> import java.sql.*;
>
> public class TestTableTrigger
> {
>
>         public static void testTableUpdate(Connection triggerConn, String
> testmessage) throws Exception
>         {
>
>                         System.out.println("TestTableTrigger -
>                         testmessage =
> " + testmessage);
>         }
>
> }
>
>
> CREATE PROCEDURE testclob(testmessage CLOB(1M))
> MODIFIES SQL DATA
> BEGIN ATOMIC
>
>      INSERT INTO testtable3(clobcolumn)
>      VALUES(testmessage);
>
> END
>
>
>
>
> --
> View this message in context:
> http://hsqldb.10974.n7.nabble.com/Error-with-Clob-tp151p3860.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_mar
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Tmo_hsqldb_q
Hi Fred

I might have misunderstood the point you made: "LONGVARCHAR is simply an alias for VARCHAR(16M)"
I tried the following but get an error when creating the Trigger:
"unexpected token: 20M : line: 4 / Error Code: -5581 / State: 42581"


CREATE TABLE testtable3
(
   clobcolumn CLOB(20M)     NOT NULL
);


CREATE TRIGGER testtable3_TRIG AFTER INSERT ON testtable3
referencing NEW ROW AS newrow
FOR EACH ROW WHEN (newrow.clobcolumn IS NOT NULL)
CALL testtableupdate3(CAST(newrow.clobcolumn AS VARCHAR(20M)));
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Fred Toussi-2
The column definition for VARCHAR(n) does not accept the shortcuts such
as 20M, but accepts 20000000 or other length values.

use VARCHAR(20725760) instead of VARCHAR(20M).

Fred

On Fri, Mar 22, 2013, at 17:37, Tmo_hsqldb_q wrote:

> Hi Fred
>
> I might have misunderstood the point you made: "LONGVARCHAR is simply an
> alias for VARCHAR(16M)"
> I tried the following but get an error when creating the Trigger:
> "unexpected token: 20M : line: 4 / Error Code: -5581 / State: 42581"
>
>
> CREATE TABLE testtable3
> (
>    clobcolumn CLOB(20M)     NOT NULL
> );
>
>
> CREATE TRIGGER testtable3_TRIG AFTER INSERT ON testtable3
> referencing NEW ROW AS newrow
> FOR EACH ROW WHEN (newrow.clobcolumn IS NOT NULL)
> CALL testtableupdate3(CAST(newrow.clobcolumn AS VARCHAR(20M)));
>
>
>
>
> --
> View this message in context:
> http://hsqldb.10974.n7.nabble.com/Error-with-Clob-tp151p3863.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_mar
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Tmo_hsqldb_q
Thanks.  Could you possibly have meant VARCHAR(20971520) as 20M can hold 1,048,576 x 20 characters?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Tmo_hsqldb_q
Is it possible to dynamically at runtime detect the column size of newrow.clobcolumn and then use that size and pass it into the varchar(<size>) in the cast below?


CREATE TRIGGER testtable3_TRIG AFTER INSERT ON testtable3
referencing NEW ROW AS newrow
FOR EACH ROW WHEN (newrow.clobcolumn IS NOT NULL)
CALL testtableupdate3(CAST(newrow.clobcolumn AS VARCHAR(<at runtime enter size here?>)));


I know this is not necessary as VARCHAR only allocates the memory that is required.  But just double checking to see if something like that is possible.

many thanks,
Tony
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Error with Clob

Fred Toussi-2
You can find the size of any column, including CLOB, by querying the
INFORMATION_SCHEMA.COLUMNS table.

But you cannot write a cast with a dynamic size.

As the VARCHAR parameter is in memory, it makes good sense to use a
fixed size limit to avoid out of memory errors at runtime.

Fred

On Mon, Mar 25, 2013, at 19:16, Tmo_hsqldb_q wrote:

> Is it possible to dynamically at runtime detect the column size of
> newrow.clobcolumn and then use that size and pass it into the
> varchar(<size>) in the cast below?
>
>
> CREATE TRIGGER testtable3_TRIG AFTER INSERT ON testtable3
> referencing NEW ROW AS newrow
> FOR EACH ROW WHEN (newrow.clobcolumn IS NOT NULL)
> CALL testtableupdate3(CAST(newrow.clobcolumn AS VARCHAR(<at runtime enter
> size here?>)));
>
>
> I know this is not necessary as VARCHAR only allocates the memory that is
> required.  But just double checking to see if something like that is
> possible.
>
> many thanks,
> Tony
>
>
>
> --
> View this message in context:
> http://hsqldb.10974.n7.nabble.com/Error-with-Clob-tp151p3866.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> _______________________________________________
> Hsqldb-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_mar
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...