Insert Query with Dynamic Table Name,Colums and parameters

Ignition
1

Hello,
I am trying to write a dynamic SQL Insert Query in that all values are dynamic like This:
There will be any number of columns that is not fixed.

allCols=‘Company,Email,First_Name’
ColValues=‘MuleSoft,maxthemule@mulesoft.com,Max’
Table=‘Mapping_Info’
DB=‘Ignition_Integration’
insertQuery=‘insert into ‘+str(Table)+’ (’+str(allCols)+’) values (?,?,?)’
print ‘insertQuery’,insertQuery
value=system.db.runPrepUpdate(insertQuery,[ColValues],str(DB))

But it is throwing exception.Is it possible to do like this?
I have referred the solution given in forum but i didn’t get it.
Can anyone Help me out this.
Thanks.

2

Try modifying your script to something like this:

colValues = ['MuleSoft','maxthemule@mulesoft.com','Max']
allCols = ['Company', 'Email', 'First_Name']
cols = ''
for col in allCols:
	cols += col + '=? '
table = 'Mapping_Info'
query = "update %s set %s" %(table, cols)
database = 'Ignition_Integration'
system.db.runPrepUpdate(query, colValues , database=database)
3

Thanks for the reply.
I have tried ,There is some syntax error in this but It will work for update query.
I am facing issue with insert query, can you please explain for insert.

4

@Anita.Shinde sorry about that.

Try this for an insert:

colValues = ['MuleSoft','maxthemule@mulesoft.com','Max']
cols = 'Company, Email, First_Name'
args = '?,' * len(colValues)
args = args[:-1]
table = 'Mapping_Info'
query = "insert into %s (%s) VALUES (%s)" %(table, cols, args)
database = 'Ignition_Integration'
system.db.runPrepUpdate(query, colValues , database=database)
5

Thank You. It’s working.

1 Like