pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Different query results in psql and Perl DBI with Postgres 7.2.1 3+ messages / 3 participants [nested] [flat]
* Different query results in psql and Perl DBI with Postgres 7.2.1 @ 2004-01-07 11:14 Brendan LeFebvre <brendanl@iname.com> 2004-01-08 12:05 ` Re: Different query results in psql and Perl DBI with Postgres 7.2.1 boyd <tbmoore@bealenet.com> 0 siblings, 1 reply; 3+ messages in thread From: brendanl@iname.com @ 2004-01-07 11:14 UTC (permalink / raw) To: pgsql-sql I have a script that updates one record in a PostgreSQL 7.2.1 database through the Perl DBI. Here is the output from my log, which outputs the query sent to the DBI and the return value: --- Update query: UPDATE item SET status='SOLD' WHERE item_id=1129832 1 rows affected. Update query: UPDATE item SET status='STORE' WHERE item_id=1130081 0E0 rows affected. --- In both cases, the target record exists without question, and in the latter case, I receive the expected "UPDATE 1" when I execute the same query from within psql. 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. Where do I even begin to attempt a diagnosis? ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Different query results in psql and Perl DBI with Postgres 7.2.1 2004-01-07 11:14 Different query results in psql and Perl DBI with Postgres 7.2.1 Brendan LeFebvre <brendanl@iname.com> @ 2004-01-08 12:05 ` boyd <tbmoore@bealenet.com> 2004-01-11 19:17 ` Re: Different query results in psql and Perl DBI with Postgres 7.2.1 Greg Sabino Mullane <greg@turnstep.com> 0 siblings, 1 reply; 3+ messages in thread From: boyd @ 2004-01-08 12:05 UTC (permalink / raw) To: pgsql-sql 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 "$rows rows 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Different query results in psql and Perl DBI with Postgres 7.2.1 2004-01-07 11:14 Different query results in psql and Perl DBI with Postgres 7.2.1 Brendan LeFebvre <brendanl@iname.com> 2004-01-08 12:05 ` Re: Different query results in psql and Perl DBI with Postgres 7.2.1 boyd <tbmoore@bealenet.com> @ 2004-01-11 19:17 ` Greg Sabino Mullane <greg@turnstep.com> 0 siblings, 0 replies; 3+ messages in thread From: Greg Sabino Mullane @ 2004-01-11 19:17 UTC (permalink / raw) To: pgsql-sql -----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----- ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-11 19:17 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-01-07 11:14 Different query results in psql and Perl DBI with Postgres 7.2.1 Brendan LeFebvre <brendanl@iname.com> 2004-01-08 12:05 ` boyd <tbmoore@bealenet.com> 2004-01-11 19:17 ` Greg Sabino Mullane <greg@turnstep.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox