Output entire sql statement that gave database error 1

[aarontra]

In ColdFusion I like the way you can dynamicly create queries to the database.
But how do I display the entire sql statement that causes a database error?

Of course we are using MS Access.
Aaron

 

[Sarky78]

the easiest way is to turn your debugging on from the coldfusion administrator. you simply specify what you want to see and who you want to see it (IP address) and submit. refresh your page and at the bottom you have all of the variables that were used along with all of the database queries that were executed against your database and the time taken

if you don't have access to the administrator then you could do this:

<CFSET query = &quot;SELECT * FROM a where ID=1&quot;>

<CFQUERY datasource=&quot;yourdatasource&quot; name=&quot;whatever&quot;>
#query#
</cfquery>

<cfoutput>
This is the query that was executed:<BR>
#query#
</cfoutput>

hope this helps!

Tony

 

[csteinhilber]

You can also use CFTRY/CFCATCH... which will do two things for you; 1) it will wrap the debug message(s) up so that if no error is thrown, you won't have to see any extraneous output, and 2) it will expose several new CFCATCH properties that do exactly what you want.

This is almost directly from the CF documentation on Exception Handling... but I use it all the time:

<CFTRY>
   <CFQUERY name=&quot;myquery&quot; datasource=&quot;mydsn&quot;>
      SELECT foobar from MYTABLE
   </CFQUERY>
<CFCATCH TYPE=&quot;database&quot;>
   <CFOUTPUT>
   <P><B>DATABASE ERROR</B><BR />
   <UL>
   <LI><B>Message:</B> #CFCATCH.Message#
   <LI><B>Native error code:</B> #CFCATCH.NativeErrorCode#
   <LI><B>SQLState:</B> #CFCATCH.SQLState#
   <LI><B>Detail:</B> #CFCATCH.Detail#
   </UL>
   </CFOUTPUT>
</CFCATCH>
</CFTRY>

The CFCATCH.NativeErrorCode and CFCATCH.SQLState properties may or may not contain anything depending on what database type and driver you're using. But CFCATCH.Detail will almost always contain, amoung other things, the SQL statement that threw the error.


Other little known debugging methods are:
to turn debugging on for an individual page, add &quot;mode=debug&quot; to the end of the URL (ie - mydomain.com/mydir/mypage.cfm?mode=debug).
to turn debugging on for an individual query, add the parameter &quot;DEBUG&quot; to the open CFQUERY tag (ie - <CFQUERY name=&quot;myquery&quot; datasource=&quot;mydsn&quot; DEBUG>)

Both of these work no matter whether debugging is turned on in CF admin or not (if not, you should be able to implicitly tell CF to display it by using <CFSETTING SHOWDEBUGOUTPUT=&quot;Yes&quot;>)



Hope it helps,
-Carl

 

[aarontra]

Thank you Carl

Does not give the sql statement, but that is because of MS Access ...

The only way I was able to turn Debuging on was to append
&quot;?mode=debug&quot; onto the end of action of my form. (Without talking to the Admin.)

<FORM action=&quot;mypage.cfm?mode=debug&quot; METHOD=&quot;Post&quot;>
.
.
.
</form>

 

[csteinhilber]

That's two things I'm surprised about; MS Access doesn't report back the SQL statement in CFCATCH.detail, and CFQUERY DEBUG doesn't work for you.

Can't imagine why (on either of those).

Glad you were able to turn it on with the query string, at least.

One thing to watch... adding query string parameters to a FORM with method set to POST is not recommended. While it helps you out for testing, you'll want to be sure to remove it before going live... or switching your form to a GET, and adding a hidden input of name &quot;mode&quot; with a value of &quot;debug&quot; to your page.

 

[aarontra]

When in Debug mode, if there is no database error then it shows the Query (sql statement)

 

[CFDude]

Let me throw out another way -- create the sql statement outside of the cfquery as a variable and then stick it in the cfquery -- I do this all the time as a check before I do any database work and if it bombs, I have the whole query that can be output first -- then I can copy and paste the generated query into a separate sql program that I can work with.
e.g.

<cfset sqlQry = &quot;Select foo1, foo2 from bar where foo1 = '#form.somevar#'&quot;>

<cfoutput> Query: #sqlQry# </cfoutput>

<cfquery name=&quot;qryFoo&quot; datasource=&quot;bar&quot;>
#PreserveSingleQuotes(sqlQry)#
</cfquery>

(You need the preservesinglequotes function to ensure that it keeps the single quotes in the query)