Embedded SQL bug?

Question

Question

Fiona Griffiths · Nov 22, 2018

Hi

Is this a bug? We came across it on an older version of Cache but I've also just tried it in version "Cache for Windows (x86-64) 2017.2.2 (Build 865U)" and got the same result.

Create a Class like so:

Class My.Email Extends %Persistent
{

Property AddSeq As %Integer(MAXVAL = 32767, MINVAL = -32768) [ Required ];

Property EffBegDat As User.SQLdatetime;

Property EffEndDat As User.SQLdatetime;

Property EntSys As %Integer(MAXVAL = 2147483647, MINVAL = -2147483648) [ Required ];

Property Lbl As %String(MAXLEN = 20, TRUNCATE = 1);

Property Email As %String(MAXLEN = 60, TRUNCATE = 1);

Index EntSysLbl On (EntSys, Lbl);

Index LblKey On (EntSys, Lbl, EffEndDat);

}

Create an entry:

SET ;
s email=##class(My.Email).%New()
s email.EntSys=2726
s email.AddSeq=0
s email.EffBegDat="2018-09-14 00:00:00"
s email.EffEndDat=""
s email.Lbl="HOME"
S email.Email="test1@sky.com"
s sc=email.%Save()
q

Run a bit of embedded SQL:

GETEMAILDETS
S SN=2726
K (SN)
S (PrevEntEmailCID,PREVEMAIL,vPrevEmailSt,vPrevEmailEnd)=""
&sql(SELECT %ID,Email,EffBegDat,EffEndDat INTO :PrevEntEmailCID,:PREVEMAIL,:vPrevEmailSt,:vPrevEmailEnd FROM My.Email WHERE EntSys = :SN AND Lbl = 'HOME' AND AddSeq=0 AND EffBegDat IS NOT NULL AND EffEndDat IS NOT NULL ORDER BY EffBegDat Desc)
W !,"vPrevEmailSt=",vPrevEmailSt
Q

and vPrevEmailSt contains a value even though there are no rows that match all the conditions.

Discussion (2)3

Log in or sign up to continue

Sean Connelly · Nov 22, 2018

Hi Fiona,

You shouldn't trust variables populated with embedded SQL.

You need to check the SQLCODE value to make sure you have actually found data, if SQLCODE=0 trust the data, if SQLCODE=100 the query didn't find any data, any other value should probably throw an error as there will be a problem with the query.

More here...

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RERR_sql_noerr

4 0

score 0 · Answer 1 · 2018-11-22T06:42:11-05:00

Another reference - https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...

The section headed SQLCODE is reproduced below:

SQLCODE
After running an embedded SQL Query, you must check the SQLCODE before processing the output host variables.
If SQLCODE=0 the query completed successfully and returned data. The output host variables contain field values.
If SQLCODE=100 the query completed successfully, but output host variable values may differ. Either:
The query returned one or more rows of data (SQLCODE=0), then reached the end of the data (SQLCODE=100), in which case output host variables are set to the field values of the last row returned. %ROWCOUNT>0.
The query returned no data, in which case the output host variables are undefined. %ROWCOUNT=0.
If a query returns only aggregate functions, the first FETCH always completes with SQLCODE=0 and %ROWCOUNT=1, even when there is no data in the table. The second FETCH completes with SQLCODE=100 and %ROWCOUNT=1. If there is no data in the table or no data matches the query conditions, the query sets output host variables to 0 or the empty string, as appropriate.
If SQLCODE is a negative number the query failed with an error condition. For a list of these error codes and additional information, refer to the SQLCODE Values and Error Messages chapter of the Caché Error Reference.
Depending on how Embedded SQL is invoked, you may have to NEW the SQLCODE variable before entering Embedded SQL. With trigger code, setting SQLCODE to a nonzero value automatically sets %ok to zero.
In Dynamic SQL, the corresponding %SQLCODE property returns SQL error code values.