Written by

Sr Sales Engineer (Public Sector) at InterSystems Corporation

STAFF

Question Chip Gore · Jun 21, 2016

Is there an Embedded SQL Cursor "rewind"?

#Caché #SQL

Hi -

I'm trying to rewind a cursor back to the first row after looping part of the way through the implied result set, but I'm not finding a way to make this happen, is there some such iterator variable or directive that I can leverage to accomplish this?

I could code around it by pulling identifiers and/or values into a local array, and then hand code up an iterator over my local results copy, but this feels like a "redesigning of a wheel" approach, and I thought I would check before I start down this path.

Thanks

Discussion (2)1

Add reply

Comments

Eduard Lebedyuk · Jun 21, 2016

You can close the cursor and open it again:

ClassMethod Test()
{
     &sql(  DECLARE C1 CURSOR FOR
            SELECT TOP 10 ID
            INTO :id
            FROM Sample.Person
    )

    &sql(OPEN C1)
    &sql(FETCH C1)
    
    Set first = $$$YES

    While (SQLCODE = 0) {
        Write id,!       
        &sql(FETCH C1)
        If id=9 && first {
            Set first = $$$NO
                &sql(CLOSE C1)
                &sql(OPEN C1)
                &sql(FETCH C1)
        }
    }

    &sql(CLOSE C1)
}

0 0

Chip Gore Jun 22, 2016 to Eduard Lebedyuk

Thanks (I didn't think of that)

0 0