New post
Question
· Apr 21, 2022

Using host variables with embedded SQL isn't working for me

#SQL #InterSystems IRIS

Hi

I'm using an embedded SQL statement with a a cursor-based Embedded SQL query that uses host variables in the where clause, however, what I'm doing doesn't seem to work. Can anyone help?

The code I'm using is

set tMessageName = "AssessmentsMessage"
set tIdentifier = "SectionCode" 
set ID = 0
&sql(
    Declare IDs Cursor For 
    select  %ID
    INTO :ID
    from GMECC_DocmanConnect_Tables.ParisConnecMessagetSettings
    Where  MessageName = :tMessageName
    and Identifier = :tIdentifier
)

&sql(Open IDs) For {
   &Sql(fetch IDs) If SQLCODE Quit // SQLCODE = 100, now rows found
       w !, ID 
       If $$$ISERR(tSC) Quit
} &Sql(Close IDs)

The table being queried contains  9 rows, and with the two parameters provided, it should return only 1 row, and instead returns nothing. 

What am I missing?

Product version: IRIS 2020.1
$ZV: IRIS for Windows (x86-64) 2020.1 (Build 215U) Mon Mar 30 2020 20:14:33 EDT
Discussion (15)1
Log in or sign up to continue
Andy Stobirski · Apr 22, 2022

When I do that, it returns 0: 0.

If I don't have a where clause on the statement, it returns the correct number of rows 9, outputs 0:9.

If I include the first part of the where clause (MessageName = :tMessageName)   and omit the second (Identifier= :tIdentifier) it returns 7, which is a value I'd expect. Having both returns 0, and I have checked the spelling of the second variable which is correct.

0 0