same querys in different RDBMS

[arnaud]

Hi,

I try to develop a BMP-EJB using (with JDBC) a data model that can be implemented
on any RDBMS (Oracle , SQL Server, ...).

I have defined a file of querys to access data but the querys I have developed
for SQL Server don't work on Oracle :
- problem with the ; at the end of a query
- difference in syntax (BEGIN & END, ...)
- problem in increment identifier : We use in oracle dual tables

Therefore I have to manage different files of querys for each RDBMS. Do you
think it is possible to define a query file that can be OK whatever the RDBMS
is (which is the claim of JDBC API) ? How is it possible ? Do we have to
make changes in our data model (no increment identifier, ...)

Thanks for your help.

[Bjarki Holm]

Arnaud,

this is a common problem. You could probably create SQL queries that you
could use in most common RDBMSs, but then you would have to refrain from
using many of the advanced features of SQL (that, unfortunately, are usually
very vendor-dependent). This includes OUTER JOINs, conversion functions,
etc.

Java provides a few solution that allow for independence of the underlying
database system. One solution are CMP Entity Beans. Most of the time, CMP
offer not only data store independence, but also considerable performance
increase over BMP beans. Before you start using BMP, you should always evaluate
whether you could accomplish the same using CMP.

However, there are cases where CMPs are not sufficient. I assume that your
case is one of those. For those scenarios, the new Java Data Objects (JDO)
architecture might offer a replacement over plain JDBC. JDO offers transparent
persistence of Java objects, and allows for query and comparison of persistent
objects.

But JDO will never become a replacement of custom SQL calls through JDBC.
A third solution, would be encapsulating the database calls in stored procedures
in the database. JDBC provides a stored procedure SQL escape syntax that
allows stored procedures to be called in a standard way for all RDBMSs. Example:

{?= call <procedure-name>[<arg1>,<arg2>, ...]}
{call <procedure-name>[<arg1>,<arg2>, ...]}


This way, you could use the same naming conventions for stored procedures
in different database systems, and thus encapsulate the persistence logic
from the business logic of your Java classes.

Let me know if any of this sounds feasible!

- Bjarki

[arnaud]

Thanks a lot for your answer!

In fact, today we separate in the querys the one which are common (simple
querys : select, ...) and the others which are specific.

In fact, there are two main problems that explain my question :
First is that we have automatic increment identifiers, which is managed differently
in Oracle and SQL Server. Do you know alternatives solutions to identify
an "persistent object" with the same level of performance ? (by the way,
how is it managed with CMP-EJB and JDO?)

Second is that we have constraints in our datamodel which seems incompatible
with CMP and JDO...

Thanks again.

"Bjarki Holm" <holm@vyre.com> wrote:
>
>Arnaud,
>
>this is a common problem. You could probably create SQL queries that you
>could use in most common RDBMSs, but then you would have to refrain from
>using many of the advanced features of SQL (that, unfortunately, are usually
>very vendor-dependent). This includes OUTER JOINs, conversion functions,
>etc.
>
>Java provides a few solution that allow for independence of the underlying
>database system. One solution are CMP Entity Beans. Most of the time, CMP
>offer not only data store independence, but also considerable performance
>increase over BMP beans. Before you start using BMP, you should always evaluate
>whether you could accomplish the same using CMP.
>
>However, there are cases where CMPs are not sufficient. I assume that your
>case is one of those. For those scenarios, the new Java Data Objects (JDO)
>architecture might offer a replacement over plain JDBC. JDO offers transparent
>persistence of Java objects, and allows for query and comparison of persistent
>objects.
>
>But JDO will never become a replacement of custom SQL calls through JDBC.
>A third solution, would be encapsulating the database calls in stored procedures
>in the database. JDBC provides a stored procedure SQL escape syntax that
>allows stored procedures to be called in a standard way for all RDBMSs.
Example:
>
> {?= call <procedure-name>[<arg1>,<arg2>, ...]}
> {call <procedure-name>[<arg1>,<arg2>, ...]}
>
>
>This way, you could use the same naming conventions for stored procedures
>in different database systems, and thus encapsulate the persistence logic
>from the business logic of your Java classes.
>
>Let me know if any of this sounds feasible!
>
>- Bjarki
>

[Bjarki Holm]

Hi Arnaud!

The increment identified problem is quite common. I once solved it for a
project of mine, by implementing a central Sequence Manager, that was aware
of the RDBMS being used, and instantiated an appropriate Sequence interface
implementation based on that. The Sequence interface defined methods like


public long getSequence();

A specific sequence would be acquired from the SequenceManager, by supplying
the specific sequence name:

public Sequence getSequence(String name);

The SequenceManager was designed to be Singleton.

Now, each implementation of the Sequence interface would then use the associated
RDBMSs method of incrementation. For Oracle, that would be accomplished by
selecting a SEQ_NAME.NEXT_VAL, and for SQL Server, one could create an empty
table, that had a single IDENTITY column. You get the picture?

(Also, the Sequence implementation would support some sort of caching, so
that a database call wouldn't be necessary each time a new identity value
was needed.)

I think that most JDO vendors will supply auto-increment behind the scenes,
of course depending on the system being used. CMPs are usually supplied with
an identity value from the application developer - which has to be obtained
with a method like the one shown above.

You say that your database model has constraints not possible to map with
CMP. Have you looked at the EJB 2.0 specification? That proposes a lot of
enhancements over the previous spec, which could help you achive your goal!

Cheers,

Bjarki