New post
Discussion
· Mar 7, 2019

Embedded SQL vs ObjectScript SQL. What do you prefer and why?

#SQL #Caché #InterSystems IRIS

Hi Community!

There are two general ways to execute arbitrary SQL  in serverside ObjectScript code: EmbeddedSQL and ObjectScript SQL a.k.a. Dynamic SQL.

E.g. if we want to get the value of the property of instance with a certain ID using SQL we can do:

&sql(SELECT Name INTO :name FROM Sample.Person WHERE ID=1)

write name

Same result with %SQL.Statement:

set rs=##class(%SQL.Statement).%ExecDirect(,"SELECT Name as name FROM Sample.Person where ID=1")
  do rs.%Next()
  write rs.name

Another typical task is to work through a set of records.

With Embedded SQL you do:

 &sql(DECLARE C1 CURSOR FOR 
       SELECT Name INTO :name 
       FROM Sample.Person
       WHERE Age > 60
       ORDER BY Name)
  &sql(OPEN C1)
      QUIT:(SQLCODE'=0)
  &sql(FETCH C1)
  WHILE (SQLCODE = 0) {
     WRITE name,!
    &sql(FETCH C1) }
  &sql(CLOSE C1)

And with SQL.Statement you have:

set rs=##class(%SQL.Statement).%ExecDirect(,"SELECT Name as name FROM Sample.Person WHERE Age > 60 ORDER BY Name  ")

 while rs.Next {

   write rs.Name,!
 }

My preference is %SQL.Statement or New Dynamic SQL.  Because it's more readable and not that wordy to work with resultsets.

What's your preference and why?

Good reads on this topic: Old/New Dynamic SQL Cheat Sheet, Class Queries in InterSystems Caché , Documentation on Executing SQL

Discussion (2)2
Log in or sign up to continue
Warlin Garcia · Mar 7, 2019

I like them in the following order:

1) Class Queries

2) Dynamic Queries

3) Embedded SQL

There was a time when Embedded SQL were way faster than dynamic queries, however I think that's not that different anymore. The readability provided by dynamic queries (especially for people new to Cache) outweighs the performance embedded SQL could provide. Of course each situation is different and maybe your application might benefit greatly from that extra performance.

One "drawback" from embedded SQL is that if it's used within routines you need to [manually] compile those routines when pushing changes for a class/table that is part of the embedded SQL. This is true even if those new changes doesn't directly affect that routine or embedded SQL. Classes are compiled automatically depending on the flags you use when compiling the affected table otherwise you need to [manually] compile them as well.

0 0
Eduard Lebedyuk · Mar 7, 2019

I try to separate SQL from ObjectScript code. So mainly, I'm using queries, for example:

/// Some report. To display in terminal call:
/// do ##class(class).reportFunc().%Display()
Query report(date As %String = {$zd($h-1,8)}) As %SQLQuery(SELECTMODE = "ODBC")
{
SELECT
  ID,
  Value,
  EventDate
FROM myTable
WHERE EventDate>=TO_POSIXTIME(:date,'YYYYMMDD') AND EventDate<TO_POSIXTIME(:date+1,'YYYYMMDD')
ORDER BY EventDate
}

Queries can be calles from ObjectScript using autogenerated Func method:

/// Really %sqlcq.<NAMESPACE>.cls<NUMBER>
#dim rs As %SQL.ISelectResult
set rs = ..reportFunc(date)
//do rs.%Display()

while rs.%Next() {
    write rs.ID,!
}

I found this approach improves readability of the codebase. More about queries in this article.

Special case - one value.

Sometimes you don't need a resultset, but one value. In that case:

If you know ID it's possible to use GetStored method:

set value = ##class(test).<PropertyName>GetStored(ID)

If you know unique indexed value but don't know ID, it's possible to get id with Exists method:

ClassMethod <IndexName>Exists(val, Output id) As %Boolean

And after that use GetStored method.

More on auto-generated methods, such as GetStored and Exists in this article.

Finally, if you can't use above methods or you need one value but it's an aggregate, use embedded SQL if it's a short SQL and Query if it's long.

0 0