Page 1 of 1
[solvedish]Changing from MySql to MS SQL (the php code part)
Posted: Thu Jul 08, 2004 9:52 am
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
Posted: Thu Jul 08, 2004 11:29 am
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
Posted: Thu Jul 08, 2004 11:44 am
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.