CODEWITHSUNDEEP
rubydatabasesqlite
confetti
confetti

Reputation: 1100

Unable to close sqlite3 database with ruby

begin
    db = SQLite3::Database.open "dbfile.db"
    dbins = db.prepare("INSERT INTO table(a,b,c) VALUES (?,?,?);")
    dbins.execute(vala,valb,valc)
rescue SQLite3::Exception => e
    puts("Something went wrong: " + e)
ensure
    db.close if db
end

So that would be the code I use to open an SQLite3 database and write data to it.
My problem is that this code always gives me the following error:

unable to close due to unfinalized statements or unfinished backups

If I remove the db.close if db part it works, but after several hours of the script running I get the too many open files error. Please do not advise me to raise my inode file limit, that would only be a temporary solution to a greater problem.

I do not want the script to just keep the database open forever, whenever an event happens I want it to open the db, write the data, and close it again, just how it's expected to work.

Note that this answer isn't helping because of the reason in the comment, which is true.

What do I have to do to "finish" the statement so I can close the database? I have tried to just add a sleep(5) before closing the database, but that had no effect.

I've found this Q suggesting to use finalize on the statement, but that seems to be only relevant for the C/C++ interface and not for ruby's sqlite3.

Upvotes: 0

Views: 676

Answers (1)

confetti
confetti

Reputation: 1100

Reading the source code for the ruby gem helped. Specifically the file statement.c's following codeblock:

/* call-seq: stmt.close
 *
 * Closes the statement by finalizing the underlying statement
 * handle. The statement must not be used after being closed.
 */
static VALUE sqlite3_rb_close(VALUE self)
{
  sqlite3StmtRubyPtr ctx;

  Data_Get_Struct(self, sqlite3StmtRuby, ctx);

  REQUIRE_OPEN_STMT(ctx);

  sqlite3_finalize(ctx->st);
  ctx->st = NULL;

  return self;
}

So using .close on the statement (e.g. dbins.close after the .execute in my code) will finalize the statement and make me able to close the database file.

Upvotes: 1

Related Questions