Problem with additionalScope not passing string

Queries and Resources
1

I'm trying to pass a string of id values to a query using the additionalScope parameter to the query.

UPDATE "CT_Statuses"
SET "TempTrackPositionID" = v.tpid
FROM (VALUES
{{saveupdateslist}}
) v(vid,tpid)
WHERE v.vid = "CT_Statuses".id

Here is the call to it from Javascript:

UpdateTrackPositionsQuery.trigger({
additionalScope: {saveupdateslist:'(150,123),(151,124)'}});

If I modify the query to have the list of ids directly within it, the query executes as expected when triggered from javascript:

UPDATE "CT_Statuses"
SET "TempTrackPositionID" = v.tpid
FROM (VALUES
(150,123),(151,124)
) v(vid,tpid)
WHERE v.vid = "CT_Statuses".id

Using the additionalScope i get the error "syntax error at or near "$1"". When I look at the query state I see the following:

"UPDATE "CT_Statuses" SET "TempTrackPositionID" = v.tpid FROM (VALUES ) v(vid,tpid) WHERE v.vid = "CT_Statuses".id".

Note that the nothing is passed in and inserted after "VALUES".

Any idea what I am doing wrong? Thanks in advance for any help!

2

I believe the issue is that (with a prepared statement?) you cannot pass in the VALUES "parenthesized list of expressions" though I'm not certain as to why.

I've seen this type of question arise a couple of times, but I haven't seen a solution. I suppose a workaround would be to insert the values into a (temporary) table in the DB that you truncate when the use of it is done. You may also be able to execute the SQL as you have it if you turn off prepared statements, but I have not tried and generally try not to go that route.

Or I suppose you could put the values in an array of objects and then trigger an update query with additionalScope variables in a forEach loop, something along the lines of:

let foo = [{vid: 150, tpid: 123},{vid: 151, tpid: 124}] 
foo.forEach(obj => updateQuery.trigger({
  additionalScope: {
    CTstatusesId: obj.vid,    // add {{CTstatusesId}} in your update query
    TempTrackPosId: obj.tpid  // add {{TempTrackPosId}} in your update query
  }
});

If you come up with a solution to make your original approach work, would love to hear it.

3

I tried a few more things an was not successful. Ultimately I created a retool bulk update query and passed an array to it using {{SaveUpdate.data}} which worked. Thanks for your help!

1 Like