The only thing I'd like to throw into the mix, is that doing a
dump/abort can be problematic; I tend to write temporary tracking issues
(primarily for debugging) to the log files e.g.
<cfsavecontent variable="dumpLog">
<cfoutput>
<cfdump var="#yourVarHere#">
</cfoutput>
</cfsavecontent>
<cflog log="application" text="#dumpLog#">
This works nice for otherwise silent failures - like instantiating
components inside (nested) try/catch blocks.
Darin Kohles, Application Developer
[EMAIL PROTECTED]
Digital Positions, Inc.
2289 Peachtree Road NE
Atlanta, GA 30309
404-351-8878 support phone
404-351-2366 main phone
404-351-4055 fax
http://www.d-p.com/
________________________________
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fennell,
Mark P.
Sent: Tuesday, June 19, 2007 7:34 AM
To: [email protected]
Subject: RE: [ACFUG Discuss] Catching SQL Errors
Yes. Validation would help 9 times out of 10. Unfortunately, there's 10
years of code in this application and I'm the only developer. As for
maintenance, this file hardly ever changes. Users tend to make the same
mistakes over and over and over. :(
But, yes. At some point, I'll go back and rewrite the bad code I wrote
years ago.
Perhaps my errors will help someone else learn the value of validation.
No, I didn't want this to be a learning experience, I just work here. :)
Thanks.
mf
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
Douglas Knudsen
Sent: Monday, June 18, 2007 10:18 PM
To: [email protected]
Subject: Re: [ACFUG Discuss] Catching SQL Errors
WOW, that code looks like a maintenance nightmare. At one time
long ago in a land far far away called Oracle 7 I started to do
something like this and gave up quickly. Can you not use CF to validate
this data? Besides the usual validations, isDate(), isNumeric() etc...,
cfqueryparam can be used as another layer of validation. I too have a
error template that sticks all the error variables into a DB including
the stack trace, which can be quite helpful.
DK
On 6/18/07, Fennell, Mark P. <[EMAIL PROTECTED]> wrote:
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.
mfuld
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]
<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]
<mailto:[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 FusionLink <http://www.fusionlink.com>
-------------------------------------------------------------
--
Douglas Knudsen
http://www.cubicleman.com
this is my signature, like it?
-------------------------------------------------------------
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 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
-------------------------------------------------------------
