Different query results in psql and Perl DBI with Postgres 7.2.1

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / 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>
  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-08 12:05  boyd <tbmoore@bealenet.com>
  parent: Brendan LeFebvre <brendanl@iname.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-11 19:17  Greg Sabino Mullane <greg@turnstep.com>
  parent: boyd <tbmoore@bealenet.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