Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Convert CFML to CFSCRIPT?

Community Beginner ,
Nov 20, 2018 Nov 20, 2018

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!

1.8K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 20, 2018 Nov 20, 2018

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 (troubleshooter, carehart. org)
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Nov 20, 2018 Nov 20, 2018

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Nov 20, 2018 Nov 20, 2018

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"});
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Nov 21, 2018 Nov 21, 2018

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!

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Nov 21, 2018 Nov 21, 2018

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 21, 2018 Nov 21, 2018

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>

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Nov 21, 2018 Nov 21, 2018

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 21, 2018 Nov 21, 2018

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Nov 21, 2018 Nov 21, 2018

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 21, 2018 Nov 21, 2018

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

writeOutput("  VALUES(");

writeOutput("  UNION ALL  ");

writeOutput("  SELECT  ");

writeOutput("  FROM DUAL  ");

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 22, 2018 Nov 22, 2018
LATEST

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

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