SQL Insert with Dynamic Variables

Permanently deleted user

20 December 2017 19:36

I'm working on a workflow that would allow a user to input a large number of attributes into a non-spatial table. In an attempt to consolidate things and allow me to manage my variable and attribute assignments, I've managed all these attributes in a dictionary with the key being the exact field name where the attribute will be stored. My goal was to loop through the dictionary and construct 2 strings; one of field names and one of values. My plan was to use these comma separated value strings to pass as parameters into a SQL Non Query Activity like so:

 

Parameters: :schema = FIELD_1, FIELD_2, FIELD_3 :values = 'cat', 'dog', 'mouse' Command: INSERT INTO TABLE (:schema) VALUES (:values) However, this method doesn't seem to work and instead I recieve an error pertaining to a missing SELECT statement. Therefore, I've assumed that this use of variables isn't permitted but if someone could confirm my suspicion that would be great.

 

 

0

• 

  Permanently deleted user

  21 December 2017 08:40

  Hi Warren,

   

  I think I had same problem in the past and I ended up passing individual values as parameters.

   

  You could try a couple of things:

  • Double check that parameters name are exactly the same in parameter and command sections (they got me so many times … SQL activity is very fussy with that …)
  • Move the insert statement into a store procedure and pass your Schema and Values parameters to the store procedure.
  • Try to wrap insert statement with Begin End; (it works for calling store procedures in Oracle but not sure for insert statement)

  Which DB are you using? This might make a difference: depending on DB type you could have different possibilities …

   

  I hope this helps …

   

   

  0
• 

  Permanently deleted user

  21 December 2017 14:02

  Hi Francesca,

   

  I'm using Oracle 11g. I've been asked to build a form for data submission that has an unwieldly 45+ attributes that need to be written to the table so I'm looking to avoid passing everything as a single parameter. I've double checked the parameter names and even print the whole statement in a Alert box and have confirmed that the statement will successfully execute outside of workflow.

   

  I was hoping I could use the SQL Activity but alternatively my back up is to publish the table as a service and use the feature service editing activities.

  0

Du måste logga in om du vill lämna en kommentar.