Re: ruby/postgres - getting assoc array of rows?

--- Michael Fuhr <mike(at)fuhr(dot)org> wrote:

> On Sun, Nov 20, 2005 at 02:31:45AM -0800, CSN wrote:
> > I tried:
> > puts "by name: #{row['id']} #{row['name']}"
> >
> > but it exits with:
> > pg.rb:16:in `[]': can't convert String into Integer (TypeError)
>
> What versions of Ruby and ruby-postgres are you using? Did you use
> exactly the code I posted or did you do something different? The
> only way to get a PGrow object (other than building it yourself)
> is from PGresult#each or some method that calls it, so this won't
> work:

I'm using Ruby 1.8 and postgres 0.7.1 (installed via gem). Doing 'gem list -r' I see there's also
this, which I don't have installed:

postgres-pr (0.4.0, 0.3.6, 0.3.5, 0.3.4, 0.3.3, 0.3.2, 0.3.1, 0.3.0, 0.2.2, 0.2.1, 0.2.0, 0.1.1,
0.1.0, 0.0.1)
A pure Ruby interface to the PostgreSQL (>= 7.4) database

My code is nearly identical to what you posted, however PGconn.new('dbname=mydb') gives this
error:
pg.rb:6:in `new': could not translate host name "dbname=mydb" to address: Name or service not
known (PGError)

This in the only connection code I could get to work:
conn = PGconn.connect("localhost", 5432, '', '', "mydb", "user", "password")

My code also differs in that I need to put "require 'rubygems'" before "require 'postgres'",
otherwise I get this error:
`require': no such file to load -- postgres (LoadError)

> conn = PGconn.new('dbname=test')
> result = conn.exec('SELECT 1 AS x')
> row = result[0] # returns an Array
> puts row['x'] # raises TypeError
>
> but this does work:
>
> row = result.to_a[0] # returns a PGrow
> puts row['x']

With that, I get (I'm doing "select * from items order by id limit 10"):
nil
nil
nil
nil
nil
nil
nil
undefined method `[]' for nil:NilClass (NoMethodError)

Here's my complete code I'm using with various lines comments/uncommented. Using "result.to_a[0]"
vs. "result.each", etc. appears to give a different type of row and some of the "puts" lines give
errors where they worked before, or work when they previously gave errors.

require 'rubygems'
require 'postgres'

conn = PGconn.connect("localhost", 5432, '', '', "mydb", "user", "password")
#conn = PGconn.new("dbname=mydb")
#conn = PGconn.connect(:pghost=>"localhost", :dbname=> "mydb", :login=>"user",
:password=>"password")

res = conn.exec("select * from items order by id limit 10;")

#rows=res.collect
#for row in res.result
res.each do |row|
#for row in res.to_a[0]
#puts row[0] + row[1]
puts "#{row['title']}" # error: undefined method `[]' for nil:NilClass (NoMethodError)
#puts "by name: #{row['id']} #{row['title']}" # gives error
#print row # works
#print row.inspect # works

#puts row[0] # works
#puts row.getvalue(0,0) # error: undefined method `getvalue' for "7518":String (NoMethodError)
end

> I'm not sure if the PGrow behavior is version-specific. I don't
> see it mentioned in the ruby-postgres ChangeLog, so I'd have to dig
> a little more to find out.

Thanks for your help!
csn

>
> --
> Michael Fuhr
>

__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs