I have function that should save some form fields. This function is able to process multiple insert rows. So far I was able to create logic to handle this but the only issue so far I have once user tries to save the record. The server response looks like this:
The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code. Null Pointers are another name for undefined values. coldfusion
At first I was looking and trying to find which value is not defined/missing in my function but so far I couldn't detect the issue. The next thing I did was commenting out the cfquery. After that I was getting message Form successfully saved.. This tells me that my code is breaking inside of the cfquery tag. Here is example of my code:
<cffunction name="saveRecords" access="remote" output="false" returnformat="JSON">
<cfargument name="formID" type="string" required="true" default="">
<cfargument name="status1" type="string" required="true" default="0">
<cfargument name="status2" type="string" required="true" default="0">
<cfargument name="status3" type="string" required="true" default="0">
<cfargument name="status4" type="string" required="true" default="0">
<cfargument name="status5" type="string" required="true" default="0">
<cfargument name="comment1" type="string" required="true" default="">
<cfargument name="comment2" type="string" required="true" default="">
<cfargument name="comment3" type="string" required="true" default="">
<cfargument name="comment4" type="string" required="true" default="">
<cfargument name="comment5" type="string" required="true" default="">
<cfset local.fnResults = structNew() />
<cfset local.runProcess = true />
<cfset local.arrStatus = arrayNew(1) />
<cfloop collection="#arguments#" item="i">
<cfif findNoCase(left(i,6), "status") and arguments[i] eq 1>
<cfset arrayAppend(local.arrStatus, right(i,1)) />
</cfif>
</cfloop>
<cfset local.frmValidation = {
formID : len(arguments.formID),
status1 : ArrayContains([0,1], trim(arguments.status1)),
status2 : ArrayContains([0,1], trim(arguments.status2)),
status3 : ArrayContains([0,1], trim(arguments.status3)),
status4 : ArrayContains([0,1], trim(arguments.status4)),
status5 : ArrayContains([0,1], trim(arguments.status5))
}/>
<cfloop collection="#frmValidation#" item="i">
<cfif !frmValidation[i] or !arrayLen(arrStatus)>
<cfset local.runProcess = false />
<cfset local.fnResults = {status: 400, message: "Form data is either missing or incorrect."}>
<cfbreak>
</cfif>
</cfloop>
<cfif runProcess>
<!---
<cfquery name="saveRec" datasource="testDB">
<cfloop array="#arrStatus#" index="i">
INSERT INTO formDetails (
formid,
refid,
status,
comment,
userid,
lastupdate
)
SELECT
<cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.formID)#">,
#i#,
<cfqueryparam cfsqltype="cf_sql_bit" value="#trim(arguments["status"&i])#">,
<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="8000" value="#trim(arguments["comment"&i])#" null="#!len(trim(arguments["comment"&i]))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" maxlength="6" value="#trim(session.userid)#" null="#!len(trim(session.userid))#">,
#now()#
WHERE NOT EXISTS (
SELECT refid
FROM formDetails
WHERE formid = <cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.formID)#">
AND refid = #i#
)
</cfloop>
</cfquery>
--->
<cfset local.fnResults = {status: 200, message: "Form successfully saved!"}>
</cfif>
<cfreturn fnResults>
</cffunction>
One thing that I forgot to mention is that before I commented out cfquery, I tried to save the records and error will occur that I mentioned above but at the same time record is saved in database. That is weird since I didn't expect query to execute successfully because of the error. I use Sybase database and here are the details about formDetails table:
column name data type length
recid numeric 18 PK
formid numeric 10 FK
refid numeric 18 FK
status bit 1
comment varchar 8000
userid varchar 6
lastupdate datetime 23
I'm not sure where my code is breaking and why I'm getting an error message about undefined value. If anyone can help or provide some useful resource on how to fix this issue please let me know.
UPDATE
I have been looking for solution on how to fix this problem and I tried using cfscript with UNION ALL. This was suggested from few people and here is example of my code:
remote function saveRecords(required string formID, string status1="0", string status2="0", string status3="0", string status4="0", string status5="0", string comment1="", string comment2="", string comment3="", string comment4="", string comment5="") output=false returnFormat="JSON" {
local.fnResults = structNew();
local.runProcess = true;
local.arrReference = arrayNew(1);
try {
local.frmValidation = {
formID : len(arguments.formID),
status1 : ArrayContains([0,1], arguments.status1),
status2 : ArrayContains([0,1], arguments.status2),
status3 : ArrayContains([0,1], arguments.status3),
status4 : ArrayContains([0,1], arguments.status4),
status5 : ArrayContains([0,1], arguments.status5)
};
for ( i in frmValidation ) {
if ( !frmValidation[i] ) {
local.runProcess = false;
local.fnResults = {status: 400, message: "Form data is either missing or incorrect."};
break;
}
}
for ( fld in arguments ) {
if ( findNoCase(left(fld,6), "status") && arguments[fld] == 1 ) {
arrayAppend(arrReference, right(fld,1));
}
}
if ( runProcess ) {
local.qrySql = "INSERT INTO formDetails(recid, formid, refid, status, comment, userid, lastupdate)";
local.qryParams = [];
for ( idx=1 ; idx<=arraylen(arrReference) ; idx++ ) {
local.referenceID = arrReference[idx];
local.recPK = trim(arguments.formID) & trim(referenceID);
local.statusVal = trim(arguments["status" & local.referenceID]);
local.commentVal = trim(arguments["comment" & local.referenceID]);
if ( idx != 1 ) {
local.qrySql &= " UNION ALL ";
}
local.qrySql &= " SELECT ?,?,?,?,?,?,?";
qryParams.append({cfsqltype="cf_sql_numeric", value=local.recPK});
qryParams.append({cfsqltype="cf_sql_numeric", value=trim(arguments.formID)});
qryParams.append({cfsqltype="cf_sql_numeric", value=local.referenceID});
qryParams.append({cfsqltype="cf_sql_tinyint", value=local.statusVal});
qryParams.append({cfsqltype="cf_sql_varchar", value=local.commentVal, maxlength=8000, null=!len(local.commentVal)});
qryParams.append({cfsqltype="cf_sql_varchar", value=trim(session.userid), maxlength=6, null=!len(trim(session.userid))});
qryParams.append({cfsqltype="cf_sql_timestamp", value=now()});
}
local.qryResult = queryExecute(qrySQL, qryParams, {datasource="#application.datasource#", result="insertRecords"});
local.fnResults = {result: insertRecords, sql: qrySql, params: qryParams, array: arrReference, args: arguments, status: 200, message: "Form successfully saved!"};
}
} catch (any e) {
local.fnResults = {sql: qrySql, params: qryParams, error: e, status: 400, message: "Error! Something went wrong..."};
}
return fnResults;
}
After I tried to save the record from I got the message Something went wrong.... I looked in response and all I can see is error that is pointing to the this line:
local.qryResult = queryExecute(qrySQL, qryParams, {datasource="#application.datasource#", result="insertRecords"});
I checked the database and records are in there. No details about this error nothing. At least I'm not getting an error that I described in the question above but still no clue what is causing my code to crash.
