Hi, great, its working now :) (using mysql 5 with The FEDERATED Storage Engine) to access a local and a remote database within one query. http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html
I've just installed - MySql 5.0.18 on my xp-machine (+ the latest phpMyAdmin-version + mysql-administrator) - then my datasources didn't verfy using the built-in (mx7) odbc-driver MySql (3.x) - so I downloaded and installed the MySQL Connector J JDBC driver (3.1.7) from ftp://mirror.services.wisc.edu/mirrors/mysql/Downloads/Connector-J/mysql-connector-java-3.1.7.zip (notes from mm/adobe creating a datasource: http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=6ef0253 and thanks ben & comments: http://www.forta.com/blog/index.cfm?mode=entry&entry=A61BD155-3048-80A9-EF63F5397C4511AA) - then I saw executing 'show engines;' in the mySql-console that the federated engine was not supported. - to get it running I stopped and uninstalled the running mysql-service (mysqld --remove) - and re-installed the service to support the federated engine with: mysqld-max --install (thx to user-comment in the first url at the top) - to test it I created a sql-dump of a database and imported it on a linux-machine in my network running mySql 3.x and imported it local on my xp-machine running mySql5. - in my local database I deleted a table products and then created it new again with a sql-command (and exact fields, keys .. like on the remote machine) just at the end of the command added: ENGINE = FEDERATED CONNECTION = 'mysql://user:[EMAIL PROTECTED]:3306/databasename/products'; Now when I view the data of my products-table locally, mysql retrieves the data from the remote-machine. great :-) Two things I've to worry about is now: what if the remote db is down (argh, don't know yet, how I'll handle, maybe just try/catch..) the second is, when my app is ready to get released on production server, I'll need mysql5, the connector and the federated storage engine support) anyway, I really like that.. -- Sebastian Mork [EMAIL PROTECTED] -- On Tue, 7 Feb 2006 15:00:11 +0000 Michael Traher <[EMAIL PROTECTED]> wrote: > could be a candidate for doing in two or three steps and using a query of > queries to combine the data. > > Just a thought > Mike T > > > On 2/7/06, Sebastian Mork <[EMAIL PROTECTED]> wrote: > > > > found something in the mySql-forums.. > > http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html > > (The FEDERATED Storage Engine since mySql 5.0.3) > > -- > > Sebastian Mork > > [EMAIL PROTECTED] > > -- > > > > On Mon, 06 Feb 2006 23:49:35 +0100 > > Sebastian Mork <[EMAIL PROTECTED]> wrote: > > > > > ok, my mistake.. > > > > > > so this would work, if both databases would be on the same server. > > > So I could at first use the dataSource-name of my odbc-conn in the > > cfquery tag and get access to another database directly by > > > its database name (dbname.tablename.fieldname) > > > > > > e.g. this would work if both were local: > > > > > > cfquery name="test" datasource="myDataSourceName" > > > select > > > tbl_products.fld_pid as pid > > > ,myLocalMysqlDataBaseName.tbl_pdata.fld_teaser as teaser > > > from > > > tbl_products > > > left join > > > myLocalMysqlDataBaseName.tbl_pdata on > > myLocalMysqlDataBaseName.tbl_pdata.fld_pid=tbl_products.fld_pid > > > where 1=1 > > > /cfquery > > > > > > But is it possible to get access to an additional database through its > > datasource-name (because its on a remote server)? > > > -- > > > Sebastian Mork > > > [EMAIL PROTECTED] > > > -- > > > > > > On Mon, 06 Feb 2006 22:23:55 +0100 > > > Sebastian Mork <[EMAIL PROTECTED]> wrote: > > > > > > > Hi, > > > > > > > > is it in general possible (using mysql 4.1.10) in any way to get data > > > > from two datasources (combined in one query-tag)??? > > > > > > > > Asking google showed me some infos about ms sql (combining data from > > several sources.. > > http://www.databasejournal.com/features/mssql/article.php/3578546) > > > > > > > > Hmm... well.. I thought of just doing something simple like this.. > > > > select > > > > #strApp.localDsn#.tbl_products.fld_pid as pid > > > > from > > > > #strApp.localDsn#.tbl_products > > > > left join > > > > #strApp.RemoteDsn#.tbl_pdata on > > #strApp.RemoteDsn#.tbl_pdata.fld_pid=#strApp.localDsn#.tbl_products.pid > > > > where 1=1 > > > > > > > > But at first it (of course :) doesnt work, 2nd problem would be the > > dsnUser+Pass-args that can be set only > > > > once in the query-tag. (didn't look at queryparams yet) > > > > > > > > Anyway.. is that generally possibly? Is it possible with mysql? > > > > > > > > -- > > > > Sebastian Mork <[EMAIL PROTECTED]> > > > > > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231594 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
