Embedded SQL vs ObjectScript SQL. What do you prefer and why?
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