Square brackets

Ruby
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

On Sun, Feb 14, 2010 at 2:00 PM, Andreu [email protected] wrote:

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,

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.

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

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

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.

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 B. http://agileconsultingllc.com
[email protected]

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.

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

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.