Convert CFML to CFSCRIPT?
Copy link to clipboard
Copied
Hello everyone, I have cfml code that needs to be converted to cfscript. The most of the code I was able to convert but the cfquery part that has the loop seems little complicated. Here is example of the cfml code:
<cfquery name="insertRec" datasource="db" result="qryInsert">
INSERT INTO Contacts (First, Last, Email, Subject, Description)
<cfif trim(form.type) EQ 1>
VALUES(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.subject)#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.appdescr)#" maxlength="500">
)
<cfelse>
<cfloop from="1" to="#arrayLen(arrDpr)#" index="idx">
<cfif idx GT 1>UNION ALL</cfif>
SELECT
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.first)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.last)#" maxlength="50">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form.email)#" maxlength="320">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form["column" & arrDpr[idx]])#" maxlength="100">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(form["datadescr" & arrDpr[idx]])#" maxlength="500">
FROM DUAL
</cfloop>
</cfif>
</cfquery>
How this code would look like in cfscript? Is there a good example so I can use to convert this code? Please let me know if you see any way this code can be improved in cfscript as well. Thank you!
Copy link to clipboard
Copied
Save yourself a lot of time, using the free service https://www.cfscript.me. It will convert any tag-based cfml to script.
That said (and in case you or another reader might need to answer your question via docs rather than a toll), there are certainly many resources, from Adobe and others, which show how to write all that in cfscript (queries, queryparams, if's, else's, and loops being foundational, of course) and more:
ColdFusion Help | Using CFScript statements
https://www.petefreitag.com/cheatsheets/coldfusion/cfscript/
For Loops in CFML CFScript CFML Documentation
cfloop Code Examples and CFML Documentation
/Charlie (troubleshooter, carehart. org)
Copy link to clipboard
Copied
Hello Charlie, thanks for providing useful resources. I already looked into cfscript.me while it's very handy there are still some issues if you just copy/paste the code. For example code that I provided above translates to solution that includes writeOutput();. That seems little odd and very long. I'm looking if this can be achieved using the addParam(); method.
Regards,
Milos
Copy link to clipboard
Copied
Using queryExecute is probably the best way to go here. While I usually pass the queryparams as a struct in this case since you have a dynamic number of params I would pass them as an array, something like this:
sql = "INSERT INTO Contacts (First, Last, Email, Subject, Description)";
params = [];
if ( trim(form.type) == 1 ) {
sql &= "VALUES(?,?,?,?,?)";
params.append( { maxlength=50, cfsqltype="cf_sql_varchar", value=trim(form.first) } );
params.append( { maxlength=50, cfsqltype="cf_sql_varchar", value=trim(form.last) } );
//etc...
} else {
//etc...
}
insertRec = queryExecute(sql, params, {datasource="db" result="qryInsert"});
Copy link to clipboard
Copied
Hello Pete,
This would work the best in my case but my CF server is on CF10. Seems that queryExecute () is supported from version CF11. Is there any other alternative in that case? I'm wondering if new query() then addParam() can achieve the same output. Please let me know if you have any suggestions. Thank you!
Copy link to clipboard
Copied
Yeah that syntax should work on CF9+ queryExecute only works on CF11+. I prefer queryExecute when possible, but in your case the query cfc approach should work.
Copy link to clipboard
Copied
Hi pirlo89​,
Let pete_freitag answer it for you via his tag-to-script conversion site, http://cfscript.me/
The site gives:
<cfscript>
cfquery( name="insertRec", datasource="cfmx_db", result="qryInsert" ) {
writeOutput("INSERT INTO Contacts (First, Last, Email, Subject, Description)");
if ( trim(form.type) == 1 ) {
writeOutput("VALUES(");
cfqueryparam( maxlength=50, cfsqltype="cf_sql_varchar", value=trim(form.first) );
writeOutput(",");
cfqueryparam( maxlength=50, cfsqltype="cf_sql_varchar", value=trim(form.last) );
writeOutput(",");
cfqueryparam( maxlength=320, cfsqltype="cf_sql_varchar", value=trim(form.email) );
writeOutput(",");
cfqueryparam( maxlength=100, cfsqltype="cf_sql_varchar", value=trim(form.subject) );
writeOutput(",");
cfqueryparam( maxlength=500, cfsqltype="cf_sql_varchar", value=trim(form.appdescr) );
writeOutput(")");
} else {
for ( idx=1 ; idx<=arrayLen(arrDpr) ; idx++ ) {
if ( idx > 1 ) {
writeOutput("UNION ALL");
}
writeOutput("SELECT");
cfqueryparam( maxlength=50, cfsqltype="cf_sql_varchar", value=trim(form.first) );
writeOutput(",");
cfqueryparam( maxlength=50, cfsqltype="cf_sql_varchar", value=trim(form.last) );
writeOutput(",");
cfqueryparam( maxlength=320, cfsqltype="cf_sql_varchar", value=trim(form.email) );
writeOutput(",");
cfqueryparam( maxlength=100, cfsqltype="cf_sql_varchar", value=trim(form["column" & arrDpr[idx]]) );
writeOutput(",");
cfqueryparam( maxlength=500, cfsqltype="cf_sql_varchar", value=trim(form["datadescr" & arrDpr[idx]]) );
writeOutput("FROM DUAL");
}
}
}
</cfscript>
Copy link to clipboard
Copied
Have you tried to run that code? For me that didn't work.
Copy link to clipboard
Copied
I tested only the first (insert) part. It worked.
Copy link to clipboard
Copied
That is the problem, the first part will work with majority of the solutions that I tried. Second part with the loop is something that caused problems. Even when I found solution to build correct SQL string, still parameters were repeating same values instead of inserting correct fields.
Copy link to clipboard
Copied
It might help to ensure there are ample spaces in the lines
writeOutput(" VALUES(");
writeOutput(" UNION ALL ");
writeOutput(" SELECT ");
writeOutput(" FROM DUAL ");
Copy link to clipboard
Copied
pirlo89​, did you get this to work on your side?

