[solvedish]Changing from MySql to MS SQL (the php code part)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

[solvedish]Changing from MySql to MS SQL (the php code part)

Post by waskelton4 »

Hey group,

I am a php/mysql programmer that was just reciently sent by my boss to a mssql class..

I have been developing a program using mysql that i now feel i should migrate to mssql. (not because i think it's better.. but because i feel obligated to.. :) ) my two questions..

1. Is there an easy way to do this? (i.e. Find and replace or a program that will convert it for me...)

or

2. Would it be beneficial to use something like the adodb libraries or PEAR so i could use mysql and mssql with the script?

any help is greatly apprecitated..

Thanks
Will
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post by waskelton4 »

well.. i started poking around trying to fix it before i got a reply..

and find and replace did a pretty good job....for the most part

FYI...

if you look at the documentation the main functins map pretty directly..

mysql_query = mssql_query....

to get mysql_insert_id() you need..
mssql_result(mssql_query("SELECT @@IDENTITY AS id"), 0, "id");


now i'm having problems with some of the database aggregate functions..
in mysql AVG() will get the average of fields in a text field. mssql requires a numeric data type.. also.. no FORMAT() in mssql..

off to the Database forum..

Will
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

To answer your initial post...

There is no (as far as I'm aware) automation that would tackle this job for you. While a simple search & replace will take of the function names, there is (in many cases) subtle differences in the actuall functions e.g. I know that mysql_query takes it's arguments in the form of mysql_query(query, resource) whereas pgsql_query takes it's arguments in the form of pgsql_query(resource, sql). I realise you are not converting from MySQL to PostgreSQL but that is just an example.

There can also be subtle differences in the actual query syntax which can mean some reworking of some of your queries.

Secondly, I would recommend some sort of database abstraction layer as you may want/need to change/support some other database in the future. As to which one to use, I couldn't make a recomendation. I decided to write my own which is still work in progress but is looking/functioning OK. The hardest part was/is writing support for MySQLs auto_incrementing id for other databases.

There would be more work involved to add an abstraction layer to an existing app but once it is done and inplace it does make switching databases much easier but due to differences in SQL syntax between databases it will never be seamless.
Post Reply