Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: Matthew Bourke

ColdFusion CFQuery Tag To Pull From Two Data Sources (a.k.a. Faking It With Bad Programming)

By Ben Nadel on

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:

  1. Probably does not work in all (or even many) other database management systems (I test in MS SQL Server 2000).
  2. It is slower than a standard query as we have to SEND much more data to the SQL server.
  3. 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.
  4. 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.



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

Reply to this Comment

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?

Reply to this Comment

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 ;)

Reply to this Comment

Todd,

Nice post. Yes, great minds do think alike :)

As far as the math.... come on man, you gotta work for it :)

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.