SBL_MC
December 17, 2020, 2:25pm
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?
1 Like
SBL_MC:
linje = “linje1_time”
system.db.runPrepQuery(“SELECT mProdusert FROM ? WHERE t_stamp > DATEADD(mi, -60,GETDATE()) AND t_stamp < DATEADD(mi, -59,GETDATE())”,[linje])
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,[])
1 Like
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)
1 Like
lrose
December 17, 2020, 2:32pm
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.
3 Likes
SBL_MC
December 17, 2020, 2:34pm
5
Thanks all. That was quick.
Made the query dynamic as suggested by bpreston.