Skip to main content
P
pirlo89
Known Participant
November 20, 2018
Question

Convert CFML to CFSCRIPT?

  • November 20, 2018
  • 3 replies
  • 2099 views

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!

This topic has been closed for replies.

3 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
November 21, 2018

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

P
pirlo89Author
Known Participant
November 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.

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
November 21, 2018

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

writeOutput("  VALUES(");

writeOutput("  UNION ALL  ");

writeOutput("  SELECT  ");

writeOutput("  FROM DUAL  ");

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
November 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>

P
pirlo89Author
Known Participant
November 21, 2018

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

Charlie Arehart
Community Expert
Charlie ArehartCommunity Expert
Community Expert
November 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)
P
pirlo89Author
Known Participant
November 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

pete_freitag
pete_freitag
Participating Frequently
November 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"});
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices