Re: accessing PG using Perl:DBI

First, read the Perl DBI documentation that is relevant:
perldoc DBD::Pg
perldoc DBI

Your examples do not make sense. You "prepare" a SQL statement, not just
data. E.g.:

$sth = $dbh->prepare("INSERT INTO test3(nameval, boolval) VALUES (?, ?)")
or die($sth->errstr);
foreach my $nm ('Joe', 'Fred', 'Sam') {
$sth->bind_param(1, $nm);
$sth->bind_param(2, 'true');
$sth->execute;
die($sth->errstr) if $sth->err; #very important to check for errors,
if RaiseError not set
print "inserted $nm\n";
}

or
$sth = $dbh->prepare("INSERT INTO test3(nameval, boolval) VALUES (?, ?)")
or die($sth->errstr);
foreach my $nm ('Joe', 'Fred', 'Sam') {
$sth->execute($nm, 'true');
die($sth->errstr) if $sth->err;
print "inserted $nm\n";
}
whichever form you like better.

For a repeated SELECT:
$sth = $dbh->prepare("SELECT nameval, boolval from test3 where nameval =
?") or die($sth->errstr);
foreach my $nm ('Joe', 'Fred', 'Sam') {
$sth->execute($nm);
while (@data = $sth->fetchrow_array) {
print "data: $data[0] $data[1]\n";
}
}

If I understood question 1, I don't see why you would even want to string
multiple SQL statements together. They can only be actually executed
serially anyway, one at a time. And, you really should check for errors
after each statement executed, too. Of course, you can use "do" instead
of "prepare" and "execute" for non-SELECT statements with no placeholders
(internally, it does the prepare/execute for you).

You only use "bind_param" if using placeholders in the prepared statement.
If you have varchar data in an INSERT or UPDATE, but are not using
placeholders, you need to use $dbh->quote($txtval) to properly escape
data, e.g.:

$bq=$dbh->quote('false');
foreach my $nm ('Joe', 'Fred', 'Sam') {
$nameq=$dbh->quote($nm);
$rows_affected = $dbh->do("INSERT into test3(nameval, boolval) VALUES
($nameq, $bq)");
die($dbh->errstr) if (! $rows_affected);
print "inserted $rows_affected row: $nm\n";
}

To wrap the whole thing in a transaction (this is a simple example, see
the perldoc documentation for a more robust example):

$dbh->{AutoCommit} = 0; #assuming that AutoCommit was previously set to
1
$sth = $dbh->prepare("INSERT INTO test3(nameval, boolval) VALUES (?, ?)");
foreach my $nm ('Joe', 'Fred', 'Sam') {
$sth->execute($nm, 'true');
die($sth->errstr) if $sth->err;
print "inserted $nm\n";
}
$dbh->commit;

Lots of examples are in the perldoc documentation.

Susan Cassidy

Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com>
Sent by: pgsql-general-owner(at)postgresql(dot)org
08/30/2007 01:07 AM

To
pgsql-general(at)postgresql(dot)org
cc

Subject
[GENERAL] accessing PG using Perl:DBI

Hi all,

I'm sure some of you guys do perl-dbi to access perl. need some
pointers. (pg specific I guess)

1. Possible to execute queries to PG using multiple statemments?
eg:
prepare("A")
bind_param($A)
execute()
prepare("BB")
bind_param($B)
execute()
prepare("CC")
bind_param($B)
execute()

right now, I found that this works..

prepare("A;BB;CC")
but not sure how bind_param will work in this context

2. how do I perform a list of SQL using transactions. eg: like above,
but wrap it into a transaction.

Many Thanks

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

----------------------------------------------------
Tiered Data Protection Made Simple
http://www.overlandstorage.com/
----------------------------------------------------