We finally figured out a decent way to do this a few weeks ago, but
haven't had time to reply to this posting...
Tom: Thanks for the suggestion using cflog, unfortunately it was
unable to pull back the query.
Bart: We started doing it this way, but since we use cfqueryparam it
was a huge headache
Sam: We thought of this as well but since we don't control our servers
or our backups we couldn't rely on this system.
We ended up using a cfquery attribute called "result" which will give
you all of the generated sql (including anything wrapped in
cfqueryparam) in a structure, then we grabbed the sql out of the
structure and then looped through the sqlparameters to get the values.
It looks something like this....
<cffunction name="test" output="false" returntype="boolean"
access="public">
<cfquery name="test" datasource="sql" result="qryResult">
Insert into table (column1, column2) (<cfqueryparam value="1"
cfsqltype="cf_sql_int">,<cfqueryparam value="1"
cfsqltype="cf_sql_int">)
</cfquery>
<cfinvoke component = "logQuery" method="captureSQL">
<cfinvokeargument name="result" value="#qryResult#">
</cfinvoke>
</cffunction>
<cffunction name="logQuery" output="no" returntype="boolean"
access="public">
<cfif isDefined("Arguments.result.SQLParameters")>
<cfset newSql =
Replace(Arguments.result.sql,"?","'#trim(Arguments.result.SQLParameters[1])#'")>
<!--- Loop through the array to get the sql parameters
--->
<cfloop index="i" from="2"
to="#arraylen(Arguments.result.SQLParameters)#">
<cfset newSql =
Replace(newSql,"?","'#Arguments.result.SQLParameters[i]#'")>
</cfloop>
<cfelse>
<cfset newSql = Arguments.result.sql>
</cfif>
<cfset newSql = Replace(newSql,"''","NULL","all")>
<cfset newSql = Replace(newSql,"'NO'","0","all")>
<cfset newSql = Replace(newSql,"'YES'","1","all")>
<cfset newSql = Replace(newSql,"#chr(10)#","","all")>
<cfset newSql = Replace(newSql,"#chr(9)#","","all")>
<cfquery name="qrySQLLOG" datasource="#Application.dataSource#">
INSERT INTO LOG (LOG_SQL_TXT, LOG_DT)
VALUES('#newSql#',<cfqueryparam value="#now()#"
cfsqltype="cf_sql_timestamp">)
</cfquery>
</cffunction>
Thanks for everyones help and input.
-Ben
On Mar 25, 3:28 pm, Xen <[EMAIL PROTECTED]> wrote:
> Is there any way to log a cfquery statement? I need to do something
> like...
>
> <cfquery name="test" datasource="sql">
> Insert into table (columns) (values)
> </cfquery>
>
> Is it possible to log the query statement?
>
> Thanks for any help. I'm using cf7 and sql2000.
>
> -Ben
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CFCDev" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cfcdev?hl=en
-~----------~----~----~----~------~----~------~--~---
