ColdFusion CFQuery Tag To Pull From Two Data Sources (a.k.a. Faking It With Bad Programming)
Is it possible to get the cfquery tag to pull from two data sources? I know that I can do a query of query type query, but this doesn't quite accomplish what I need to do. Any suggestions?
I just want to start this off by saying I do NOT recommend doing this. The following is just an exploration of ideas.
As you already know, ColdFusion query of queries allows you to join queries taken from two different data sources. This is something that can't really be done within the CFQuery tag (without doing a query of queries). Or rather, it cannot be done (to the best of my knowledge) without employing some bad programming. And... I'm never one who is afraid to program badly, so let's give it a go.
As an experiment, let's say that we store file references in a database table "document". All entries in the document table should correspond to a physical file in the local directory. We can consider the database and the file system to be two different data sources, correct? After all, they can both return a query object. Let's say that you want to query for files from the database, but ONLY want to return files that exist in the local file system as well.
There are a number of ways that this can be done, but only one requires pulling from two data sources in a non-query of queries database call. To do this, we are going to call one data source, then use those results to create an in-memory table in the second data source. Then, we are going to join those tables together.
<!---
Query the file system for a list of files that should
sync to the database.
--->
<cfdirectory
action="LIST"
directory="#ExpandPath( './documents/' )#"
name="qFile"
/>
<!---
Query our file query to get only the directory items that
are FILES. Since we are doing this, might as well trim the
column list.
--->
<cfquery name="qFile" dbtype="query">
SELECT
name
FROM
qFile
WHERE
LOWER( type ) = 'file'
</cfquery>
<!---
ASSERT: We are done querying our first data source,
the file system.
--->
<!---
Query the database for files. We only want to get files
that also exist in the file system (ie. that also exist
in the directory query we just ran).
--->
<cfquery name="qDocument" datasource="...">
<!---
Define a table to hold the data from our first data
source (the names of physical files).
--->
DECLARE @file TABLE(
server_name VARCHAR( 100 ),
server_ext VARCHAR( 10 )
);
<!--- Insert files into temp file table. --->
<cfloop query="qFile">
INSERT INTO @file (
server_name,
server_ext
) VALUES (
<cfqueryparam
value="#ListFirst( qFile.name, '.' )#"
cfsqltype="CF_SQL_VARCHAR"
/>,
<cfqueryparam
value="#ListLast( qFile.name, '.' )#"
cfsqltype="CF_SQL_VARCHAR"
/>
);
</cfloop>
<!---
ASSERT: At this point, we have created a temporary,
in-memory table that has the essential data from our
first data source query. Now, we can use this temp
table to join to tables that exist in THIS data source.
--->
<!--- Query for files from the database. --->
SELECT
d.id
FROM
document d
INNER JOIN
@file f
ON
(
d.server_name = f.server_name
AND
d.server_ext = f.server_ext
)
</cfquery>
So there you have it. We are basically building the required data from the first data source in the second data source and treating the second data source as having all the required information. This is the way to fake calling two data sources in one CFQuery tag (without using ColdFusion query of queries).
Now, as I said, I would NOT recommend doing this. It is:
Probably does not work in all (or even many) other database management systems (I test in MS SQL Server 2000).
It is slower than a standard query as we have to SEND much more data to the SQL server.
It is more likely to crash when your in-memory table gets to big. On my machine, I wasn't able to insert more than 1050 rows into the temp table. Not sure if this is a time-out issue or a limitation of memory size or the database itself.
There is no way this can scale well.
Ok, but enough with the CONS. There is a PRO to doing it this way: SQL server is more powerful than the ColdFusion query of queries. I LOVE query of queries, but SQL Server can simply do more stuff. If you absolutely need to do some higher level computation (or even a simple one like TOP) this might be a solution you can use (if query of queries or manually altered queries have been ruled out).
DISCLAIMER: This was only an example. I am not saying you SHOULD do this for this scenario.
Want to use code from this post? Check out the license.
Reader Comments
I think the question may have been more suited to explaining about linked servers in SQL Server. I understand what your example shows (in fact, I posted an exact example of this a few months back), but I get the feeling the question was more geared towards an example like this:
select *
from database1.dbo.tblOne as a
inner join database2.dbo.tblTwo as b
on a.something = b.something
To do this database1 and database2 must be linked servers in SQL server.
There are also ways to link Oracle, etc servers to an SQL server and query via things like openquery (though I'm no expert).
Todd,
I appreciate your comment, but unfortunately I can only write about what I know and I don't know about linked SQL servers :)
Can you post a link to your example you mentioned, I would love to take a look at it. Did you have any problems with the limitations of in-memory table sizes?
Essentially I think we're using the same technique (great minds think alike)! But here's my post: http://cfsilence.com/blog/client/index.cfm/2006/7/14/My-QofQ-Trick
If I have some time, maybe I'll put up a post about linked servers sometime.
As far limitations, I don't like to use this technique with huge recordsets, so I've yet to come across any - but it wouldn't surprise me if that were true.
P.S. It's not fair to make me do math - I suck at math ;)
Todd,
Nice post. Yes, great minds do think alike :)
As far as the math.... come on man, you gotta work for it :)