Highlighted

Convert CFML to CFSCRIPT?

Community Beginner ,
Nov 20, 2018

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!

Views

821

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Convert CFML to CFSCRIPT?

Community Beginner ,
Nov 20, 2018

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!

Views

822

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Nov 20, 2018 0
Adobe Community Professional ,
Nov 20, 2018

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

Looping | Learn CF in a Week

/Charlie (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 20, 2018 0
Community Beginner ,
Nov 20, 2018

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 20, 2018 0
Enthusiast ,
Nov 20, 2018

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 20, 2018 0
Community Beginner ,
Nov 21, 2018

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 21, 2018 0
Enthusiast ,
Nov 21, 2018

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 21, 2018 0
Adobe Community Professional ,
Nov 21, 2018

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 21, 2018 0
Community Beginner ,
Nov 21, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 21, 2018 0
Adobe Community Professional ,
Nov 21, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 21, 2018 0
Community Beginner ,
Nov 21, 2018

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 21, 2018 0
Adobe Community Professional ,
Nov 21, 2018

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 21, 2018 0
BKBK LATEST
Adobe Community Professional ,
Nov 22, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 22, 2018 0