Database abstraction later - any hep?

Hello - I need to use MS SQL 2000 on an IIS machine with PHP, we are used to developping on a Unix platform and want to be able to write SQL for MySQL and have it work on the MS SQL databse, i.e. we want to use a database abstraction to be able to transfer our code between the two platform.


Basically, what I want to know is will anyone recommend for or against AdoDB and ODBC as a database abstraction layer IIS / are there any benchmarks for MS SQL performance with these factors?

BTW - I can NOT use the php native database connection classes - sql code will be different between MySQL and MSSQL.
Thanks.

Steve

I do not have a good answer for your question but I thought I would throw out a couple of suggestions that might help you with your problem. If I wanted to develop MS SQL code on a Unix box and port it over IIS with the minimum amount of changes I might try:

1. Download the free version of Sybase for Linux. Sybase syntax and SQL Server syntax are very close to one another since they used to be the same product back at version 4.x. I have developed code on a laptop using the Microsoft MSDE and ported the other way (to a Sybase database) and it worked well.

2. Abstract your database by using stored procedures instead of straight SQL statements. If you had Sybase, Oracle or any other database that supported stored procedures running on a Unix box, as long as your stored procedures in SQL Server were named the same, accepted the same parameters and performed the same task, the code should be portable.

3. Have an IF-THEN-ELSE statement based on a global variable to pick the correct SQL statement. You would just toggle the variable depending on which machine you were running on:

if MySQL
sql = "SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM tablename"
else
sql = "SELECT FirstName + ' ' + LastName AS FullName FROM tablename"

4. Have all of your database specific SQL statements and connection routines defined in a seperate file. Include that file in your web pages. The file would be named the same on both the IIS and the Unix machine, but each one would have different content.

Try ADODB http://php.weblogs.com/ADODB hope this helps. And please report on your experience!

Just as a note, when comparing ODBC to the native database functions in php, ODBC is slow.

If you're not bound to PHP, then I suggest checking out perl and DBI, which is a true database abstraction layer.

It's very, very mature and allows you to use databases with the exact same interface- if you don't use database specific functions, you can actually switch seamlessly between different RDBMS's.

And Perl, run under mod_perl, is just as fast as PHP. (there is also mod_perl similar modules for IIS, I believe activestate has them.)

I am tied to php for now, and also to microsoft sql server 2000. AdoDB seems like a possibility for now. Anyone have a reason why not or any REAL benchmarks (the ones on their site suck).
Thanks again

Excuse a newbie for interrupting a discussion above my head, but I am very curious to know if there is any special reason PEAR is not mentioned here? (URL)

It's the only database-abstraction-something that I knew before I read this thread, and since I'm working on an application that must connect to all kinds of places I use it. And it's free - are they all? I only tried it with MySQL so far, though.

Kindly, Beetrader