Re: Follow up on JBoss + OJB + HSQLDB
Larry, I will be publishing all my results to this group. Luckily I have documented everthing. I'm cleaning up the Ant script for my app and I can send you a zip file of it sometime this week. Brian Larry V. Streepy, Jr. wrote: Brian Sam-Bodden wrote: Torque. For those unintiated with Torque the whole thing is a bit confusing. I found no easy way to create the OJB internal tables from the build without creating the test tables also. So I had to put my own build together to get just what I needed. I don't know what to suggest here but maybe the build could be simplified. Brian, I completely agree with you here. Trying to decipher OJB, Torque, and a few other things has been interesting. I have on my to do list, a note to create a build script that will do just what you state above (internal table creation). If you wouldn't mind sharing that build fragment, I'd be very grateful. Thanks - and good luck on this problem. -- Larry V. Streepy, Jr. Senior Vice President and CTO http://www.healthlanguage.com/ We speak the language of healthcare Direct: +1 425-373-0600 Fax:+1 425-373-1633 Corp.: +1 303-307-4400 Health Language, Inc. 3950 Lewiston Street, Suite 210 Aurora, Colorado 80011 The information in this email message is intended only for the use of the intended recipient and may contain confidential information. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended addressee, please promptly delete this message and notify the sender of the delivery error by e-mail or you may call Health Language's corporate offices in Aurora, Colorado USA at (+1) 303-307-4400. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Follow up on JBoss + OJB + HSQLDB
Perhaps I can shed some light on this (or I'm merely restating what you already know ;-). The target for generating the test db contains this: !-- create sql scripts -- ant dir=. antfile=${torque.buildFile} target=project-sql-classpath/ !-- create db -- ant dir=. antfile=${torque.buildFile} target=project-create-db-classpath/ !-- create data sql -- ant dir=. antfile=${torque.buildFile} target=project-datasql-classpath/ !-- create tables -- ant dir=. antfile=${torque.buildFile} target=project-insert-sql/ These are all targets in the torque build file, i.e. they use torque to do something. project-sql-classpath generates SQL for creating the configured database. For this it processes all files ending on -schema.xml (line 449-451 in build-torque.xml) in the ${schemaDirectory} which is configured in build.properties to be src/schema. As there are two such files (ojbcore-schema.xml and ojbtest-schema.xml), the generated sql is for both schemas together. The generated sql is put into ${outputDirectory}/sql which is target/src/sql project-create-db-classpath processes the same schema files, generates database-creation sql (not for the tables) and executes it (e.g. for MySQL something like create database ojbtest). project-datasql-classpath processes the three files ${project}-schema.xml (database definition), ${project}-data.dtd (test data dtd) and ${project}-data.xml (actual test data) with ${project} = ojbtest to generate sql for inserting the test data into the database. The generated sql is in ${outputDirectory}/sql/${project}-data.sql, i.e. target/src/sql/ojbtest-data.sql. project-insert-sql, finally, executes the SQL code found in all .sql files in the ${outputDirectory}/sql/ directory. So if you want to process a specific schema only, I suggest you copy it to some temporary directory ${temp.dir}/schemas, then use the project-create-db-classpath and project-sql-classpath targets like: copy file=${schemaDirectory}/ojbcore-schema.xml todir=${temp.dir}/schemas/ ant dir=. antfile=${torque.buildFile} target=project-create-db-classpath property name=schemaDirectory value=${temp.dir}/schemas/ property name=outputDirectory value=${temp.dir}/sql/ /ant ant dir=. antfile=${torque.buildFile} target=project-sql-classpath property name=schemaDirectory value=${temp.dir}/schemas/ property name=outputDirectory value=${temp.dir}/sql/ /ant Hope that helps somewhat. Tom - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Follow up on JBoss + OJB + HSQLDB
All, Well I'm getting closer. I found some of my mistakes where in the actual mapping of my class to the table If you remember my tables looks like CREATE TABLE Conferences ( pk_Id INTEGER NOT NULL PRIMARY KEY, Name varchar(64), Description LONGVARCHAR, StartDate DATETIME, EndDate DATETIME, AbstractSubmissionStartDate DATETIME, AbstractSubmissionEndDate DATETIME ); Before I was mapping the DATETIME fields incorrectly as shown below. I was using DATE. Doh! field-descriptor name=startDate column=STARTDATE jdbc-type=DATE Of course when I change to DATETIME it didn't like it either. As it happens HSQLDB DATETIME can also be called TIMESTAMP which is what is in the repository DTD. So I switch to TIMESTAMP and all works fine. Of course if you look at the SQL TYPE to JAVA TYPE table in the docs, TIMESTAMP maps automatically to java.sql.TimeStamp which is no good since I want those to be java.util.Date. So, I opted to using one of the conversion classes, in particular JavaDate2SqlTimestampFieldConversion as shown below field-descriptor name=startDate column=STARTDATE jdbc-type=TIMESTAMP conversion=org.apache.ojb.accesslayer.conversions.JavaDate2SqlTimestampFieldConversion org.apache.ojb.broker.accesslayer.conversions SO DOES IT WORK NOW, YOU MIGHT BE ASKING YOURSELF, UNFORTUNATELLY THE ANSWER IS NO! In frustration and about to lose every single strand of hair on my head I decided to debug the life out of this thing. So I turn debugging in every possible place, for JBoss and for HSQLDB. I also when ahead and decided to configure P6Spy to trace all SQL calls The P6Spy installations intructions on JBoss are a bit inacurate so I started looking and what do you know, somebody on this list had the answer (Phil Warrick): http://www.mail-archive.com/[EMAIL PROTECTED]/msg01661.html So I got that setup and ran another test, my server log still shows a SQLException for a not supported function: ** 2004-01-06 12:21:48,844 DEBUG [org.apache.ojb.broker.core.PersistenceBrokerFactoryDefaultImpl] Obtain broker from pool, used PBKey is org.apache.ojb.broker.PBKey: jcdAlias=tcms, user=sa, password=* 2004-01-06 12:21:48,844 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionFactoryAbstractImpl] do datasource lookup, name: java:tcmsDS, user: sa 2004-01-06 12:21:48,844 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionFactoryAbstractImpl] Create new connection using DataSource: [EMAIL PROTECTED] 2004-01-06 12:21:48,844 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionManagerImpl] Request new connection from ConnectionFactory: [EMAIL PROTECTED] 2004-01-06 12:21:48,844 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionManagerImpl] localBegin was called for con [EMAIL PROTECTED] 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] SQL:SELECT PK_ID,ABSTRACTSUBMISSIONENDDATE,ABSTRACTSUBMISSIONSTARTDATE,NAME,DESCRIPTION,ENDDATE,STARTDATE FROM conferences WHERE PK_ID = ? 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.StatementManager] closeResources was called 2004-01-06 12:21:48,854 INFO [STDOUT] [EMAIL PROTECTED] target object=[Conference] id = 3, name = EJDOAB Conference, description = Brian's Conference, start date = 2004-01-06 12:21:48.834 source [EMAIL PROTECTED] eventType=org.apache.ojb.broker.PBLifeCycleEvent$Type [type= BEFORE_INSERT] ] 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] executeInsert : [Conference] id = 3, name = EJDOAB Conference, description = Brian's Conference, start date = 2004-01-06 12:21:48.834 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionFactoryAbstractImpl] do datasource lookup, name: java:tcmsDS, user: sa 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionFactoryAbstractImpl] Create new connection using DataSource: [EMAIL PROTECTED] 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionManagerImpl] Request new connection from ConnectionFactory: [EMAIL PROTECTED] 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] SQL:INSERT INTO conferences (PK_ID,NAME,DESCRIPTION,STARTDATE,ENDDATE,ABSTRACTSUBMISSIONSTARTDATE,ABSTRACTSUBMISSIONENDDATE) VALUES (?,?,?,?,?,?,?) 2004-01-06 12:21:48,854 ERROR [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] SQLException during the execution of the insert (for a com.ejdoab.pojos.Conference): This function is not supported java.sql.SQLException: This function is not supported at org.hsqldb.Trace.getError(Trace.java:180) at org.hsqldb.Trace.getError(Trace.java:144) at org.hsqldb.Trace.error(Trace.java:192) at org.hsqldb.jdbcPreparedStatement.getNotSupported(jdbcPreparedStatement.java:1602)
Re: Follow up on JBoss + OJB + HSQLDB
Me again, Tried a simpler table class-descriptor class=com.ejdoab.pojos.Track table=tracks field-descriptor name=id column=PK_ID jdbc-type=INTEGER primarykey=true nullable=false autoincrement=true /field-descriptor field-descriptor name=title column=TITLE jdbc-type=VARCHAR length=32 /field-descriptor field-descriptor name=subTitle column=SUBTITLE jdbc-type=VARCHAR length=32 /field-descriptor field-descriptor name=description column=DESCRIPTION jdbc-type=LONGVARCHAR /field-descriptor /class-descriptor Still the same error. So I'm pretty sure is not a datatype problem (unless VARCHAR AND LONGVARCHAR are causing problems). So I'll upgrade to a newer JBoss + HSQLDB version and let you all know. Thanks again, Brian Brian Sam-Bodden wrote: All, Well I'm getting closer. I found some of my mistakes where in the actual mapping of my class to the table If you remember my tables looks like CREATE TABLE Conferences ( pk_Id INTEGER NOT NULL PRIMARY KEY, Name varchar(64), Description LONGVARCHAR, StartDate DATETIME, EndDate DATETIME, AbstractSubmissionStartDate DATETIME, AbstractSubmissionEndDate DATETIME ); Before I was mapping the DATETIME fields incorrectly as shown below. I was using DATE. Doh! field-descriptor name=startDate column=STARTDATE jdbc-type=DATE Of course when I change to DATETIME it didn't like it either. As it happens HSQLDB DATETIME can also be called TIMESTAMP which is what is in the repository DTD. So I switch to TIMESTAMP and all works fine. Of course if you look at the SQL TYPE to JAVA TYPE table in the docs, TIMESTAMP maps automatically to java.sql.TimeStamp which is no good since I want those to be java.util.Date. So, I opted to using one of the conversion classes, in particular JavaDate2SqlTimestampFieldConversion as shown below field-descriptor name=startDate column=STARTDATE jdbc-type=TIMESTAMP conversion=org.apache.ojb.accesslayer.conversions.JavaDate2SqlTimestampFieldConversion org.apache.ojb.broker.accesslayer.conversions SO DOES IT WORK NOW, YOU MIGHT BE ASKING YOURSELF, UNFORTUNATELLY THE ANSWER IS NO! In frustration and about to lose every single strand of hair on my head I decided to debug the life out of this thing. So I turn debugging in every possible place, for JBoss and for HSQLDB. I also when ahead and decided to configure P6Spy to trace all SQL calls The P6Spy installations intructions on JBoss are a bit inacurate so I started looking and what do you know, somebody on this list had the answer (Phil Warrick): http://www.mail-archive.com/[EMAIL PROTECTED]/msg01661.html So I got that setup and ran another test, my server log still shows a SQLException for a not supported function: ** 2004-01-06 12:21:48,844 DEBUG [org.apache.ojb.broker.core.PersistenceBrokerFactoryDefaultImpl] Obtain broker from pool, used PBKey is org.apache.ojb.broker.PBKey: jcdAlias=tcms, user=sa, password=* 2004-01-06 12:21:48,844 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionFactoryAbstractImpl] do datasource lookup, name: java:tcmsDS, user: sa 2004-01-06 12:21:48,844 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionFactoryAbstractImpl] Create new connection using DataSource: [EMAIL PROTECTED] 2004-01-06 12:21:48,844 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionManagerImpl] Request new connection from ConnectionFactory: [EMAIL PROTECTED] 2004-01-06 12:21:48,844 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionManagerImpl] localBegin was called for con [EMAIL PROTECTED] 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] SQL:SELECT PK_ID,ABSTRACTSUBMISSIONENDDATE,ABSTRACTSUBMISSIONSTARTDATE,NAME,DESCRIPTION,ENDDATE,STARTDATE FROM conferences WHERE PK_ID = ? 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.StatementManager] closeResources was called 2004-01-06 12:21:48,854 INFO [STDOUT] [EMAIL PROTECTED] target object=[Conference] id = 3, name = EJDOAB Conference, description = Brian's Conference, start date = 2004-01-06 12:21:48.834 source [EMAIL PROTECTED] eventType=org.apache.ojb.broker.PBLifeCycleEvent$Type [type= BEFORE_INSERT] ] 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] executeInsert : [Conference] id = 3, name = EJDOAB Conference, description = Brian's Conference, start date = 2004-01-06 12:21:48.834 2004-01-06 12:21:48,854 DEBUG [org.apache.ojb.broker.accesslayer.ConnectionFactoryAbstractImpl] do datasource lookup, name: java:tcmsDS, user: sa 2004-01-06 12:21:48,854 DEBUG
Re: Follow up on JBoss + OJB + HSQLDB
Brian Sam-Bodden wrote: Torque. For those unintiated with Torque the whole thing is a bit confusing. I found no easy way to create the OJB internal tables from the build without creating the test tables also. So I had to put my own build together to get just what I needed. I don't know what to suggest here but maybe the build could be simplified. Brian, I completely agree with you here. Trying to decipher OJB, Torque, and a few other things has been interesting. I have on my "to do" list, a note to create a build script that will do just what you state above (internal table creation). If you wouldn't mind sharing that build fragment, I'd be very grateful. Thanks - and good luck on this problem. -- signature Larry V. Streepy, Jr. Senior Vice President and CTO "We speak the language of healthcare" Direct:+1 425-373-0600 Fax: +1 425-373-1633 Corp.: +1 303-307-4400 Health Language, Inc. 3950 Lewiston Street, Suite 210 Aurora, Colorado 80011 The information in this email message is intended only for the use of the intended recipient and may contain confidential information. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended addressee, please promptly delete this message and notify the sender of the delivery error by e-mail or you may call Health Language's corporate offices in Aurora, Colorado USA at (+1) 303-307-4400. inline: HL_PMS_smaller.jpg