Using context variable in code block

Question

Question

Michael Wood · Jan 15

#Business Process (BPL) #SQL #InterSystems IRIS for Health

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?

Product version: IRIS 2023.1

Discussion (4)2

Log in or sign up to continue

Yaron Munz · Jan 15

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

1 0

score 0 · Answer 1 · 2025-01-15T10:39:05-05:00

David Hockenbroch · Jan 15

This is a case where you will need to use Dynamic SQL, not Embedded SQL.

0 0

score 0 · Answer 2 · 2025-01-15T11:00:30-05:00

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
       
 }

score 0 · Answer 3 · 2025-01-16T04:04:08-05:00

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