How to execute sql queries using js/transformer

Queries and Resources
,
1

I have the following JS Query that generates SQL Statements:

js_query:

// Assuming your array is stored in a variable named 'dataArray'
let dataArray = transformer14.value;

// Create an array to store SQL update statements
let sqlStatements = [];

// Check if dataArray is defined and not empty
if (dataArray && dataArray.length > 0) {
  // Iterate through the dataArray and generate SQL update statements
  dataArray.forEach((row) => {
    let updateStatement = `UPDATE table SET quote_adjustment = ${row.quote_adjustment} `;
    updateStatement += `WHERE Quotation_no = '${row.Quotation_no}' AND SKU = '${row.SKU}';`;
    sqlStatements.push(updateStatement);
  });
} else {
  sqlStatements.push("No valid data found in dataArray.");
}

// Return the array of SQL statements
return sqlStatements;

How do I execute these queries, if the output is something like this:

{{ js_query.data }}

[
"UPDATE table SET quote_adjustment = 1 WHERE Quotation_no = 'ZAPAK' AND SKU = 'CAT';",
"UPDATE table SET quote_adjustment = 1 WHERE Quotation_no = 'ZAPAK' AND SKU = 'RAT';"
]

Thanks in advance.

2

Have you considered using additionalScope instead?

3

@ScottR , can you provide an example of how that will work in the above scenario?

I'm actually just trying to save changes made to the table into the mssql db. I was able to do it using the above way. I cannot use SQL GUI, that's why this lengthy approach.

4

You don't need to use the GUI.

if (dataArray && dataArray.length > 0) {
  // Iterate through the dataArray and generate SQL update statements
  dataArray.forEach((row) => {
yourUpdateQuery.trigger({
  additionalScope: {
    quote_adjustment: ${row.quote_adjustment},
    Quotation_no: ${row.Quotation_no},
    SKU: ${row.SKU}
  },
  // You can use the argument to get the data with the onSuccess function if you want to
  onSuccess: function (data) {
    console.log("Successully ran!");
  },
});
}

Then in yourUdateQuery resource, simply pass in
UPDATE table SET quote_adjustment = {{quote_adjustment}} WHERE Quotation_no = {{Quotation_no}} AND SKU = {{SKU}}

You may not need the OnSuccess argument....

You should also consider using Promises....

1 Like
5

Thanks! Exactly what I was looking for :smiley:

1 Like