Forum Discussion

luk4rahul's avatar
luk4rahul
New Contributor
3 years ago

Running multiple SQL statements from a JSON file

I am new to Snaplogic and seek advise from the experts here.
I have a JSON file which has array sections for
Truncate,
Insert ,
Update and
Select Update (where I have to select from one table and use the selected values to update in another staging table with the derived values from select).
Each above section of array can have multiple truncate or insert or update statements and will change as per the JSON which it selects.
I mean the JSON is a dynamic JSON for a framework I am designing so the number of SQL statements can be dynamic too.

I am using the JSON parser etc…and everything is going fine till then. But when I try to run these sqls in sequential manner meaning
1)Truncate (using child pipeline)
2) Insert (using another child pipeline)

The statements are split in array fashion like objects and not like strings. So when we pass these statements to Oracle Execute, it doesn’t understand that it needs to run it as a SQL string and not an array object.
the output of the array is going as :

“truncate_sql”:
[
“TRUNCATE TABLE ABC”
“TRUNCATE TABLE BCD”
]

How can I run these sequentially in an Oracle execute ?

3 Replies

  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor
    3 years ago

    Hi @luk4rahul,

    If you split those with the Json Splitter snap, the Oracle snap will receive them as a separate objects, and thus, as separate queries.

    • luk4rahul's avatar
      luk4rahul
      New Contributor
      3 years ago

      Thanks @bojanvelevski ! I tried the splitter and have moved a little ahead. But one more challenge is that I have to run Selects and Updates one after the other from the JSON. What I mean here is for e.g.
      Customer Age : Select a,b from table1;
      Stage_table_update_for_age: Update stagetable set age=a (value ‘a’ from above select) where list of conditions.

      Customer Sex : Select c,d from table1;
      Stage_table_update_for_sex: Update stagetable set sex=c (value ‘c’ from above select) where list of conditions.

      There are multiple such selects and updates to be done one after the other irrespective of how many I have. I am not sure what snaps to use for this type of handling.

      I am able to get the inputs from the JSON in format above but how can we segregate the selects with the exact update it needs ?

      so my mapper has the outputs as listed in the diagram attached. I want to make sure I send these to SQL execute in the same manner as selects and updates for those selects.

      Here it shows 3 for Age, Gender and Quantity. But it can be any number of such statements maybe 6 or 8 based on my framework. So should be scalable too.

      I tried to attach a pic of the JSON how it looks but I dint find any attachment related button.