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></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: <mailto:[EMAIL PROTECTED]> [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" < <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]> To: < <mailto:[email protected]> [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/ >> <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 > <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 <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 http://www.fusionlink.com -------------------------------------------------------------
