Re: Connecting website with SQL-database.....

Hello Lincoln,

On 20-Apr-00 00:59:07, you wrote:

>>I may be mistaken, but the last time that I looked at Perl DBI, it didn't
>>seem to a complete database abstraction layer than it is needed. For
>>instance, you want retrieve data from date fields the results come
>>formatted in a database dependent way. This means that your DBI
>>applications can't really be that much database independent as you still
>>have to handle datatype differences in the application code.
>>

>I wish you all the best. And there's a chance you may succeed (tho it looks
>real slim from here).

>I may be wrong but don't see much hope of you succeeding without chopping
>off miscellaneous database specific features which make some people choose
>to use those various databases in the first place.

It's a trade-off. If you want to take the most of a database non-standard
extensions, don't use database abstraction packages at all. If you want
flexibility in such way that your applications will be more portable,
database abstraction packages are the way to go.

>I think perl DBI took the pragmatic approach, well in the spirit of Perl's
>more than one way to do it. Messy, but works rather well. The DBI Proxy
>thingy was a real saver for one of my friends.

DBI Proxy is an interesting approach to solve some problems, but I didn't
quite follow what that has to do with what we were talking.

>>With this Metabase package in PHP date fields are always returned formatted
>>in the industry standard ISO 3166 (YYYY-MM-DD HH:MI:SS). Then you do
>>whatever processing you want with dates formatted this way, but it's always
>>DBMS independent.

>OK this one is nice. Is there also a standard for timezones and finer than
>one second resolution?

No, that's outside the scope of the package to go that far when most
databases differ greatly. It could be an idea to add support to
conditionally enable subsecond time representation or even time zones.

After all it also comes with an option to choose the number of places on
the right of the point for decimal fields. That is an option that should
only be changed before install time. So could be subsecond time fields
eventually with time zones.

>Transactions for MySQL would be interesting to see.

I don't know what you got but Metabase MySQL driver does not attempt to
implement emulate transactions. That's too complex. It is well outside
the scope of Metabase.

>Plus some people seem to want Postsgresql to do transactions Oracle style,
>whereas some might want Oracle to do transactions Postgresql style. So how
>about Metabase helping out?

Like other database abstraction packages Metabase only provides 3 functions
to handle transactions: AutoCommit(On/Off), Commit and Rollback.
AutoCommit(Off) implicitly starts a new transaction. AutoCommit(On)
implicitly ends an ongoing transaction commiting any work done. Commit
commits the current transaction if AutoCommit is Off and restarts a new
transaction. Rollback does the same except that it aborts the transaction
instead of commiting.

Whenever there is an error within a transaction whether from a database
statement or some problem in your application, the transaction should be
aborted explicitly by calling rollback.

If for some reason your PHP scripts exit with a transaction in progress,
Metabase uses its registered script shutdown handler to explicitly abort
a pending transaction that was not ended.

This is very important because PHP supports persistent database
connections. This means that the same process may reuse the same database
connection to be used to run different scripts from the same server thread.
If you leave a pending transaction open not only it may cause errors in the
next script that is run by the same server process, but it also may block
other processes to run transactions in the same database.

As for differences in implementations of transactions, I don't know, but
this seems to be the scheme that all the other database abstraction
packages seem to be using.

>I think if you can put in direct non ODBC support for DB2 and Oracle you
>could have a much bigger market for your stuff. Then maybe we could move
>apps seamlessly among Postgresql, DB2, Oracle environments.

Currently there is support for MySQL, mSQL, PostgreSQL, Oracle using OCI. The
MS SQL server driver is almost ready. There is somebody working on
Informix driver and possibly Sybase ASE. We could already move seeminglessly
between applications.

>You'll probably end up with a lot of work just keeping up with changes and
>developments though.

I am not alone. Metabase developments went farther than other database
abstraction packages in the direction of the needs of many Web developers.
Despite it has been under private development over an year, it only has
been publicly released in January 2000.

Despite of that, the lack of such a complete database abstraction package
for PHP is attracting many developers and some are willing to contribute
with new drivers.

I am about to make another public release with some fixes and new drivers.
One important add-on that this release will come is a Metabase DBMS driver
class test suite.

Basically it is a script that uses some techniques of regression testing to
verify the conformance of any driver. This already helped to fix some
subtle bugs in the existing drivers but it will help further to detect faults
in future drivers and problemns when interfacing with new releases of
databases.

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos(at)acm(dot)org
--
E-mail: mlemos(at)acm(dot)org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--