Prev: Berkeley DB BDB Install on Windows
Next: File size vs. Directory size problem
From: Robert Citek on 9 Nov 2009 14:09
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

From: steve on 9 Nov 2009 21:11
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
> 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
>

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.
From: Robert Citek on 10 Nov 2009 07:58
On Mon, Nov 9, 2009 at 9:15 PM, steve <zyzygy(a)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
 | 
Pages: 1
Prev: Berkeley DB BDB Install on Windows
Next: File size vs. Directory size problem