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?
You could create the complete SQL string prior to the prepare statement. There are similiar examples in the documentation here as well: https://docs.intersystems.com/csp/documatic/%25CSP.Documatic.cls?CLASSNAME=%25SQL.Statement
While the table name cannot be a query argument, you can sanitize the input using:
And as far as sanitizing the table access itself - SQL access privileges should take care of that.