Why does not this prepared statement work

Ignition
1

[code]I have this code below
equipment = ‘test’
model = ‘test’
pm = [‘test1’,‘test2’]
etch_type = ("SELECT b.requiredrun, b.status "
"FROM factory_vim.tbl_EtchTypeInfo a "
"JOIN "
"requiredruninfo b ON a.etchtype = b.layer "
"WHERE "
"b.equipment = ? "
"AND a.recipe = ? "
"AND b.processresource in ")
etch_type += ‘(’ + ‘,’.join(’?’ for x in pm) + ‘)’
print etch_type

etch_type_qry = system.db.runPrepQuery(etch_type,[equipment,model,’,’.join(pm)])[/code]
this is the error im getting.
caused by Exception: Error executing system.db.runPrepQuery(SELECT b.requiredrun, b.status FROM factory_vim.tbl_EtchTypeInfo a JOIN requiredruninfo b ON a.etchtype = b.layer WHERE b.equipment = ? AND a.recipe = ? AND b.processresource in (?,?), , [test, test, test1,test2],
caused by GatewayException: No value specified for parameter 4
caused by SQLException: No value specified for parameter 4

2

Hello,

In my opinion, the ‘,’.join(pm) is counted as a unique String parameter containing a comma (and not two parameters separated by a coma).
Your actual parameters list is : [‘test’, ‘test’, ‘test1,test2’]
It should be : [‘test’, ‘test’, ‘test1’,‘test2’]

3

Amarth is correct. Your last statement should have just been:etch_type_qry = system.db.runPrepQuery(etch_type, [equipment, model] + pm)

4

etch_type doesn’t need to be in brackets either. Remember that you can use triple quotes when entering multiple line strings:

etch_type = """ SELECT b.requiredrun, b.status FROM factory_vim.tbl_EtchTypeInfo a JOIN requiredruninfo b ON a.etchtype = b.layer WHERE b.equipment = ? AND a.recipe = ? AND b.processresource in """I think this looks neater and it’s easier to type!

5

Thanks everyone, this works for me