Yone Moreno · 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!


0 275
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.

@Yone Moreno , I assume it's an exercise. In that case, do you mind to share your code in a public repo and people will understand what you are going to achieve and will PR the answer. 


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

I think 

set booksRS = ##class(%ResultSet).%New("Library.Book,BooksLoaned")

should be 

set booksRS = ##class(%ResultSet).%New("Library.Book:BooksLoaned")

Both syntax are good. the %New can handle "," or ":"