# Why does not this prepared statement work

**URL:** <https://forum.inductiveautomation.com/t/why-does-not-this-prepared-statement-work/12198>\
**Category:** Ignition\
**Created:** [October 5, 2016, 6:55am UTC](https://forum.inductiveautomation.com/t/why-does-not-this-prepared-statement-work/12198 "2016-10-05T06:55:02Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ella\_ramos](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ella_ramos/32/3455_2.png) [@ella\_ramos](https://forum.inductiveautomation.com/u/ella_ramos)\
**Post date:** [October 5, 2016, 6:55am UTC](https://forum.inductiveautomation.com/t/why-does-not-this-prepared-statement-work/12198/1 "2016-10-05T06:55:02Z")

</div>

[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

---

<div class="post-metadata">

**Author:** ![Amarth](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarth/32/3313_2.png) [@Amarth](https://forum.inductiveautomation.com/u/Amarth)\
**Post date:** [October 5, 2016, 7:56am UTC](https://forum.inductiveautomation.com/t/why-does-not-this-prepared-statement-work/12198/2 "2016-10-05T07:56:58Z")

</div>

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’]

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 5, 2016, 11:41am UTC](https://forum.inductiveautomation.com/t/why-does-not-this-prepared-statement-work/12198/3 "2016-10-05T11:41:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\
**Post date:** [October 18, 2016, 8:47pm UTC](https://forum.inductiveautomation.com/t/why-does-not-this-prepared-statement-work/12198/4 "2016-10-18T20:47:56Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![ella\_ramos](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ella_ramos/32/3455_2.png) [@ella\_ramos](https://forum.inductiveautomation.com/u/ella_ramos)\
**Post date:** [October 21, 2016, 3:25am UTC](https://forum.inductiveautomation.com/t/why-does-not-this-prepared-statement-work/12198/5 "2016-10-21T03:25:47Z")

</div>

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

Thanks everyone, this works for me