# Errors when using sqlite3's query and prepare methods

**URL:** <https://rubytalk.org/t/errors-when-using-sqlite3s-query-and-prepare-methods/56145>\
**Category:** ruby-talk\
**Created:** [9 November 2009 19:09 UTC](https://rubytalk.org/t/errors-when-using-sqlite3s-query-and-prepare-methods/56145 "2009-11-09T19:09:36Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Robert\_Citek](https://avatars.discourse-cdn.com/v4/letter/r/5f9b8f/32.png) [@Robert\_Citek](https://rubytalk.org/u/Robert_Citek)\
**Post date:** [9 November 2009 19:09 UTC](https://rubytalk.org/t/errors-when-using-sqlite3s-query-and-prepare-methods/56145/1 "2009-11-09T19:09:36Z")

</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  
&nbsp;&nbsp;CREATE TABLE foobar (foo text, bar int);  
&nbsp;&nbsp;INSERT INTO "foobar" VALUES('hello',1);  
&nbsp;&nbsp;INSERT INTO "foobar" VALUES('world',2)  
&nbsp;&nbsp;eof  
db.execute\_batch(sql)

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

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

# querying the database via a prepare does not work  
db.prepare( "select \* from foobar" ) do |stmnt|  
&nbsp;&nbsp;stmnt.execute do |row|  
&nbsp;&nbsp;&nbsp;&nbsp;puts row.join("\t") ;  
&nbsp;&nbsp;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\>  
&nbsp;&nbsp;from (irb):43  
&nbsp;&nbsp;from /usr/lib/ruby/1.8/sqlite3/database.rb:278:in `query'  
&nbsp;&nbsp;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\>  
&nbsp;&nbsp;from (irb):49  
&nbsp;&nbsp;from /usr/lib/ruby/1.8/sqlite3/statement.rb:166:in `execute'  
&nbsp;&nbsp;from (irb):48  
&nbsp;&nbsp;from /usr/lib/ruby/1.8/sqlite3/database.rb:187:in `prepare'  
&nbsp;&nbsp;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:** ![steve9](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@steve9](https://rubytalk.org/u/steve9)\
**Post date:** [10 November 2009 02:15 UTC](https://rubytalk.org/t/errors-when-using-sqlite3s-query-and-prepare-methods/56145/2 "2009-11-10T02:15:10Z")

</div>

Robert Citek wrote:

> 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  
> &nbsp;&nbsp;CREATE TABLE foobar (foo text, bar int);  
> &nbsp;&nbsp;INSERT INTO "foobar" VALUES('hello',1);  
> &nbsp;&nbsp;INSERT INTO "foobar" VALUES('world',2)  
> &nbsp;&nbsp;eof  
> db.execute\_batch(sql)
> 
> # querying the database via execute works  
> db.execute( "select \* from foobar" ) do |row|  
> &nbsp;&nbsp;puts row.join("\t") ;  
> end
> 
> # querying the database via query method does not work  
> db.query( "select \* from foobar" ) do |row|  
> &nbsp;&nbsp;puts row.join("\t") ;  
> end
> 
> # querying the database via a prepare does not work  
> db.prepare( "select \* from foobar" ) do |stmnt|  
> &nbsp;&nbsp;stmnt.execute do |row|  
> &nbsp;&nbsp;&nbsp;&nbsp;puts row.join("\t") ;  
> &nbsp;&nbsp;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\>  
> &nbsp;&nbsp;from (irb):43  
> &nbsp;&nbsp;from /usr/lib/ruby/1.8/sqlite3/database.rb:278:in `query'  
> &nbsp;&nbsp;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\>  
> &nbsp;&nbsp;from (irb):49  
> &nbsp;&nbsp;from /usr/lib/ruby/1.8/sqlite3/statement.rb:166:in `execute'  
> &nbsp;&nbsp;from (irb):48  
> &nbsp;&nbsp;from /usr/lib/ruby/1.8/sqlite3/database.rb:187:in `prepare'  
> &nbsp;&nbsp;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

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:** ![Robert\_Citek](https://avatars.discourse-cdn.com/v4/letter/r/5f9b8f/32.png) [@Robert\_Citek](https://rubytalk.org/u/Robert_Citek)\
**Post date:** [10 November 2009 12:58 UTC](https://rubytalk.org/t/errors-when-using-sqlite3s-query-and-prepare-methods/56145/3 "2009-11-10T12:58:58Z")

</div>

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

> **···**
>
> On Mon, Nov 9, 2009 at 9:15 PM, steve \<zyzygy@telstra.com\> wrote:
> 
> > 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