# ruby-DBI and SQLite3? **URL:** \ **Category:** Ruby\ **Created:** [January 17, 2007, 2:54am UTC](https://www.ruby-forum.com/t/ruby-dbi-and-sqlite3/82057 "2007-01-17T02:54:32Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Jos\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@Jos\_B](https://www.ruby-forum.com/u/Jos_B)\ **Post date:** [January 17, 2007, 2:54am UTC](https://www.ruby-forum.com/t/ruby-dbi-and-sqlite3/82057/1 "2007-01-17T02:54:32Z")
Is anybody interested in working with me on updating ruby-DBI to support SQLite3? I need it in order to ward off the evil P-monster (both!) at work. ---
**Author:** ![Jos\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@Jos\_B](https://www.ruby-forum.com/u/Jos_B)\ **Post date:** [January 17, 2007, 4:39am UTC](https://www.ruby-forum.com/t/ruby-dbi-and-sqlite3/82057/2 "2007-01-17T04:39:43Z")
On Wed, Jan 17, 2007 at 10:53:29AM +0900, Jos B. wrote: > Is anybody interested in working with me on updating ruby-DBI to support > SQLite3? I need it in order to ward off the evil P-monster (both!) at work. There already exists two implementations of a SQLite3 dbd that I’m aware of. Neither are actively maintained as far as I know, they were posted to the ruby-dbi mailing list sometime (I found them on Google, was never a subscriber myself). Anyways, the more featureful of the two has been packaged in Debian for some time now. Over the summer I made a few improvements to it, including adding transaction support. I procrastinated way too long in getting the patch in the Debian package, but I sent it in a little over a week ago, and it has since been committed. If you’re running debian, install package libdbd-sqlite3-ruby1.8. Otherwise download: [http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby\_0.1.1.orig.tar.gz](http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1.orig.tar.gz) [http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby\_0.1.1-4.diff.gz](http://ftp.us.debian.org/debian/pool/main/libd/libdbi-ruby/libdbi-ruby_0.1.1-4.diff.gz) The patch contains the SQLite3 dbd with transaction support. Also, be aware that the SQLite3 dbd requires the sqlite3-ruby library. I also recently sent some patches to Debian that fix a few issues when using the SQLite3 dbd, namely when binding variables to a prepared statement. The sqlite3-ruby changes are available in the libsqlite3-ruby1.8 package, or from: [http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby\_1.1.0.orig.tar.gz](http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0.orig.tar.gz) [http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby\_1.1.0-4.diff.gz](http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0-4.diff.gz) It also appears that these changes have made it back into sqlite3-ruby upstream in the 1.2.0 release that came out just a few days ago. However, I’ve not had a chance to verify that the dbd works properly with that version (and I probably won’t be able to for some time). In fact, if you try sqlite3-ruby 1.2.0, I’d appreciate knowing if everything seems to work OK with it. Good luck! ---
**Author:** ![Jos\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@Jos\_B](https://www.ruby-forum.com/u/Jos_B)\ **Post date:** [January 17, 2007, 6:10am UTC](https://www.ruby-forum.com/t/ruby-dbi-and-sqlite3/82057/3 "2007-01-17T06:10:01Z")
Hey Mike, On Wed, Jan 17, 2007 at 12:38:48PM +0900, Mike K. wrote: > On Wed, Jan 17, 2007 at 10:53:29AM +0900, Jos B. wrote: > > > Is anybody interested in working with me on updating ruby-DBI to support > > SQLite3? I need it in order to ward off the evil P-monster (both!) at work. > > There already exists two implementations of a SQLite3 dbd that I’m aware > of. Neither are actively maintained as far as I know, they were posted > to the ruby-dbi mailing list sometime (I found them on Google, was never > a subscriber myself). I did see the Debian package but it didn’t occur to me to look for the tarballs. > Anyways, the more featureful of the two has been packaged in Debian for > some time now. Over the summer I made a few improvements to it, > including adding transaction support. I procrastinated way too long in > getting the patch in the Debian package, but I sent it in a little over > a week ago, and it has since been committed. > > If you’re running debian, install package libdbd-sqlite3-ruby1.8. Sadly, I’m on CentOS. > statement. > > The sqlite3-ruby changes are available in the libsqlite3-ruby1.8 > package, or from: > > [http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby\_1.1.0.orig.tar.gz](http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0.orig.tar.gz) > [http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby\_1.1.0-4.diff.gz](http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0-4.diff.gz) Excellent! > It also appears that these changes have made it back into sqlite3-ruby > upstream in the 1.2.0 release that came out just a few days ago. > However, I’ve not had a chance to verify that the dbd works properly > with that version (and I probably won’t be able to for some time). > > In fact, if you try sqlite3-ruby 1.2.0, I’d appreciate knowing > if everything seems to work OK with it. I just installed the sqlite3-ruby 1.2.0 gem and aside from spewing a bunch of warnings like ``` sqlite3_api_wrap.c:1171: warning: dereferencing type-punned pointer ``` will break strict-aliasing rules it appears to install okay. Running the demo from the README mostly works, too: # cat x require ‘dbi’ DBI.connect(‘DBI:SQLite3:test’, ‘testuser’, ‘testpwd’) do | dbh | sql = “insert into songs (SongName, SongLength) VALUES (?, ?)” dbh.prepare(sql) do | sth | 1.upto(13) { |i| sth.execute(“Song #{i}”, “#{i\*10}”) } end dbh.select\_all(‘select \* from songs’) do | row | p row end dbh.do(‘delete from songs where internal\_id \> 10’) end # ruby x [“Song 1”, 10] [“Song 2”, 20] [“Song 3”, 30] [“Song 4”, 40] [“Song 5”, 50] [“Song 6”, 60] [“Song 7”, 70] [“Song 8”, 80] [“Song 9”, 90] [“Song 10”, 100] [“Song 11”, 110] [“Song 12”, 120] [“Song 13”, 130] [“Song 1”, 10] [“Song 2”, 20] [“Song 3”, 30] [“Song 4”, 40] [“Song 5”, 50] [“Song 6”, 60] [“Song 7”, 70] [“Song 8”, 80] [“Song 9”, 90] [“Song 10”, 100] [“Song 11”, 110] [“Song 12”, 120] [“Song 13”, 130] [“Song 1”, 10] [“Song 2”, 20] [“Song 3”, 30] [“Song 4”, 40] [“Song 5”, 50] [“Song 6”, 60] [“Song 7”, 70] [“Song 8”, 80] [“Song 9”, 90] [“Song 10”, 100] [“Song 11”, 110] [“Song 12”, 120] [“Song 13”, 130] /usr/lib/ruby/site\_ruby/1.8/DBD/SQLite3/SQLite3.rb:212:in `initialize': no such column: internal_id (DBI::ProgrammingError) from /usr/lib/ruby/site_ruby/1.8/DBD/SQLite3/SQLite3.rb:99:in `new’ from /usr/lib/ruby/site\_ruby/1.8/DBD/SQLite3/SQLite3.rb:99:in `prepare' from /usr/lib/ruby/site_ruby/1.8/dbi.rb:891:in `execute’ from /usr/lib/ruby/site\_ruby/1.8/dbi.rb:898:in `do' from /usr/lib/ruby/site_ruby/1.8/dbi.rb:496:in `do’ from x:15 from /usr/lib/ruby/site\_ruby/1.8/dbi.rb:430:in `connect' from /usr/lib/ruby/site_ruby/1.8/dbi.rb:215:in `connect’ from x:3 # I’m going to do a more realistic app with it soon. I’ll let you know how it goes. > Good luck! Thanks for the great info! Looks like I’m on my way. ---
**Author:** ![Jos\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/779978/32.png) [@Jos\_B](https://www.ruby-forum.com/u/Jos_B)\ **Post date:** [April 4, 2007, 8:14pm UTC](https://www.ruby-forum.com/t/ruby-dbi-and-sqlite3/82057/4 "2007-04-04T20:14:51Z")
On Wed, Jan 17, 2007 at 12:38:48PM +0900, Mike K. wrote: > Anyways, the more featureful of the two has been packaged in Debian for > > [http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby\_1.1.0.orig.tar.gz](http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0.orig.tar.gz) > [http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby\_1.1.0-4.diff.gz](http://ftp.us.debian.org/debian/pool/main/s/sqlite3-ruby/sqlite3-ruby_1.1.0-4.diff.gz) > > It also appears that these changes have made it back into sqlite3-ruby > upstream in the 1.2.0 release that came out just a few days ago. > However, I’ve not had a chance to verify that the dbd works properly > with that version (and I probably won’t be able to for some time). > > In fact, if you try sqlite3-ruby 1.2.0, I’d appreciate knowing > if everything seems to work OK with it. I have been using sqlite3-ruby for a while now and it seems to work fine. Thanks again, Mike.