SQL Query status!!!!

[micjohnson]

Hi there!


I am using ColdFusion with SQL Server 7.0. Is there any way to check the status of the query after its execution?(I used in DB2 like SQLCODE = 0 for successful execution,...etc)

Help me!!!


Thanx in advance,
micjohnson

 

[strantheman]

Well there are a few checkup options, im not sure what 'status' you want to know about the query, but if you don't get any errors then it ran successfully.

If you want to see the SQL code at the bottom of your template, use "DEBUG" in your query tag.

<CFQUERY name=&quot;x&quot; datasource=&quot;#ds#&quot; DEBUG>
</CFQUERY>

If you want to see how many records were returned, or rather, that there were at least 1:

<CFIF x.recordcount gt 0>
do some stuff with the data
<CFELSE>
I have no data to do stuff with
</CFIF>

 

[philhege]

Enclose your query in a CFTRY/CFCATCH set, mic.
CFCATCH will return error codes in the SQLSTATE variable;
you can evaluate it to determine the success/failure of the
operation.

I use the code below to circumvent the dreaded &quot;Timeout Expired&quot; error. This loop tries until the query works.

You evaluate the SQLSTATE variable in the CFCATCH section.

<Cfset success = &quot;no&quot;>

<cfloop condition=&quot;success is 'no'&quot;>
<Cftry>

<cfquery name=&quot;getdata&quot; datasource=&quot;mydatasource&quot;>
(Your select statement here)
</cfquery>
<cfset success = &quot;yes&quot;>
<cfcatch type=&quot;Database&quot;>

<cfoutput>Error returned was #CFCATCH.SQLSTATE#</cfoutput>
<cfset success = &quot;no&quot;>

</cfcatch>
</CFTRY>

</cfloop>

HTH,
PH