The maze book for programmers!

Algorithms, circle mazes, hex grids, masking, weaving, braiding, 3D and 4D grids, spheres, and more!

DRM-Free Ebook

The Buckblog

assorted ramblings by Jamis Buck

SQLite3/Ruby 1.2.0

13 January 2007 — 3-minute read

After nearly 2 years, I’m happy to announce the release of a new version of sqlite3-ruby. This version is primarily a bug-fix release, so don’t expect lots of new goodies, but it does vastly improve the stability of the sqlite3 bindings for Ruby.

To install:

gem install sqlite3-ruby

There’s even a precompiled binary gem for you Windows users. (Boy, the world would be a lot less complicated if that OS just went away…I spent about as much time just building that Windows gem as I did applying the patches and fixing all the bugs below, but that’s a rant for another day.)

Two of the most significant things you should note:

  • The DL (pure-ruby) driver has been deprecated. I will no longer be maintaining that driver, and it will be removed altogether in a future release. Honestly, though, there is very little reason why you should be using it. It is quite buggy, mostly due to the limitations of Ruby’s DL library.
  • The SWIG dependency that bit so many users before is gone. I now bundle the generated C file, so it should build fine even if you don’t have SWIG installed.

The list of fixed bugs, more or less in order of severity:

  • Sylvain Joyeux submitted (a long time ago, to my great chagrin) a patch that fixed various problems with the different callbacks (busy_handler, set_authorize, and trace). If you were experiencing problems with sqlite3 crashing randomly, this might very well solve those for you.
  • When using type translation with results that had typeless columns, an exception would be raised. This includes queries with “count(*)”, or even the pragma queries like “table_info”. No more! Queries with typeless columns are now handled safely.
  • Prepared statements are automatically reset when you bind new variables to them. Before, the statement was not reset until it was called again, which made reusing statements problematic.
  • Some exceptions were not being caught inside a transaction, which was causing the transaction to commit, instead of roll back. This is now fixed.
  • Bignum values may now be bound in a prepared statement. Before, you’d get errors about “Bignum being too large for a long”.
  • When a database cannot be created, you’ll now get more detailed error information, such as “file could not be found”. Before, the error was just a generic “the database could not be opened”.
  • There were lots of warning messages that showed up when running ruby with the “-w” flag. Almost all of those have been fixed. (One, that I’m aware of, remains, but it is due to the SWIG bindings, and I’m not sure how to work around it.)

And, lastly, one (minor) new feature: Before this release, if you used named placeholders for bind variables, you had to refer to them with the colon character:

db.execute "select * from users where user_name = :user",
    ":user" => "mrmacho"

Now, you can leave the colon off, or even use a symbol:

db.execute "select * from users where user_name = :user",
  "user" => "mrmacho"

# or

 db.execute "select * from users where user_name = :user",
  :user => "mrmacho"

So, there you have it. Enjoy!

Reader Comments

This is just great! We’ll work to integrate this into Og ASAP :)

Though installing SWIG is a good learning experience, I’m glad the dependency is gone. One less hurdle to convince coworkers to use SQLite. Thanks so much for your work on this library!

Great work Jamis! I pleased to see the SWIG dependency is gone! You make our wold better! :)

Good work, thanks for the hard work on the blog, definitely one of the best around.

I have an sqlite3 related question – I’m using it in my test environment, but have noticed that it’s not defaulting model attributes to nil, instead setting them to 0, despite no default being set in the database and allow null enabled. Any ideas why this might be?


Thanks for the bug fixes!

One thing I was hoping to see fixed-and I don’t know if it’s a sqlite3-ruby isssue or just the AR adapter-is the obscure error messages. MySQL gives a helpful error message that says what problem was (or at least if it was an error with the SQL statement, a field doesn’t exist, etc.). Is this a something that can be fixed, or is it just an sqlite issue?

Teken, it’s probably something in the Rails adapter for sqlite3, and not in the sqlite3 bindings. Please do investigate and see what you can find.

Brandon, I’m not sure what you mean. Can you give an example?

I also had a significant problem with sqlite3 in test environment. It was converting my blank strings (as in ’’) into two quotes (as in ”’’”). It was doing this on OSX and windows on both ruby 1.8.4 and 1.8.5, so I’m guessing it’s a rails problem. Unfortunately I couldn’t find anymore info about it.

cool … should streamline new SQLite install a great deal.

Thanx for the effort


Here’s an example. If I have a mis-named fixture when I run my tests, mysql raises this helpful error:

ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'first_nam' in 'field list': INSERT INTO users (`first_nam`, `id`) VALUES ('Joe', 11)

Where as, sqlite raises this:

/opt/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.2.0/lib/sqlite3/errors.rb:94:in `check': SQL logic error or missing database (SQLite3::SQLException)

I like to use sqlite when developing locally, but this bites me every time and usually forces me to switch to mysql.

Brandon, I can’t duplicate that second error using the sqlite3 adapter with ActiveRecord. It’s probably a Rails issue, though, so if you post detailed steps to reproducing the problem on the Rails bugtracker, there’s a better chance of it getting fixed, or at least addressed.