2

I'm having a problem with a recent database crash. After recovering the data, I now get some odd parse errors, but it isn't clear if it is in the data, or if it something else. See the attached image, but it is from a form submit that cfupdate is storing data to table.

There is an input button called "next" in the form and that content gets saved to the DB.

So I have the following question:

  1. Is there a way to see what the SQL Query is and where that error occurred?
  2. Any clues or hints to why I'm getting this error?
  3. Any way to see more information that might help? Debug mode?

This is running on ColdFusion MX7 with MS Access Database.

The error:

Error Executing Database Query.

Syntax error: Stopped parse at NEXT

The error occurred in \\******\scholardetails2_en.cfm: line 19
17 : <!--- if coming from page1, save data to database and renew session.txtEmail --->
18 : <cfif IsDefined("Form.txtFirstName")>
19 :    <cfupdate dataSource="****" tableName="tblApplications">
20 :    <!--- <cfset Session.txtEmail=Form.txtEmail> --->
21 :    <!--- <cfset Session.language=Form.language> --->
SQL   update tblApplications set TXTDOB= (param 1) 
,TXTPFIRSTNAME= (param 2) ,TXTPOB= (param 3) 
,TXTFLYNAME= (param 4) ,TXTOFFNAME= (param 5) 
,TXTOFFCITY= (param 6) ,TXTFIRSTNAME= (param 7) 
,TXTPROVINCE= (param 8) ,TXTOFFPCODE= (param 9) 
,TXTGRANTS= (param 10) ,TXTEMPID= (param 11) 
,RDOGENDER= (param 12) ,CBOCOUNTRY= (param 13) 
,TXTSTADR2= (param 14) ,TXTPFLYNAME= (param 15) 
,TXTTELNO= (param 16) ,LANGUAGE= (param 17) 
,TXTOFFSTREET2= (param 18) ,TXTOFFSTREET3= (param 19) 
,TXTPOSITION= (param 20) ,TXTCITY= (param 21) 
,CBOOFFCOUNTRY= (param 22) ,TXTSTADR1= (param 23) 
,TXTPMIDNAME= (param 24) ,NEXT= (param 25) 
,TXTDEPT= (param 26) ,TXTPCODE= (param 27) 
,TXTPPHONE= (param 28) ,TXTMIDNAME= (param 29) 
,TXTOFFSTREET1= (param 30) ,CBOCOC= (param 31) ,TXTOFFPROVINCE= (param 32) 
,TXTSTADR3= (param 33) ,TXTPEMAIL= (param 34) 

where txtEmail= (param 35)
DATASOURCE    ****
VENDORERRORCODE   172032
SQLSTATE      2A000
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
user3525618
  • 235
  • 1
  • 2
  • 6
  • 3
    I do not use Access or CFUpdate, but at a guess `next` might be a reserved word. If so, rename the column to avoid the issue. That said, one of the downsides of using `cfupdate` is exactly what you discovered - it is difficult to debug. I would strongly recommend ditching `cfupdate` and using a standard `update` cfquery instead. – Leigh Apr 12 '14 at 01:22
  • check the values of param 24 as well as param 25. See does the columns for those values allow NULL. What data type are they.
    Update someTable set x=param1, y=param2 where Somecol=someVal. Now this can throw error probably if param1 is blank, expecting numeric and is blank. If the error is reproducible at will, I will suggest putting whole sql statement in without the cfupdate tag.     – CFML_Developer Apr 12 '14 at 04:18
  • Access & `cfupdate`? wow....just...wow – Scott Stroz Apr 12 '14 at 05:41
  • Scott: This was done by a previous programmer, so I have just extended what is there. CFML_Developer: What is odd is that these errors weren't there before the database corruption. I am wondering if the corruption occurred, because something in the software changed i.e. MS Access drivers or something. Maybe that is what caused the corruption in the first place. Is there a function call to get detailed information about the CF server akin to phpinfo()? – user3525618 Apr 12 '14 at 09:58
  • A function like ? However, if you want to see what is happening with the driver, you'll have to see what it is on the admin page. – Dan Bracuk Apr 12 '14 at 13:44
  • *a recent database crash* My tired brain overlooked that part. My bad. If you are absolutely certain it worked previously, and did not change anything, like switching drivers, etcetera, then reserved words may not be the issue. That said, my recommendation about ditching `cfupdate` stands. Converting it to a standard update query would would be better for several reasons, easier debugging certainly. If you have tried the other options, with no change, that would be a good next step. – Leigh Apr 12 '14 at 21:02

1 Answers1

3

My personal recommendation would be to replace that cfupdate statement with comparable cfquery. Any error information you get is going to be shrouded by the architecture of the cfupdate tag.

If you really feel married to the original code, you could add a formfields attribute to the cfupdate tag and omit the "next" field from the list of columns to update. 

<cfupdate datasource="***" tableName="tblApplications" 
formFields="txtpfirstname,txtpmidname,txtplastname,txtdob,txtpob,..." />

My first guess would be that the content of the "next" form field is too long for that table column, but if you remove it from the list of updated columns, that will tell you where the error is actually occurring. If after removing the "next" column, you get an error on "txtdept" then you'll know that the error is actually occurring on "txtpmidname" (and my guess would still be the value is too long).

Dan is correct that you can find information about the server (version number, etc) in the server scope. A Railo or BlueDragon server will have an extra structure, but like Dan mentioned, that won't tell you anything about the database drivers. That information is in the ColdFusion Administrator if it's anywhere, but imo it's unlikely that seeing the driver versions will shed any light on the problem. All of this brings me back to my original suggestion: replace the cfupdate tag with a cfquery - that's the fastest, simplest solution.

Samuel Dealey
  • 263
  • 1
  • 7
  • I appreciate everyone helping out. I narrowed down the problem to the word "next" and "previous" as part of the database field and form field names as a problem. It is kind of odd, the code worked fine for years with no changes, and all of a sudden database corruption, followed by cleanup and then this odd error. My suspicion is the corruption and subsequent bug was due to a software change at the hosting company. Of course they won't tell you the real reason, because of liability. A BIG THANKS TO EVERYONE FOR HELPING ME OUT!!! =D – user3525618 Apr 13 '14 at 17:37
  • (Edit) "Part of" or used as the *entire* column name? I could see the latter being a reserved word/driver issue, which could be affected by software updates. The former sounds similar to [an old cfinsert/cfupdate issue](http://helpx.adobe.com/coldfusion/kb/coldfusion-mx-6-1-hot-4.html). That one was supposedly fixed back in MX 6, but if it is an issue with "partial names", cfupdate could still be partly to blame. Since cfinsert/cfupdate are a bit of a black art to begin with, it is probably a good replace it with a standard update query. – Leigh Apr 14 '14 at 19:14
  • I would have expected that the `cfupdate` tag would escape all the column names, so you wouldn't run into reserved word issues, but I suppose it's possible that an update to the CF Server with possible new JDBC drivers might have impacted (read broken) that. – Samuel Dealey Apr 14 '14 at 19:52
  • 1
    That would be my expectation as well, but key words can vary by driver. (I seem to remember from the Adobe forums the Access unicode driver has a few that the standard driver does not). Plus given that it is cfupdate, whose internal behavior is largely undocumented, and MS Access to boot (unsupported), I would not rule it out as a possibility ;-) – Leigh Apr 15 '14 at 02:39
  • 1
    I recalled Access still being supported as late as CF8, although I admit I haven't kept track of it (no need) and I could be misremembering. I kind of had the impression that Adobe dropping support for Access would be one of those ongoing urban legends, much like the semi-annual "ColdFusion is dead" debate. By my count, ColdFusion has "died" something like 12 times, all while its user-base continued to grow. ;P – Samuel Dealey Apr 15 '14 at 04:39
  • *I admit I haven't kept track of it (no need) and I could be misremembering.* That makes two of us. I tend to set my expectations on "low | expect the unexpected" when it comes to Access and things like cfupdate/insert ;-) – Leigh Apr 15 '14 at 16:44