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
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'