SQL query confusion

Question

Question

Rochdi Badis · Apr 10, 2022

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

Discussion (7)3

Log in or sign up to continue

Vitaliy Serdtsev · Apr 11, 2022

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

0 0

Vitaliy Serdtsev · Apr 11, 2022

Class dc.test Extends %Persistent
{

Property F As %Integer;

ClassMethod Test()
{

  d ..%KillExtent()

  &sql(insert into dc.test(F) values(22))
  &sql(insert into dc.test(F) values(11))

  s label = "00007I0Q"
  &sql(select ID into :cnt from dc.test where ID = :label)
  
  w 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.

0 0

Sergei Shutov · Apr 11, 2022

This is true for this particular version of Ensemble, but in IRIS this behaviour has changed -- even Embedded queries are using Dynamic SQL now. See discussion here https://community.intersystems.com/post/what-happened-embedded-sql

2 0

score 0 · Answer 1 · 2022-04-11T01:30:03-04:00

Dmitry Maslennikov · Apr 11, 2022

Did you try to get only ID in your query in SMP?

0 0

score 0 · Answer 2 · 2022-04-11T08:18:04-04:00

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.

score 0 · Answer 3 · 2022-04-13T16:26:01-04:00

Lucas Fernandes · Apr 13, 2022

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

0 0

score 0 · Answer 4 · 2022-04-11T09:01:05-04:00

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.)