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 <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/30/2007 01:07 AM
To
pgsql-general@postgresql.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/
