Insert parameterized query

Question

I want to perform insert operation on a list of objects.

My insert query looks like below

insert into table name (#[flowVars['columnNames']]) values (#[flowVars['values']]) 

#[flowVars['columnNames'] contains the comma separated columnNames like col1,col2

#[flowVars['values'] contains #[payload.?val1], #[payload.?val2]

I get the below error.

Invalid column name '@P0'. (com.microsoft.sqlserver.jdbc.SQLServerException)

How can i solve this issue?

Answer 1

I can suggest you debug and check whether you have correct value has been set for flowVars['columnNames'] and flowVars['values'], also you can put it in logger to see its value in log. Then check if the output from the above is matching with the column name required for this insert operation.

Answer 2

IMHO, the Query above is not suitable for the Parameterized query. Because the constructed query will be incorrectly interpreted by Mule. It might be constructed like this: insert into tableName ('col1,col2') values ('\'val1\',\'val2\'')

Therefore, I suggest to change the Query Type to: Dynamic.