<cfsilence>

DSN-Less CFQuery

Posted At : November 7, 2007 9:49 AM | Posted By : todd sharp
Related Categories: ColdFusion

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

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?

Comments (12) |

Print |

Send |

Download |

Code |

del.icio.us |

Linking Blogs | 2712 Views

Comments

[Add Comment]

This is pretty darn cool!

# Posted By Raymond Camden | 11/7/07 11:31 AM

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 | 11/7/07 12:08 PM

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 | 11/7/07 12:55 PM

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 | 11/7/07 1:25 PM

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 | 11/7/07 2:10 PM

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 | 11/7/07 2:18 PM

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 | 11/7/07 4:36 PM

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 | 11/7/07 4:57 PM

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 | 11/7/07 5:39 PM

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 | 11/8/07 10:35 AM

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 | 11/9/07 10:33 AM

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 | 11/9/07 3:10 PM

[Add Comment]