Greg Sabino Mullane wrote:
> JJ Merelo wrote:
> [...]> that is, column is of type whatever and should be of type whatever > else. The aforementioned column is of type serial. I'm using Perl
Hard to say without knowing what version you have
Before upgrading however, please run the test suite (make test) and send us the results so we can possibly narrow down the problem.
I don't know if this is going to help in some way, but...
I've put up a test case that is failing with DBD::Pg 1.40 and works ok with DBD::Pg 1.32.
Environment is: Pg 8.0.1 Perl 5.8.6 DBI 1.46
1.40 builds and tests fine, while 1.32 has errors (detail can be provided if necessary).
Run of test script with DBD::Pg 1.32:
$ perl t/24_server_side_prepare.t 1..9 ok 1 - database ready ok 2 - create test table ok 3 - insert sth prepared correctly # skipping bind_param(1, undef, SQL_INTEGER) on DBD::Pg < 1.40 # because it triggers error: Can't change TYPE ... to 0 ... # execute()=1 [EMAIL PROTECTED] ok 4 - execute of insert query ok 5 - rollback transaction ok 6 - create test table ok 7 - insert sth prepared correctly ok 8 - execute of insert query ok 9 - rollback transaction
Run of test script with DBD::Pg 1.40:
$perl t/24_server_side_prepare.t
1..9
ok 1 - database ready
ok 2 - create test table
ok 3 - insert sth prepared correctly
# execute()=1 [EMAIL PROTECTED]
ok 4 - execute of insert query
ok 5 - rollback transaction
ok 6 - create test table
ok 7 - insert sth prepared correctly
DBD::Pg::st execute failed: ERROR: column "myfield1" is of type integer but expression is of type character varying
HINT: You will need to rewrite or cast the expression.
not ok 8 - execute of insert query
# Failed test (t/24_server_side_prepare.t at line 83)
# $dbh->err=7 $dbh->errstr=ERROR: column "myfield1" is of type integer but expression is of type character varying
# HINT: You will need to rewrite or cast the expression.
ok 9 - rollback transaction
# Looks like you failed 1 tests of 9.
I've tried to understand what's going on in Pg.pm/xs but I need to dig some more to be somewhat helpful.
Here it is the test script that I used:
------------------8<---------------------------------- #!/usr/bin/perl # Tests about server side prepared statements in Pg # $Id: 24_server_side_prepare.t,v 1.1 2005/02/16 10:50:42 cosimo Exp $ # use Test::More; plan tests => 9; use strict; use warnings; use DBI qw(:sql_types);
sub create_test_table {
my $dbh = $_[0];
$dbh->do(<<SQL);
CREATE TABLE dbdpg_test1 (
myfield1 integer,
myfield2 numeric(5,0),
myfield3 numeric(8,3)
)
SQL
}my $dbh; my $sth; my $ok = 0; my $sql = 'INSERT INTO dbdpg_test1 (myfield1) VALUES (?)';
# Connect to pgperltest database in transactional mode
$dbh = DBI->connect('DBI:Pg:dbname=pgperltest', undef, undef,
{RaiseError=>1, PrintError=>1, AutoCommit=>0}
);
ok( ref $dbh && $dbh->ping(), 'database ready');#
# Prepare first insert statement w/o server side prepare
#
ok( create_test_table($dbh), 'create test table');
eval { $sth = $dbh->prepare($sql, {pg_server_prepare=>0}) };
ok( $sth && ! $@, 'insert sth prepared correctly');#
# The following bind_param() makes the test work
# If you omit this, does not work anymore with DBD::Pg 1.40 (see below)
# with DBD::Pg 1.32 everything works as expected
#
if( DBD::Pg->VERSION() > 1.32 ) {
$sth->bind_param(1, undef, SQL_INTEGER);
} else {
diag('skipping bind_param(1, undef, SQL_INTEGER) on DBD::Pg < 1.40');
diag('because it triggers error: Can\'t change TYPE ...');
}eval { $ok = $sth->execute(99) };
diag(' execute()='.$ok.' [EMAIL PROTECTED]'.$@);
ok( $ok && ! $@, 'execute of insert query');
if( ! $ok ) {
diag(' $dbh->err='.$dbh->err().' $dbh->errstr='.$dbh->errstr());
}
ok( $dbh->rollback(), 'rollback transaction' );#
# This time we repeat all the test without binding param
# Test fails with DBD::Pg 1.40 !
#
$ok = 0;
ok( create_test_table($dbh), 'create test table');
eval { $sth = $dbh->prepare($sql, {pg_server_prepare=>0} ) };
ok( $sth && ! $@, 'insert sth prepared correctly');eval { $ok = $sth->execute(99) };
ok( $ok && ! $@, 'execute of insert query');
if( ! $ok ) {
diag(' $dbh->err='.$dbh->err().' $dbh->errstr='.$dbh->errstr());
}
ok( $dbh->rollback(), 'rollback transaction' );# # End of test # ------------------8<----------------------------------
-- Cosimo
