[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
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect(
'dbi:Pg:dbname=test',
'postgres',
'',
{
AutoCommit => 0,
RaiseError => 1,
PrintError => 1
}
);
my $sql = <<SQL;
INSERT INTO foo_tab (foo_text)
VALUES (?)
SQL
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";
}
$dbh->commit;
$dbh->disconnect;
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 :)
Cheers,
D
--
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