Re: Converting code to work with MS SQL from MySQL

On Fri, 23 Aug 2002 12:30:49 -0500, you wrote: >We are using alot of mysql specific functions in our code >(mysql_fetch_array, mysql_query, etc). However, we are >contemplating switching to MS SQL. Will it be as simple >as doing a global search on the function names and replace >the "mysql_" with "mssql_"? >Does anyone have any experience switching from MySQL >to MS SQL? What problems, if any, did you experience? >Any snags? Well, I haven't switched, but I do use both of them extensively (along with PostgreSQL, my current favorite of the three). I wouldn't expect that you'd be able to do a mere global search and replace and then fire up your pages with no problems whatsoever. It depends highly on the types of queries your applications use. For the most part MS-SQL's features are a super-set of MySQL, but there are some things in MS-SQL that are missing (at least in version 7.0 which is the only one I have any experience with). The main one, and the one you're most likely to run into, is that MS-SQL does not have a LIMIT clause. My understanding is that LIMIT is an extension to the SQL92 standard. Both MySQL and Postgres have it but MS-SQL v7.0 does not. I think that MySQL has some full-text indexing features that are lacking in MS-SQL 7 (but I think are present in MS SQL 2000). I think there are differences in how some of the larger field types (such as the text type) are handled too. Basically I think the amount of hassle you are going to have is directly proportional to the complexity of your application.... One other thing. I use a very very simple DB class that wraps all of the different PHP db functions so I can call them with common names. It doesn't do ANY abstraction of the actual queries, but it does prevent me from having to type different function names all the time. Here's an example: $db = new DB; $query = "select foo from bar"; $result = $db->query($query); while($row = $db->fetch_object($result)) { echo $row->foo.'<br>'; } I have a different class for each type of database I use (MySQL, MS-SQL, and PostgreSQL) but all of the method calls have the same names. That way I could theoretically switch from one to the other and only have to worry about the SQL syntax itself. This method also gives me the added benefit of being able to take a particular action with every query on my site...for example while I'm debugging I often throw an echo $query inside my DB::query() method and this lets me see all the queries as they are being executed, which can be a lifesaver. Anyway, if you're interested in the (extremely simple) wrapper I'm using I can give you the code... HTH