FMDB ResultSet always returns only one row

I am trying to use a sqlite database in one of my projects.

It was working fine; but for a reason, something happened and I couldn't find that bug.

The resultSet object always quit from after the first record. The array always has only 1 record in it. (Probably it left the while because of the error)

I created a DBManager class, and this DBManager class contains different inner Classes. I have a private global FMDatabase instance (And I initialise it somewhere before using it)

As you see, there are 2 different print error line

When I run, the second print line gives this error:

Error calling sqlite3_step (21: out of memory) rs Error Domain=FMDatabase Code=7 "out of memory" UserInfo=0x790308d0 {NSLocalizedDescription=out of memory}

And the array which should contain over 300 records, has only 1 record in it. (Last print line is always 1)

This part is looking simple. (I have totally similar code somewhere else, but it works fine)

private var database : FMDatabase!

class DBManager{

    class Element{

        class func get()->[DataElement]{
            database.open()
            println( database.lastError() )

            var result = [DataElement]()

            var resultSet: FMResultSet! = database!.executeQuery("SELECT * FROM Element WHERE working = 1", withArgumentsInArray: nil)

            while resultSet.next(  ) {
                let data = DataElement( 
                    id : Int(resultSet.intForColumn("id")),
                    name: resultSet.stringForColumn("name"), 
                    server: resultSet.stringForColumn("server"), 
                    working: resultSet.boolForColumn("working") )
                result.append( data )
            }

            println( database.lastError() )
            database.close()

            println( result.count )
            return result
        }
    }
}

PS: Only difference between those tables is (as I realize ) the "id" column. This Element table has an "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, but the other one does not have any id column. But both of them worked well for a long time.

Solution

The "out of memory" error is a misleading SQLite error that means that a SQLite function was called with a NULL value for the sqlite3* pointer. In FMDB this means that you closed the database but then tried to continue using the same FMDatabase instance (without calling open again).

Now, I don't see you doing that in this code sample, but this code sample is employing some practices that make that sort of error possible. Namely, rather than instantiating the FMDatabase object locally, you are using some database property and you run the risk that some other function may have used it (maybe the init method for DataElement? maybe some other function that you removed for the sake of brevity? maybe some other thread?).

Let's imagine that this function called some other function that opened the database again (which FMDB will silently let you do, basically returning immediately if the database is already open), perform some SQL, and then closed the database, then this routine, when it went to retrieve the second row of information, would of have found the database closed, resulting in the error you describe. The opening and closing of the database object is not recursive. Once the subroutine closed it, it's completely closed.

Now all of this is hypothetical, because without seeing what the rest of your code does, it's impossible for me to confirm. But its a scenario that would fit what code you have shared combined with the the symptoms you've described.

Assuming that this is really the case, there are two possible solutions here:

You could simply open the database once and leave it open until the app is terminated. This is probably the easiest approach and is probably more efficient than what you have here. SQLite is actually pretty robust in terms of committing individual SQL statements (or transactions), so people usually leave the database open.

I might even go step further, and suggest that if you might have different threads interacting with this database, that you instantiate a single FMDatabaseQueue object, and use that throughout the app, again not opening and closing all the time. Note, if you use FMDatabaseQueue, you'll want to be even more judicious about making sure that one function that is in the middle of an inDatabase or inTransaction block doesn't call another function that tries to do another inDatabase or inTransaction block (or else you'll deadlock). But like any shared resource, you want to be sensitive to where a resource is locked and when it's released, and databases are no exception to that rule.
If you're determined to open and close the database in every function like this code sample suggests (again, a practice I wouldn't advise), then do not use a class property to keep track of the database. Sure, have some function that opens the database, but return this FMDatabase object, and each function would have it's own local instance and would close that instance when it's done with it.

But you really want to avoid the unintended consequences of one function's closing the database affecting the behavior of some other function.