Square brackets

ruby-talk
1

I'm using Ruby 1.9.1 and sqlite3 with sqlite3-Ruby (1.2.5)

Doing the following query:
pc1 = db.execute("select count(*) from mytab where(n2=n1)")
I get say: pc1 = [["123"]]

and when using:
pc1 = db.get_first_row("select count(*) from mytab where(n2=n1)")
I get say: pc1 = ["123"]

What I really want is the numeric value alone without the
square brackets, quotes, and the like, as several queries are
'chained' and this gets really annoying.

Ruby 1.8.7 doesn't exibit that behaviour and work as expected.
Am I missing something ?

Thanks, Andreu.

2

What I really want is the numeric value alone without the
square brackets, quotes, and the like, as several queries are
'chained' and this gets really annoying.

Ruby 1.8.7 doesn't exibit that behaviour and work as expected.
Am I missing something ?

? Ruby 1.8.7 works exactly that way for me:

rows = db.execute( "SELECT COUNT(*) FROM users" )

=> [["3"]]

rows[0]

=> ["3"]

rows[0][0]

=> "3"

rows[0][0].to_i

=> 3
(using ruby 1.8.7 (2008-08-11 patchlevel 72) [universal-darwin10.0])

I'm not sure why SQLite3 returns a count as a string, but aside from
that -- you're retrieving a collection of rows, which are each typically
a collection of fields. I'm not sure it would ever make sense to have
it displayed differently, but at least it's easy to post-process :slight_smile:

HTH,

···

On Sun, Feb 14, 2010 at 2:00 PM, Andreu <sys1@sci.org> wrote:
--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
twitter: @hassan

3

Andreu wrote:

I'm using Ruby 1.9.1 and sqlite3 with sqlite3-Ruby (1.2.5)

Doing the following query:
pc1 = db.execute("select count(*) from mytab where(n2=n1)")
I get say: pc1 = [["123"]]

A SQL select in general returns multiple rows, each of which has
multiple columns.

So it's returning an array of rows, with one member, which is an array
of columns, with one member.

and when using:
pc1 = db.get_first_row("select count(*) from mytab where(n2=n1)")
I get say: pc1 = ["123"]

Here you explicitly asked only for the first row, so you get an array of
columns, with one member.

What I really want is the numeric value alone without the
square brackets, quotes, and the like, as several queries are
'chained' and this gets really annoying.

Pull it out, using

pc1 = db.execute(...)[0][0].to_i
or
pc1 = db.execute(...).first.first.to_i

Note this will raise an exception if you get zero rows returned for some
reason, but in this particular query you shouldn't.

to_i is required because sqlite only implements text types.

Ruby 1.8.7 doesn't exibit that behaviour and work as expected.

You need to show exactly what code you are running under both 1.9.1 and
1.8.7, and exactly what return values you get in both cases (p pc1, or
puts pc1.inspect), and also show exactly what versions of the sqlite3
gem you have in both cases.

···

--
Posted via http://www.ruby-forum.com/\.

4

Hassan and Brian, thanks for your answer.
This is the code I'm using:

tbls = db.execute("SELECT name FROM sqlite_master
                    WHERE type='table' ORDER BY name")
tbls.each do |tnam|
  ... some code
end

When using Ruby 1.8.7 I get the expected table names
as TAB1 TAB2 etc, but with 1.9.1 I get [["TAB1"]["TAB2"]] etc
and the 'some code' line does not execute because the table
name includes the square brackets and quotes.
I have done several test, even with sqlite-ruby and amalgalite
and I'm pretty sure something has changed in Ruby, as changing
only the Ruby version changes the value format returned.
I guess will need to use the to_i func as suggested, and wait
for better times...

Thanks for your time, Andreu.

5

That's because the behavior of Array#to_s changed in 1.9. It used to be like Array#join, but now is more like Array#inspect.

You can try changing from an implicit call to to_s "#{tnam}" to an explicit call to join "#{tnam.join}"

-Rob

Rob Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com

···

On Feb 15, 2010, at 10:25 AM, Andreu wrote:

Hassan and Brian, thanks for your answer.
This is the code I'm using:

tbls = db.execute("SELECT name FROM sqlite_master
                  WHERE type='table' ORDER BY name")
tbls.each do |tnam|
... some code
end

When using Ruby 1.8.7 I get the expected table names
as TAB1 TAB2 etc, but with 1.9.1 I get [["TAB1"]["TAB2"]] etc
and the 'some code' line does not execute because the table
name includes the square brackets and quotes.
I have done several test, even with sqlite-ruby and amalgalite
and I'm pretty sure something has changed in Ruby, as changing
only the Ruby version changes the value format returned.
I guess will need to use the to_i func as suggested, and wait
for better times...

Thanks for your time, Andreu.

6

Rob, thanks for your explanation. I feel more 'comfortable' now.
The .join did the trick, although I'm not sure to
completely understand it. (After all, I want to 'split'
or 'remove' instead of 'join') but anyway it works.
Need to spend some time reading the manual.

Best regards, Andreu.

Rob Biedenharn wrote:

···

That's because the behavior of Array#to_s changed in 1.9. It used to be like Array#join, but now is more like Array#inspect.

7

Andreu wrote:

Rob, thanks for your explanation. I feel more 'comfortable' now.
The .join did the trick, although I'm not sure to
completely understand it. (After all, I want to 'split'
or 'remove' instead of 'join') but anyway it works.

Compare:

$ irb --simple-prompt

RUBY_VERSION

=> "1.8.6"

a = ["foo"]

=> ["foo"]

b = ["foo","bar"]

=> ["foo", "bar"]

puts a.to_s

foo
=> nil

puts b.to_s

foobar
=> nil

puts b.join(",")

foo,bar
=> nil

to:

$ irb19 --simple-prompt

RUBY_VERSION

=> "1.9.2"

a = ["foo"]

=> ["foo"]

b = ["foo","bar"]

=> ["foo", "bar"]

puts a.to_s

["foo"]
=> nil

puts b.to_s

["foo", "bar"]
=> nil

puts b.join(",")

foo,bar
=> nil

That is, ["TAB1"].to_s shows just 'tab1' in 1.8, but '["tab1"]' in 1.9.
The data structure returned by execute is the same; it's what you're
doing with it.

Since you know that tnam is an array with one element, I'd say that the
simplest fix is

    name = tnam.first

However

    name = tnam.join

will achieve the same in a more obscure way, since you're taking the one
string element, joining it (to nothing else) and getting the string.

Note also that "puts" special-cases arrays. "puts foo" is not the same
as "puts foo.to_s" if foo is an array. (This is true for 1.8 as well as
1.9)

puts a

foo
=> nil

puts b

foo
bar
=> nil

puts a.to_s

["foo"]
=> nil

puts b.to_s

["foo", "bar"]
=> nil

···

--
Posted via http://www.ruby-forum.com/\.

8

Brian, thanks for your detailed explanation.
It's clear now. I'm going to use 1.9.1 and modify
the program to use the new syntax.

Andreu.