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 NCDevCon 2011 (Raleigh, NC) with: Mike Kingery and Jaana Gilbert

Running ColdFusion Queries Inside CFSript Tags

By Ben Nadel on
Tags: ColdFusion, SQL

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:

  • <cfquery name="qGirlfriends" datasource="kinky_solutions">
  • SELECT
  • id,
  • name,
  • hotness
  • FROM
  • girlfriend
  • ORDER BY
  • hotness DESC
  • </cfquery>

Easy right? Now, let's look at how that is done inside ColdFusion CFScript tags:

  • <cfscript>
  • // First, we need to get access to the ColdFusion service
  • // factory. This is a Java object that we can instantiate.
  • objFactory = CreateObject(
  • "java",
  • "coldfusion.server.ServiceFactory"
  • );
  •  
  • // Get the Data Source service from the service factory.
  • objDataService = objFactory.DataSourceService;
  •  
  • // The data service object has access to all data sources
  • // running on the server. Let's get a connection to our
  • // datasource before running the query.
  • objDataSource = objDataService.GetDataSource(
  • "kinky_solutions"
  • );
  •  
  • // Open the connection. Here, we have the option to pass
  • // in a username and password. Since I am on the dev
  • // server, no need to do so.
  • objConnection = objDataSource.GetConnection(
  • // USERNAME, PASSWORD if needed //
  • );
  •  
  • // Prepare the SQL statement that you want to run. Much
  • // harder than the CFQuery tag, but not impossible.
  • objStatement = objConnection.PrepareStatement(
  • "SELECT " &
  • "id, " &
  • "name, " &
  • "hotness " &
  • "FROM " &
  • "girlfriend " &
  • "ORDER BY " &
  • "hotness DESC"
  • );
  •  
  • // NOTE: I break up the SQL above to emulate the look and
  • // feel of a CFQuery tag. This would be the SAME EXACT as
  • // just doing:
  • // SELECT id, name, hotness FROM girlfriend ORDER BY hotness DESC
  •  
  •  
  • // Execute the prepared SQL statement. This line alone returns
  • // the java class: "macromedia.jdbc.base.BaseResultSet". In order
  • // to work with this most effectively, we have to turn it into
  • // the Java class "coldfusion.sql.QueryTable". This is the class
  • // of object that is returned from the CFQuery tag, and the type
  • // of object that we are all used to working with.
  • objResults = CreateObject(
  • "java",
  • "coldfusion.sql.QueryTable"
  • ).Init( objStatement.ExecuteQuery() );
  •  
  • // Close the connection.
  • objConnection.Close();
  •  
  • </cfscript>

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.



Reader Comments

Is there a way to pass datasource object from coldfusion to java? I don't want to manage database connection info in two places.

thanks

Reply to this Comment

Su,

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

Reply to this Comment

Thanks Ben,

Actually I was making a silly mistake. I was able to pass the connection object to my java program using your examples.

Thanks again
su

Reply to this Comment

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.

Cheers,
Matt

Reply to this Comment

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.

Regards,

Joop

Reply to this Comment

@Joop,

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.

Reply to this Comment

@Ben,

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.

Regards,

Joop

Reply to this Comment

The be exact, the longer sample does close the connect with the statement:

objConnection.Close();

So it works OK. But again,using a <CFQUERY> is must easier.
Regards,

Joop

Reply to this Comment

this to cool to use.
i just want to know is this beneficial for performance. i know internally in some new releases CF can change the java classes and it will become a big change for us.

CF does not recommend any java classes to use as they can be changed by any new releases according to their new development.

Reply to this Comment

This got a back-link today on a Blogger.com blog that's syndicated on feeds.adobe.com ... Here's the blog:

http://jeetualex.blogspot.com/2008/11/using-cfquery-inside-cfscript.html

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.

http://www.datafaucet.com of course for those who are interested in the tool -- I know you know about it already, Ben. :)

And for comparison, here's how it would look in DataFaucet in a cfscript block:

query = ds.getSelect("id,name,hotness","girlfriend").setValue("orderby","hotness desc").execute();

Or if you prefer on CF8 you can use the dynamic methods:

query = ds.query_id_name_hotness_from_girlfirend_order_by_hotness_desc();

Reply to this Comment

@Ike,

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.

Reply to this Comment

@DeepDown,

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.

Anyway, just a random thought. Glad this helped you in some way.

Reply to this Comment

A few years late but cfquery has been supported in script tags since coldfusion 7 at the latest. Where I work we still stuck using coldfusion 7.

The format is

<cfscript>
SQL_STRING = "SELECT " &
"id, " &
"name, " &
"hotness " &
"FROM " &
"girlfriend " &
"ORDER BY " &
"hotness DESC"

qGirlfriends = CFQUERY(SQL_STRING,"kinky_solutions");

</cfscript>

PS
Long time lurker, wanted to thank you for a great blog. I have learned a lot from reading blogs like your in the past 2 years since I got my first job working in coldfusion.

Reply to this Comment

Blah ignore my last post. Completely forgot that actually a user made function and not part of ColdFusion language. That what I get for trying to think after work.

Reply to this Comment

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?

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.