From: Michael Neumann <neumann@...>
Date: 2002-02-07T03:04:12+09:00
Subject: Re: some dbi questions (probably postres specific)

Martin Maciaszek wrote:
> I'm playing around with dbi and postgres. After a while two problems remained
> that I couldn't solve so far.
> 
> 1. after doing a DBI::DatabaseHandle.select_all I get an array containing
>    the result. In php I could also get the field names for the result. (
>    using pg_fieldname($result, $index)) Is there something similar in
>    ruby's dbi?

The result from select_all is an array of DBI::Row objects. Each
DBI::Row object "remembers" it's column names. You can query it using
 method column_names (or field_names).

  rows = dbh.select_all("SELECT a, b FROM test")
  p rows[0].column_names   #=> ["a", "b"]


> 2. another problem arose when I tried wrapping objects around tables. I
>    created a table with the following structure:
>  Column|  Type   |                     Modifiers                        
> -------+---------+------------------------------------------------------
>  id    | integer | not null default nextval('"test_table_id_seq"'::text)
>  name  | text    | 
>  phone | text    | 
> 
>    now I insert some data into the table with the following code:
>    aConnection.execute "insert into test_table (name, phone) values
>                         ('foo', '42');"
> 
>    the id field is also my primary key, so I would like to know which id
>    got assigned to the values I just inserted. I've been told that perl-dbi
>    and php have some way to get some kind of handle to the last insert.

I know of Mysql having such a function (last_insert_id or something
similar).

>    I came up with two solutions that both look like ugly hacks. 1. I could
>    try to read test_table_id_seq so I would know the last id. This works
>    unless somebody else inserts another record in the mean time. 2. I could
>    ask for exactly the same velues I just inserted into the database. This
>    one looks better but could fail horribly if there is already an entry
>    for "foo" with phone number "42" but a different id.

The following works for me:

  dbh.transaction do
    # first generate a unique id for this table
    id = dbh.select_one("SELECT nextval('"test_table_id_seq"').first

    # now insert row
    dbh.do("INSERT INTO test (id, name, phone) VALUES (?,?,?)", id, "foo", "42")
  end

Regards,

  Michael

-- 
Michael Neumann
merlin.zwo InfoDesign GmbH
http://www.merlin-zwo.de