Question
Rochdi Badis · Apr 10

SQL query confusion

Hi Guys,

I'm a bit confused where if I run a query trough SMP I don't get any result which should be true but if I run the same query from a classmethod I get a query result which shouldn’t be even if I pass any gibberish parameter into my class method because I'm sure there should be any data? see attached  

 

 

Thanks 

  

Product version: Caché 2014.1
0
0 165
Discussion (7)3
Log in or sign up to continue

You should always check the SQLCODE after the embedded query.
If you do this, you will see that the SQLCODE returns the value 100 for your case. In this case, you should ignore host variables.

Host Variables

Class dc.test Extends %Persistent
{

Property As %Integer;

ClassMethod Test()
{

  ..%KillExtent()

  &sql(insert into dc.test(Fvalues(22))
  &sql(insert into dc.test(Fvalues(11))

  label "00007I0Q"
  &sql(select ID into :cnt from dc.test where ID :label)
  
  SQLCODE,":",cnt
}

}

Output for Caché 2018.1:

100:00007I0Q

Output for IRIS 2021.2:

100:

PS: it is a pity that my answer was ignored.

You can also try rebuilding the Index Table or clearing Cached Queries. Portal uses cached query and in its code it uses Embedded SQL (&sql) - Doc.: There is no need for a cache for Embedded SQL, because Embedded SQL statements are replaced with inline code at compilation time.

I didn't know about this change in IRIS. Thanks for clarifying.

Could be a difference of behavior related to select mode - could you try running the management portal query in ODBC and Display mode as well and see if that makes a difference? (This is most often the root cause I see when getting different results for the same query in different contexts.)