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.diagnosticscontains "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.diagnosticscontains
"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.diagnosticscontains
"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.diagnosticscontains
"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] 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></font></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]>
> 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 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 http://www.fusionlink.com
-------------------------------------------------------------
