RE: [PHP] Database abstraction

Hello Andrew, On 30-Aug-00 11:29:44, you wrote: >A properly written ODBC driver _should_ hide most of those things from the >application. The ODBC API provides support for querying the database about >autoincrementing capability (SQLColAttribute's SQL_DESC_AUTO_UNIQUE_VALUE). Yes, but how do I retrieve the value that was just inserted into a auto-incremented column? Still, this doesn't solve the problem of creating sequences in database independent way with DBMS that do not support auto-incremented columns but support real sequences, like Oracle, PostgreSQL, mini-SQL, etc... >There are hundreds of other calls that drivers will make to describe back >end functionality, based on the return values of the driver you should be >able to handle differences. Plus, an ODBC driver in many cases should mask >functional differences, performing appropriate translations for different >databases. Actually, now that I have studied a little more about it, it's up to the application to perform any translations after querying the driver capabilities. For instance, if you want to build a CREATE TABLE statement you need to get the type information for each type you want to use and then build the SQL statment from that. 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? I downloaded the ODBC part of Microsoft Data Access SDK, but the part on GetTypeInfo call is not suficiently clear to let deduce that pseudo-code. I have been wild-guessing what to do with the values of the returned fields, but I'm afraid it won't work. A related subject, why does "CREATE TABLE test (test INTEGER NOT NULL DEFAULT 0)" fail with Microsoft Access ODBC driver? >Returning limited result sets should be handled by a cursor. There are five >cursor models, that an ODBC driver should support: static, forward only >scrollable, bi-directional scrollable, keyset driven, and mixed. Even But how do I pick the appropriate cursor type in PHP? It seems that there is no way to do it? Actually it seems that PHP ODBC API is lacking of many important things like: how do I figure if a result column is a NULL? NULL are set to an empty string that may be an valid non-NULL value for text columns. Bad implementation decision! PHP Informix API also has that problem. I see this as a bug, and before this gets forgotten I will post a bug report so somebody can do anything about it soon. How do figure if a driver supports transactions? There seems to be no way to query a driver at that level. If ODBC could be used as database abstraction no application should need to make any assumptions on the underlying database. In this case bindings to SQLGetInfo is missing in PHP. >Lastly, (since I'm ranting anyway :) ODBC should be faster than native >drivers. The reason is that native drivers connect a client layer to the >native communications protocol, which then speaks to the Call Level >Interface of the database. So you have 2 layers on top of the CLI in a 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. >I hope this has been helpful - not tryign to argue per se, just dispel some >myths about ODBC. Yes, all those slow/limiting/difficult myths were true >several years ago, but because of the commodization of databases, a few >vendors have worked to realize the promise of ODBC. I tend to see it from another angle. It seems very hard to use ODBC to develop Web applications that need a database abstraction, without making assumptions about the underlying database. Regards, Manuel Lemos Web Programming Components using PHP Classes. Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org -- E-mail: mlemos@acm.org URL: http://www.mlemos.e-na.net/ PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp --