system.db.runPrepQuery, with FROM as a argument?

Ignition
1

Hi all,

I’ve been trying to loop trough a set of tables in runPrepQuery, but seem to not be allowed to use the FROM statement linked to an argument.

Wont work:
linje = “linje1_time”
system.db.runPrepQuery(“SELECT mProdusert FROM ? WHERE t_stamp > DATEADD(mi, -60,GETDATE()) AND t_stamp < DATEADD(mi, -59,GETDATE())”,[linje])

Works:
system.db.runPrepQuery(“SELECT mProdusert FROM linje1_time WHERE t_stamp > DATEADD(mi, -60,GETDATE()) AND t_stamp < DATEADD(mi, -59,GETDATE())”,[])

Is there any tricks to get around this?

2

How about:

linje = "linje1_time"
query = "SELECT mProdusert FROM %s WHERE t_stamp > DATEADD(mi, -60,GETDATE()) AND t_stamp < DATEADD(mi, -59,GETDATE())" % (linje)
system.db.runPrepQuery(query,[])
3 
linje = “linje1_time”
sqlQuery = 'SELECT mProdusert FROM {} WHERE t_stamp > DATEADD(mi, -60,GETDATE()) AND t_stamp < DATEADD(mi, -59,GETDATE())'.format(linje )
system.db.runPrepQuery(sqlQuery,[])

If you aren’t passing in any parameters though you can just just the system.db.runQuery:

linje = “linje1_time”
sqlQuery = 'SELECT mProdusert FROM {} WHERE t_stamp > DATEADD(mi, -60,GETDATE()) AND t_stamp < DATEADD(mi, -59,GETDATE())'.format(linje)
system.db.runQuery(sqlQuery)
4

To answer your question, there is no tricks to get around this. SQL will not allow schema objects (tables, columns, etc.) to be sent as parameters.

As @bpreston and @bschroeder have shown, the only option is to make the query string dynamic.

Be careful not to allow user input into the this though as you will be open to SQL injection.

5

Thanks all. That was quick.
Made the query dynamic as suggested by bpreston.