Thank you all for your suggestions.
I should've given more info up front. I was hoping to find a more global
solution. Something to stick in the request_error.cfm page rather than
trying to cftry/cfcatch each query. Thanks.
mf
FWIW, I'll share my error.cfm template here:
<!--- begin error template --->
<LINK rel = STYLESHEET href = "/ian/ianstyle.css" Type = "text/css">
<div align="center">
<img src="/ian/images/logo2.gif" border="0"><br><br>
<cfif parameterExists(cookie.iansec) eq "No" and #error.type# neq
"unknown">
<cfoutput>You must be logged in to use this feature.<br>
Please user your browser's back button or click <a
href="/ian/index.cfm?returnto=#replace(cgi.script_name,'/','~','ALL')#*#
Replace(cgi.query_string,'&','!','ALL')#">here</a> to
login.<br><br></cfoutput>
<cfset URL.returnto =
"#replace(cgi.script_name,'/','~','ALL')#*#Replace(cgi.query_string,'&',
'!','ALL')#">
<cfinclude template="/ian/login.cfm">
<cfelse>
An error has occurred.<br>
<cfoutput>
#error.datetime#<br><br><br>
<table border="0" width="357" cellspacing="0" cellpadding="3"
align="CENTER">
<tr>
<td valign="BOTTOM" colspan="2">
<br>
<div align="LEFT">
<cfif error.diagnostics contains "ORA-01847" or error.diagnostics
contains "is an invalid date or time string" or error.diagnostics
contains "ORA-01843" or error.diagnostics contains "ORA-01821" or
error.diagnostics contains "ORA-01839" or error.diagnostics contains
"ORA-01858" or error.diagnostics contains "ORA-01861">
<font style="font-weight: bold;">ERROR: Invalid Date
Format</font><br>
<font style="font-weight: bold;">FIX:</font> Use your browser's back
button and change the date format to dd-mmm-yyyy unless otherwise
specified on the form. <br><font style="font-weight:
bold;">Example:</font> 01-JAN-2003<br>
</cfif>
<cfif error.diagnostics contains "ORA-01850" or error.diagnostics
contains "ORA-01849">
<font style="font-weight: bold;">ERROR: Invalid Time
Format</font><br>
<font style="font-weight: bold;">FIX:</font> Use your browser's back
button and change the time format to HH24MM. <br><font
style="font-weight: bold;">Example:</font> 1700 or 0500<br>
</cfif>
<cfif error.diagnostics contains "ORA-01722">
<font style="font-weight: bold;">ERROR: Invalid Number
Format</font><br>
<font style="font-weight: bold;">FIX:</font> Use your browser's back
button and enter numbers without commas, dollar signs($) and percent
signs(%). <br><font style="font-weight: bold;">Example:</font>
1003.57<br>
</cfif>
<cfif error.diagnostics contains "ORA-00001">
<font style="font-weight: bold;">ERROR: Duplicate Entry</font><br>
<font style="font-weight: bold;">FIX:</font> The database reports
that this record already exists. Please check your entry against the
list of current entries before proceeding.<br>
</cfif>
<cfif error.diagnostics contains "ORA-20001">
<font style="font-weight: bold;">ERROR: Appraisal Closed</font><br>
<font style="font-weight: bold;">FIX:</font> The database reports
that this performance appraisal has been signed. Once an appraisal has
been signed, no changes may be applied.<br>
</cfif>
<cfif error.diagnostics contains "ORA-01704">
<font style="font-weight: bold;">ERROR: Text Field
Overflow</font><br>
<font style="font-weight: bold;">FIX:</font> Use your browser's back
button to reduce the number of characters in a text field.<br>
</cfif>
<cfif error.diagnostics contains "ORA-04031">
<font style="font-weight: bold;">ERROR: Known Bug</font><br>
<font style="font-weight: bold;">FIX:</font> The database has
reported an internal error. Please use your browser's BACK button and
perform the action again. This error is rare and does not typically
indicate a loss of data.<br>
</cfif>
<cfif error.diagnostics contains "ORA-08102">
<font style="font-weight: bold;">ERROR: Unique Record
Requirement</font><br>
<font style="font-weight: bold;">FIX:</font> The record that you are
attempting to insert or edit must be unique. For example, appointment
dates and times must be unique. Sometimes, several fields must be
unique. For example, patient name, date of birth, and appointment time
must be unique.<br>
</cfif>
<cfif error.diagnostics contains "ORA-08177">
<font style="font-weight: bold;">ERROR: Transaction
Serialization</font><br>
<font style="font-weight: bold;">FIX:</font>In order to ensure that
accurate data is recorded, each action on ian is issued an unique serial
number. This error usually is the result of too much clicking or
excessive page refreshing. Remember, you only have to click once in ian.
<br>
</cfif>
<cfif error.diagnostics contains "ORA-12541" or error.diagnostics
contains "ORA-02068" or error.diagnostics contains "ORA-03113">
<font style="font-weight: bold;">ERROR: Service Not
Available</font><br>
<font style="font-weight: bold;">FIX:</font> The page you requested
requires a service outside of ian, e.g. Tempus, that is currently
unavailable.<br>
</cfif>
<cfif error.diagnostics contains "undefined in FORM">
<font style="font-weight: bold;">ERROR: Missing Form
Information</font><br>
<font style="font-weight: bold;">FIX:</font> Use your browser's back
button to complete the form by filling in all form fields.<br>
</cfif>
<cfif error.diagnostics contains "Variable SUBMIT is undefined.">
<font style="font-weight: bold;">ERROR: Use Submit Button</font><br>
<font style="font-weight: bold;">FIX:</font> Some forms require you
to use the submit button rather than just pressing the Enter key to
submit the information. The form you attempted to submit is one such
form.<br>
</cfif>
<cfif error.Diagnostics contains "Element USERID is undefined in
COOKIE">
<strong>ERROR: Your browser has lost track of your user
cookie.</strong><BR>
<strong>FIX: </strong> Please close all instances of Internet
Explorer, and log back in to Ian.<BR>
</cfif>
<cfif error.Diagnostics contains "attribute TO is invalid">
<strong>ERROR: The recipient of your email/request does not have a
valid email address in Ian. </strong><BR>
<strong>FIX: </strong> Please send an email or imail to [EMAIL PROTECTED]
with the person's name and email address and we'll add them in ASAP.<BR>
</cfif>
</div>
</td>
</tr>
<tr>
<td valign="TOP"><font style="color: FFFFFF;">URL: </font></td>
<td valign="BOTTOM"><font style="color: FFFFFF;">#error.template#
</font></td>
</tr>
<tr>
<td valign="TOP"><font style="color: FFFFFF;">Diag: </font></td>
<td valign="BOTTOM"><font style="color:
FFFFFF;">#replaceNoCase(error.diagnostics,'<hr>','','ALL')#<br><br></fon
t></td>
</tr>
</table>
<!--- <cflog log="APPLICATION" type="Error" date="yes" time="yes"
application="yes" text="USER ERROR: #cookie.ianname# ::
#error.diagnostics# ::
#replace(cgi.script_name,'/','~','ALL')#*#Replace(cgi.query_string,'&','
!','ALL')#"> --->
<cfquery name="qErrorLog" datasource="armc_linux">
insert into armcweb.errmsg values
(
'#cookie.ianname# (#cookie.deptno#)',
'#cgi.server_name#:#cgi.script_name#*#cgi.query_string#',
'#error.diagnostics#',
systimestamp,
'#cgi.http_referer#'
)
</cfquery>
</cfoutput>
</cfif>
<cfoutput><BR><BR>#cgi.server_name#</cfoutput>
</div>
<CFABORT>
<!--- end error template --->
________________________________
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ajas
Mohammed
Sent: Monday, June 18, 2007 2:06 PM
To: [email protected]
Subject: Re: [ACFUG Discuss] Catching SQL Errors
I dont know if this is what you want but if you want to view the sql
statement that is causing the error, then the best option is <cfdump var
= "#error#">. I usually mail this info to my email adrr and it really
helps me to understand what is going on with the query.
But if you want to show information to the user, I am not sure how that
can be achieved.
Thanks,
Ajas.
On 6/18/07, Teddy R Payne <[EMAIL PROTECTED]> wrote:
If you are also looking to trap the error in CF, <cfcatch
type="database">
is generic to DB related issues.
If you are trying to cater to a particular error code, you will
have to
parse the cfcatch structure.
cfcatch has a key for specific database features:
cfcatch.sql
cfcatch.queryError
The more generic keys are:
cfcatch.message
cfcatch.detail
hth,
T
----- Original Message -----
From: "Cameron Childress" < [EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]> >
To: <[email protected]>
Sent: Monday, June 18, 2007 1:50 PM
Subject: Re: [ACFUG Discuss] Catching SQL Errors
>I think that you're going to find what you want in a
CFTRY/CFCATCH
> combo. You can drop TRY/CATCH blocks around your SQL
operations and
> catch different types of errors, handling them in different
ways. In a
> development environment, try the following and see if it gives
what
> you need:
>
> <cftry>
>
> <cfquery>bad SQL goes here</cfquery>
>
> <cfcatch>
> <cfdump var="#cfcatch#"/>
> <cfabort>
> </cfcatch>
>
> </cftry>
>
> you can do whatever you want in that catch block, including
logging
> the error, the SQL statement, or emailing things to yourself.
>
> -Cameron
>
> On 6/18/07, Fennell, Mark P. <[EMAIL PROTECTED]> wrote:
>>
>>
>> Does anyone know of a simple way to catch the SQL command
which causes an
>> error?
>> For example, I have request_error.cfm that records the
error.diagnostics
>> and
>> the error.template as well as decodes most errors to let the
user know
>> why
>> they've been bad. But, I'd like to be able to see the
statement that
>> generates the error especially if it's a "inserted value too
large for
>> column" or something else that would indicate user-induced
problems.
>> Thanks.
>> mf
>>
>>
>> ps. Red Hat Linux Advanced Server release 2.1AS, CFMX
7.0.0.91690, Oracle
>> 9.2.0.4
>>
>> mark fennell
>> athens regional medical center
>> athens, ga
>>
>>
>>
>>
------------------------------------------------------------------------
--------------------------------------
>> This email is intended only for the named recipient(s). It
may contain
>> information that is proprietary, confidential or otherwise
prohibited
>> from
>> disclosure. If you are not the named addressee, you are not
authorized
>> to
>> read, print, retain, copy or disseminate this message or any
part of it.
>> If
>> you have received this message in error, please reply
immediately by
>> email
>> or telephone me at 706-475-4357 and delete all copies of the
message.
>>
------------------------------------------------------------------------
--------------------------------------
>> -------------------------------------------------------------
>> Annual Sponsor - Figleaf Software
>>
>> To unsubscribe from this list, manage your profile @
>> http://www.acfug.org?fa=login.edituserform
>>
>> For more info, see http://www.acfug.org/mailinglists
>> Archive @
>> http://www.mail-archive.com/discussion%40acfug.org/
>> List hosted by FusionLink
>> -------------------------------------------------------------
>
>
> --
> Cameron Childress
> Sumo Consulting Inc
> http://www.sumoc.com
> ---
> cell: 678.637.5072
> aim: cameroncf
> email: [EMAIL PROTECTED]
>
>
> -------------------------------------------------------------
> Annual Sponsor FigLeaf Software - http://www.figleaf.com
>
> To unsubscribe from this list, manage your profile @
> http://www.acfug.org?fa=login.edituserform
>
> For more info, see http://www.acfug.org/mailinglists
> Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> List hosted by http://www.fusionlink.com
> -------------------------------------------------------------
>
>
>
-------------------------------------------------------------
Annual Sponsor FigLeaf Software - http://www.figleaf.com
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
--
<Ajas Mohammed />
http://ajashadi.blogspot.com
No matter what, find a way. Because thats what winners do.
-------------------------------------------------------------
Annual Sponsor - Figleaf Software <http://www.figleaf.com>
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink <http://www.fusionlink.com>
-------------------------------------------------------------
-------------------------------------------------------------
Annual Sponsor FigLeaf Software - http://www.figleaf.com
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
