Forum: Ruby Bug in SQLite3 Ruby wrapper when results_as_hash=true?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jean-Denis M. (Guest)
on 2009-02-05 18:07
(Received via mailing list)
Hello,

I am in my first days of Ruby, so I may have done something stupid, but
my
search did not uncover it.

It seems to me that the SQLite3 wrapper doesn't return correct results
when
results_as_hash is true. I get spurious entries in the hash.

Here is a redux of my problem:

    require 'rubygems'
    require 'sqlite3'

    #File.delete "test.db"
    db = SQLite3::Database.new("test.db");
    db.results_as_hash=true
    db.execute("create table repertoire (Name TEXT,Firstname
TEXT,Telephone
TEXT)")
    db.execute("insert into repertoire (Name, Firstname, Telephone)
values
('Doe', 'John', '555-123-4567')")
    p db.execute('select * from repertoire;')


The results is indeed a hash, but with spurious entries:

[{"Name"=>"Doe", 0=>"Doe", 1=>"John", 2=>"555-123-4567",
"Telephone"=>"555-123-4567", "Firstname"=>"John"}]

I am running Ruby:
ruby 1.8.6 (2008-03-03 patchlevel 114) [universal-darwin9.0]

Under MacOS X Leopard 10.5.6
With SQLite3 3.4.0

Executing the query with the sqlite3 command line tool returns the
expected
result:

$ sqlite3 test.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> select * from repertoire;
Doe|John|555-123-4567
sqlite>

Any confirmation/workaround/fix?

Many thanks.

Jean-Denis
Ryan D. (Guest)
on 2009-02-05 22:30
(Received via mailing list)
On Feb 5, 2009, at 08:06 , Jean-Denis M. wrote:

>
> The results is indeed a hash, but with spurious entries:
>
> [{"Name"=>"Doe", 0=>"Doe", 1=>"John", 2=>"555-123-4567",
> "Telephone"=>"555-123-4567", "Firstname"=>"John"}]

looks like that is intentional. You can access a value by column name
or index (like an array).
Jean-Denis M. (Guest)
on 2009-02-06 12:05
(Received via mailing list)
Quoting Ryan D. <removed_email_address@domain.invalid>:

> or index (like an array).
>
>

This may be, but it is not documented as such, and it breaks any attempt
to
iterate over the returned hash (without special treatment).

Since I need to iterate over all values for all rows, I am now facing a
choice:

1- Either I iterate using [an array of] hashes, testing each key as I go
2- Or I iterate in the default mode of [an array of] arrays, with no
test, as
each value is present once and only once.

Without the test, option 1 was more attractive as the iterative
treatment
consists of outputting all records one line per column name, followed by
one
line per corresponding value.

With the test, Option 2 becomes equally attractive, and (I suspect),
more
efficient.

Comments welcome.

Jean-Denis
Jean-Denis M. (Guest)
on 2009-02-06 12:15
(Received via mailing list)
Quoting Ryan D. <removed_email_address@domain.invalid>:

> or index (like an array).
>
>

It seems indeed intentional. The Database class uses a [SQL] Statement
class,
which uses a ResultSet class.

The ResultSet.next method has the following comment:

    # For hashes, the column names are the keys of the hash, and the
column
    # types are accessible via the +types+ property.

Yet, the code looks like this:

        [...]
        if @db.results_as_hash
          new_row = HashWithTypes[ *( @stmt.columns.zip( row
).to_a.flatten ) ]
          row.each_with_index { |value,idx| new_row[idx] = value }
          row = new_row
        else [...]

The line beginning with "row.each_with_index" is the one adding the
spurious
entries in the Hash. So this seems intentional, even though the comment
seems to
say otherwise.

My suggestion is to get rid of this line. I commented it out from my
version,
and it now works as I expected.

However, my change will get reversed next time I "gem update", and the
architecture of sqlite3-ruby makes it rather difficult to subclass.

Yet again, I am a Ruby noob, so I welcome suggestions.

Jean-Denis
This topic is locked and can not be replied to.