Re: Follow up on JBoss + OJB + HSQLDB

[Brian Sam-Bodden]

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 
[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) 

    at 
org.hsqldb.jdbcPreparedStatement.setCharacterStream(jdbcPreparedStatement.java:1375) 

    at 
com.p6spy.engine.spy.P6PreparedStatement.setCharacterStream(P6PreparedStatement.java:239) 

    at 
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.setCharacterStream(WrappedPreparedStatement.java:644) 

    at 
org.apache.ojb.broker.platforms.PlatformDefaultImpl.setObjectForStatement(PlatformDefaultImpl.java:261) 

    at 
org.apache.ojb.broker.accesslayer.StatementManager.bindInsert(StatementManager.java:509) 

    at 
org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(JdbcAccessImpl.java:234) 

    at 
org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(PersistenceBrokerImpl.java:1384) 

    at 
org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrokerImpl.java:1301) 

    at 
org.apache.ojb.broker.core.PersistenceBrokerImpl.store(PersistenceBrokerImpl.java:688) 

    at 
org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(DelegatingPersistenceBroker.java:165) 

    at 
org.apache.ojb.broker.core.DelegatingPersistenceBroker.store(DelegatingPersistenceBroker.java:165) 

    at 
com.ejdoab.beans.ConferenceFacadeBean.saveConference(ConferenceFacadeBean.java:130) 

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 

    at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) 

    at java.lang.reflect.Method.invoke(Method.java:324)
    at 
org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:660) 

    at 
org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:186) 

    at 
org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:77) 

    at 
org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:107) 

    at 
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:210) 

    at 
org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:98)
    at 
org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:130) 

    at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:208)
    at 
org.jboss.ejb.StatelessSessionContainer.invoke(StatelessSessionContainer.java:313) 

    at org.jboss.ejb.Container.invoke(Container.java:738)
    at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
    at 
org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:383)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 

    at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) 

    at java.lang.reflect.Method.invoke(Method.java:324)
    at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
    at sun.rmi.transport.Transport$1.run(Transport.java:148)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
    at 
sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
    at 
sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701) 

    at java.lang.Thread.run(Thread.java:534)

****************************************************************************************************************** 

The Spy log shows that the INSERT statement was NEVER actually submitted 
to the database. It seems that something is being asked to the driver 
right before executing the INSERT and the driver is somehow saying: 
"nope, can't do" as the log below shows:

****************************************************************************************************************** 

1073409685280|-1||debug||com.p6spy.engine.common.P6SpyOptions reloading 
properties
1073409685290|-1||info||Using properties file: 
C:\java\jboss\jboss-3.0.8\server\tcms\db\p6spy\spy.properties
1073409685290|-1||info||No value in environment for: getStackTrace, 
using: false
1073409685290|-1||info||No value in environment for: getAppender, using: 
com.p6spy.engine.logging.appender.FileLogger
1073409685290|-1||info||No value in environment for: getFilter, using: 
false
1073409685300|-1||info||No value in environment for: getAppend, using: true
1073409685300|-1||info||No value in environment for: 
getDeregisterDrivers, using: false
1073409685300|-1||info||No value in environment for: getUsePrefix, 
using: false
1073409685300|-1||info||No value in environment for: 
getExecutionThreshold, using: 0
1073409685300|-1||info||No value in environment for: getAutoflush, 
using: true
1073409685300|-1||info||No value in environment for: getExclude, using:
1073409685300|-1||info||No value in environment for: 
getExcludecategories, using:
1073409685300|-1||info||No value in environment for: getInclude, using:
1073409685300|-1||info||No value in environment for: 
getIncludecategories, using: 
debug,statement,info,result,batch,commit,rollback
1073409685300|-1||info||No value in environment for: getLogfile, using: 
spy.log
1073409685300|-1||info||No value in environment for: getRealdriver, 
using: org.hsqldb.jdbcDriver
1073409685300|-1||info||No value in environment for: getRealdriver2, using:
1073409685300|-1||info||No value in environment for: getRealdriver3, using:
1073409685300|-1||info||No value in environment for: getSpydriver, 
using: com.p6spy.engine.spy.P6SpyDriver
1073409685300|-1||info||No value in environment for: getDateformat, using:
1073409685300|-1||info||No value in environment for: getDateformatter, 
using: null
1073409685300|-1||info||No value in environment for: getStringmatcher, 
using: com.p6spy.engine.common.SubstringMatcher
1073409685300|-1||info||No value in environment for: 
getStringMatcherEngine, using: 
[EMAIL PROTECTED]
1073409685300|-1||info||No value in environment for: getStackTraceClass, 
using:
1073409685300|-1||info||No value in environment for: getSQLExpression, 
using: null
1073409685300|-1||info||No value in environment for: 
getReloadProperties, using: false
1073409685300|-1||info||No value in environment for: 
getReloadPropertiesInterval, using: 60
1073409685300|-1||info||No value in environment for: 
getJNDIContextFactory, using: null
1073409685300|-1||info||No value in environment for: 
getJNDIContextProviderURL, using: null
1073409685300|-1||info||No value in environment for: 
getJNDIContextCustom, using: null
1073409685300|-1||info||No value in environment for: getRealDataSource, 
using: null
1073409685300|-1||info||No value in environment for: 
getRealDataSourceClass, using: null
1073409685300|-1||info||No value in environment for: 
getRealDataSourceProperties, using: null
1073409685300|-1||info||reloadProperties() successful
1073409685310|-1||debug||Registered driver: org.hsqldb.jdbcDriver, 
realdriver: [EMAIL PROTECTED]
1073409685310|-1||debug||Registered factory: 
com.p6spy.engine.logging.P6LogFactory with options: null
1073409685310|-1||debug||Driver manager reporting driver registered: 
[EMAIL PROTECTED]
1073409685310|-1||debug||Driver manager reporting driver registered: 
[EMAIL PROTECTED]
1073409685310|-1||debug||found new driver [EMAIL PROTECTED]
1073409685310|-1||debug||this is 
[EMAIL PROTECTED] and passthru is 
[EMAIL PROTECTED]
1073409708703|10|0|statement|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0
1073409708703|0|0|result|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0
1073409708744|-1||resultset|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0|DESCRIPTION = null, NAME = Open Source Java 
Conference, PK_ID = 0
1073409708744|0|0|result|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0
1073409708744|-1||resultset|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0|DESCRIPTION = My Test Conf, NAME = TESTCONF, PK_ID = 1
1073409708744|0|0|result|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0
1073409708744|-1||resultset|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0|DESCRIPTION = HOO, NAME = BOO, PK_ID = 13
1073409708744|0|0|result|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0|SELECT 
A0.PK_ID,A0.ABSTRACTSUBMISSIONENDDATE,A0.ABSTRACTSUBMISSIONSTARTDATE,A0.NAME,A0.DESCRIPTION,A0.ENDDATE,A0.STARTDATE 
FROM conferences A0
1073409708854|0|0|statement|SELECT 
PK_ID,ABSTRACTSUBMISSIONENDDATE,ABSTRACTSUBMISSIONSTARTDATE,NAME,DESCRIPTION,ENDDATE,STARTDATE 
FROM conferences WHERE PK_ID = ? |SELECT 
PK_ID,ABSTRACTSUBMISSIONENDDATE,ABSTRACTSUBMISSIONSTARTDATE,NAME,DESCRIPTION,ENDDATE,STARTDATE 
FROM conferences WHERE PK_ID = '3'
1073409708854|0|0|result|SELECT 
PK_ID,ABSTRACTSUBMISSIONENDDATE,ABSTRACTSUBMISSIONSTARTDATE,NAME,DESCRIPTION,ENDDATE,STARTDATE 
FROM conferences WHERE PK_ID = ? |SELECT 
PK_ID,ABSTRACTSUBMISSIONENDDATE,ABSTRACTSUBMISSIONSTARTDATE,NAME,DESCRIPTION,ENDDATE,STARTDATE 
FROM conferences WHERE PK_ID = '3'

****************************************************************************************************************** 

So, my next step is to try an example with very simple, string/varchar 
only columns and see how this thing behaves. I'll keep on hacking at it 
today. Maybe later I'll upgrade to JBoss 3.2.1 which has a newer version 
of HSQLDB (I think) and see how that goes. After that I think my only 
choice is to try Hibernate.

If anybody has any insight into this problem. Any help would be greatly 
appreciated. Over all I like OJB, I think people's complains on the 
documentation are a bit misleading. The docs are good, they are just all 
over. I would suggest (and I can help with later) to have a one pass 
tutorial which covers the whole shabang, downloading, installing, 
configuring, testing. Something that flows together. These can be in 
several flavors, standalone, in an app server environment, difference 
databases, servers, etc. I think this can be done with the xdocs and 
some good XSLTing.

Another suggestion would be to get rid of the damn splash screens in the 
build process. They are instrusive and annoying and in a headless system 
with no X the might even blow up an automated build. Why load that 
mostruosity Swing during a console app?

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.

Just my two cents. Overall this is a great product with great potential.

Thanks,
Brian


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]