# PrepQuery with SQL Variable

**URL:** <https://forum.inductiveautomation.com/t/prepquery-with-sql-variable/66509>\
**Category:** Ignition\
**Tags:** scripting\
**Created:** [November 15, 2022, 8:12am UTC](https://forum.inductiveautomation.com/t/prepquery-with-sql-variable/66509 "2022-11-15T08:12:34Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![julien.rouillard](https://avatars.discourse-cdn.com/v4/letter/j/a9adbd/32.png) [@julien.rouillard](https://forum.inductiveautomation.com/u/julien.rouillard)\
**Post date:** [November 15, 2022, 8:12am UTC](https://forum.inductiveautomation.com/t/prepquery-with-sql-variable/66509/1 "2022-11-15T08:12:34Z")

</div>

Hello,

I am trying to see if it is possible to use the sql variables with prep query.

The idea is to have something like that:

```auto
queryTest = "DECLARE @vWorkerID VARCHAR(30);SET @vWorkerID = ?; SELECT * FROM TbWorker WHERE WorkerID = @vWorkerID"
system.db.runPrepQuery(queryTest,'1234')

```

I get an error:

```auto
java.lang.ClassCastException: java.lang.ClassCastException: Cannot coerce value '1234' into type: class [Ljava.lang.Object;

```

Whereas if I do a normal query:

```auto
queryTest = "DECLARE @vWorkerID VARCHAR(30);SET @vWorkerID ='1234'; SELECT * FROM TbWorker WHERE WorkerID = @vWorkerID"
system.db.runQuery(queryTest)

```

I get a result.

Any advise ?

Julien

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 15, 2022, 8:19am UTC](https://forum.inductiveautomation.com/t/prepquery-with-sql-variable/66509/2 "2022-11-15T08:19:38Z")

</div>

runPrepQuery expects a **list** of arguements [inside square brackets]. You supplied a string.

```auto
system.db.runPrepQuery(queryTest, ['1234'])

```

[https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepQuery](https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepQuery)

---

<div class="post-metadata">

**Author:** ![julien.rouillard](https://avatars.discourse-cdn.com/v4/letter/j/a9adbd/32.png) [@julien.rouillard](https://forum.inductiveautomation.com/u/julien.rouillard)\
**Post date:** [November 15, 2022, 8:40am UTC](https://forum.inductiveautomation.com/t/prepquery-with-sql-variable/66509/3 "2022-11-15T08:40:53Z")

</div>

Well now I feel foolish. Thank you it is exactly the problem.

---

<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:** [November 15, 2022, 12:49pm UTC](https://forum.inductiveautomation.com/t/prepquery-with-sql-variable/66509/4 "2022-11-15T12:49:01Z")

</div>

Do note that using SQL variables is SQL _ **scripting** _, which isn't actually in the JDBC spec. Some drivers allow it, but you really shouldn't be doing that if you want your work to be portable.

Single statements for JDBC. Scripts belong in stored procedures.