Using context variable in code block
I need to use a context variable in my code block for the table to query. When I use :content.table, it does not work. When using the literal, it does. How do I get the :context to work?
.png)
Comments
According to the documentation for Embedded SQL: "A host variable cannot be used to specify an SQL identifier, such as a schema name, table name, field name, or cursor name. A host variable cannot be used to specify an SQL keyword".
Using Embedded SQL | Using InterSystems SQL | InterSystems IRIS Data Platform 2023.1
You will need to use dynamic SQL
This is a case where you will need to use Dynamic SQL, not Embedded SQL.
Worked like a charm. Thank you!
set tQuery="SELECT ProvId, AllwOpenSchYN, IsAllwSchedYN FROM "_context.EpicClaritySerMycTable_" WHERE ProvId='"_context.ProvId_"' AND Market='"_context.Market_"'"
set tStatement = ##class(%SQL.Statement).%New()
set qStatus = tStatement.%Prepare(tQuery)
set rset = tStatement.%Execute()
do rset.%Next()
if rset.%SQLCODE<0 {
$$$TRACE("SQL Query ["_tQuery_"] FAILED")
} else {
set tAllwOpenSchYN = rset.AllwOpenSchYN
set tIsAllwSchedYN = rset.IsAllwSchedYN
set tProvId = rset.ProvId
}You should never concatenate query parameters in the query text, instead use placeholders and parameters.
Like this:
set tQuery="SELECT ProvId, AllwOpenSchYN, IsAllwSchedYN FROM "_context.EpicClaritySerMycTable_" WHERE ProvId=? AND Market=?"set tStatement = ##class(%SQL.Statement).%New()
set qStatus = tStatement.%Prepare(tQuery)
set rset = tStatement.%Execute(context.ProvId,context.Market)
do rset.%Next()
if rset.%SQLCODE<0 {
$$$TRACE("SQL Query ["_tQuery_"] FAILED")
} else {
set tAllwOpenSchYN = rset.AllwOpenSchYN
set tIsAllwSchedYN = rset.IsAllwSchedYN
set tProvId = rset.ProvId
}
Suggested reading: Dynamic SQL Best Practices