| Adding MSSQL support to a MySQL/Postgres script [message #170077] |
Fri, 08 October 2010 10:34  |
DiamondEagle
Messages: 11
Registered: October 2010
Karma: 0
|
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.
|
|
|
|
| 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: 0
|
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
==================
|
|
|
|
| Re: Adding MSSQL support to a MySQL/Postgres script [message #170123 is a reply to message #170081] |
Mon, 11 October 2010 11:06 |
DiamondEagle
Messages: 11
Registered: October 2010
Karma: 0
|
Junior Member |
|
|
On Oct 8, 11:34 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> 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.
Thanks. My problem seems to be that $tables is empty. When I debug
using print_r($tables) it shows that $tables is empty.
I'm "certain" that the problem is the line in the code below where
$tablename gets defined i.e
$tablename = trim($table['table_name']);
When I run the code with some added debug lines I see a few "failed
empty check" messages so the if(!empty($tablename)) check is saying
that $tablename is empty.
case 'mssql_n':
$query = mssql_query("select * from information_schema.tables");
if (mssql_num_rows($query) > 0) {
while ($table = mssql_fetch_assoc($query)) {
$tablename = trim($table['table_name']);
if (!empty($tablename)) {
echo "passed empty check<br />";
$tables[] = $tablename;
}
else {
echo "failed empty check<br />";
}
}
}
break;
And I don't know why!
|
|
|
|
| Re: Adding MSSQL support to a MySQL/Postgres script [message #170128 is a reply to message #170123] |
Mon, 11 October 2010 12:25 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/11/2010 7:06 AM, DiamondEagle wrote:
> On Oct 8, 11:34 pm, Jerry Stuckle<jstuck...@attglobal.net> wrote:
>
>> 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.
>
> Thanks. My problem seems to be that $tables is empty. When I debug
> using print_r($tables) it shows that $tables is empty.
>
> I'm "certain" that the problem is the line in the code below where
> $tablename gets defined i.e
> $tablename = trim($table['table_name']);
>
> When I run the code with some added debug lines I see a few "failed
> empty check" messages so the if(!empty($tablename)) check is saying
> that $tablename is empty.
>
> case 'mssql_n':
> $query = mssql_query("select * from information_schema.tables");
> if (mssql_num_rows($query)> 0) {
> while ($table = mssql_fetch_assoc($query)) {
> $tablename = trim($table['table_name']);
> if (!empty($tablename)) {
> echo "passed empty check<br />";
> $tables[] = $tablename;
> }
> else {
> echo "failed empty check<br />";
> }
> }
> }
> break;
>
> And I don't know why!
So, what do you actually have in $table?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Adding MSSQL support to a MySQL/Postgres script [message #170132 is a reply to message #170128] |
Mon, 11 October 2010 21:39 |
DiamondEagle
Messages: 11
Registered: October 2010
Karma: 0
|
Junior Member |
|
|
On Oct 11, 2:25 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
> So, what do you actually have in $table?
>
Thanks Jerry.
The only reference to $table is in the "while ($table =
mssql_fetch_assoc($query))" loop in the case statement. I think I
fixed the script by copying the Postgres SQL into the MSSQL case and
modifying it to use the MSSQL functions. The switch statement in
mergeuser_get_userid_columns() function now has:
case 'mssql_n':
$query = mssql_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 (mssql_num_rows($query) > 0) {
while ($column = mssql_fetch_assoc($query)) {
if (!empty($column['column_name'])) {
$columns[] = trim($column['column_name']);
}
}
}
break;
And the switch statement in mergeuser_get_tables() function has:
case 'mssql_n':
$query = mssql_query("SELECT * FROM information_schema.tables");
if (mssql_num_rows($query) > 0) {
while ($table = mssql_fetch_assoc($query)) {
$tablename = trim($table['TABLE_NAME']);
if (!empty($tablename)) {
$tables[] = $tablename;
}
}
}
break;
It seems to be working now. Thanks.
|
|
|
|
| Re: Adding MSSQL support to a MySQL/Postgres script [message #170133 is a reply to message #170132] |
Mon, 11 October 2010 22:31 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 10/11/2010 5:39 PM, DiamondEagle wrote:
> On Oct 11, 2:25 pm, Jerry Stuckle<jstuck...@attglobal.net> wrote:
>>
>> So, what do you actually have in $table?
>>
>
> Thanks Jerry.
>
> The only reference to $table is in the "while ($table =
> mssql_fetch_assoc($query))" loop in the case statement. I think I
> fixed the script by copying the Postgres SQL into the MSSQL case and
> modifying it to use the MSSQL functions. The switch statement in
> mergeuser_get_userid_columns() function now has:
>
> case 'mssql_n':
> $query = mssql_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 (mssql_num_rows($query)> 0) {
> while ($column = mssql_fetch_assoc($query)) {
> if (!empty($column['column_name'])) {
> $columns[] = trim($column['column_name']);
> }
> }
> }
> break;
>
>
> And the switch statement in mergeuser_get_tables() function has:
>
> case 'mssql_n':
> $query = mssql_query("SELECT * FROM information_schema.tables");
> if (mssql_num_rows($query)> 0) {
> while ($table = mssql_fetch_assoc($query)) {
> $tablename = trim($table['TABLE_NAME']);
> if (!empty($tablename)) {
> $tables[] = $tablename;
> }
> }
> }
> break;
>
> It seems to be working now. Thanks.
Glad to hear it's working. But an easier way to handle this would be to
use inheritance - define an abstract class 'Database' and derive your
other classes from it. Then just use the appropriate class - function
names would be the same in all classes.
Much simpler and easier to maintain and troubleshoot.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Adding MSSQL support to a MySQL/Postgres script [message #170139 is a reply to message #170133] |
Tue, 12 October 2010 12:27 |
DiamondEagle
Messages: 11
Registered: October 2010
Karma: 0
|
Junior Member |
|
|
On Oct 12, 12:31 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
> Glad to hear it's working. But an easier way to handle this would be to
> use inheritance - define an abstract class 'Database' and derive your
> other classes from it. Then just use the appropriate class - function
> names would be the same in all classes.
>
> Much simpler and easier to maintain and troubleshoot.
>
Thanks Jerry. You've been a great help. Anything to make it simpler
and better sounds like a good idea, so I'll certainly try to apply
your advice (even though I've never done any PHP programming before!)
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]