Re: Database abstraction

Hello Andrew, Hello Manuel, >BTW, do you know any source of information (URL) or have any pseudo-code >that can be used to look into data returned by odbc_gettypeinfo() call and >build a field declaration suitable for use in a CREATE TABLE statement? In theory, you get all the information required for doing this - you "only" need a mapping from every possible native datatype to a corresponding datatype available in the specfic datasource. But how would you handle a request for a varchar(5000) column when e.g. Oracle only supports up to 4000? Map it to a CLOB column? Cut it down to 4000 chars? Or split it to two columns? The idea of a database abstraction layer sounds compelling, but IMHO beyond a certain level it gets impossible. Shure, it would be nice to have an abstraction for a CREATE TABLE statement - but normally, when building a web application, you design your data model, feed it to the database and that's it. If you want to support different databases, you supply sql scripts for them, probably with the help of a database modeling tool. After you have installed your application, you will rarely be doing DDL - at least you shouldn't be. Additionally, as Andrew pointed out, ODBC already tries to abstract from database specific SQL, e.g. by defining the placeholder to be "?". Another problem related to data design is how to support foreign key constraints? While most SQL databases support this, MySQL does not and requires the application to do this integrity checking - this can't be done by an abstraction layer. Shure, you can add the required logic to your application, but why make the application more complex when your database can handle this more efficient for you? You will always have the decide between maximum database portability and using database specific features depending on your application. I wouldn't base a complex business/ECommerce application on a database that doesn't support transactions or foreign key constraints while a simple bulletin board might well live without this. IMHO it is good to have an abstraction layer that provides a common functional or OO interface to databases like Perl's DBI, JDBC, or ODBC. Apart from being very complicated, such SQL abstraction would add a serious amount of overhead. >I believe that performance could match native driver access on DBMS that >are already are being interfaced via SQL. But for databases that need >a SQL engine over it to be accessed it should be much slower to manipulate >them like with Microsoft Access. The need to add an SQL interface to an ODBC driver shure adds overhead, but most ODBC drivers have the ODBC layer sitting piggyback on their native CLI. E.g. the Oracle drivers require the complete Oracle client libraries to be installed. MySQL is another example, it is said that ODBC access is around 20% slower than native access. Only a few databases (e.g. Solid) that use ODBC/CLI functions as their native C Api don't suffer from this speed I'm planning to rewrite the ODBC module, lifting it up to ODBC 3.5x - unfortunatly I'm rather busy these days. Many of the restrictions mentioned have "historical" reasons - the module was originally developed using the Adabas D ODBC driver. It is almost impossible to take into account and handle automatically every driver and it's implemantation quirks or limitations. I agree that the user should be given more control over setting driver specific features (e.g. selecting cursor types on statement level) while keeping things simple and fast for every day use. Nobody wants to write dozens line of code just to do a simple query and fetch the results. -Andreas