same querys in different RDBMS


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: same querys in different RDBMS

  1. #1
    arnaud Guest

    same querys in different RDBMS


    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.
    Share on Google+

  2. #2
    Bjarki Holm Guest

    Re: same querys in different RDBMS


    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

    Share on Google+

  3. #3
    arnaud Guest

    Re: same querys in different RDBMS


    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
    >


    Share on Google+

  4. #4
    Bjarki Holm Guest

    Re: same querys in different RDBMS


    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

    Share on Google+

Similar Threads

  1. Advance RDBMS
    By Humair in forum Talk to the Editors
    Replies: 0
    Last Post: 07-04-2002, 06:33 AM
  2. XML schema generation from/to RDBMS
    By Donovan in forum XML
    Replies: 1
    Last Post: 02-25-2002, 02:36 PM
  3. Multiple Select statements in a query's sql
    By jon028 in forum Database
    Replies: 1
    Last Post: 12-21-2001, 10:35 AM
  4. Leaders in RDBMS a Comparison
    By Saurabh in forum Talk to the Editors
    Replies: 0
    Last Post: 06-19-2001, 12:06 AM
  5. RDBMS for SAP
    By Walter Vogel in forum Database
    Replies: 0
    Last Post: 04-19-2001, 02:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center