Discussions

News: Liberty DataBase Connectivity (LDBC) Released

Bookmark and Share Get thread feed

  1. Liberty DataBase Connectivity (LDBC) Released (16 messages)

    • Posted by: Dion Almaer
    • Posted on: August 26 2003 11:35 EDT
    Thomas Mueller has released Liberty DataBase Connectivity (LDBC), a JDBC driver that abstracts the SQL grammar differences between databases. JDBC gives you a standard interface for SQL statements, but LDBC tries to take another step and maps different vendor SQL into a common language. This allows you to *really* port your code to a different db.

    This is a fairly new project, and not all db features are supported, so you find yourself working with a subset (sound familiar? (EJB-QL, JDO-QL, ...).

    Check out the LDBC Home Page

    Does it make sense to try to do something like this?
    Reply to this

    Threaded Messages (16)

  2. see also: CrossDB[ Go to top ]

    Another open source project:

    http://www.crossdb.com/
    Reply to this Reply to original

  3. CrossDB Differences[ Go to top ]

    A major difference between LDBC and CrossDB is that LDBC still follows the JDBC API whereas CrossDB uses a different database API. Sounds like using CrossDB locks you in to their API.

    This comment is based on just a quick look at the two sites.
    Reply to this Reply to original

  4. Jakarta Commons SQL[ Go to top ]

    Another project worth looking at:

    http://jakarta.apache.org/commons/sandbox/sql/
    Reply to this Reply to original

  5. non programatic use[ Go to top ]

    The two open source projects mentioned feel more like the SQL building logic ripped from a OO-RDBMS mapping tool. For programmatic access from within an application that is nice, but a uniform SQL dialect has a slightly different scope e.g. usage in one of the universal SQL query tools.
    Reply to this Reply to original

  6. Oracle and multi-database mapping[ Go to top ]

    I must admit that I'm very comfortable with the old Oracle (+) syntax too, mainly because I've used it for so long before other DBs even supported the concept and probably since before the SQL standard existed (I'm prepared to be corrected :)

    To add to my previous list, oracle has a to_number(), which you also need to convert to integer() double(), etc when you move to a stricter db - the list goes on. What I've tended to do in conversions is to solve cross db issues by putting the ugly SQL code into a view rather than my JDBC - I've generally found that non-standard SQL only make up around 5 to 10% of the code if that, so it's not a huge bridge to cross.

    I think the main use for this sort of tool would be to assist in the porting of applications from 1 DB platform to another. I've very much stuck to struts/servlets/JDBC in the past because of the bang for buck you get out of them - ie. free container, 4 times the performance of EJB (at least), simplicity of configuration, etc, etc, however the downside is hardcoded SQL in your POJOs. If I was starting a new project now I would be tending towards EJB CMP which now looks almost reasonable or an OR tool like Toplink to solve the cross DB issues.
    Reply to this Reply to original

  7. Oracle and multi-database mapping[ Go to top ]

    I've very much stuck to struts/servlets/JDBC in the past because of the bang for buck you get out of them - ie. free container, 4 times the performance of EJB (at least), simplicity of configuration, etc, etc, however the downside is hardcoded SQL in your POJOs. If I was starting a new project now I would be tending towards EJB CMP which now looks almost reasonable or an OR tool like Toplink to solve the cross DB issues.


    Hibernate has nice support for cross DB handling too: Set the respective dialect, and off you go. If you consider TopLink, I definitely recommend also checking out Hibernate. Furthermore, it matches your previously simple and free configuration nicely :-)

    Juergen
    Reply to this Reply to original

  8. Jakarta Commons SQL[ Go to top ]

    From what I read Jakarta Commons SQL reads the schema from one database, and generates SQL to create the same schema in another database type. However, are Oracle "number" types handled, when the actual datatype is int, or long? AFAIK, Oracle metadata simply does not have all the information needed to re-create the schema on other databases. Other DBs might have the same problem in other areas.
    Reply to this Reply to original

  9. Liberty DataBase Connectivity (LDBC) Released[ Go to top ]

    Why not use standard SQL instead? Follow the specifikation and you don't have to translate between different dialects. That's the point in having SQL in the first place.
    Reply to this Reply to original

  10. Liberty DataBase Connectivity (LDBC) Released[ Go to top ]

    If only that were true...
    Reply to this Reply to original

  11. Liberty DataBase Connectivity (LDBC) Released[ Go to top ]

    Does it make sense to try to do something like this?


    Yes, but the problem with such an effort is that it only supports the lowest common dominator features. What if I want to use "REPLACE" command in MySQL for example?

    -------------------

    Cameron Zemek
    Reply to this Reply to original

  12. Main issues[ Go to top ]

    This sounds like a good idea, but doesn't address the main issues I've seen in cross database support.

    I've done a lot of oracle work as well as DB2, Progress and SQL server. The main issues I have are:

    1. Commonly used functions called different things in different databases. My main gripes are NVL in Oracle/Progress called COALESCE in DB2, SQL Server. Also the use of DECODE instead of CASE statements. The other issue was outer joins, but Oracle/Progress now support industry standard outer join syntax.

    2. Sequence numbers - all DBs handle these differently, if at all (SQL server doesn't). Basically need completely different code for different databases - eg. In oracle you would use SELECT xxx.netval FROM DUAL, DB2 is VALUES xxx.nextval, Progress SELECT pub.xxx.nextval FROM ? (very dodgey hardcoded userid), etc

    3. difference between CHAR and VARCHAR fields - in Oracle, DBAs tend to use VARCHAR2's for everything, whereas DB2 DBAs use CHARs for fields less than 50 bytes as they perform better. When trying to support both, you get into a lot of trouble with CHAR fields and trailing blanks - the basic solution is to truncate all trailing blanks, but this isn't so good if you want them there..

    4. difference between integer / number handling with different JDBC drivers. Oracle doesn't really support an integer datatype, whereas DB2 does. Need to go to a bit of effort on porting to make sure the Objects you get back from are castable to BigDecimal / Integer, etc.

    If you could solve these problems then it looks to be a good product..
    Reply to this Reply to original

  13. Main issues[ Go to top ]

    Very good point you made: thanks a lot.

    I'd just like to elaborate a bit about your comment on the outer joins: as far a I know Oracle support for the SQL syntax for outer joins is only available starting with Oracle 9i. Anything older than that and you're stuck with the proprietary "+" syntax.

    Stéphane
    Reply to this Reply to original

  14. Main issues[ Go to top ]

    You're stuck with the '+', which is like so cool. I wonder if this product is not a way to eliminate all vendor specific strenghts, apart from eliminating the differences.
    Reply to this Reply to original

  15. Oracle syntax for outer joins[ Go to top ]

    You're stuck with the '+', which is like so cool. I wonder if this product is not a way to eliminate all vendor specific strenghts, apart from eliminating the differences.


    Sorry but although it may be a nice syntax (I personally like it), Oracle's proprietary syntax for outer joins is definitely an issue as far as portability is concerned.

    It is important to realize that this is not a "cool" feature from Oracle which users are at a liberty to use if they want to leverage Oracle: prior to 9i, Oracle DOES NOT support the standard SQL syntax for outer joins which effectively forces all users to write Oracle proprietary code everytime they need to perform an outer join.

    Stéphane
    Reply to this Reply to original

  16. Integration with C-JDBC ?[ Go to top ]

    C-JDBC (or Clustered JDBC) is a Java middleware that allows database clustering at the JDBC level. C-JDBC supports heterogeneous databases since it works more or less like a proxy at the JDBC level, but query rewriting is very limited.
    In most cases, users have an existing application using a specific RDBMS and they would like to add a different database without rewriting their app. The LDBC approach seems to force you to adopt the LDBC syntax to map it to the vendor specific SQL. One thing that would be interesting, would be to have a way to define a translation mechanism to map queries from one database to another one. Do you think some parts of LDBC could be reusable to do this?
    Could it be integrated with C-JDBC?
    Reply to this Reply to original

  17. Re: Integration with C-JDBC[ Go to top ]

    <In most cases, users have an existing application using a specific RDBMS and they would like to add a different database without rewriting their app. The LDBC approach seems to force you to adopt the LDBC syntax to map it to the vendor specific SQL. One thing that would be interesting, would be to have a way to define a translation mechanism to map queries from one database to another one.
    <<<<<<<<<<<<<<<<<<</QUOTE>

    The above problem is what precisely our prouduct Vembu SwisSQL solves. You can take an existing application and plugin SwisSQL. It will translate SQL queries written for one database and convert it to other databases. We support conversion of most of the in-built SQL functions and other syntax variations. The product is available as a

    1. SwisSQL Java API (with virtual JDBC driver)
    2. SwisSQL.NET API
    3. SwisSQL Console GUI

    We believe we support about 70 to 80 % of the differences today. Hopefully it will be much better in another few months. The idea is to basically write SQL (including proprietary features) for a database and have the ability to plug-in other databases.

    The 1.0 product release was posted here in TSS in April. We have got couple of customers already who embed SwisSQL in their J2EE application. We currently support Oracle, SQL-Server, DB2, Informix, PostgreSQL and MySQL (with some limitations).

    Please check out Vembu SwisSQL too.


    Thanks
    Sekar Vembu
    Vembu Technologies
    www.vembu.com
    Reply to this Reply to original