· Oct 13, 2019

Using class queries to select all rows and return an array: unexpected behaviour

We have in DB two books, first is loaned because it has the Friend ID, and the other is in the shelf.

I execute a class query to get all loaned books:

    Query BooksLoaned() As %SQLQuery
        SELECT *
        FROM Library.Book
        WHERE Friend IS NOT NULL

Which I have tested throught the portal:

My task is to retrieve all the loaned books and return them in an array. Method code:

ClassMethod GetLoanedBooks() As %ArrayOfObjects
        set booksRS = ##class(%ResultSet).%New("Library.Book,BooksLoaned")
        set rsStatus = booksRS.Execute()
        set i = 1
        set ^books = ""
        if rsStatus = $$$OK {
            while booksRS.Next() {
                set book = ##class(Library.Book).%New()
                set book.Title = booksRS.Get("Title")
                set book.Author = booksRS.Get("Author")
                set book.Genre = booksRS.Get("Genre")
                set dbFriend = ##class(Library.Person).%OpenId(booksRS.Get("Friend"))
                set book.Friend = dbFriend
                set ^books(i) = book
                set i = i + 1
            w !,"Error fetching books in GetoanedBooks()"
        do booksRS.Close()
        return ^books

The question is:

Why when I execute in the terminal:

set books = ##class(Library.Book).GetLoanedBooks()
zw books

It outputs:


And when I write:

zw ^books

I know ^books is a global variable, but I do not know why if I am currently returning the books array and setting it into the terminal, it doesn't have the loaned book inside.


Thanks for your help.

Any recommended lecture is appreciated!


Discussion (6)2
Log in or sign up to continue

First issue (empty books):

  • books is a local variable, existing in a current frame stack, as soon as you leave the method it disappears. Well, in your case you're not using it, so it's empty
  • ^books is a global variable, so it's stored in a database and you're using it

Second issue (12@Library.Book in value). 12@Library.Book is an OREF. As it's essentially a pointer it cannot be stored in a persistent way.

What do you want to achieve with your code? has hundrers of online courses. Check multimodel development with ObjectScript.


your method returns %ArrayOfObjects so you need to create and populate it within your code...

your code should look like (I have highlighted the relevant changes in code) :

set booksRS = ##class(%ResultSet).%New("Library.Book,BooksLoaned")
        set rsStatus = booksRS.Execute()
        books = ##class(%ArrayOfObjects).%New()
        if rsStatus = $$$OK {
            while booksRS.Next() {
                set book = ##class(Library.Book).%New()
                set book.Title = booksRS.Get("Title")
                set book.Author = booksRS.Get("Author")
                set book.Genre = booksRS.Get("Genre")
                set dbFriend = ##class(Library.Person).%OpenId(booksRS.Get("Friend"))
                set book.Friend = dbFriend
                Set sc = books.SetAt(book,$Increment(i))
            !,"Error fetching books in GetoanedBooks()"
        do booksRS.Close()
        return books