More cf_query - Now With cf_queryparam

Posted By : todd sharp Posted At : November 8, 2007 9:24 AM Posted In: ColdFusion

3

I decided to play around a bit more with my cf_query custom tag (see this post if you missed it). Basically this tag lets you run queries against a database without setting up a DSN in CF Administrator. A few folks commented on that post and helped me figure out that a driver class needs to be specified for certain drivers in order for the query to work properly (MySQL and Oracle being a few that didn't work without it) so I've added an attribute called driverClass that will accept and set the proper value. In addition I've added another tag called cf_queryparam to the mix that will mimic cfqueryparam pretty much identically to allow you to use bound parameters in your query. There is a huge performance hit in doing this - and I have no idea why. Hopefully someone will pick up on something, but for some reason the query jumps to about 25 seconds when using the cf_queryparam tag!! Again this is all just in fun so if no one has any ideas I'm not going to be heart broken.

Here's a new look at how you'd use the tag:

<cf_query driverClass="macromedia.jdbc.MacromediaDriver" jdbcURL="jdbc:macromedia:sqlserver://server:port;databaseName=db;" username="user" password="pass" qName="test">
select *
from tbl
where something = <cf_queryparam value="theValue" cfsqltype="cf_sql_varchar" />
and otherstuff = <cf_queryparam value="otherValue" cfsqltype="cf_sql_varchar" />
</cf_query>

Updated zip is attached. I've added some temporary trace points to the tag(s) so you can see where the bottleneck is. If you pass parameters without using the cf_queryparam tag you'll see the performance drastically improved. Anyone have a clue here?

Oh one other thing - I tried adding support for nulls in the cf_queryparam and that seems to bomb too. It's commented out for now - but again - if anyone has a clue let me know.

2010 Views Download Send Print Comments (3)

Comments (3)

Subscribe to Comments Add Comment

Phillip Senn's Gravatar The ColdFusion Muse inspired me with this:
<cfprocparam dbvarname="@ID" cfsqltype="cf_sql_varchar" maxlength="36" value="#arguments.ID#" null="#YesNoFormat(NOT Len(arguments.ID))#">

Posted by: Phillip Senn on: 11/8/07 2:16 PM

todd sharp's Gravatar Oh it's not that that's goofing up Phillip. It's the underlying Java method for setNull() that is bombing out when I try to call it.

Posted by: todd sharp on: 11/8/07 2:49 PM

Qasim Rasheed's Gravatar Todd,

I cannot get this to work with Oracle with a very simple query. It just keep giving me an error the "The selected method setString was not found".

Neverthless if you want to see a better performance with cfqueryparam, I believe you should be using PreparedStatement Object and not Statement.

Thanks

Posted by: Qasim Rasheed on: 11/8/07 3:23 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.