[sf-perl] PostgreSQL DBD ---- repost: sorry, I had the wrong subject in my previous post

David Fetter david at fetter.org
Thu Mar 31 20:56:14 PST 2005

On Thu, Mar 31, 2005 at 07:41:46PM -0800, Shane Hill wrote:
> > Looking at the DBI docs, it says, "For some drivers the $catalog,
> > $schema, $table, and $field parameters are required (e.g., Pg)..."
> That is from the DBI docs but looking at the docs for DBD::Pg it
> says:
> "If you do not know the name of the sequence, you can provide a
> table name and DBD::Pg will attempt to return the correct value."
> and an example from the DBD::Pg docs is:
> $dbh->last_insert_id(undef,undef,"lii2",undef)
> well, that is what I am doing and I get an undefined return value.
> and yes, I am calling this immediately after I do an insert, as the
> DBD::Pg docs suggest.

The following worked for me on DBD::Pg 1.40, DBI 1.43, PostgreSQL 8.0.1:

test=# \d foo_tab
                              Table "public.foo_tab"
  Column  |  Type   |                          Modifiers
 foo_id   | integer | not null default nextval('public.foo_tab_foo_id_seq'::text)
 foo_text | text    | not null Indexes:
    "foo_tab_pkey" PRIMARY KEY, btree (foo_id)

8<------- code starts here
use strict;
use warnings;

use DBI;

my $dbh = DBI->connect(
        AutoCommit => 0,
        RaiseError => 1,
        PrintError => 1

my $sql = <<SQL;
INSERT INTO foo_tab (foo_text)

my $sth = $dbh->prepare($sql);
$sth->execute('I love the smell of Napalm in the morning');
my $id = $dbh->last_insert_id(undef, undef, 'foo_tab', undef);
if (defined $id) {
    print "Id is >$id<\n";
} else {
    print "d00d!  wtf?!?\n";
8<------- code ends here

> > Actually, $catalog should be undef (no such concept in Pg), but
> > the schema is probably 'public' or your username, unless you're
> > using different schemas.
> I tried both 'public' and my username and I get the same result.
> nada.

Does the above work for you?

> > Anyhow, I'm puzzled, but I have a fix.  After
> > you've checked whether the INSERT succeeded (you're doing that anyway,
> > RIGHT?!? ;) just do:
> > 
> >     SELECT currval('the_name_of_the_appropriate_seq');
> > 
> > Not quite as convenient, and I've got a query in to the DBD::Pg dev
> > team, but it will work.
> not convenient at all and yes I am checking for errors.  I have been
> doing this successfully for years with the Pg module / class using:
> $result->oidStatus

I suspect that Pg gets less maintenance than DBD::Pg.

> but I switch to the DBI in an effort to "standardize" my code and my
> system takes a steaming dump on me.  lovely.

Bummer.  It works at least one place.  Let's see about getting it
working for you :)

David Fetter david at fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

More information about the SanFrancisco-pm mailing list