Convert CFML to CFSCRIPT?

[pirlo89]

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!

[Charlie Arehart]

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

Looping | Learn CF in a Week

[pirlo89]

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

[pete_freitag]

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"});

[pirlo89]

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!

[pete_freitag]

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.

[BKBK]

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>

[pirlo89]

Have you tried to run that code? For me that didn't work.

[BKBK]

I tested only the first (insert) part. It worked.

[pirlo89]

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.

[BKBK]

It might help to ensure there are ample spaces in the lines

writeOutput("  VALUES(");

writeOutput("  UNION ALL  ");

writeOutput("  SELECT  ");

writeOutput("  FROM DUAL  ");

[BKBK]

pirlo89​, did you get this to work on your side?