[Middlegen-user] Oracle Sequences

[Yuri Kouzmov]

I had the airline sample built and deployed successfully when using
HSQLDB. Now I'm trying to deploy it using Oracle without much success.
It seems like there are a number of problems I'm running into. First of
all, if I go with the sequenceblock element, build works fine, but I get
a run-time error from JBoss during deployment:



Caused by: java.sql.SQLException: Wrong data type: VARCHAR2 in statement
[CREATE TABLE SEQ_BLOCK (name VARCHAR

2(255) NOT NULL, idx NUMBER(10) NOT NULL, CONSTRAINT PK_SEQ_BLOCK
PRIMARY KEY (name))]

at org.hsqldb.Trace.getError(Trace.java:180)

at org.hsqldb.Result.<init>(Result.java:175)

at
org.hsqldb.jdbcConnection.executeHSQL(jdbcConnection.java:907)

at org.hsqldb.jdbcConnection.execute(jdbcConnection.java:718)

at org.hsqldb.jdbcStatement.fetchResult(jdbcStatement.java:686)

at org.hsqldb.jdbcStatement.executeUpdate(jdbcStatement.java:85)

at
org.jboss.resource.adapter.jdbc.local.LocalStatement.executeUpdate(Local
Statement.java:231)

at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCom
mand.java:166)

... 83 more



Looks like for some reason it's trying to use the HSQL database for
SEQ_BLOCK table, even though I'm deploying on Oracle (The create table,
and build really did use oracle. I verified that the database was set up
correctly). Without getting too much into trying to figure out why it's
using HSQLDB for SEQ_BLOCK table, I decided to try using oracleSequence,
without much success. I've seen someone mention a work-around for
Weblogic, but I'm using JBoss.



Ideally, I'd like to use the oracleSequence element, but I get the
following errors message, despite the fact that documentation (mentioned
above) suggests that such element is supported by cmp20 plugin:



BUILD FAILED

file:c:/middlegen-2.0-b1/samples/build.xml:139: Class
middlegen.plugins.entitybean.CMP20Plugin doesn't support

the nested "oraclesequence" element.



When trying to use the sequenceblock nested element (with schema
attribute which is required for Oracle), I get the following error:



BUILD FAILED

file:c:/middlegen-2.0-b1/samples/build.xml:139: Class
middlegen.plugins.entitybean.SequenceBlock doesn't suppo

rt the "schema" attribute.



According to documentation
(http://boss.bekk.no/boss/middlegen/plugins/cmp20.html#sequenceblock),
there is a schema attribute for sequenceblock and there also is a
oracleSequence nested element for cmp20 plug-in.



I'm using jboss-3.0.4_tomcat-4.1.12, Middlegen 2.0-b1, XDoclet 1.2b2 and
Oracle 9i.



Any help would be much appreciated.







Y.

[Yuri Kouzmov]

An update on the issue:

I realized that the documentaion was probably updated since the last
beta build. I grabbed the latest source from CVS and built middlegen. I
am now able to succesfully build and deploy the EAR, but am getting a
run-time error:

16:45:12,855 INFO [MainDeployer] Deployed package:
file:/C:/jboss-3.0.4_tomcat-4.1.12/server/default/deploy/airline.ear

16:45:25,355 ERROR [Engine] ActionServlet: Can't find entity

javax.ejb.FinderException: Find failed: java.sql.SQLException: Column
not found: PERSON_ID in statement [SELEC

T t0_o.RESERVATION_ID, t0_o.PERSON_ID, t0_o.FLIGHT_ID FROM RESERVATIONS
t0_o]

at
org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbst
ractQueryCommand.java:148)

at
org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindE
ntitiesCommand.java:40)



That column is most certainly there and the query does work when I run
it manually. Does anyone have any thoughts on this? Again, running
jboss-3.0.4_tomcat-4.1.12, Middlegen 2.0-b1, XDoclet 1.2b2 and Oracle
9i.

Thanx,



Y.

-----Original Message-----
From: Yuri Kouzmov
Sent: Thursday, February 27, 2003 1:05 PM
To: middlegen-***@lists.sourceforge.net
Subject: [Middlegen-user] Oracle Sequences



I had the airline sample built and deployed successfully when using
HSQLDB. Now I'm trying to deploy it using Oracle without much success.
It seems like there are a number of problems I'm running into. First of
all, if I go with the sequenceblock element, build works fine, but I get
a run-time error from JBoss during deployment:



Caused by: java.sql.SQLException: Wrong data type: VARCHAR2 in statement
[CREATE TABLE SEQ_BLOCK (name VARCHAR

2(255) NOT NULL, idx NUMBER(10) NOT NULL, CONSTRAINT PK_SEQ_BLOCK
PRIMARY KEY (name))]

at org.hsqldb.Trace.getError(Trace.java:180)

at org.hsqldb.Result.<init>(Result.java:175)

at
org.hsqldb.jdbcConnection.executeHSQL(jdbcConnection.java:907)

at org.hsqldb.jdbcConnection.execute(jdbcConnection.java:718)

at org.hsqldb.jdbcStatement.fetchResult(jdbcStatement.java:686)

at org.hsqldb.jdbcStatement.executeUpdate(jdbcStatement.java:85)

at
org.jboss.resource.adapter.jdbc.local.LocalStatement.executeUpdate(Local
Statement.java:231)

at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCom
mand.java:166)

... 83 more



Looks like for some reason it's trying to use the HSQL database for
SEQ_BLOCK table, even though I'm deploying on Oracle (The create table,
and build really did use oracle. I verified that the database was set up
correctly). Without getting too much into trying to figure out why it's
using HSQLDB for SEQ_BLOCK table, I decided to try using oracleSequence,
without much success. I've seen someone mention a work-around for
Weblogic, but I'm using JBoss.



Ideally, I'd like to use the oracleSequence element, but I get the
following errors message, despite the fact that documentation (mentioned
above) suggests that such element is supported by cmp20 plugin:



BUILD FAILED

file:c:/middlegen-2.0-b1/samples/build.xml:139: Class
middlegen.plugins.entitybean.CMP20Plugin doesn't support

the nested "oraclesequence" element.



When trying to use the sequenceblock nested element (with schema
attribute which is required for Oracle), I get the following error:



BUILD FAILED

file:c:/middlegen-2.0-b1/samples/build.xml:139: Class
middlegen.plugins.entitybean.SequenceBlock doesn't suppo

rt the "schema" attribute.



According to documentation
(http://boss.bekk.no/boss/middlegen/plugins/cmp20.html#sequenceblock),
there is a schema attribute for sequenceblock and there also is a
oracleSequence nested element for cmp20 plug-in.



I'm using jboss-3.0.4_tomcat-4.1.12, Middlegen 2.0-b1, XDoclet 1.2b2 and
Oracle 9i.



Any help would be much appreciated.







Y.

[Yuri Kouzmov]

I think I'm back to the original problem: it's using HSQLDB instead of
Oracle, and I really need to figure out why



Caused by: java.sql.SQLException: Wrong data type: NUMBER in statement
[CREATE TABLE FLIGHTS (FLIGHT_ID NUMBER

(10) NOT NULL, NAME VARCHAR2(255), DEPARTURE_UTC TIMESTAMP(9),
ARRIVAL_UTC TIMESTAMP(9), CONSTRAINT PK_FLIGHTS

PRIMARY KEY (FLIGHT_ID))]

at org.hsqldb.Trace.getError(Trace.java:180)

at org.hsqldb.Result.<init>(Result.java:175)

at
org.hsqldb.jdbcConnection.executeHSQL(jdbcConnection.java:907)

at org.hsqldb.jdbcConnection.execute(jdbcConnection.java:718)

at org.hsqldb.jdbcStatement.fetchResult(jdbcStatement.java:686)

at org.hsqldb.jdbcStatement.executeUpdate(jdbcStatement.java:85)

at
org.jboss.resource.adapter.jdbc.local.LocalStatement.executeUpdate(Local
Statement.java:231)

at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCom
mand.java:166)

... 83 more



What changes do I have to make to the build file other than <!ENTITY
database SYSTEM "file:./config/database/oracle.xml"> at the top and
oracleSequence element in order to make it use Oracle?



Any help at all would be much appreciated.





Y.



-----Original Message-----
From: Yuri Kouzmov
Sent: Thursday, February 27, 2003 4:54 PM
To: middlegen-***@lists.sourceforge.net
Subject: RE: [Middlegen-user] Oracle Sequences



An update on the issue:

I realized that the documentaion was probably updated since the last
beta build. I grabbed the latest source from CVS and built middlegen. I
am now able to succesfully build and deploy the EAR, but am getting a
run-time error:

16:45:12,855 INFO [MainDeployer] Deployed package:
file:/C:/jboss-3.0.4_tomcat-4.1.12/server/default/deploy/airline.ear

16:45:25,355 ERROR [Engine] ActionServlet: Can't find entity

javax.ejb.FinderException: Find failed: java.sql.SQLException: Column
not found: PERSON_ID in statement [SELEC

T t0_o.RESERVATION_ID, t0_o.PERSON_ID, t0_o.FLIGHT_ID FROM RESERVATIONS
t0_o]

at
org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbst
ractQueryCommand.java:148)

at
org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindE
ntitiesCommand.java:40)



That column is most certainly there and the query does work when I run
it manually. Does anyone have any thoughts on this? Again, running
jboss-3.0.4_tomcat-4.1.12, Middlegen 2.0-b1, XDoclet 1.2b2 and Oracle
9i.

Thanx,



Y.

-----Original Message-----
From: Yuri Kouzmov
Sent: Thursday, February 27, 2003 1:05 PM
To: middlegen-***@lists.sourceforge.net
Subject: [Middlegen-user] Oracle Sequences



I had the airline sample built and deployed successfully when using
HSQLDB. Now I'm trying to deploy it using Oracle without much success.
It seems like there are a number of problems I'm running into. First of
all, if I go with the sequenceblock element, build works fine, but I get
a run-time error from JBoss during deployment:



Caused by: java.sql.SQLException: Wrong data type: VARCHAR2 in statement
[CREATE TABLE SEQ_BLOCK (name VARCHAR

2(255) NOT NULL, idx NUMBER(10) NOT NULL, CONSTRAINT PK_SEQ_BLOCK
PRIMARY KEY (name))]

at org.hsqldb.Trace.getError(Trace.java:180)

at org.hsqldb.Result.<init>(Result.java:175)

at
org.hsqldb.jdbcConnection.executeHSQL(jdbcConnection.java:907)

at org.hsqldb.jdbcConnection.execute(jdbcConnection.java:718)

at org.hsqldb.jdbcStatement.fetchResult(jdbcStatement.java:686)

at org.hsqldb.jdbcStatement.executeUpdate(jdbcStatement.java:85)

at
org.jboss.resource.adapter.jdbc.local.LocalStatement.executeUpdate(Local
Statement.java:231)

at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCom
mand.java:166)

... 83 more



Looks like for some reason it's trying to use the HSQL database for
SEQ_BLOCK table, even though I'm deploying on Oracle (The create table,
and build really did use oracle. I verified that the database was set up
correctly). Without getting too much into trying to figure out why it's
using HSQLDB for SEQ_BLOCK table, I decided to try using oracleSequence,
without much success. I've seen someone mention a work-around for
Weblogic, but I'm using JBoss.



Ideally, I'd like to use the oracleSequence element, but I get the
following errors message, despite the fact that documentation (mentioned
above) suggests that such element is supported by cmp20 plugin:



BUILD FAILED

file:c:/middlegen-2.0-b1/samples/build.xml:139: Class
middlegen.plugins.entitybean.CMP20Plugin doesn't support

the nested "oraclesequence" element.



When trying to use the sequenceblock nested element (with schema
attribute which is required for Oracle), I get the following error:



BUILD FAILED

file:c:/middlegen-2.0-b1/samples/build.xml:139: Class
middlegen.plugins.entitybean.SequenceBlock doesn't suppo

rt the "schema" attribute.



According to documentation
(http://boss.bekk.no/boss/middlegen/plugins/cmp20.html#sequenceblock),
there is a schema attribute for sequenceblock and there also is a
oracleSequence nested element for cmp20 plug-in.



I'm using jboss-3.0.4_tomcat-4.1.12, Middlegen 2.0-b1, XDoclet 1.2b2 and
Oracle 9i.



Any help would be much appreciated.







Y.

[Yuri Kouzmov]

Yup. That's what I had to do to get it working last night. I ended up
building a CVS copy of MiddleGen to get sequences to work and then had
to configure JBoss (as described below) to deploy the EAR propertly.

I was new to EJB, MiddleGen, XDoclet and JBoss and wasn't sure what was
causing the problem. I've only had about 2 days to play with everything.

Thanx,


Y.


-----Original Message-----
From: Matthias Flor [mailto:***@isb-ag.de]
Sent: Friday, February 28, 2003 6:37 AM
To: Yuri Kouzmov
Subject: Re: [Middlegen-user] Oracle Sequences

Post by Yuri Kouzmov
I think I'm back to the original problem: it's using HSQLDB instead of
Oracle, and I really need to figure out why

Well, it's not an Middlegen-issue, it's an JBoss issue. I had also some
trouble as I started with jboss.

Post by Yuri Kouzmov
What changes do I have to make to the build file other than <!ENTITY
database SYSTEM "file:./config/database/oracle.xml"> at the top and
oracleSequence element in order to make it use Oracle?

To use Oracle with JBoss fiddle in some files
First you need an oracle-service.xml in your
$JBOSS_HOME/server/default/deploy
(If you run JBoss with the -c option change 'deploy' to the value of
-c). There is an template oracle-service.xml in
$JBOSS_HOME/docs/examples/jca
Set the properties for ConnectionURL, DriverClass, Password and UserName
according to your needs.

In $JBOSS_HOME/server/default/conf/standardjaws.xml replace
<datasource>java:/DefaultDS</datasource>
<type-mapping>Hypersonic SQL</type-mapping>
with
<datasource>java:/OracleDS</datasource>
<type-mapping>Oracle8</type-mapping>

In standardjbosscmp-jdbc.xml (in the same directory) change
<datasource>java:/DefaultDS</datasource>
<datasource-mapping>Hypersonic SQL</datasource-mapping>
to
<datasource>java:/OracleDS</datasource>
<datasource-mapping>Oracle9i</datasource-mapping>

This should help. Start JBoss and have a look in the output if you get a
datasource named OracleDS properly installed.

This setup should run with the sequenceblock-Element.
Try this first before you proceed with oracle sequences!
If you want to use "real" oracle sequences you have to create a sequence
for each table ending with _SEQ. And enable the oraclesequence in the
build-File.

Hope this helps.

regards,
Matthias