Mailing-List: contact empire-db-dev-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: empire-db-dev@incubator.apache.org
Received-SPF: pass (athena.apache.org: local policy)
Date: Mon, 13 Jul 2009 23:18:56 +0200
From: news@eknet.org
To: empire-db-dev@incubator.apache.org
Subject: error using apache derby and CLOB datatype
Message-ID: <20090713211855.GA14317@green.ekch>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="SUOF0GtieIMvvwua"
Content-Disposition: inline
User-Agent: Mutt/1.5.18 (2008-05-17)

--SUOF0GtieIMvvwua
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline


hi there

i was just experimenting with apache derby and found the following
situation:

one of my columns needs to store really long texts. so what i did with
empire-db is this:

  C_DESCRIPTION = addColumn("description", DataType.CLOB, 1024*1024, false);

not being really sure, if this is the desired way to go, it worked
with mysql (5.0.27). the same results in a "BadSQLGrammarException"
using apache derby 10.4.2.0. the CLOB datatype is translated into
this:

  CREATE TABLE betmarket (
  ...
     description LONGTEXT, 
  ...
  
im really new to apache derby, but having a quick look at their page i
could not find the data type "LONGTEXT" in their manual. they have
CLOB and LONG VARCHAR as i could see. where LONG VARCHAR goes up to
32700 characters and a CLOB may be up to 2,147,483,647. 

i will attach a tiny patch which worked in my case, but im not sure if
i missed something...

kind regards, eike

--SUOF0GtieIMvvwua
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="DBDatabaseDriverDerby.java.patch"

Index: src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java
===================================================================
--- src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java	(revision 793701)
+++ src/main/java/org/apache/empire/db/derby/DBDatabaseDriverDerby.java	(working copy)
@@ -573,7 +573,11 @@
             }
                 break;
             case CLOB:
-                sql.append("LONGTEXT");
+            	if (c.getSize()<32700)
+            		sql.append("LONG VARCHAR");
+            	else
+            		sql.append("CLOB");
+
                 break;
             case BLOB:
                 sql.append("BLOB");

--SUOF0GtieIMvvwua--