Re: [PHP-DB] Query Across DBMS Boundaries (Linked Database Servers?)

[Chris]

Dee Ayy wrote:

Is there something which can perform a query across different database systems?

It would extend the database.table.field notation to maybe
dbms.database.table.field and allow queries across MySQL and MSSQL


I don't think so.

Both mysql and mssql would need to know how to parse that syntax and 
make it all work (besides other problems like database permissions, 
different connection strings and so on).


Depending on how much data you are looking to move from one to the 
other, you could get mssql to export to a csv file (if it supports it - 
no idea) then get mysql to import it. Either use the csv storage engine 
(http://dev.mysql.com/doc/refman/4.1/en/csv-storage-engine.html) or load 
it into another table using 'load data infile' 
(http://dev.mysql.com/doc/refman/4.1/en/load-data.html).


If you need to do it on the fly, my only other suggestion would be to 
set up two database connections - one for mssql and one for mysql and 
aggregate the data in php.


--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Query Across DBMS Boundaries (Linked Database Servers?)

[Dee Ayy]

Is there something which can perform a query across different database systems?

It would extend the database.table.field notation to maybe
dbms.database.table.field and allow queries across MySQL and MSSQL
such as:
//$sql =
SELECT MySQLidentifier.aDatabase.aTable.aField,
MSSQLidentifier.anotherDatabase.anotherTable.anotherField
FROM MySQLidentifier.aDatabase.aTable,
MSSQLidentifier.anotherDatabase.anotherTable
WHERE MySQLidentifier.aDatabase.aTable.aKeyField =
MSSQLidentifier.anotherDatabase.anotherTable.anotherKeyField
//Or through JOINS, etc.

I've seen the linked server option in MSSQL.  Technically I can write
to MSSQL, but company policy prohibits it.  To steer clear of
ambiguity, personally I've decided that I will also not create
triggers (or any object) on MSSQL (which is a write in my mind).
Basically I will only read information from MSSQL.

I was thinking of PDO, which raises an interesting distinction of
data-access abstraction layer versus database abstraction (which as I
understand means no SQL rewrite or missing feature emulation -- SQL
rewrite example: TOP n prefix versus LIMIT n suffix).  The PDO
developers may be close to creating such a feature.
$MySQLidentifier = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$MSSQLidentifier = new PDO('mssql:host=localhost;dbname=test', $user, $pass);
$dbh = new PDO_LinkedServers($MySQLidentifier, $MSSQLidentifier);
$dbh-exec($sql);

Looking at MSSQL's OPENQUERY syntax, I don't even know if MSSQL
provides this extended query notation.  It just seems to allow a query
on a remote (or distributed) server.

Regards.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php