Having built our interface we could get on with bulding the application. A couple of auxiliary PHP programs were writte so that we could always drop all the tables in the database and re-create them empty – these are run using PHP stand-alone instead of as a CGI script – and we found no serious problems other than normal application development concerns.
Most of the development was done using Linux tools with the NT virtual server directory mounted onto the Linux system (using smbmount) so that the one set of files, located on the NT system, were simultaneously being used to run the Linux and NT versions of the site. Switching our configuration files to tell each server which database to use allowed us to ensure that the software operated properly with both SQL Server and MySQL.
Of course we found incompatibilities between the dialects of SQL that the two databases use. MySQL considers every data type to be a string (or at least works as if it does) so that something like:
insert into blah values(“abc”, “123”)
works even if the second column was declared to be a number. SQL Server won’t stand for that and you need to be type-conscious.
A special function had to be written to deal with string concatenation in a consistent manner (see
DBconcat above) because we needed it often enough. A small number of places in the software discovered irritating inconsistencies which had be dealt with.Here is a very frustrating example of serious database-dependency:
<?
// Annoying database incompatibility! MS Sql server can't cope with the ON
// clause of a LEFT JOIN involving more than two tables, so it has to have a
// special version of this query written using sub-selects
if ($DBms == 'mssql')
list($found) = DBfetch_row(DBquery("
SELECT COUNT(part.id)
FROM part, price_factor, customer
WHERE part.id = '$SearchPartNum'
AND (allow_usa = 1 OR allow_uk = 1)
AND part.price_group = price_factor.part_group
AND price_factor.customer_group = customer.price_group
AND factor > 0
AND customer.id = '$Customer'
AND part.price_group NOT IN
(
SELECT part_group
FROM vending_prohibition, customer
WHERE customer_type_id = customer_type_id
AND customer.id = '$Customer'
)
", $Con));
else
list($found) = DBfetch_row(DBquery("
SELECT COUNT(part.id)
FROM part, price_factor, customer
LEFT JOIN vending_prohibition
ON part.price_group = vending_prohibition.part_group
AND vending_prohibition.customer_type_id = customer.type_id
WHERE part.id = '$SearchPartNum'
AND allow_usa = 1
AND part.price_group = price_factor.part_group
AND price_factor.customer_group = customer.price_group
AND factor > 0
AND customer.id = '$Customer'
AND vending_prohibition.customer_type_id IS NULL
", $Con));
?>
