Thread: Re: Different query results in psql and Perl DBI with Postgres 7.2.1

In article <cf7bc53b.0401070314.41bc05ad@posting.google.com>,brendanl@iname.com (Brendan LeFebvre) wrote:

> I have a script that updates one record in a PostgreSQL 7.2.1 database
> through the Perl DBI.
> 

> Where do I even begin to attempt a diagnosis?

Here is the output from a script I ran with no problems under 7.2.1, 
followed by the script:

OUTPUT
---------
DB info : PostgreSQL 7.2.1        Table "item"Column  |  Type   | Modifiers 
---------+---------+-----------item_id | integer | status  | text    | 

doing query < TRUNCATE item > 
-1 rows affected
doing query < INSERT INTO item VALUES(1129832,'BACKORDER') > 
1 rows affected
doing query < INSERT INTO item VALUES(1130081,'OUTOFSTOCK') > 
1 rows affected
doing query < UPDATE item SET status='SOLD' WHERE item_id=1129832 > 
1 rows affected
doing query < UPDATE item SET status='STORE' WHERE item_id=1130081 > 
1 rows affected

SCRIPT
------
#!/usr/bin/perl -w

use strict;
use DBI;

our $DBH;
sub dienicely {   warn "$DBH->errstr";   $DBH->disconnect;
}

sub runquery {   my $sql = shift;   print "doing query < $sql > \n";   my $rows = $DBH->do($sql) or dienicely;   print
"$rowsrows affected\n";
 
}

$DBH = DBI->connect("dbi:Pg:dbname=boyd","","");
my $info = $DBH->get_info(17) .' '. $DBH->get_info(18);
$info =~ s/0//g; # the get_info adds extraneous '0' to the version number
print "DB info : $info\n";
print `psql -c '\\d item'`;

runquery("TRUNCATE item");

runquery( "INSERT INTO item VALUES(1129832,'BACKORDER')" );

runquery( "INSERT INTO item VALUES(1130081,'OUTOFSTOCK')" );

runquery( "UPDATE item SET status='SOLD' WHERE item_id=1129832" );

runquery( "UPDATE item SET status='STORE' WHERE item_id=1130081" );

$DBH->disconnect;

Hope that helps.
Boyd

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Brendan LeFebvre writes:
> Even more perplexing: the "0E0" occurs ONLY when setting status to
> 'STORE', and not 100% of the time. (though it happens far more often
> than not.) It seems to operate in stretches, too: when I am getting
> 1-row updates back from the latter query type, it happens several
> times in succession.
"0E0" is perl's way of saying "0 but true", which means that the
statement succeeded, but did not return any rows. If it simply
returned a "0", we would not be able to differentiate between
an error (false) and no rows affected (0).
> Where do I even begin to attempt a diagnosis?
It looks as thogh the row you are trying to update does not
exist. Not much more advice is possible without seeing a more
complete sample of the code.
"boyd" writes:
> $info =~ s/0//g; # the get_info adds extraneous '0' to the version number
It is not extraneous: it is required per the ODBC spec. For a simpler
number, try using this:
my $pgversion = $dbh->{private_dbdpg}{server_version};
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200401111401
-----BEGIN PGP SIGNATURE-----
iD8DBQFAAaEwvJuQZxSWSsgRAk59AJ4zeNNm225TdecB2wgcQnFIJNqpmgCePmdX
XAPC7vVv+517CR2g3p/6U6c=
=oSYK
-----END PGP SIGNATURE-----