Home »
Imported messages »
comp.lang.php »
Adding MSSQL support to a MySQL/Postgres script
| Adding MSSQL support to a MySQL/Postgres script [message #170077] |
Fri, 08 October 2010 10:34  |
DiamondEagle
Messages: 11
Registered: October 2010
Karma:
|
Junior Member |
|
|
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.
|
|
|
|
Current Time: Tue Nov 26 21:24:49 GMT 2024
Total time taken to generate the page: 0.04239 seconds
Calendar
Search
Help
Members
Register
Login
Home
]