Running ColdFusion Queries Inside CFSript Tags

The ease in which it is possible to run queries in ColdFusion is hands down one of the biggest selling points of the ColdFusion language. Anyone who has ever tried to prepare a complex SQL statement using string concatenation should find the ColdFusion CFQuery tag a freakin' GOD-SEND! If you use ColdFusion, feel free to laugh at those people... those silly, silly people.

As easy as the CFQuery tag makes running queries, it is much harder to run queries inside a CFScript tag. Inside a CFScript tag, you cannot use ColdFusion tags directly and hence, no CFQuery tag. However, it is possible to execute SQL queries without the CFQuery tag. It's not easy though, but for those of you is ASP and PHP who are used to doing so much work, you might be right at home.

The trick to executing queries inside CFScript tags is the ColdFusion ServiceFactory. This Java object was not technically supported in earlier versions of ColdFusion, but I believe with ColdFusion MX 7, the ServiceFactory object is totally acceptable. Among many services, the ColdFusion ServiceFactory object provides a Data Source service. While it is not pretty, all queries can be run through this service.

Let's take a look at a small example in which I query a girlfriend table. At first, let's do this with the ColdFusion CFQuery tag so you can see how easy it is:

As you can see, this is NOT an easy way to run queries. What is one CFML tag becomes many lines of CFScript code. However, if you are ever in a pinch and you for some reason NEED to run a query without CFQuery, know that at least it is possible. If possible though, I would suggest putting your query inside a CFC method call if you need in CFScript tags (but that's another topic covered by much smarter people than myself).

You can combine that whole thing into what is "technically" one line of code if you want. In the following example, I am putting in line returns for "easier" reading, but it would work just the same if you put it all on one physical line:

<cfscript>

objResults2 = CreateObject(

"java",

"coldfusion.sql.QueryTable"

).Init(

CreateObject(

"java",

"coldfusion.server.ServiceFactory"

).DataSourceService.GetDataSource(

"kinky_solutions"

).GetConnection().PrepareStatement(

"SELECT id, name, hotness " &

"FROM girlfriend ORDER BY hotness DESC"

).ExecuteQuery()

);

</cfscript>

So, it's possible to run queries inside CFScript tags. This is a demo to show that it is possible. I am in no way recommending it. Please Don't! CFQuery tags are freakin' sweet. Use them please.

I am not exactly sure what you mean. You can always pass data from ColdFusion to Java when calling methods. If you are talking about maintaining DSN variables in CF and in Java, you might want to look into having a configuration file (such as an XML file or an INI file) that both CF and Java would load. That way, CF and Java use the same DSN but it is only maintained in one place.

Additionally, if you want to pass around queries from CF to Java, you might try looking at: http://www.bennadel.com/index.cfm?dax=blog:151.view

I think the real power of this technique is being able to pass that connection object to a java class constructor which could then just rely on the java.sql.Connection interface to run its methods. That way you can have java components using your persistent coldfusion connection object and they're completely decoupled from any specific connection and or database type.

Interesting code. You might want to mention that the one liner does not close the connection so the application can eventually run out of connections. And luckily you can manipulate a query that is created with CFQUERY, in CFSCRIPT. So there should be no need to use this code to run queries.

I am not sure if regular CFquery tags close the connection either? In my CF Admin, I often have the "maintain connections" checkbox clicked. I believe that this maintains the connections across page requests.

Coldfusion does connection pooling so it would reuse a connection for other queries. Whereas the sample does not reuse the connections, it keeps opening new ones until the underlying datasource runs out, as I found out by trial and error.

As far as I know, the serviceFactory is still undocumented, so it still carries the same caveats. Boy do I know it actually, because I've been struggling the past 2 days with an upgrade for DataFaucet to deal with the fact that it's still (years later) a frustrating challenge to get db metadata with ColdFusion.

I was doing it with information_schema and in some cases proprietary techniques years ago on ColdFusion 5 and then about the time 6.1 was released, I updated the tools to use JDBC because it's much more consistent and reliable than what's available via information_schema. And then in CF8 Adobe gave hosting providers the ability to disable access to the serviceFactory ... which *should* have been okay because they also provided cfdbinfo to give people access to that info...

The problem I'm having is that when they added cfdbinfo they radically changed the format of the returned data, and *omitted* some critical info that I actually still need. So now I'm kind of back to square 1 with, not just 2 different "standards" for getting the metadata, but now 3 different standards, and trying to merge them seamlessly to create a single API interface to them... UGH! Sometimes being a forerunner is a real challenge.

I've only just used the DBInfo stuff for the first time. I am curious as to what information ColdFusion is excluding that is making your life difficult? I have used it to script a database export from MySQL to ACCESS that will dynamically drop and re-create the tables.

No problem my man. It's funny, if you look at CF9, the way that CFQuery is supported in CFScript, it actually just creates a Query component, that utilizes CFQuery - it doesn't fool around with this kind of stuff.

Since putting together strings for a query has been addressed here, this is probably the best place to ask this question. From a processing-time standpoint, is it better, say, to use cfif's and put them in your query within cfquery, or would it be better to put together an sql string and then use it within cfquery? If you did it the last way, I could see you getting away with putting in fewer cfif's, so I would think the processing time would be improved, but I could be wrong. Alternatively, would it be better, from a processing-time standpoint, to just write 3 different queries (or whatever), or would it be better (processing-time wise) to use conditionals and one or less queries?

To put the question in perspective: say you have a report. This report needs 1. data from different groups. and then, 2. Data from different groups by year to date, and finally, 3. by month. So, technically, that is 6 data needs if, say, you have 3 groups. For example, let's say you have 3 sports you are tracking data for: soccer, football, and basketball. You want both YTD data for each, and monthly data for each. So you, you would have 6 different data needs: soccer - YTD, soccer - by month, football - YTD, football - by month, basketball - YTD, basketball - per month. And sorry to those who aren't American if I offended you, I am referring to American football and American soccer.

So, for the example above, which of the 3 approaches above that would be better? 1. Breaking the query up into string and piecing it together by string (lessening the amount of conditional cfif logic), 2. Just doing one (or fewer) queries, and putting more conditional logic in there, or 3. more queries...a query for just about every different set of data you need. Or is there even another way I haven't thought of or discussed here?