errors when using sqlite3's query and prepare methods

Discussion in 'Ruby' started by Robert Citek, Nov 9, 2009.

  1. Robert Citek

    Robert Citek Guest

    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
     
    Robert Citek, Nov 9, 2009
    #1
    1. Advertising

  2. Robert Citek

    steve Guest

    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.
     
    steve, Nov 10, 2009
    #2
    1. Advertising

  3. Robert Citek

    Robert Citek Guest

    On Mon, Nov 9, 2009 at 9:15 PM, steve <> wrote:
    > Look carefully at the error messages. =C2=A0In the case that works you ar=

    e
    > applying a join. =C2=A0If you look at the type of the object it is
    > SQLite3::ResultSet::ArrayWithTypesAndFields. This object responds to join=
     
    Robert Citek, Nov 10, 2009
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mark Goldin

    Errors, errors, errors

    Mark Goldin, Jan 17, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    1,027
    Mark Goldin
    Jan 17, 2004
  2. =?Utf-8?B?SmVyZW15?=

    Help - How to prepare for release?

    =?Utf-8?B?SmVyZW15?=, Jan 21, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    311
    Kevin Spencer
    Jan 21, 2004
  3. Jeffrey 'jf' Lim
    Replies:
    5
    Views:
    538
    Jeffrey 'jf' Lim
    Apr 9, 2007
  4. SunSw0rd
    Replies:
    4
    Views:
    288
    SunSw0rd
    Jul 2, 2009
  5. DBI prepare and fetch

    , Aug 30, 2005, in forum: Perl Misc
    Replies:
    5
    Views:
    157
Loading...

Share This Page