Stack Overflow in Insert query

[Tim_Mar]

Hello,

I'm adding just two fields to an existing form that then inserts the data in
to a table (as attached). I get a stack overflow error in the insert FRom CF
not JDBC (the insert statement gets cut off at about 75%) when executed either
as SQL or just text. I could split the data but I've never had to limit the
length of SQL in any DB/IDE. It there a limit and if so what are the best
proactices for solving this... or do I need a patch.

The error:

Error Occurred While Processing Request
Java bean field access exception.
An exception occurred while calling the get or the put function. FieldName is
Classes. Possible cause . The cause of this exception was that:
java.lang.StackOverflowError.

Thanks in advance.

Tim



<cfquery name="InsertMove" datasource="EmpChange">
Insert into TblMoveInfo

(LAST_nm,FIRST_nm,MI,Empl_Nbr,Type,DEPT_NBR,EFF_DT E,JOB_CLASS,CONDITION_DE
SC,LOCATION,SUPV_EMPL_NBR,SUPV_PHONE,NEW_DEPT_NBR, NEW_LOCATION,

NEW_SUPV_EMPL_NBR,NEW_SUPV_PHONE,COMMENTS,Updated_ DTE,Phone_Required,VM_Re
quired,Cell_Required,Computer_Required,

Network_Required,GW_Required,Officespace_Required, Requestor_NM,Requestor_E
mail)
Values
('#form.last_nm#','#form.first_nm#','#form.mi#','# form.Empl_Nbr#','Move','#form.
dept_nbr#',###dateformat(right(trim(form.eff_dte), len(form.eff_dte)-5))###,

'#form.job_class#','#form.condition_desc#','#form. location#','#form.Supv_e
mpl_nbr#','#form.SUPV_PHONE#','#form.NEW_DEPT_Nbr# ','#form.NEW_LOCATION#',

'#form.NEW_SUPV_NBR#','#form.NEW_SUPV_PHONE#','#fo rm.COMMENTS#',###datefor
mat(now())###,'#form.Phone_isRequired#','#form.VM_ isRequired#',

'#form.Cell_isRequired#','#form.Computer_isRequire d#','#form.Network_isReq
uired#','#form.GW_isRequired#','#form.Officespace# ','#Requestor_NM#','#Requestor
_Email#')
</cfquery>

[zoeski80]

Hi Tim

Have you tried outputting what the values of all the fileds are and viewing
the whole insert statement to make sure it is in the correct format.

Why have you got extra # marks around the dateFormat fields? I have found that
some databases need the dates to be in ODBCDateFormat rather the just
DateFormat to work properly.
Also might want to check that 'Type' isn't a reserved word cos it is in some
databases.

Zoe

[Tim_Mar]

Zoe,

Thanks for the reply. This is just an Access backend. (that was working fine
with "Type" and the triple # date formats). The evaluared insert statement does
look fine (to me) as text (displayed before the CFQUERY call) but it is cut off
about 3/4 of the string. This is why I'm bettting there's some limit to the
length of a SQL command.

[sdupre]

Can you send me an example that fails (cut-and-paste from the debug output so we have all the data).

Stephen Dupre
Macromedia QA

[Tim_Mar]

I actually solved it I think. CF wanted the SQL in shorter lines. It might be a
best practice but I think it's more difficult to read.

Insert into TblMoveInfo

(LAST_nm,FIRST_nm,MI,Empl_Nbr,Type,DEPT_NBR,EFF_DT E,JOB_CLASS,CONDITION_DE
SC,LOCATION,SUPV_EMPL_NBR,SUPV_PHONE,NEW_DEPT_NBR, NEW_LOCATION,

NEW_SUPV_EMPL_NBR,NEW_SUPV_PHONE,COMMENTS,Updated_ DTE,Phone_Required,VM_Re
quired,Cell_Required,Computer_Required,

Network_Required,GW_Required,Officespace_Required, Requestor_NM,Requestor_E
mail)
Values
('#form.last_nm#','#form.first_nm#','#form.mi#','# form.Empl_Nbr#','Move','#form.
dept_nbr#',###dateformat(right(trim(form.eff_dte), len(form.eff_dte)-5))###,

'#form.job_class#','#form.condition_desc#','#form. location#','#form.Supv_e
mpl_nbr#','#form.SUPV_PHONE#','#form.NEW_DEPT_Nbr# ','#form.NEW_LOCATION#',

'#form.NEW_SUPV_NBR#','#form.NEW_SUPV_PHONE#','#fo rm.COMMENTS#',###datefor
mat(now())###,'#form.Phone_isRequired#','#form.VM_ isRequired#',

'#form.Cell_isRequired#','#form.Computer_isRequire d#','#form.Network_isReq
uired#','#form.GW_isRequired#','#form.Officespace# ','#Requestor_NM#','#Requestor
_Email#')

[mxstu]

Tim_Mar

I could be wrong, but is that any different than the query in your first post?


Also, exactly how many characters were you talking about? At what point in
the SQL statement did it cut off ... ie. after 5001 characters ?