Experience with using JBoss/CMPs as a DB independent platfor

[bmacy]

I've spent the last 2 weeks playing with my Alpha J2EE application (developed on JBoss 3.0.1rc1 and PostgreSQL 7.2) on Oracle 9i, Firebird, and with PostgreSQL pseudo-XA support. It's been interesting and probably nothing new to anyone here but I figured I'd post a bullet list of my experiences (and maybe someone can tell me what I'm smoking)...

- 2pc... Using JMS and CMP with a MessageDrivenBean and a non-2pc JDBC driver is basically gauranteed warnings about how you aren't getting the transaction integrity you are expecting. This is true, but seeing as I haven't seen anyone screaming about any great DBs with JDBC drivers with full XA support this is a bit of an issue to get full use of J2EE.

- The DS setup is a pain... the example config files (.e.g oracle-service.xml) aren't right, especially when it comes to some of the things they depend on. For instance, "Minerva JDBC LocalTransaction ResourceAdapter" doesn't seem to actually exist and the JBoss name needs to be substituted. Not a big deal but it can take quite some time to debug if you aren't used to it.

- Unique Ids... everyone does them differently... hopefully we'll get JDBC 3.0 drivers that will resolve this.

- Reserved words... I was surprised to find that the JCA layer doesn't map/escape column names to avoid conflicts with reserved words.

- Oracle Objects.... I still can't get Oracle to work with Objects. I get StreamCorruptedExceptions when reading in HashMaps (small < 500 bytes) for my CMP objects. There doesn't seem to be a solution for this and I've tried everything I could find.

- Foreign Keys Constraints... great things to have but I couldn't even get Firebird to create the tables because alter table was being called on "open tables". Essentially, CMP B would be deployed containing a foreign key to CMP A's table. The alter table on CMP A's table would fail. I ended up removing them... made it a pain to drop tables anyways.

- Firebird character transliteration... not sure what is up with this but it seems that I can't fetch NULL VARCHAR values our of the database as java.lang.String without getting org.firebirdsql.gds.GDSExceptions.

Anyways, so basically I'm 1 for 3... hopefully in the next couple of weeks that will improve.

Brian Macy

[bmacy]

Wanted to make a correction as I did get Firebird to work. I needed to specify UNICODE_FSS as the default charset when creating the database. I would expect JCA to specify this for each column.

Brian Macy

[davidjencks]

> I've spent the last 2 weeks playing with my Alpha
> J2EE application (developed on JBoss 3.0.1rc1 and
> PostgreSQL 7.2) on Oracle 9i, Firebird, and with
> PostgreSQL pseudo-XA support. It's been interesting
> and probably nothing new to anyone here but I figured
> I'd post a bullet list of my experiences (and maybe
> someone can tell me what I'm smoking)...
>
> - 2pc... Using JMS and CMP with a MessageDrivenBean
> and a non-2pc JDBC driver is basically gauranteed
> warnings about how you aren't getting the transaction
> integrity you are expecting. This is true, but seeing
> as I haven't seen anyone screaming about any great
> DBs with JDBC drivers with full XA support this is a
> bit of an issue to get full use of J2EE.

What about firebird?? I don't like the warning appearing so often, perhaps I should eliminate it entirely.

>
> - The DS setup is a pain... the example config files
> (.e.g oracle-service.xml) aren't right, especially
> when it comes to some of the things they depend on.
> For instance, "Minerva JDBC LocalTransaction
> ResourceAdapter" doesn't seem to actually exist and
> the JBoss name needs to be substituted. Not a big
> deal but it can take quite some time to debug if you
> aren't used to it.

Where did you get this Oracle-service.xml? it looks to me as if the one with the distro should be right.
>
> - Unique Ids... everyone does them differently...
> hopefully we'll get JDBC 3.0 drivers that will
> resolve this.

Feel free to contribute this to firebird. Support for returning ids is in blr, you just need to fix the sql parser to handle the appropriate string and the firebird driver to read the results.
>
> - Reserved words... I was surprised to find that the
> JCA layer doesn't map/escape column names to avoid
> conflicts with reserved words.

This is certainly not a function of a jca adapter nor the server jca components. I thought you could get the cmp engine to quote all names, thus avoiding the problem with a reasonably compliant jdbc driver. This support might have been added since your rather old jboss version.
>
> - Oracle Objects.... I still can't get Oracle to work
> with Objects. I get StreamCorruptedExceptions when
> reading in HashMaps (small < 500 bytes) for my CMP
&gt; objects. There doesn't seem to be a solution for this
&gt; and I've tried everything I could find.
&gt;
&gt; - Foreign Keys Constraints... great things to have
&gt; but I couldn't even get Firebird to create the tables
&gt; because alter table was being called on "open
&gt; tables". Essentially, CMP B would be deployed
&gt; containing a foreign key to CMP A's table. The alter
&gt; table on CMP A's table would fail. I ended up
&gt; removing them... made it a pain to drop tables
&gt; anyways.

?? Do you have a simple example that demonstrates this? Is this caused by firebird waiting to the end of a tx to execute ddl? I think this should work in later jboss versions that allow the connection to autocommit in this setup code.

&gt;
&gt; - Firebird character transliteration... not sure what
&gt; is up with this but it seems that I can't fetch NULL
&gt; VARCHAR values our of the database as
&gt; java.lang.String without getting
&gt; org.firebirdsql.gds.GDSExceptions.

If you want the cmp engine to set character set values for each read operation you should talk about it on the cmp forum.

&gt;
&gt; Anyways, so basically I'm 1 for 3... hopefully in the
&gt; next couple of weeks that will improve.
&gt;
&gt; Brian Macy