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
Comments
Did you try to get only ID in your query in SMP?
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.
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.
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.
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
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.)