How to Create an App Package Supporting both SQL Server and MySQL

By Sunitha Muthukrishna

February 16, 2012

Applications that currently support MySQL as their main DBMS can also support Microsoft SQL Server. The SQL Server Driver for PHP is available to PHP developers, it is a cost-effective and efficient solution for supporting SQL Server from within PHP applications. This article provides the steps needed for creating a Web Deployment package to install an application that supports both SQL Server and MySQL.

Adding MySQL and SQL Server Databases

Add both directives dbfullsql and dbmysql to add a SQL Server database and a MySQL database within the Manifest.xml file

<dbfullsql path="sqlserver_script.sql" />
<dbmysql path="mysql_script.sql" />

“path” attribute is used to set the path to the Sql scripts associated with the directive. You may add multiple dbfullsql/dbmysql directives within manifest file if you wish to use more than one Sql script for given database type.

Adding SQL Server and MySQL parameters in Parameters.xml file

You need to create two sets of parameters one for SQL Server database and another for MySQL if they depend on the database type.

Important Note: All the parameter names must be unique.

Here is an example for Database Server parameter .

<!-- Prompt the user for the database admin username. -->

<!-- Displayed if MySQL database selected -->
<parameter name="MySQL Database Server"
description="Enter Hostname, server name or IP. This is usually localhost”" defaultValue="localhost"
tags="MySQL,DbServer" >
</parameter>

<!-- Displayed if SQL Server database selected -->
<parameter name="MSSQL Database Server"
description="Database server name"
defaultValue=".\sqlexpress"
tags="SQL, DbServer" >
</parameter>

Similarly you would need to create MySQL and SQL parameters each for Database username, Database user password , Database administrator , Database administrator password .

If user’s choice is MySQL database during installation, the parameter "MSSQL Database Server" will be skipped and will not be visible to the user.

This allows you to set different default values, descriptions based on database type which makes it user-friendly. This feature also allows you to hide/show custom parameters specific to the application.

For example :

If your application uses a Database type parameter in the application configuration file , say configuration.php ; then we can parameter this in the parameters.xml as follows

<!-- SQL Server Custom parameter for Dtabase type --> <parameter defaultValue="public $dbtype = 'sqlsrv';" name="Database Type SQL" tags="Hidden, SQL,NoStore" > <parameterEntry match="public[\s]*\$dbtype[\s]*=[\s]*'([^']*)';" scope="configuration.php" type="TextFile"/> </parameter> <!-- MySQL Custom parameter for Database type --> <parameter defaultValue="public $dbtype = 'mysql';" name="Database Type MySQL" tags="Hidden, MySQL,NoStore" > <parameterEntry match="public[\s]*\$dbtype[\s]*=[\s]*'([^']*)';" scope="configuration.php" type="TextFile"/> </parameter>

Connection Strings

Two connection string parameters must be included one for SQL server database parameters and one for MySQL database parameters

<!-- SQL Server Connection String --> <parameter defaultValue="Server={Database Server SQL};Database={Database NameSQL};uid={Database Administrator SQL};Pwd={Database Administrator Password SQL};" description="Automatically sets the connection string for the connection request." name="ConnectionStringSQL" tags="Hidden,SQLConnectionString,SQL"> <parameterEntry match="sqlserver_script.sql" scope="dbfullsql" type="ProviderPath"/> </parameter> <!-- MySQL Connection String --> <parameter defaultValue="Server={Database Server MySQL};Database={Database Name MySQL};uid={Database Administrator MySQL};Pwd={Database Administrator Password MySQL};" description="Automatically sets the connection string for the connection request." name="ConnectionStringMySQL" tags="Hidden,MySQLConnectionString,MySQL"> <parameterEntry match="mysql_script.sql" scope="dbfullsql" type="ProviderPath"/> </parameter>