Remote Synthesis
Search my blog:
Viewing By Entry / Main
Dec 02, 2005

Queryparam Issue and Arf

I have been testing out Arf and it is a great tool. I plan to cover my experiences in more detail soon, but I ran into an issue that isn't specific to Arf but came up while using it having to do with cfqueryparam and prepared statements.I am running two seemingly identical queries:

<cfquery name="qry" datasource="dsn">    SELECT   adminLocale.*    FROM   admin          JOIN adminLocale ON admin.adminId = adminLocale.adminId    WHERE   adminLocale.adminId = <cfqueryparam cfsqltype="cf_sql_varchar" value="E322A790-D56F-E112-4B7D2D018B975819">
</cfquery>
<cfdump var="#qry#">
<cfquery name="qry2" datasource="dsn">    SELECT             adminLocale.*          FROM             admin                JOIN adminLocale                ON admin.adminId                = adminLocale.adminId          WHERE                adminLocale.adminId                                   = <cfqueryparam cfsqltype="cf_sql_varchar" value="E322A790-D56F-E112-4B7D2D018B975819">
</cfquery>
<cfdump var="#qry2#">
<cfabort>

(note: what I am querying isn't an issue as this is just testing) The first query is the query Arf was using for one of my components with white space cleaned up, the other is copied exactly how Arf wrote the query (whitespace and all). However, the first query returns this column list: ADMINID,ADMINLOCALEID,ISDEFAULT,SUPPORTEDLOCALEID and the second returns ADMINID,ADMINLOCALEID,ISDEFAULT,LOCALEID. Notice the last column is different. The first one is returning the proper column (that I recently changed) and the second is not. I believe this is because of the use of the asterisk (rather than actual column names) and the prepared statement allows SQL (I believe or is it on the CF side - see below?) to use cached results (which, in this case miss the db change).

Obviously even a simple change to the whitespace in the query can resolve this and force the query to be re-prepared. However, the query is created dynamically by Arf in this case, so I don't have access to it. After reading this post by Ray Camden, I was able to force the query to be re-prepared by setting the Max Pooled Statements to 0 for my datasource (and then setting it back after re-running the page). I could not find a better way to accomplish this. Anyone got any ideas in case it comes up again?

Comments
Sean Corfield
Doesn't Arf! have an option to force it to reload the database metadata? Sort of dev mode vs prod mode?


Brian Rinaldi
It does and it was set to dev mode, but in this case the query would not change. It would still use the select * even after the metadata reloads. Therefore, it still appears to be the exact same query to the cache and would still load from cache.


Sean Corfield
Interesting. Good to know that dev mode doesn't resolve this. Good to know the Max Pooled workaround too. Thanx!


Write your comment



(it will not be displayed)