In discussing a possible caching solution at work a suggestion was made to use a secondary database (on the same server) to store some key application data. It’s not really important to describe why we wanted to do this, suffice to say that my concern was if and when the application would need to perform a SQL “JOIN” between the primary and secondary (caching) database as I wasn’t sure if it was technically possible within our environment (ColdFusion 8, MySQL).
I quickly mocked up a proof of concept and was pleased to see that this works fine with ColdFusion and MySQL. It looks as though <cfquery> simply wraps up your SQL and sends it to the database server for execution (as you’d probably expect), so this is really the ability of the database server rather than any specific ColdFusion engine.
The only thing you need to do is make sure that you explicitly qualify the references to your column and table names with the actual database name and very importantly make sure the database user (as defined in your cfquery datasource attribute) has access to both databases! Remember that you’re using a single ColdFusion datasource to talk to 2 different databases.
Here’s a code sample qualifying just the database and column names:
<cfquery name="qGetSomething" datasource="myDatasource"> SELECT mydb1.Column1, mydb1.Column2, mydb2.Column3 FROM mydb1.Table1 t1 INNER JOIN mydb2.Table2 t2 ON t1.id = t2.id </cfquery>
Here’s a code sample qualifying the database, table and column names:
<cfquery name="qGetSomething" datasource="myDatasource"> SELECT mydb1.Table1.Column1, mydb1.Table1.Column2, mydb2.Table2.Column3 FROM mydb1.Table1 t1 INNER JOIN mydb2.Table2 t2 ON t1.id = t2.id </cfquery>
If you get an error like the following back, you know you have a database user permission error 
SELECT command denied to user 'mydbuser'@'localhost' for table 'Table2'
5 Comments
First… you entered the code twice, you should remove the duplicate.
Second is the questions…
Do you have performance ratings?
Can you insert and update like this? (rather limiting otherwise)
Has this been tested on MSSQL or Oracle or Derby?
*** How did you set up a single data source to connect to two databases???
We had done this as well as a proof of concept having been addressed a situation similarly.
Never went past the point of MySQL to see if it would work on other platforms.
@John
>First… you entered the code twice, you should remove the duplicate.
Hmm, the 2nd code block is slightly different as it qualifies the table name in the SELECT statement.
>Do you have performance ratings?
Nothing at this stage but I would be curious to run some tests to get a general idea as to how good or bad this may be in heavy usage. For us this was really a proof of concept so I haven’t investigated much further than actually getting it to work. Of course referential integrity is out the window, but for most users of MySQL this won’t be a problem as the MyISAM engine doesn’t allow for that anyway.
>Can you insert and update like this? (rather limiting otherwise)
Whilst I didn’t specifically test for this I can’t see it working, just like you can’t INSERT or UPDATE columns across multiple tables.
>Has this been tested on MSSQL or Oracle or Derby?
Not yet, as I said we’re at POC stage.
>*** How did you set up a single data source to connect to two databases???
Ok, when in CF Admin create your datasource which of course just talks to a single database which you specify. The important thing here is the database user you in the “username” and “password” fields of the datasource. Let’s say you used the following:
u: mydbusername
p: mydbpassword
This will allow ColdFusion to open a connection to the database using those credentials. Now you of course need to make sure that those user details are in place on the database server for the database specified in the ColdFusion datasource, but you also attach another database to that user (in the database user manager).
This means your single db user has access to multiple databases, so when ColdFusion sends the SQL to the database server for execution server it is successfully run because the user in your initial datasource has access to both databases.
Make sense?
@John
Pretty sure this doesn’t work in MSSQL, I did try many years ago and failed.
In terms of a datasource connecting to a different database, when you specify the database to it doesn’t stop the database being changed with a USE statement.
http://dev.mysql.com/doc/refman/5.0/en/use.html
This is a MySQL feature that is relatively unknown as CF makes it so easy to connect to db’s. It is also another reason to be careful about SQL Injection attacks, as it’s not just the current database that can be accessed.
If the following SQL was injected into your application it could give away a lot of information:
USE information_schema;
SELECT * FROM tables;
I think it’s a great feature that MySQL enables, but you just need to be aware of it so that you can secure against it being misused.
just a couple of notes, i’m using MySQL 5.1 and CF8.01 hotfix3, and you can do this quite simply with aliases as normal without needed to explicitly qualify dbs in column references. You didnt need to qualify tables in the default database for the datasource either.
e.g. if there was the default DB of users, and a seperate DB accountsDB:
SELECT u.userName, a.account
FROM userDetails u INNER JOIN accountsDB.accountDetails a ON u.userid = a.userID
WHERE u.userid = 1;
Also, this approach definitely works in MSSQL (or did work when i last used it 3 years ago) we had large telecommunications databases to report across, the only difference in approach is that you need to add a schema,
e.g. in the above scenario if the default dbo schema
SELECT u.userName, a.account
FROM userDetails u INNER JOIN accountsDB.dbo.accountDetails a ON u.userid = a.userID
WHERE u.userid = 1;
you could also omit the default schema from memory but the period marker is required, so you could do:
SELECT u.userName, a.account
FROM userDetails u INNER JOIN accountsDB..accountDetails a ON u.userid = a.userID
WHERE u.userid = 1;
additionally if you configured linked servers inside MSSQL administrator you could take it a level higher to communicate between multiple SQL Servers with an additional servername prefix to the database name:
SELECT u.userName, a.account
FROM userDetails u INNER JOIN differentServer.accountsDB.dbo.accountDetails a ON u.userid = a.userID
WHERE u.userid = 1;
so the absolute reference to the table was of the format: server.database.schema.table