New post
Question
· Feb 21, 2018

Input Host Variables in a Dynamic SQL text?

#SQL #Caché

I am working my way through some SQL documentation.

The documentation follows and my question comes after.

A query supplied to %Prepare() can contain input host variables, as shown in the following example:

  SET minage = 80

  SET myquery = 3

  SET myquery(1) = "SELECT %ID AS id, Name, DOB, Home_State"

  SET myquery(2) = "FROM Person WHERE Age > :minage"

  SET myquery(3) = "ORDER BY 2"

  SET qStatus = tStatement.%Prepare(.myquery)

This talks about input host variables

I thought that input host variables only work with Embedded SQL, not Dynamic SQL

Thank you

Discussion (5)1
Log in or sign up to continue
Robert Cemper · Feb 21, 2018

An interesting observation!
IT WORKS !

I just retyped it a little bit extended for fast retry:

set minage = 80
set myquery = 3
set tStatement = ##class(%SQL.Statement).%New()
set myquery(1) = "SELECT top 10 %ID AS id, Age , Name, %ODBCOUT(DOB) DOB, Home_State"
set myquery(2) = "FROM Sample.Person WHERE Age > :minage"
set myquery(3) = "ORDER BY 2"
set qStatus = tStatement.%Prepare(.myquery)
set tResult = tStatement.%Execute()
do tResult.%Display()
in SAMPLES:
id      Age     Name                    DOB     Home_State
16      82      Schaefer,Alvin S.       1935-05-05      HI
108     82      Adams,Brian Q.  1936-02-21      IL
199     82      Yeats,Ashley K. 1935-10-28      NC
74      83      Ravazzolo,Molly I.      1934-12-31      WV
63      84      Cheng,Filomena J.       1933-12-27      NM
69      84      Yeats,Patrick U.        1933-04-19      KY
92      85      Lepon,Liza M.   1932-06-03      MN
94      87      Browne,Patricia I.      1930-04-05      AL
111     87      Orlin,Edward J. 1930-04-10      OR
197     87      Rogers,Barbara M.       1930-12-06      WI

It also works using traditional %ResultSet

Though I didn't interpret nor use it that way it is documented here:

Dynamic SQL versus Embedded SQL    (4th point)

Dynamic SQL can accept a literal value input to a query in two ways:
input parameters specified using the “?” character,
and input host variables (for example, :var).
Embedded SQL uses input and output host variables (for example, :var).
1 0
Robert Cemper · Feb 21, 2018

It was first documented in 2015.2
chapter 12.1.1 p.108
http://docs.intersystems.com/documentation/cache/20152/pdfs/GSQL.pdf

• Dynamic SQL can accept a literal value input to a query in two ways: input parameters specified at execution time
using the “?” character, and input host variables (for example, :var) specified at prepare time. Embedded SQL uses
input and output host variables (for example, :var).

1 0
Alexander Koblov · Feb 22, 2018

Thank you for sharing Mike.

Please notice that Dynamic SQL looks for the host variable in the global scope.

For example, consider following method:

Class Sample.Sqlbindtest [ Abstract ]
{

ClassMethod test()
{
     set minage = 80
 set myquery = 3
 set tStatement = ##class(%SQL.Statement).%New()
 set myquery(1) = "SELECT top 10 %ID AS id, Age , Name, %ODBCOUT(DOB) DOB, Home_State"
 set myquery(2) = "FROM Sample.Person WHERE Age > :minage"
 set myquery(3) = "ORDER BY 2"
 set qStatus = tStatement.%Prepare(.myquery)
 set tResult = tStatement.%Execute()
 do tResult.%Display()
}

}

The query will refer to minage variable defined at the variable scope outside of method itself:

SAMPLES>d ##class(Sample.Sqlbindtest).test()
id      Age     Name    DOB     Home_State

0 Rows(s) Affected

SAMPLES>set minage=20 // now we define minage

SAMPLES>d ##class(Sample.Sqlbindtest).test()
id      Age     Name    DOB     Home_State
41      21      Beatty,Emily I. 1997-01-21      ID
163     21      Mastrolito,David X.     1996-04-14      AL
32      22      Adam,Sophia V.  1995-12-12      CO
33      22      Xiang,Laura L.  1995-03-02      MN
87      22      Paladino,Violet P.      1995-09-14      MN
139     22      Goncharuk,Stavros D.    1996-01-24      RI
173     22      Zucherro,Dmitry R.      1995-03-28      AK
56      23      Zubik,Quigley N.        1994-03-26      ID
46      24      Rogers,Mo D.    1994-02-16      OR
142     24      Orwell,Zelda M. 1993-07-12      AL

10 Rows(s) Affected
1 0