<cfsilence>

More cf_query - Now With cf_queryparam

Posted At : November 8, 2007 10:24 AM | Posted By : todd sharp
Related Categories: ColdFusion

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.

Comments (3) |

Print |

Send |

Download |

Code |

del.icio.us |

Linking Blogs | 1330 Views

Comments

[Add Comment]

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

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

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 | 11/8/07 3:23 PM

[Add Comment]