Re: PostgreSQL 7.4.x/CDBI bug

[prev] [thread] [next] [Date index for 2005/02/27]

I ran your test with no problems on my system
Class::DBI - 0.96
DBD::Pg - 1.32
PostgreSQL - 7.4.7

Have you tried using the set_up_table command in Class::DBI::Pg to do
the setup for you?  Also, have you looked at the actual table you
created in postgres? ( run '\d foo' from within psql ).  Mine looks
like this after creating the table.

 Column |         Type          |                      Modifiers
--------+-----------------------+------------------------------------------=
-----------
 id     | integer               | not null default
nextval('public.foo_id_seq'::text)
 bar    | character varying(20) |

It doesn't sound like a Class::DBI problem to me, and is more likely
in the lower level drivers (DBD::Pg or DBI).  Have you upgraded or
re-installed those components?

Can you figure out what module is actually throwing the error (include
the exact error message in your reply next time).  Is it Postgres that
is throwing the error?

As a last resort, turn on DBI  tracing, or turn on tracing in postgres
to see what exactly is happening in the database (ie what SQL
statements are being executed, and what values are being passed.

I usually add the following simple methods to my Class::DBI base
class, which allows me to turn DBI tracing on and off directly in my
code:

sub trace_on {
  my $self  =3D shift;
  my $level =3D shift || 1;
  my $file =3D shift || '/tmp/dbi.trace';
  $self->db_Main->trace($level, $file);
}
sub trace_off {
  my $self =3D shift;
  $self->db_Main->trace(0);
}

Foo->trace_on;

Cheers,

Cees


On Sun, 27 Feb 2005 11:12:14 +0100, JJ Merelo <jjmerelo@xxxxx.xxx> wrote:
> Hi,
> I have mentioned before the error I had when upgrading to PostgreSQL
> 7.4.x and the latest version of the DBI/DBD::Pg drivers. Now I have
> nailed down to a few bits of code.
> Here's the table:
> CREATE TABLE foo
> (
>   id serial NOT NULL,
>   bar varchar(20)
> )
> --
> Here's the module definition:
> --
> package Foo;
>=20
> use strict;
> use warnings;
>=20
> use base 'Class::DBI::Pg';
> use DBD::Pg qw(:pg_types);
>=20
> __PACKAGE__->connection( "dbi:Pg:dbname=3Dtemplate1" );
> __PACKAGE__->table('foo');
> __PACKAGE__->columns( All =3D> qw( id bar ) );
> __PACKAGE__->sequence( 'foo_id_seq' );
> __PACKAGE__->data_type( id =3D> { pg_type =3D> 'int4'} );
>=20
> 'Sacab=F3'
> --
> Please note the undocumented (or at least, not found in Class::DBI
> docs) data_type. What it does, deep down in the Class::DBI code, is to
> bind a parameter to a type using bind_param($param-number,undef,type).
> It does this correctly, as far as I understand, however, I can't drill
> down from there since it seems to be XS code or whatever. I think I'm
> doing this correctly, but it's hard to say.
> And here's the test code:
> --
> #!/usr/bin/perl
>=20
> use Test::More 'no_plan';
>=20
> use Foo;
>=20
> my $foo =3D Foo->find_or_create( { bar =3D> 'baz'} );
> $foo->update();
> $foo->dbi_commit();
>=20
> isa_ok( $foo, 'Foo' );
> is( $foo->bar, 'baz' );
> --
>=20
> It yields the usual "id is of type varchar when it should be of type
> int" or suchlike (sorry I can't C&P, Linux and PG is in another
> partition). However, I have bound explicitly id to its correct type;
> problem is, it does not seem to pay attention to it, be it in
> DBIx::ContextualFetch or at a higher level. I really don't know.
> I don't know where can the problem be. It seems to be discarding
> explicit type bindings. And I _really_ need this to work, or I'd had
> to do a major refactoring discarding all numeric-type ids, so any help
> will be much appreciated.
>=20
> Thanks!
>=20
> JJ
>=20


--=20
Cees Hek