Community
Participate
Working Groups
Build Identifier: 2.1.0.v20100614-r7608 When generation a DDL from persistence.xml for HSQL the String fields of the entities are specified without a length. Upon execution of the statements HSQL will throw a java.sql.SQLException (length must be specified in type definition: VARCHAR). This did not occur in version 2.0.2 (2.0.2.v20100323-r6872) where String members would be translated to varchar fields with length 255. Reproducible: Always Steps to Reproduce: 1. Create entity with al least one String member 2. Create persistence.xml and enable ddl generation (eclipselink.ddl-generation => create-tables and eclipselink.ddl-generation.output-mode => database) 3. Trying to create an EntityManagerFactory from the Persistent Unit would trigger the ddl generation and the exception.
>I happen to have an EE example running on JBoss 6 with DDL gen on for the default HSQL datasource - will verify shortly
Created attachment 174115 [details] Maven based test case Attached zip file contains a simple maven based test case. It uses a tesng unit test to load the persistent unit and tries to persist an entity called Thing. With eclipselink version 2.0.2 this unit test succeeds. With 2.1.0 the test fails because the backing table is not created due to an invalid create statement. Simply switch versions by updating the pom. Java 6 is needed to run the test case.
Have you already found time to verify this issue? The attached (maven based )test case could help. (In reply to comment #1) > >I happen to have an EE example running on JBoss 6 with DDL gen on for the default HSQL datasource - will verify shortly
>I'll check it out tomorrow back at the office - it is the long weekend right now >temp assigning so it shows up in the queue >i don't use maven
>Testing with http://wiki.eclipse.org/EclipseLink/Examples/JPA/JBoss_Web_Tutorial >running 14:11:41,530 INFO [ServerImpl] Release ID: JBoss [EAP] 5.0.0.GA (build: SVNTag=JBPAPP_5_0_0_GA date=200910202128) 14:11:44,340 INFO [ServerInfo] Java Runtime: Java(TM) SE Runtime Environment (build 1.6.0_17-b04) 14:11:44,340 INFO [ServerInfo] Java VM: Java HotSpot(TM) Client VM 14.3-b01,Sun Microsystems Inc. 14:11:44,340 INFO [ServerInfo] OS-System: Windows 7 6.1,x86 Database: HSQL Database Engine Version: 1.8.0 Driver: HSQL Database Engine Driver Version: 1.8.0 >using the PU <persistence-unit name="example2" transaction-type="JTA"> <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider> <jta-data-source>java:/DefaultDS</jta-data-source> <properties> <property name="eclipselink.target-server" value="JBoss"/> <property name="eclipselink.target-database" value="HSQL"/> <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/> <property name="eclipselink.ddl-generation.output-mode" value="database"/> >and the CM PU in the SSB @Stateless(name="ApplicationService", mappedName="ApplicationService") @Remote(ApplicationServiceRemote.class) public class ApplicationService implements ApplicationServiceRemote { @PersistenceContext(unitName="example2", type=PersistenceContextType.TRANSACTION) private EntityManager entityManager;
>My Entity is the same as yours with a field access String member @Entity @Table(name="EL_CELL") public class Cell implements Serializable { private static final long serialVersionUID = -2507245383514759526L; @Id // keep the sequence column name under 30 chars to avoid an ORA-00972 @SequenceGenerator(name="EL_SEQUENCE_CELL", sequenceName="EL_CELL_SEQ", allocationSize=25) @GeneratedValue(generator="EL_SEQUENCE_CELL") private BigInteger id; @Column(name="TSEQ") private String transitionSequence; //@Column(name="STATE") private String state; ... // added this midway through testing to verify VARCHAR field can be set public Cell() { state = "0"; } } >I see the TSEQ VARCHAR field dropped/created ok in the following DDL generation logs and SQL 14:13:32,397 INFO [STDOUT] [EL Fine]: 2010-11-03 14:13:32.397--ServerSession(6276604)--Connection(7007546)--Thread(Thread[http-127.0.0.1-8080-1,5,jboss])--DROP TABLE EL_CELL 14:13:32,397 INFO [STDOUT] [EL Finest]: 2010-11-03 14:13:32.397--ServerSession(6276604)--Thread(Thread[http-127.0.0.1-8080-1,5,jboss])--Execute query DataModifyQuery(sql="CREATE TABLE EL_CELL (ID BIGINT NOT NULL, STATE VARCHAR, TSEQ VARCHAR, RIGHT_ID BIGINT, ACELLATTRIBUTE_ID INTEGER, PRIMARY KEY (ID))") >Runtime testing shows both "0" and Null being set ok http://127.0.0.1:8080/enterprise/FrontController?action=demo >results after DDL generation 14:48:29,882 INFO [STDOUT] [EL Finest]: 2010-11-03 14:48:29.882--UnitOfWork(22137484)--Thread(Thread[http-127.0.0.1-8080-1,5,jboss])--Execute query InsertObjectQuery(org.eclipse.persistence.example.jpa.server.business.Cell@21860931( id: 4 state: 0 left: null right: null parent: HashSet@47705762 references: HashSet@47705762)) 14:48:29,882 INFO [STDOUT] [EL Fine]: 2010-11-03 14:48:29.882--ClientSession(13154896)--Connection(13691200)--Thread(Thread[http-127.0.0.1-8080-1,5,jboss])--INSERT INTO EL_CELL (ID, STATE, TSEQ, ACELLATTRIBUTE_ID, RIGHT_ID) VALUES (?, ?, ?, ?, ?) bind => [4, 0, null, null, null] 14:48:30,101 INFO [STDOUT] [EL Finest]: 2010-11-03 14:48:30.101--UnitOfWork(12378075)--Thread(Thread[http-127.0.0.1-8080-1,5,jboss])--Register the existing object org.eclipse.persistence.example.jpa.server.business.Cell@10452323( id: 4 state: 0 left: null right: null parent: HashSet@41022608 references: HashSet@41022608) >I am sorry but much as I would like to, I cannot reproduce your error using EclipseLink 2.1.2 (18 Oct) and HSQL 1.8 on the SUN 1.6 JVM with a similar model >closing bug If you have a more specific version of HSQL and/or details logs and exception stack traces - please forward them
Tested it with eclipselink version 2.1.2.v20101023-r8395 (maven artefact version 2.1.2-RC2) but error remains. It might be the hsql version that causes this different behaviour. I am using 2.0.0 instead of 1.8.0. Bellow a snippet from test output: Running TestSuite 4-nov-2010 7:40:20 org.eclipselink.TestThing loadEntityManagerFactory INFO: Building JPA EntityManager for unit tests [EL Info]: 2010-11-04 07:40:23.406--ServerSession(21101238)--EclipseLink, version: Eclipse Persistence Services - 2.1.2.v20101023-r8395 [EL Info]: 2010-11-04 07:40:24.187--ServerSession(21101238)--file:/C:/JAVA-DEV/eclipse-workspaces/workspace-uwv/hsql-ddl-generation/target/c lasses/_testPersistentUnit login successful [EL Warning]: 2010-11-04 07:40:24.265--ServerSession(21101238)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.2.v20101023 -r8395): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: length must be specified in type definition: VARCHAR Error Code: -5599 Call: CREATE TABLE THING (ID BIGINT NOT NULL, STRVALUE VARCHAR, INTVALUE INTEGER, DATEVALUE TIMESTAMP, PRIMARY KEY (ID)) Query: DataModifyQuery(sql="CREATE TABLE THING (ID BIGINT NOT NULL, STRVALUE VARCHAR, INTVALUE INTEGER, DATEVALUE TIMESTAMP, PRIMARY KEY (ID ))")
It is the hsql version. Below is the test output when I'm using hsql version 1.8.0.10 and generations is completed without any errors. Running TestSuite 4-nov-2010 7:50:29 org.eclipselink.TestThing loadEntityManagerFactory INFO: Building JPA EntityManager for unit tests [EL Info]: 2010-11-04 07:50:32.464--ServerSession(20526817)--EclipseLink, version: Eclipse Persistence Services - 2.1.2.v20101023-r8395 [EL Info]: 2010-11-04 07:50:32.98--ServerSession(20526817)--file:/C:/JAVA-DEV/eclipse-workspaces/workspace-uwv/hsql-ddl-generation/target/classes/_testPersistentUnit login successful 4-nov-2010 7:50:33 org.eclipselink.TestThing closeEntityManagerFactory INFO: Closing Entity Manager Factory [EL Info]: 2010-11-04 07:50:33.417--ServerSession(20526817)--file:/C:/JAVA-DEV/eclipse-workspaces/workspace-uwv/hsql-ddl-generation/target/classes/_testPersistentUnit logout successful Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 5.156 sec Results : Tests run: 1, Failures: 0, Errors: 0, Skipped: 0 ##### end of test output I changed the status to reopened since this issue can be reproduced with 2.0.0 hsql.
Yes, I can confirm. Everything worked fine with hsql 1.8.x but updating to 2.0 caused this issue to occur. Also, it would be great if this issue could be fixed also in 2.1-branch.
>retesting with the higher version HSQL 2.0.1
>Reproduced issue with 2.0.0 running EclipseLink trunk with an SE JPA app <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver"/> <property name="eclipselink.target-database" value="HSQL"/> <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:hsql://127.0.0.1"/> <property name="javax.persistence.jdbc.user" value="sa"/> <property name="javax.persistence.jdbc.password" value=""/> <property name="eclipselink.logging.level" value="ALL"/> <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/> <property name="eclipselink.ddl-generation.output-mode" value="database"/> Call: DROP TABLE EL_CELL Query: DataModifyQuery(sql="DROP TABLE EL_CELL") [EL Finest]: 2010-11-04 10:30:13.815--ServerSession(11108810)--Thread(Thread[main,5,main])--Execute query DataModifyQuery(sql="CREATE TABLE EL_CELL (ID BIGINT NOT NULL, STATE VARCHAR, TSEQ VARCHAR, ACELLATTRIBUTE_ID INTEGER, RIGHT_ID BIGINT, PRIMARY KEY (ID))") [EL Fine]: 2010-11-04 10:30:13.815--ServerSession(11108810)--Connection(26435810)--Thread(Thread[main,5,main])--CREATE TABLE EL_CELL (ID BIGINT NOT NULL, STATE VARCHAR, TSEQ VARCHAR, ACELLATTRIBUTE_ID INTEGER, RIGHT_ID BIGINT, PRIMARY KEY (ID)) [EL Fine]: 2010-11-04 10:30:13.815--ServerSession(11108810)--Thread(Thread[main,5,main])--CALL 1 [EL Warning]: 2010-11-04 10:30:13.815--ServerSession(11108810)--Thread(Thread[main,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - @VERSION@.@QUALIFIER@): org.eclipse.persistence.exceptions.DatabaseException >Internal Exception: java.sql.SQLException: length must be specified in type definition: VARCHAR Error Code: -5599
>Note: both of the following versions are flagged by McAfee as containing viruses and/or spyware - so run a full scan after installing HSQL http://sourceforge.net/projects/hsqldb/files/alpha_beta/hsqldb-2.0.1-rc2.zip/download http://downloads.sourceforge.net/project/hsqldb/hsqldb/hsqldb_2_0/hsqldb-2.0.0.zip?r=http%3A%2F%2Fsourceforge.net%2Fprojects%2Fhsqldb%2Ffiles%2F&ts=1288880075&use_mirror=iweb - see Screencap >Fix: HSQLPlatform.java must either be upgraded from 1.8.1 to 2.0.0 or a new HSQL2Platform.java must be subclassed
Created attachment 182375 [details] HSQL 2.0.0 download zip is flagged as containing viruses and/or spyware
Created attachment 182376 [details] HSQL 2.0.1 alpha download zip is flagged as containing viruses and/or spyware >downloaded 2.0.0 anyway - seem to be ok
Ibiblio's Maven repository contains 2.0.0 version http://mirrors.ibiblio.org/pub/mirrors/maven2/org/hsqldb/hsqldb/2.0.0/
>stacktrace org.eclipse.persistence.example.jpa.server.common.DDLGenerationClient at localhost:52014 Thread [main] (Suspended (breakpoint at line 333 in DatabaseException)) DatabaseException.sqlException(SQLException, Call, Accessor, AbstractSession, boolean) line: 333 DatabaseAccessor.basicExecuteCall(Call, AbstractRecord, AbstractSession) line: 683 DatabaseAccessor.executeCall(Call, AbstractRecord, AbstractSession) line: 526 ServerSession.executeCall(Call, AbstractRecord, DatabaseQuery) line: 530 CallQueryMechanism(DatasourceCallQueryMechanism).executeCall(DatasourceCall) line: 206 CallQueryMechanism(DatasourceCallQueryMechanism).executeCall() line: 192 CallQueryMechanism(DatasourceCallQueryMechanism).executeNoSelectCall() line: 235 CallQueryMechanism(DatasourceCallQueryMechanism).executeNoSelect() line: 215 DataModifyQuery.executeDatabaseQuery() line: 85 DataModifyQuery(DatabaseQuery).execute(AbstractSession, AbstractRecord) line: 781 ServerSession(AbstractSession).internalExecuteQuery(DatabaseQuery, AbstractRecord) line: 2404 ServerSession(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord, int) line: 1303 ServerSession(AbstractSession).executeQuery(DatabaseQuery, AbstractRecord) line: 1285 ServerSession(AbstractSession).executeQuery(DatabaseQuery) line: 1245 ServerSession(AbstractSession).executeNonSelectingCall(Call) line: 1014 TableDefinition(DatabaseObjectDefinition).createOnDatabase(AbstractSession) line: 81 SchemaManager.createObject(DatabaseObjectDefinition) line: 188 SchemaManager.replaceObject(DatabaseObjectDefinition) line: 892 TableCreator.replaceTablesAndConstraints(SchemaManager, DatabaseSession) line: 297 TableCreator.replaceTables(DatabaseSession, SchemaManager, boolean) line: 260 SchemaManager.replaceDefaultTables(boolean, boolean) line: 954 EntityManagerFactoryProvider.createOrReplaceDefaultTables(SchemaManager, boolean) line: 85 EntityManagerFactoryProvider.writeDDLToDatabase(SchemaManager, boolean) line: 352 EntityManagerFactoryProvider.generateDDL(ServerSession, Map) line: 122 EntityManagerSetupImpl.deploy(ClassLoader, Map) line: 397 EntityManagerFactoryImpl.getServerSession() line: 164 EntityManagerFactoryImpl.getMetamodel() line: 510 DDLGenerationClient.initialize(String) line: 137 DDLGenerationClient.doQuery() line: 444 DDLGenerationClient.main(String[]) line: 435
>Preliminary fix is to move the generic VARCHAR fieldType down from DatabasePlatform to HSQLPlatform public class HSQLPlatform extends DatabasePlatform { ... @Override protected Hashtable buildFieldTypes() { Hashtable fieldTypeMapping; fieldTypeMapping = super.buildFieldTypes(); ... // 319592 > fieldTypeMapping.put(String.class, new FieldTypeDefinition("VARCHAR",DEFAULT_VARCHAR_SIZE)); >results = OK (no SQLException) Call: DROP TABLE EL_CELL Query: DataModifyQuery(sql="DROP TABLE EL_CELL") [EL Finest]: 2010-11-04 11:03:25.184--ServerSession(11108810)--Thread(Thread[main,5,main])--Execute query DataModifyQuery(sql="CREATE TABLE EL_CELL (ID BIGINT NOT NULL, STATE VARCHAR(255), TSEQ VARCHAR(255), ACELLATTRIBUTE_ID INTEGER, RIGHT_ID BIGINT, PRIMARY KEY (ID))") [EL Fine]: 2010-11-04 11:03:25.184--ServerSession(11108810)--Connection(27667505)--Thread(Thread[main,5,main])--CREATE TABLE EL_CELL (ID BIGINT NOT NULL, STATE VARCHAR(255), TSEQ VARCHAR(255), ACELLATTRIBUTE_ID INTEGER, RIGHT_ID BIGINT, PRIMARY KEY (ID)) [EL Finest]: 2010-11-04 11:03:25.184--ServerSession(11108810)--Thread(Thread[main,5,main])--Execute query DataModifyQuery(sql="DROP TABLE EL_CELL_EL_CELL")
Created attachment 182381 [details] Preliminary HSQL 2.0.0 patch with pending 1.8.1 backwards compatibility testing for VARCHAR forced length specification >regression testing on the LRG will take a couple hours
>There is an H2 dependency on this fix introduced in bug # 244125 in SVN rev# 5796 https://bugs.eclipse.org/bugs/attachment.cgi?id=151970&action=diff https://fisheye2.atlassian.com/changelog/eclipselink/trunk/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/HSQLPlatform.java?cs=5796 >Note: the H2 platform extends HSQLPlatform - Need to regression test H2 as well >JPA LRG on HSQL 2.0.0 still running
>correction, H2Platform used to extend HSQLPlatform - currently it just extends DatabasePlatform - no dependency exits >download hsql 1.8.0 (don't use the one in JBoss 5/6), do regression testing run java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 xdb >Without change - results on HSQL 1.8.0 (clean schema) <testsuite errors="14" failures="24" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2170" time="137.916" timestamp="2010-11-05T13:49:13"> >With change - results on HSQL 1.8.0 (clean schema) <testsuite errors="14" failures="24" hostname="xps435" name="org.eclipse.persistence.testing.tests.jpa.AllCMP3TestRunModel" tests="2170" time="137.155" timestamp="2010-11-05T14:05:44">
>See SVN rev# 8481 for the 2.0 stream https://fisheye2.atlassian.com/changelog/eclipselink/?cs=8481 >Note: the 2.1 stream is currently closed for check-ins
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
