Search code examples
coldfusioncoldfusion-11cfloop

CFloop query to store multiple values in database

I have few variables that contain multiple values. Basically I want to store all the values into my database. I am using this code which I got here in Stackoverflow.

<cfquery datasource="databaseName">

INSERT INTO spreadsheet 
    ([Local.Time.Stamp],
    [Energy.Delivered..kVAh.],
    [Energy.Received..kVAh.],
    [Energy.Received..kVARh.],
    [Energy.Delivered..kVARh.],
    [Real.A..kW.],
    [Real.B..kW.])

    VALUES
    (<cfloop query="excelquery">
    '#excelquery.col_1#',
    '#excelquery.col_2#',
    '#excelquery.col_3#',
    '#excelquery.col_4#',
    '#excelquery.col_5#',
    '#excelquery.col_6#',
    '#excelquery.col_7#'
    </cfloop>)

</cfquery>

However I always get a syntax error. I believe that my cfloop part is wrong, can someone please tell me the correct way for me to write that cfloop?

Solution

  • The problem is with the generated query not cfloop i.e., for entering multiple values the format should be like this:

    INSERT INTO TableName (col,col,...) VALUES (val,val,...),(val,val,...),...

    Also, use cfqueryparam to avoid sql injection.

    You can try this:

    <cfquery datasource="databaseName">

  INSERT INTO spreadsheet 
    ([Local.Time.Stamp],
    [Energy.Delivered..kVAh.],
    [Energy.Received..kVAh.],
    [Energy.Received..kVARh.],
    [Energy.Delivered..kVARh.],
    [Real.A..kW.],
    [Real.B..kW.])

  VALUES
   <cfloop query="excelquery">

     <!--- cf_sql_varchar is just an example.  --->
     (<cfqueryparam cfsqltype="cf_sql_varchar" value="#excelquery.col_1#">,
      <cfqueryparam cfsqltype="cf_sql_varchar" value="#excelquery.col_2#">,
      <cfqueryparam cfsqltype="cf_sql_varchar" value="#excelquery.col_3#">,
      <cfqueryparam cfsqltype="cf_sql_varchar" value="#excelquery.col_4#">,
      <cfqueryparam cfsqltype="cf_sql_varchar" value="#excelquery.col_5#">,
      <cfqueryparam cfsqltype="cf_sql_varchar" value="#excelquery.col_6#">,
      <cfqueryparam cfsqltype="cf_sql_varchar" value="#excelquery.col_7#">) 
      #excelQuery.currentRow NEQ excelQuery.recordCount ? ',': ''#
    </cfloop>

</cfquery>

    You might have to add a recordCount check before generating the query to avoid errors for no records.