Home »
Imported messages »
comp.lang.php »
Adding MSSQL support to a MySQL/Postgres script
| Re: Adding MSSQL support to a MySQL/Postgres script [message #170081 is a reply to message #170077] |
Fri, 08 October 2010 21:34   |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 10/8/2010 6:34 AM, DiamondEagle wrote:
> I have a php script that currently supports querying MySQL and
> Postgres databases and I'm trying to add support for MSSQL databases
> (via FreeTDS). The script caters for the different databases using a
> switch statement for the above two database engines that it currently
> supports. Below is the disaster that I've created in trying to add
> MSSQL support.
>
> The library contains two functions, mergeuser_update_field() and
> mergeuser_get_userid_columns() and it is used in a Merge Duplicate
> Users function in the application. Both functions have a switch
> statement that currently only caters for MySQL and Postgres and I've
> added a 'mssql_n' case for SQL Server suppport. The code in the
> 'mysql' and 'postgres7' cases is working on those platforms. In the
> 'mssql_n' case statement is my attempt at translating the MySQL/
> Postgres code into something that MSSQL can handle, but when I run it
> I see smoke bubbling out of the computer and the duplicates records
> are not merged.
>
> $tablename is created in mergeuser_get_tables() and then passed to
> mergeuser_get_userid_columns().
>
>
> function mergeuser_get_tables() {
> global $CFG;
>
> $tables = array();
>
> switch ($CFG->dbtype) {
> case 'mysql':
> $query = mysql_query("SHOW TABLES");
> if (mysql_num_rows($query)> 0) {
> while ($table = mysql_fetch_assoc($query)) {
> $tablename = trim($table['Tables_in_'.$CFG-
>> dbname]);
> if (!empty($tablename)) {
> $tables[] = $tablename;
> }
> }
> }
> break;
>
> case 'postgres7':
> $query = pg_query("SELECT * FROM information_schema.tables
> WHERE table_schema='public' AND table_type='BASE TABLE'");
> if (pg_num_rows($query)> 0) {
> while ($table = pg_fetch_assoc($query)) {
> $tablename = trim($table['table_name']);
> if (!empty($tablename)) {
> $tables[] = $tablename;
> }
> }
> }
> break;
>
> // For MSSQL I've also added this 'mssql_n' case:
> case 'mssql_n':
> $query = mssql_query("select table_name from
> information_schema.tables");
> if (mssql_num_rows($query)> 0) {
> while ($table = mssql_fetch_assoc($query)) {
> $tablename = trim($table['Tables_in_'.$CFG-
>> dbname]);
> if (!empty($tablename)) {
> $tables[] = $table;
> }
> }
> }
> break;
> }
>
> return $tables;
> }
>
>
> function mergeuser_get_userid_columns($tablename) {
> global $CFG;
>
> $columns = array();
>
> switch ($CFG->dbtype) {
> case 'mysql':
> $query = mysql_query("SHOW COLUMNS FROM $tablename LIKE
> 'userid'");
> if (mysql_num_rows($query)> 0) {
> $assoc = mysql_fetch_assoc($query);
> $columns[] = trim($assoc['Field']);
> }
> $query = mysql_query("SHOW COLUMNS FROM $tablename LIKE
> 'user_id'");
> if (mysql_num_rows($query)> 0) {
> $assoc = mysql_fetch_assoc($query);
> $columns[] = trim($assoc['Field']);
> }
> $query = mysql_query("SHOW COLUMNS FROM $tablename LIKE
> 'id_user'");
> if (mysql_num_rows($query)> 0) {
> $assoc = mysql_fetch_assoc($query);
> $columns[] = trim($assoc['Field']);
> }
>
> break;
>
> case 'postgres7':
> $query = pg_query("
> SELECT column_name
> FROM information_schema.columns
> WHERE table_name = '$tablename'
> AND ( (column_name LIKE 'userid')
> OR (column_name LIKE 'user_id')
> OR (column_name LIKE 'id_user')
> )
> ");
> if (pg_num_rows($query)> 0) {
> while ($column = pg_fetch_assoc($query)) {
> if (!empty($column['column_name'])) {
> $columns[] = trim($column['column_name']);
> }
> }
> }
> break;
>
> // To add MSSQL support I've added this 'mssql_n' case:
> case 'mssql_n':
> $query = "select * from information_schema.columns where
> table_name = '$tablename'";
> $query = mssql_query($query);
> if (mssql_num_rows($query)> 0) {
> $assoc = mssql_fetch_assoc($query);
> $columns[] = trim($assoc['Field']);
> }
> }
> return $columns;
> }
>
>
> When I run it against a MSSQL 2005 database PHP throws the following
> error:
>
> Notice: Undefined index: Tables_in_myDatabaseName in C:\test
> \locallib.php on line 224
>
> The $tablename = trim($table['Tables_in_'.$CFG->dbname]); statement
> seems to be the problem. The 'Tables_in_' in the $tablename =
> trim($table['Tables_in_'.$CFG->dbname]); line is probably not valid
> MSSQL syntax.
>
> Can anybody help me with the MySQL/Postgres to MSSQL translation?
>
> Thanks.
>
> PS 'mssql_n' is the name of the internal database driver that the
> application uses to query MSSQL databases.
I would suggest you start with
echo "<pre>\n";
print_r($tables);
echo "</pre>\n";
And take a look at the keys in your array.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
Current Time: Thu Sep 19 23:33:45 GMT 2024
Total time taken to generate the page: 0.04659 seconds
Calendar
Search
Help
Members
Register
Login
Home
]