--St7VIuEGZ6dlpu13
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Folks,

Perhaps this is a simple problem, but it's baffled me. My setup:

NetBSD 1.6
Postgresql 7.2.1
p5-DBI 1.21
p5-DBD-postgresql 1.13nb1
Perl 5.6.1nb7

As user "www" I can use psql to connect to my database and perform
SELECTs, UPDATEs, INSERTs etc. with no problems.

I have a perl script using DBI which I also run as the www user (from
the command line) and it can connect to the db and perform SELECTs with
no problems. However INSERTs and UPDATEs do not work. The exact same
query that works in psql fails to work in the script.

The bizarre part is that there are *no* error messages and the operation
appears to be successful i.e. for an UPDATE it returns that 1 row was
affected even though the operation never took effect!  (verifying with
psql before and after). I have explicity set AutoCommit and PrintError
to on even though they are the defaults. I enabled tracing but I can't
see where it's going wrong. I have attached the output from trace(5) and
my perl script.

Now if I try an UPDATE on a non-existant row it does return a value of
zero as expected (i.e. no row was updated).

It's not a permissions problem; it works fine in psql as the same user
and to verify this, I ran the script as a different user who only has
SELECT access and it returned a permission denied error as expected. So
it's definitely not an access or permission problem.

I have tried both prepare + execute() and do(). I have used the same
code and setup before on a different machine (running Solaris) and it
works perfectly.. 

I have already asked on the dbi mailing list.

Does anyone else have this setup working on 1.6?

-- 
Cillian

--St7VIuEGZ6dlpu13
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename=out

    DBI::db=HASH(0x818cab8) trace level set to 5 in DBI 1.21-nothread
    -> prepare for DBD::Pg::db (DBI::db=HASH(0x818cb54)~0x818cab8 'update users set username = ? where username = ?')
    dbih_setup_handle(DBI::st=HASH(0x810b460)=>DBI::st=HASH(0x810b49c), DBD::Pg::st, 810b46c, Null!)
    dbih_make_com(DBI::db=HASH(0x818cab8), DBD::Pg::st, 144)
    dbih_setup_attrib(DBI::st=HASH(0x810b49c), Err, DBI::db=HASH(0x818cab8)) SCALAR(0x818bc18) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x810b49c), State, DBI::db=HASH(0x818cab8)) SCALAR(0x81bb46c) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x810b49c), Errstr, DBI::db=HASH(0x818cab8)) SCALAR(0x818b7cc) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x810b49c), Handlers, DBI::db=HASH(0x818cab8)) ARRAY(0x818cb18) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x810b49c), Debug, DBI::db=HASH(0x818cab8)) 5 (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x810b49c), FetchHashKeyName, DBI::db=HASH(0x818cab8)) 'NAME' (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x810b49c), HandleError, DBI::db=HASH(0x818cab8)) undef (not defined)
dbd_st_prepare: statement = >update users set username = ? where username = ?<
dbd_st_preparse: statement = >update users set username = ? where username = ?<
    dbd_preparse scanned 2 distinct placeholders
    <- prepare= DBI::st=HASH(0x810b460) at ./test.pl line 10
    -> execute for DBD::Pg::st (DBI::st=HASH(0x810b460)~0x810b49c 'testing2' 'testing')
dbd_bind_ph
         bind :p1 <== 'testing2' (type 0)
dbd_st_rebind
       bind :p1 <== 'testing2' (size 8/9/0, ptype 4, otype 1043)
       bind :p1 <== 'testing2' (size 8/8, otype 1043, indp 0)
dbd_bind_ph
         bind :p2 <== 'testing' (type 0)
dbd_st_rebind
       bind :p2 <== 'testing' (size 7/8/0, ptype 4, otype 1043)
       bind :p2 <== 'testing' (size 7/7, otype 1043, indp 0)
dbd_st_execute
dbd_st_execute: statement = >update users set username = 'testing2' where username = 'testing'<
    <- execute= 1 at ./test.pl line 11
    -> finish for DBD::Pg::st (DBI::st=HASH(0x810b460)~0x810b49c)
    <- finish= 1 at ./test.pl line 12
    -> rows for DBD::Pg::st (DBI::st=HASH(0x810b460)~0x810b49c)
dbd_st_rows
    <- rows= 1 at ./test.pl line 14
    -> disconnect for DBD::Pg::db (DBI::db=HASH(0x818cb54)~0x818cab8)
dbd_db_disconnect
    <- disconnect= 1 at ./test.pl line 18
    -> DESTROY for DBD::Pg::st (DBI::st=HASH(0x810b49c)~INNER)
dbd_st_destroy
    <- DESTROY= undef during global destruction
    -> DESTROY for DBD::Pg::db (DBI::db=HASH(0x818cab8)~INNER)
dbd_db_destroy
    <- DESTROY= undef during global destruction

--St7VIuEGZ6dlpu13
Content-Type: application/x-perl
Content-Disposition: attachment; filename="test.pl"

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

use DBI;

$dbh = DBI->connect("dbi:Pg:dbname=userdb;host=localhost", undef, undef);
$dbh->trace(5);

$sql = $dbh->prepare("update users set username = ? where username = ?");
$sql->execute('testing2', 'testing') or die(DBI::errstr);
$sql->finish();

if ($sql->rows != 1) {
	die "FATAL: Failed to update"; 
}
$dbh->disconnect();

--St7VIuEGZ6dlpu13--