PrepQuery with SQL Variable

Ignition
1

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:

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

I get an error:

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

Whereas if I do a normal query:

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

2

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

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

https://docs.inductiveautomation.com/display/DOC81/system.db.runPrepQuery

1 Like
3

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

4

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.

2 Likes