Query Across DBMS Boundaries (Linked Database Servers?)

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

Re: Query Across DBMS Boundaries (Linked Database Servers?)

Wow! I just found the Federated Storage Engine.
http://dev.mysql.com/tech-resources/articles/mysql-federated -storage.html

It's not in our SHOW ENGINES though.

I still welcome comments.

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

Re: Query Across DBMS Boundaries (Linked Database Servers?)

At 14:12 21/03/2008, you wrote:
>Message-ID:
>Date: Thu, 20 Mar 2008 11:05:22 -0500
>From: "Dee Ayy"
>To: php-db@lists.php.net
>MIME-Version: 1.0
>Content-Type: text/plain; charset=ISO-8859-1
>Content-Transfer-Encoding: 7bit
>Subject: Re: Query Across DBMS Boundaries (Linked Database Servers?)
>
>Wow! I just found the Federated Storage Engine.
>http://dev.mysql.com/tech-resources/articles/mysql-federate d-storage.html
>
>It's not in our SHOW ENGINES though.
>
>I still welcome comments.


You need MySQL MaxDB version (at least on windows), which is
precompiled using --with-federated-storage-engine
That article doesn't state it, and I think I had to stumble across
this in the Federated engine discussion forum @ MySQL

It's a very useful storage type, particularly if you have a lot of
records with recent records (eg last 1 month) online, and a bunch
compressed or as ARCHIVE tables on a near-line storage server for
your older data (eg older than 1 month)

HTH
Cheers - Neil



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

Re: Query Across DBMS Boundaries (Linked Database Servers?)

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.h tml) 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