DBI Question

ruby-talk
1

Hello!

while using dbi (with postgres) I wondered if there is sth similar to dbhost#select_all(sql) for prepared statements.

I got a hint to do:

s = dbh.prepare(sql)
dbh.select_all s

but this doesnt work (I get a driver error) and I also wondered why there can't be a #select_all for a prepared statement itself like:

s = dbh.prepare(sql)
array = s.select_all

wouldn't this be much easier?

thanks!
Dominik

2

Dominik Werder wrote:

Hello!

while using dbi (with postgres) I wondered if there is sth similar to

dbhost#select_all(sql) for prepared statements.

I got a hint to do:

s = dbh.prepare(sql)
dbh.select_all s

but this doesnt work (I get a driver error) and I also wondered why

there

can't be a #select_all for a prepared statement itself like:

s = dbh.prepare(sql)
array = s.select_all

wouldn't this be much easier?

I haven't used the postgres driver but if it is DBI complient you
should be able to do the following:

···

#######
stmt = db.prepare(sql)
# stmt.bind(*vars) <--- optional
array = stmt.execute.fetch_all
stmt.finish # free resources / locks held by the statement
#######

-Charlie

3

I made a mistake in the last post.... see below for correct
(hopefully...) answer :slight_smile:

Charles Mills wrote:

Dominik Werder wrote:
> Hello!
>
> while using dbi (with postgres) I wondered if there is sth similar

to

···

> dbhost#select_all(sql) for prepared statements.
>
> I got a hint to do:
>
> s = dbh.prepare(sql)
> dbh.select_all s
>
> but this doesnt work (I get a driver error) and I also wondered why
there
> can't be a #select_all for a prepared statement itself like:
>
> s = dbh.prepare(sql)
> array = s.select_all
>
> wouldn't this be much easier?

I haven't used the postgres driver but if it is DBI complient you
should be able to do the following:

#######
stmt = db.prepare(sql)
array = stmt.execute(*bind_vars).fetch_all
stmt.finish # free resources / locks held by the statement
#######

-Charlie

4

stmt = db.prepare(sql)
array = stmt.execute(*bind_vars).fetch_all
stmt.finish # free resources / locks held by the statement

thanks a lot!!
there is a small mistake in your code but you pointed me into the right direction :slight_smile:
stmt.execute returns nil so you have to do:

stmt.execute
array=stmt.fetch_all

bye!
Dominik

5

Dominik Werder wrote:

> stmt = db.prepare(sql)
> array = stmt.execute(*bind_vars).fetch_all
> stmt.finish # free resources / locks held by the statement

thanks a lot!!
there is a small mistake in your code but you pointed me into the

right

direction :slight_smile:
stmt.execute returns nil so you have to do:

stmt.execute
array=stmt.fetch_all

Oh, looks like the DBI spec doesn't say what #execute should return:
http://ruby-dbi.sourceforge.net/DBI_SPEC.html
IMO returning self would be the most natural...
another option:
array = db.select_all(sql, *bind_vars)

-Charlie

6

Oh, looks like the DBI spec doesn't say what #execute should return:
http://ruby-dbi.sourceforge.net/DBI_SPEC.html
IMO returning self would be the most natural...

100% ack!
perhaps michael reads this too :slight_smile:

bye!
Dominik