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/
