Provide FROM table name to query as a parameter
I have an array of table names that I want to iterate over and for each table, get the number of rows. I was hoping I could do something like:
set tStatement = ##class(%SQL.Statement).%New() set tSC = tStatement.%Prepare("SELECT COUNT(*) AS ObservedRowCount FROM ?") $$$ThrowOnError(tSC) set tTableName = "" for { set tTableName = $O(tCounterAry(tTableName)) quit:tTableName=""set tResult = tStatement.%Execute(tTableName) // ... }
But that doesn't work:
ERROR #5540: SQLCODE: -1 Message: IDENTIFIER expected, ? found^ SELECT COUNT ( * ) AS ObservedRowCount FROM ?
For reasons that I hope are obvious I would prefer not to do this with something like:
set tResult = ##class(%SQL.Statement).%ExecDirect(,"SELECT COUNT(*) AS ObservedRowCount FROM "_tTableName)
(And if it's not obvious: this makes me vulnerable to a SQL injection attack. See also: https://xkcd.com/327/)
Is there a way that I can safely, dynamically query different tables with the same query?