Written by

Question 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
            }
        }else{
            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!

馃く
 

Comments

Eduard Lebedyuk 路 Oct 13, 2019

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?

Learning.InterSystems.com has hundrers of online courses. Check multimodel development with ObjectScript.

0
Evgeny Shvarov 路 Oct 13, 2019

@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. 

0
Yaron Munz 路 Oct 14, 2019

Hello,

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))
            }
        }else{
            !,"Error fetching books in GetoanedBooks()"
        }
        do booksRS.Close()
        return books

0
Peter Smit 路 Oct 15, 2019

I think 

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

should be 

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

0
Yaron Munz  Oct 15, 2019 to Peter Smit

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

0