Thread: 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
On Aug 30, 2007, at 4:03 AM, Ow Mun Heng wrote: > 2. how do I perform a list of SQL using transactions. eg: like above, > but wrap it into a transaction. assuming $dbh is your open handle to the database via DBI, then you do something like this: $dbh->begin_work() or die; $sth = $dbh->prepare(...) or die; $sth->execute(....) or die; ... more queries as needed... $dbh->commit() or die; Did you read the DBI manuals at all? It has examples.
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.Mun.Heng@wdc.com> Sent by: pgsql-general-owner@postgresql.org 08/30/2007 01:07 AM |
|
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/
----------------------------------------------------