Intersystems Cache: Embed SQL always returns only first row

Question

I copied the code from off. documentation:

&sql(SELECT *,%ID INTO :tflds()   
        FROM Sample.Person )
   IF SQLCODE=0 {
     SET firstflds=14
     FOR i=0:1:firstflds { 
       IF $DATA(tflds(i)) {
       WRITE "field ",i," = ",tflds(i),! }
     } }
   ELSE {WRITE "SQLCODE error=",SQLCODE,! }

But for some reason it only returns all fields of the first row and nothing else. Is it a bug, or am i doing smth wrong?

score 5 · Accepted Answer · answered Jul 11 '16 at 09:52

You need to use cursor to loop through rows of SQL query result.

&sql(declare c1 cursor for SELECT *,%ID INTO :tflds()   
    FROM Sample.Person)

&sql(open c1)
for  {
  &sql(fetch c1)
  quit:SQLCODE'=0
  set firstflds=14
  for i=0:1:firstflds {
      if $Data(tflds(i)) {
          write "field ",i," = ",tflds(i),!
      }
  }
  write "===NEXT ROW===",!
}

&sql(close c1)

See http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_esql#GSQL_esql_cursor for more info

Thank you. These docs are really hard to read for some reason, very confusing and not very clear sometimes. — Nikita Vasin, Jul 11 '16 at 10:04

score 1 · Answer 2 · answered Jul 12 '16 at 12:18

Embedded SQL is a good tool for performance - sensitive operations, but indeed hard to deal with if you need to retrieve more than one row. All this cursor business is a pain.

Consider using Dynamic SQL instead. It has nice resultset - like interface.

Intersystems Cache: Embed SQL always returns only first row

2 Answers2