SQL for processing changesetArray

💬 App Building
1

Question
I wonder whether anyone has a neat solution for looping through the changesetArray so as to be able to create the multiple SQL statements needed to update the database, like the ones issued by the query GUI.

The reason I ask is explained below...

Background
I'm struggling a bit because of the limitation that query GUI mode is not editable when a dynamic Resource ID is selected.

The workaround I use is to temporarily switch to fixed resource, check or edit the GUI query, and when finished switch back to the dynamic resource.

Problem
The trouble is that sometimes this may involve several queries and it is easy to miss setting them back to dynamic. This means that when I change the resource ID for the whole App, some queries may be left with a fixed Resource pointing at the wrong database.

This means that now and again I have to go through every single query, in the App, open it in the edit window and make sure it is set for dynamic Resource.

What I want to do
Because of this I've been wanting to change to using SQL queries even for the Inserts and Updates instead of the GUI. I can handle the simple kinds involving one row, or a known set of input components, but handling changesetArray needs a bit more thought - hence my question.

Update
I have manage to do this now, but it's not very neat, so I'm not so sure it's worth the trouble. Maybe I can tidy it up a bit....

3

That's an interesting question, @davblo. I can imagine it might take some messy string interpolation, but I'm down to give it a shot. Do you want it to handle bulk upserting?

4

Hi Darren,

For changesetArray it is UPDATE since all the rows already exist.

Here is my first attempt. It uses a JS Code query which loops through the changesetArray and for each changed object it creates an SQL command which it sends via additionalScope to an SQL query.

queryLoopThroughChangesetArray

chsetArr = table14.changesetArray;
nObjs = chsetArr.length;
for(iObj=0; iObj< nObjs; iObj++)
{
  obj = chsetArr[iObj];
//  console.log(obj);
  nKeys = Object.keys(obj).length;
  iLastKey= nKeys-1;
  lastKey= Object.keys(obj)[iLastKey];
  lastValue= obj[lastKey];
//  console.log(nKeys, iLastKey, lastKey, lastValue);
  iKey =0;
  for (key in obj) {
    iKey ++;
    if (iKey < nKeys) {
      sqlStr = "UPDATE `tbl_Package_titles` SET " + key + " = '" + obj[key] + "' WHERE " + lastKey + " = " + lastValue
   
      await query3UpdateRowInTable.trigger({additionalScope: {SQL_str: sqlStr}});
    }
  }
}

query3UpdateRowInTable

SET @sql = {{ SQL_str }};
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

It is simplified by just doing on SQL Update command for every change.

The GUI actually combines multiple changes on one row into one SQL Update command.

Update note:
I should mention - you can most likely see, but the code uses the fact that the Primary key is always the last one in each object in changesetArray. I'd guess that the array is created without the Primary key and updated as cells in the table are edited. Then when the array is actually used, the Primary key/values are tagged on to all objects at the last moment.
But really I don't know whether it's safe to assume that the Primary key will always be in that position or whether it's worth the longer route of searching for it in each object to be sure to get the right one.