Thread: accessing PG using Perl:DBI

accessing PG using Perl:DBI

From
Ow Mun Heng
Date:
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



Re: accessing PG using Perl:DBI

From
Vivek Khera
Date:
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.


Re: accessing PG using Perl:DBI

From
SCassidy@overlandstorage.com
Date:

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

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/
----------------------------------------------------