Search code examples
coldfusionlucee

Coldfusion/Lucee - Loop over 3D array to insert into database using multiple inserts using one query

I know the title is a mouth-full - sorry about that but trying to be specific here.

DB: MySql (technically Maria)
ColdFusion (technically Lucee: 5.x)

The array looks like the following:
enter image description here

NOTE: the outter most array only shows part of 2 and could continue through into the 30's.

I'm looking to perform a loop over the array to insert the elements marked as "string" in the image into the database using one query. Query has been trimmed for the sake of clarity and conciseness: 

for (outer = 1; outer <= ArrayLen(myArray); outer++) {
    local.currentrow = local.currentrow + 1;
    for (inner = 1; inner <= ArrayLen(myArray[outer]); inner++) {
            local.sql = "
                INSERT INTO table (uuid, typeID, menuId, activityID, userID)
                VALUES (
                    '#local.uuid#',
                    #myArray[outer][inner][1]#, 
                    #myArray[outer][inner][2]#,
                    #myArray[outer][inner][3]#,
                    #arguments.formDataStruct.userID#
            )";  
    queryExecute(local.sql);   
    }
}

I'm looking for something along this line but as written, it isn't working: 

local.sql = "
INSERT INTO table (uuid, typeID, menuId, activityID, userID)
VALUES (
    if (local.currentrow gt 1) {
        ,
    }
    for (outer = 1; outer <= ArrayLen(myArray); outer++) {
        local.currentrow = local.currentrow + 1;
        for (inner = 1; inner <= ArrayLen(myArray[outer]); inner++) {

                    '#local.uuid#',
                    '#myArray[outer][inner][1]#', 
                    '#myArray[outer][inner][2]#',
                    '#myArray[outer][inner][3]#',
                    '#arguments.formDataStruct.userID#'
        }
    })
";
queryExecute(local.sql);

The error message I'm getting is

Element at position [1] doesn't exist in array

but if I perform a writedump[1][3][3] (e.g.), I'll get the value 24.

Solution

  • I would recommend against looping over an INSERT statement and rather just loop over VALUES to generate a single INSERT statement. A single INSERT will perform significantly faster, plus it will minimize the connections to your database.

    Build out the list of values with something like:

      for (var outer in arguments.inArray) {
    for (var inner in outer) {
      // Concat elements of inner array to a SQL VALUE string. If UUID is supposed to be a unique identity for the row, use Maria's uuid() instead of CF (or skip the UUID insert and let Maria do it).
      // inArray elements and inUserID should be sanitized.
      local.values &= "( uuid(), '" & inner[1] & "','" & inner[2] & "','" & inner[3] & "'," & local.userID & ")," ;
    }
  }
  local.values = left(local.values,len(local.values)-1) ; // Get rid of the last comma.
  local.sql = "INSERT INTO table (uuid, typeID, menuId, activityID, userID) VALUES " & local.values ;

    After you've built up the SQL INSERT string, execute the query to INSERT the records. (You would probably build the above function differently to handle building the query string and parameters and then executing it all in one place.)

    Don't forget to sanitize your array and other inputs. Does the array come from a source you control or is it user input?

    https://trycf.com/gist/7ad6af1e84906b601834b0cc5ff5a392/lucee5?theme=monokai http://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=d11f45f30723ba910c58a1e3f7a7c30b