Re: Perl DBI and postgres - Mailing list pgsql-general

Hi,

Well, since we don't seem to have the actual code (you seem to have
"paraphrased" it), I can't tell for sure.

However,  here (assuming this is sort of the way you actually are using
it):
  my $target_sth = $target_dbh->prepare(
               q{ SELECT columns
                  FROM the table }) or "Can't prepare statement:
    $DBI::errstr";

You don't use "die" or "warn" or anything, so you don't know if the prepare
actually worked or not;  you just have a literal, instead.

It is complaining about the fetchrow_array, so it might be there that the
problem is occurring, since you can't fetch something that never executed
correctly.

In this statement:
    $repo_sth = $repo_dbh->prepare("INSERT into thetable
                                      (columns)
                                   VALUES (?, '$data')");

that does not appear to be the real INSERT statement, so who knows if it is
right.  Also, you should probably have a "my" on the $repo_sth (declared at
the top of the subroutine somewhere, since you use it outside the while).

In this statement:
  while (my ($data) = $target_sth->fetchrow_array()) {

fetchrow_array returns an array, not a single value.  I tried doing it this
way on a multi-column SELECT, and you just get the last column in $data
doing it this way - probably not what you want.

I am a little confused why you do a prepare and execute inside a loop using
placeholders, instead of just embedding the data (like you do with $data,
but not $snap_id).  You usually either use placeholders, or build the whole
statement, not both.   If you did the prepare outside the loop, and used
placeholders for both VALUES, you would not need to quote it first.  Also,
if you do embed it in the statement, unless you are absolutely sure that
the 2nd value inserted will never have any quotes or anything in it, you
would probably be wise to call $dbh->quote to properly quote it before you
do the INSERT.

E.g.:
  my $data_q=$repo_dbh->quote($data);
 $repo_sth = $repo_dbh->prepare("INSERT into thetable
                                      (column1, column2)
                                   VALUES (?, $data_q)");

If you don't have RAISE_ERROR turned on, you wouldn't have to do the eval,
you could just check the status after the prepare and execute statements.
E.g., something like:
  if (!($sth=$dbh->prepare($statement))) {
    warn("bad prepare for stmt $statement, error: $DBI::errstr");
    $err++;
  }
  if (!($rc=$sth->execute())) {
    warn("can't execute statement:\n$statement\nDB error: $DBI::errstr");
    $err++;
  }

Also, "finish" is normally used only when you need to make sure no data is
left over after a SELECT (if you only fetched part of a result set), so you
don't really need it on the $repo_sth handle used for the INSERT.  Probably
not on the other sth, either.  From the perldoc on DBI:

           Adding calls to "finish" after each fetch loop is a common
mistake, don't
           do it, it can mask genuine problems like uncaught fetch errors.

I assume that any real "rollback" is done in the calling routine if the
returned value $err is true.  Although, shouldn't you be checking $@  for
errors after each eval?  The way you have it, it will continue through the
while loop, I believe.  I don't know if that is what you want to do.

I could be missing something, too - just took a quick look.

Hope this helps,

Susan





                   

                   
                      david.best@sympatico.ca        To:       pgsql-general@postgresql.org
                   
                           Sent by:                  cc:
                   
                                                     Subject:  [GENERAL] Perl DBI and postgres
                   

                   
                      pgsql-general-owner@pos         |-------------------|
                   
                      tgresql.org                     | [ ] Expand Groups |
                   
                                                      |-------------------|
                   

                   
                           07/28/2005 07:05
                   
                      AM
                   

                   

                   




This is probably just a general DBI question but I get the following
errors when I run my code:

DBD::Pg::st fetchrow_array failed: no statement executing at ./snap.pl
line 110.
DBD::Pg::st fetchrow_array failed: no statement executing at ./snap.pl
line 110.

Line 110 is a disconnect statement:

$target_dbh->disconnect();

I have multiple functions which access a target database, take the
information and put it back into a repository.   They all follow this
format, the database handlers are passed in, statement handlers are
created and closed at the end of the function.

sub free_space {

 my $err=0;
 my ($repo_dbh, $target_dbh, $snap_id) = @_;
 my $target_sth = $target_dbh->prepare(
               q{ SELECT columns
                  FROM the table }) or "Can't prepare statement:
$DBI::errstr";
 $target_sth->execute() or die $DBI::errstr;

  while (my ($data) = $target_sth->fetchrow_array()) {
   eval {
    $repo_sth = $repo_dbh->prepare("INSERT into thetable
                                      (columns)
                                   VALUES (?, '$data')");
    $repo_sth->execute($snap_id) or die $DBI::errstr;
   };
  }
   # check for errors.. If there are any rollback
   if ( $@ ) {
      $err = 1;
   }

 $repo_sth->finish();
 $target_sth->finish();

 return $err;
}

The main function just opens connections to the repository and target
database and closes them at the end with disconnects and that is where the
error is occuring.

Any ideas?


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster







----------------------------------------------------------------------------------------------
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------