errors when using query and prepare methods for sqlite3

[Robert Citek]

Hello all,

I'm trying to prepare a sql statement to send to sqlite3 via ruby and
am getting errors when using the query and prepare methods.  Here's my
code:

#!/usr/bin/env ruby
require 'sqlite3'
db = SQLite3::Database.new( "test.db" )

# create the database
sql = <<-eof
 CREATE TABLE foobar (foo text, bar int);
 INSERT INTO "foobar" VALUES('hello',1);
 INSERT INTO "foobar" VALUES('world',2)
 eof
db.execute_batch(sql)

# querying the database via execute works
db.execute( "select * from foobar" ) do |row|
 puts row.join("\t") ;
end

# querying the database via query method does not work
db.query( "select * from foobar" ) do |row|
 puts row.join("\t") ;
end

# querying the database via a prepare does not work
db.prepare( "select * from foobar" ) do |stmnt|
 stmnt.execute do |row|
   puts row.join("\t") ;
 end
end

Below is the output from running the execute, query, and prepare methods in irb:

irb(main):036:0> # querying the database via execute works
irb(main):037:0* db.execute( "select * from foobar" ) do |row|
irb(main):038:1*   puts row.join("\t") ;
irb(main):039:1* end
hello   1
world   2
=> nil
irb(main):040:0>
irb(main):041:0* # querying the database via query method does not work
irb(main):042:0* db.query( "select * from foobar" ) do |row|
irb(main):043:1*   puts row.join("\t") ;
irb(main):044:1* end
NoMethodError: undefined method `join' for #<SQLite3::ResultSet:0xb7c6bae4>
       from (irb):43
       from /usr/lib/ruby/1.8/sqlite3/database.rb:278:in `query'
       from (irb):42
irb(main):045:0>
irb(main):046:0* # querying the database via a prepare does not work
irb(main):047:0* db.prepare( "select * from foobar" ) do |stmnt|
irb(main):048:1*   stmnt.execute do |row|
irb(main):049:2*     puts row.join("\t") ;
irb(main):050:2*   end
irb(main):051:1> end
NoMethodError: undefined method `join' for #<SQLite3::ResultSet:0xb7c3c190>
       from (irb):49
       from /usr/lib/ruby/1.8/sqlite3/statement.rb:166:in `execute'
       from (irb):48
       from /usr/lib/ruby/1.8/sqlite3/database.rb:187:in `prepare'
       from (irb):47

$ ruby -v
ruby 1.8.6 (2007-09-24 patchlevel 111) [i486-linux]

$ irb -v
irb 0.9.5(05/04/13)

I've been looking at the docs[1] and the FAQ[2].  Clearly I'm
overlooking something, but what?

Thanks in advance to any pointers.

[1] http://sqlite-ruby.rubyforge.org/sqlite3/
[2] http://sqlite-ruby.rubyforge.org/sqlite3/faq.html

Regards,
- Robert

[Craig Buchek]

The call to query and the execute of the prepared statement return
SQLite::ResultSet objects. (http://sqlite-ruby.rubyforge.org/classes/
SQLite/ResultSet.html) You'll need to run each on the ResultSet to get
your rows.

Craig

[Ed Howland]

Robert, in the first example, i.e. db.execute(), the block is yielding
an array or something that duck types to an array, or at lease
responds to the the join() method. But in the query and prepare
versions, you are getting a SQLite2::ResultSet which does not.

However if you modify it to:
rb(main):006:0> db.query("select * from foobar") do |rs|
irb(main):007:1* rs.each {|row| puts row.join("\t")}
irb(main):008:1> end

hello 1
world 2
=> nil

You get what you want.

Cheers,
Ed

--
Ed Howland
http://greenprogrammer.blogspot.com
http://twitter.com/ed_howland

[Robert Citek]

On Mon, Nov 9, 2009 at 2:11 PM, Robert Citek <robert...@gmail.com> wrote:

> I'm trying to prepare a sql statement to send to sqlite3 via ruby and
> am getting errors when using the query and prepare methods.  Here's my
> code:

Thanks, Craig and Ed. Indeed, I was getting a different class than I
thought I was. Based on your suggestions I verified this by modifying
my code and running it in irb:

irb(main):022:0> db.execute( "select * from foobar" ) do |row|
irb(main):023:1* puts row.class
irb(main):024:1> end
Array
Array
=> nil
irb(main):025:0> db.query( "select * from foobar" ) do |row|
irb(main):026:1* puts row.class
irb(main):027:1> end
SQLite3::ResultSet
=> nil
irb(main):028:0> db.prepare( "select * from foobar" ) do |stmnt|
irb(main):029:1* puts stmnt.class
irb(main):030:1> stmnt.execute do |row|
irb(main):031:2* puts row.class
irb(main):032:2> end
irb(main):033:1> end
SQLite3::Statement
SQLite3::ResultSet
=> nil

Sure enough. Instead of getting an Array, I was getting a ResultSet.

Modified my code and now it works:

irb(main):046:0> # querying the database via execute works
irb(main):047:0* db.execute( "select * from foobar" ) do |row|
irb(main):048:1* puts row.join("\t") ;
irb(main):049:1* end

hello 1
world 2
=> nil

irb(main):050:0>
irb(main):051:0* # querying the database via query method does not work
irb(main):052:0* db.query( "select * from foobar" ) do |rs|
irb(main):053:1* rs.each do |row|
irb(main):054:2* puts row.join("\t") ;
irb(main):055:2* end
irb(main):056:1> end

hello 1
world 2
=> nil

irb(main):057:0>
irb(main):058:0* # querying the database via a prepare does not work
irb(main):059:0* db.prepare( "select * from foobar" ) do |stmnt|
irb(main):060:1* stmnt.execute do |rs|
irb(main):061:2* rs.each do |row|
irb(main):062:3* puts row.join("\t") ;
irb(main):063:3* end
irb(main):064:2> end
irb(main):065:1> end

hello 1
world 2
=> nil

Again, many thanks.

Regards,
- Robert

[Ed Howland]

Just a followup. This page in the API docs covers execute, query and
prepare very well, if you read them carefully.

http://sqlite-ruby.rubyforge.org/sqlite3/classes/SQLite3/Database.html

Notice the Statement.execute returns a ResultSet, but
Statement.execute! returns an array of rows.so your last example could
have been written:
irb(main):003:0> db.prepare("select * from foobar") do |stmt|
irb(main):004:1* stmt.execute! {|row| puts row.join("\t")}
irb(main):005:1> end

hello 1
world 2
=> nil

prepare allows you to bind any variables to placeholders in the sql
string, by calling the statement.bind_params() method. Note how you
can manipulate stuff beforehand (like stripping out malformed stuff
entered by hackers on your web site). Then call either execute method.
db.query is just a convenience method that binds the variables for
you, then calls execute() - not execute!. There should be a
db.query!() method. Oh wait! This is ruby, just modify the class
yourself:

--- file: qbang.rb:
require "sqlite3"

class SQLite3::Database
def query!(sql, *bind_vars, &block)
stmt = prepare(sql)
stmt.bind_params(*bind_vars)
if block_given?
stmt.execute do |result|
result.each(&block)
end
else
return stmt.execute!
end
end
end
---
irb:
irb(main):039:0> load "./qbang.rb"
irb(main):039:0> db = SQLite3::Database.new("./test.db")

irb(main):035:0> db.query!("select * from foobar where foo = ?",
"world") {|row| puts row.join("\t")}
world 2
=> nil

Hope this clears up some things. But remember, I know nothing at all
about SQLite3-ruby. Except that I have to install the gem to make
rails work out of the box.

Thanks for the education. Looks like a nice package.

Ed