#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Location
    Newfoundland, Canada
    Posts
    138
    Rep Power
    14

    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
  2. #2
  3. Big Endian
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2001
    Location
    Fly-over country
    Posts
    1,172
    Rep Power
    30
    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.
  4. #3
  5. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  6. #4
  7. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    792
    Just as a note, when comparing ODBC to the native database functions in php, ODBC is slow.
  8. #5
  9. 11
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Jul 2001
    Location
    Lynn, MA
    Posts
    4,635
    Rep Power
    82
    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.)
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Location
    Newfoundland, Canada
    Posts
    138
    Rep Power
    14
    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
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    London/Stockholm
    Posts
    4
    Rep Power
    0
    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? (http://pear.php.net/manual/en/core.db.php)

    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

IMN logo majestic logo threadwatch logo seochat tools logo