# Errors when using sqlite3's query and prepare methods

**URL:** <https://www.ruby-forum.com/t/errors-when-using-sqlite3s-query-and-prepare-methods/178706>\
**Category:** Ruby\
**Created:** [November 9, 2009, 8:13pm UTC](https://www.ruby-forum.com/t/errors-when-using-sqlite3s-query-and-prepare-methods/178706 "2009-11-09T20:13:26Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![cluettr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7c8e57/32.png) [@cluettr](https://www.ruby-forum.com/u/cluettr)\
**Post date:** [November 9, 2009, 8:13pm UTC](https://www.ruby-forum.com/t/errors-when-using-sqlite3s-query-and-prepare-methods/178706/1 "2009-11-09T20:13:26Z")

</div>

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/](http://sqlite-ruby.rubyforge.org/sqlite3/)  
[2] [http://sqlite-ruby.rubyforge.org/sqlite3/faq.html](http://sqlite-ruby.rubyforge.org/sqlite3/faq.html)

Regards,

- Robert

---

<div class="post-metadata">

**Author:** ![cluettr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7c8e57/32.png) [@cluettr](https://www.ruby-forum.com/u/cluettr)\
**Post date:** [November 10, 2009, 3:16am UTC](https://www.ruby-forum.com/t/errors-when-using-sqlite3s-query-and-prepare-methods/178706/2 "2009-11-10T03:16:02Z")

</div>

Robert C. wrote:

> # create the database
> 
> end  
> end  
> =\> nil  
> irb(main):046:0\* # querying the database via a prepare does not work  
> from (irb):47  
> Thanks in advance to any pointers.
> 
> [1] [http://sqlite-ruby.rubyforge.org/sqlite3/](http://sqlite-ruby.rubyforge.org/sqlite3/)  
> [2] [http://sqlite-ruby.rubyforge.org/sqlite3/faq.html](http://sqlite-ruby.rubyforge.org/sqlite3/faq.html)
> 
> Regards,
> 
> - Robert

Hello Robert.

Look carefully at the error messages. In the case that works you are  
applying a join. If you look at the type of the object it is  
SQLite3::ResultSet::ArrayWithTypesAndFields. This object responds to  
join.

irb(main):011:0\> db.execute( “select \* from foobar” ) do |row|  
irb(main):012:1\* puts row.join(“\t”) ;  
irb(main):013:1\* puts row.class  
irb(main):014:1\> end  
hello 1  
SQLite3::ResultSet::ArrayWithTypesAndFields  
world 2  
SQLite3::ResultSet::ArrayWithTypesAndFields  
=\> nil

In the failing attempts the message indicates that the object returned  
is SQLite3::ResultSet, which does not respond to join.

Doc indicates that SQLite3::ResultSet responds to each… so

irb(main):019:0\> db.query( “select \* from foobar” ).each do |row|  
irb(main):020:1\* puts row.join(“\t”) ;  
irb(main):021:1\* puts row.class  
irb(main):022:1\> end  
hello 1  
SQLite3::ResultSet::ArrayWithTypesAndFields  
world 2  
SQLite3::ResultSet::ArrayWithTypesAndFields  
=\> nil  
irb(main):023:0\>

This may be a an omission in the documentation of teh gem as there  
appears to be no reference for the  
SQLite3::ResultSet::ArrayWithTypesAndFields object.

---

<div class="post-metadata">

**Author:** ![cluettr](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/7c8e57/32.png) [@cluettr](https://www.ruby-forum.com/u/cluettr)\
**Post date:** [November 10, 2009, 1:59pm UTC](https://www.ruby-forum.com/t/errors-when-using-sqlite3s-query-and-prepare-methods/178706/3 "2009-11-10T13:59:15Z")

</div>

On Mon, Nov 9, 2009 at 9:15 PM, steve [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> world  2  
> SQLite3::ResultSet::ArrayWithTypesAndFields  
> =\> nil

Adding the row.class was the trick, although I get different results  
from you:

irb(main):014:0\* db.execute( “select \* from foobar” ) do |row|  
irb(main):015:1\* puts row.join(“\t”) ;  
irb(main):016:1\* puts row.class  
irb(main):017:1\> end  
hello 1  
Array  
world 2  
Array  
=\> nil

irb(main):022:0\> db.query( “select \* from foobar” ) do |row|  
irb(main):023:1\* puts row.class  
irb(main):024:1\> end  
SQLite3::ResultSet  
=\> nil

irb(main):018:0\> db.query( “select \* from foobar” ).each do |row|  
irb(main):019:1\* puts row.join(“\t”) ;  
irb(main):020:1\* puts row.class  
irb(main):021:1\> end  
hello 1  
Array  
world 2  
Array  
=\> nil

In any case adding the .class method provided the clue for how to fix  
the bug.

Thanks for your help, Steve.

Regards,

- Robert