MDB2 is a PEAR package that performs some abstraction on the native PHP DB calls. In short, it allows to use $DB->query($sql) instead of mysql_query or mssql_query (plus a few more things).
The following article is the list of pitfalls we had to fix in order to get a fully compatible application, that would run the same on MySQL or MSSQL (for now).
Field types
Field types are not the same between the two engines of course, but all in all it is not too bad.
ENUMs don't exist in MS SQL so these will have to be moved to CHAR(1), MEDIUMTEXT should be TEXT.
If you want to use MDB2 Schema, beware that it considers ENUM('Y', 'N') to be booleans if the field starts with an "is_" or "has_", but then builds a database with boolean being TINYINT (we'll have to get back on them about that).
SQL syntax
There's a few things that work in MySQL and breaks in MS SQL. Here's what we found:
- REPLACE INTO: this one is a bugger, we used REPLACE extensively. Well that doesn't exist on MS SQL.
- ISNULL:
ISNULL(somefield) should be somefield IS NULL. That's the ANSI way and ISNULL won't work on MS SQL...
- IFNULL: IFNULL is ISNULL on MS SQL, but you should really use the function COALESCE, which works the same (when used with two parameters) and is more ANSI.
- ORDER BY, GROUP BY: on MS SQL, you should be carefull that all the fields that you want to ORDER BY on, should be in the SELECT clause. Same goes for GROUP BY it seems. See further for GROUP BY as it can be a real bugger...
- COUNT: on MS SQL, COUNT only works with one field somehow
- DISTINCT: DISTINCT is a bit touchy on MS SQL - you should be carefull not to have fields that have type TEXT for example.
- NOW: Won't work on MS SQL. MDB2 provides some utility functions to build dates for SQL, but that really does a
date('Y-m-d H:i:s')
- TO_DAYS: Won't work on MS SQL either. We really missed that one.
- LIMIT: LIMIT doesn't exist in MS SQL, but MDB2 prevides a setLimit() function that will do the trick on all DBMS. Good enough.
AutoIncremented fields
That's a big one. It'd be a good thing that DBMS makers could standardize on something for autoincremented indexes.
In MySQL, if your field id is an autoincremented field, you could do:
INSERT INTO my_table (id, name) VALUES (NULL, 'first');That won't work on MS SQL (you can't leave a NULL value on them). The proper way to do it with MDB2 is as follows:
$values = array();
$values['name'] = $GLOBALS['mdb2']->quote("first");
$newId = $GLOBALS['mdb2']->extended->getBeforeID('my_table', 'id', true, true);
if($newId != 'NULL')
$values['id'] = $newId;
$sql = "INSERT INTO my_table (".implode(',', array_keys($values)).") ".
" VALUES (" . implode(',', $values) . ")";
$result = $GLOBALS['mdb2']->query($sql);
if (EpiDBTools::IsMDB2Error($result))
return false;
$newId = $GLOBALS['mdb2']->extended->getAfterID($newId, 'my_table', 'id');
if (PEAR::isError($newId))
{
$newId = false;
return false;
}
It's a bit verbose but it works. The getBeforeID is required if you want your code to work some day on PostgreSQL, which uses sequences instead of AutoIncrement values.
Now if you want to insert a row in table with an autoincremented field, and still want to explicit the value of the index, you'll need to enable the IDENTITY_INSERT first, like this:
if ($GLOBALS['mdb2']->dbsyntax == 'mssql')
{
// On MS SQL, allow temporarilly to insert a row by specifying the id
$result = $GLOBALS['mdb2']->query('SET IDENTITY_INSERT my_table ON');
if (PEAR::isError($result))
return false;
}
$sql = "INSERT INTO my_table (id, name) VALUES (4, 'first')";
$result = $GLOBALS['mdb2']->query($sql);
if (PEAR::isError($result))
return false;
if ($GLOBALS['mdb2']->dbsyntax == 'mssql')
{
$result = $GLOBALS['mdb2']->query('SET IDENTITY_INSERT my_table OFF');
if (PEAR::isError($result))
return false;
}
Even more verbose, but it works.
MS SQL settings
- ANSI_NULL_DFLT_ON: you should set this parameter to ON, if you don't want all your fields to be NOT NULL...
- IMPLICIT_TRANSACTIONS: beware that if you leave that ON, then you'll have to do COMMITs from time to time, otherwise your changes won't be taken in account in the database. If you come from the MyISAM world, set this one to off (which is ON by default if you set ANSI_DEFAULTS to on).
GROUP BY
In MySQL, I allow myself to do queries like the following to get all the clients that have at least one order:
SELECT * FROM client
INNER JOIN order ON order.client_id = client.id
GROUP BY client.id
That
won't work with MS SQL. In the SELECT clause, you are allowed to put things that appear in the GROUP BY clause (ie. client.id), or GROUP BY functions (MAX, AVERAGE, ...). It appears that MySQL is much more flexible than MSSQL regarding to this.
Labels: code, mdb2, ms sql, mysql, pear, php
