DSN-Less CFQuery

Posted By : todd sharp Posted At : November 7, 2007 8:49 AM Posted In: ColdFusion

12

I was thinking about a problem yesterday and one of the crazy thoughts that ran through my head was "man would it be cool to be able to run a query against a database without having to configure a DSN". This solution is not shady in any way as it still requires you to have the proper credentials but it would be helpful at times when you need the ability to have a "throwaway" query. I suppose there are other potential uses for it - but I really just wanted to see if I could do it. Turns out it was very easy. I wrapped it in a quick custom tag so that all you need to do is something like this:

<cf_query jdbcURL="jdbc:macromedia:sqlserver://server:port;databaseName=db;" username="whoRu" password="hehateme" qName="test">
select *
from theTable
where something = 'foo'
and otherstuff = 'poo'
</cf_query>

<cfdump var="#test#">

Now you may be asking yourself well what the heck is a jdbcURL and how can I tell what I'd use here. Simple (but it takes a bit of undocumentedness):

<cfset factory = createObject("java", "coldfusion.server.ServiceFactory") />
<cfset dSourceService = factory.dataSourceService />
<cfdump var="#dSourceService.getDrivers()#" />

This will give you a list of all JDBC drivers on your machine along with a sample jdbcURL. Nice!

I've included a zip here if you'd like to play with it. I could go further with it and add support for query parameters but I'm not sure if it's worth it. Thoughts?

3103 Views Download Send Print Comments (12)

Comments (12)

Subscribe to Comments Add Comment

Raymond Camden's Gravatar This is pretty darn cool!

Posted by: Raymond Camden on: 11/7/07 11:31 AM

Daniel D's Gravatar Does it still escape single quotes in variables correctly. Can you still use cfqueryparam? Cool idea though. Should be suggested for CF 9.

Posted by: Daniel D on: 11/7/07 12:08 PM

todd sharp's Gravatar I use preserveSingleQuotes on the query yes. CFQueryparam would fail because it must be nested within a cfquery. I could create a custom tag for this if enough people thought it'd be worth it (heck I may do it just for fun). Suggestion has already been made to Adobe to include this option in a future release :)

Posted by: todd sharp on: 11/7/07 12:55 PM

Matt Williams's Gravatar Neat stuff Todd. I got the SQL Server to work, but couldn't get the mySQL to work.

Like you, I'm not sure what the use case would be for this, but cool anyway.

Posted by: Matt Williams on: 11/7/07 1:25 PM

todd sharp's Gravatar Haven't tried it myself yet on MySQL yet (will try to get to that tonight). Curious - are you on CF7 or 8?

Posted by: todd sharp on: 11/7/07 2:10 PM

Matt Williams's Gravatar CF 8, mySQL 5
Here's my cf_query call:

cf_query jdbcURL="jdbc:mysql://127.0.0.1:3306/myDBName" username="root" password="myPass" qName="test"

Posted by: Matt Williams on: 11/7/07 2:18 PM

James Netherton's Gravatar Good stuff Todd!

Matt, to get MySQL working I had to add:

<cfset class = createObject("java", "java.lang.Class") />
<cfset class.forName("com.mysql.jdbc.Driver")/>

Before the call to the cf_query tag.

Posted by: James Netherton on: 11/7/07 4:36 PM

Matt Williams's Gravatar Cool James. This worked for me also. This abbreviated version works also:
<cfset createObject("java", "java.lang.Class").forName("com.mysql.jdbc.Driver")/>

Interesting that it is just setting something. Must be something that is needed by one of the other java classes.

Posted by: Matt Williams on: 11/7/07 4:57 PM

Qasim Rasheed's Gravatar Nice Todd although I had to do something similar to what James proposed to make it work with Oracle. So may be you might want to add another optional attribute "driverClass".

Matt,

Here is a brief explanation to class.forName magic

http://cephas.net/blog/2005/07/31/java-classfornam...

Thanks

Posted by: Qasim Rasheed on: 11/7/07 5:39 PM

todd sharp's Gravatar Hey guys - awesome feedback - thanks!! I've updated the tag - if any of you have any ideas please post them on this post:

http://cfsilence.com/blog/client/index.cfm/2007/11...

Posted by: todd sharp on: 11/8/07 10:35 AM

Phillip Senn's Gravatar Can I write a ColdFusion query that executes as a different SQL Server signon depending on my login?

Right now, everyone looks the same to SQL Server - webuser.

I'd like to keep the login security at the database level instead of the application level.
Not different schemas - just different SQL Server logins.

Posted by: Phillip Senn on: 11/9/07 10:33 AM

todd sharp's Gravatar You can actually do that already with cfquery Phillip. Specifying a username/password in the cfquery will override the Admin DSN setup.

Posted by: todd sharp on: 11/9/07 3:10 PM
Add Comment

  • Calendar

    << March 2010 >>
    Sun Mon Tue Wed Thu Fri Sat
     123456
    7 8 9 10 11 12 13
    14 15 16 17 18 19 20
    21 22 23 24 25 26 27
    28 29 30 31    

  • Subscribe

    • Enter your email address to subscribe to this blog.