0
0

Written by

Caio Timm
Follow
Question Caio Timm · Jan 27

SET VALUE - CACHE SQL

#Caché #SQL

Hello,

It's possible to set a value to a "variable" in SQL Cache?

Like in SQl Server or Oracle?
Thanks

Product version: Caché 2018.1
Discussion (0)0
Log in or sign up to continue

Comments

Yaron Munz · Jan 28

Hello Caio,

There is no DECLARE @variable (SQL server) or DECLARE variable (Oracle) on Cache but there are few options:

1. Use a host variable(s) in embedded SQL:

SET variable = 2000
&SQL(SELECT Column FROM Table WHERE ID = :variable) 

2. Use the same with Dynamic SQL

SET variable = 2000
SET sql = "SELECT Column FROM Table WHERE ID = " _ variable
DO ##class(%SQL.Statement).%ExecDirect(, sql)

3. Writing a SP

CREATE PROCEDURE Procedure(IN variable  INT)
AS
BEGIN
    SELECT Column FROM Table WHERE ID = variable;
END
0
Alexander Koblov  Jan 30 to Yaron Munz

Hi Yaron.

SQL Injection police here ;-)

You can pass query parameters to %ExecDirect as well

SET variable = 2000
SET sql = "SELECT Column FROM Table WHERE ID = ?"
DO ##class(%SQL.Statement).%ExecDirect(, sql, variable)
0
Julian Matthews  Jan 30 to Alexander Koblov

Hi Alexander.

Sorry for being pedantic, but you're missing the ending quotation mark when setting the value of 'sql'

SET variable = 2000SET sql = "SELECT Column FROM Table WHERE ID = ?"DO##class(%SQL.Statement).%ExecDirect(, sql, variable)
0
Joel Solon · Feb 10

As @Yaron Munz and @Alexander Koblov correctly pointed out, you can use Embedded SQL or Dynamic SQL. You can also use Class Queries.

Using Embedded SQL, host variables in IRIS (:varname) can be inputs or outputs, just like host variables in SQL Server and Oracle, but without any DECLARE statement. Host variables used as inputs are automatically sanitized. Examples:

WHERE amount > :var1
INSERT INTO ... VALUES (:var1, :var2, :var3)
SELECT ... INTO :var1, :var2 FROM ...

Dynamic SQL allows ? placeholders for inputs only instead of host variables, also automatically sanitized. Examples:

WHERE amount > ?
INSERT INTO ... VALUES (?, ?, ?)

Dynamic SQL returns output values in the result set object it returns, which you can access and copy into variables. Examples:

set resultSet = ##class(%SQL.Statement).%ExecDirect(, sql, values for any placeholders)
while resultSet.%Next() {
    set var1 = resultSet.column1
    set var2= resultSet.column2
}

Class queries use host variables (:varname) for inputs (like Embedded SQL), and return output values in the result set object (like Dynamic SQL).

0