fetchall_arrayref failed since upgrading

[Syl]

Hi group - not sure if my error is coming from the database install,
or from the Perl DBI version

My script used to run perfectly on a Linux server with this config :
/usr/lib/perl5/5.6.0
PostgreSQL 7.1.3
DBI is version 1.20

The new server config is not accepting the fetchall_arrayref :
/usr/lib/perl5/5.8.5
PostgreSQL 8.1.0
DBI is version 1.40

This is my query :
$query="select distinct dossier,sdolink from table where dossier is
not null";
$sth = $dbh_std->prepare($query);
$sth->execute();
$data = $sth->fetchall_arrayref;
$sth->finish;

When I kick off the script from the command line, I am getting this
error :
DBD:g::st execute failed: ERROR: invalid input syntax for
integer: ""
DBD:g::st fetchall_arrayref failed: no statement executing

Any ideas ?

I'm at my wits end - and my boss is getting VERY anxious!

Thanks

 

[Paul Lalli]

Hi group - not sure if my error is coming from the database install,
or from the Perl DBI version

My script used to run perfectly on a Linux server with this config :
/usr/lib/perl5/5.6.0
PostgreSQL 7.1.3
DBI is version 1.20

The new server config is not accepting the fetchall_arrayref :
/usr/lib/perl5/5.8.5
PostgreSQL 8.1.0
DBI is version 1.40

This is my query :
$query="select distinct dossier,sdolink from table where dossier is
not null";
$sth = $dbh_std->prepare($query);
$sth->execute();
$data = $sth->fetchall_arrayref;
$sth->finish;

When I kick off the script from the command line, I am getting this
error :
DBD:g::st execute failed: ERROR: invalid input syntax for
integer: ""
DBD:g::st fetchall_arrayref failed: no statement executing

Any ideas ?

I do not believe you have a Perl problem. You have a PostgreSQL
problem. The execute error above is telling you that the database
itself refused to accept your query. The later fetchall_arrayref error
is simply telling you that you can't fetch anything because you don't
have any actively executing statement (because the execute failed).

I suggest contacting a PostgreSQL group to see if anything relevant
might have changed between versions 7.1.3 and 8.1.0

I would also recommend checking your data very carefully. I know
nothing about Pg, but it looks like it's telling you you've given a
blank string where it expects an integer...

Paul Lalli

 

[Paul Lalli]

I do not believe you have a Perl problem. You have a PostgreSQL
problem.

I suggest contacting a PostgreSQL group to see if anything relevant
might have changed between versions 7.1.3 and 8.1.0

And only *after* I posted this did I notice that it was in fact going
to a PostgreSQL group as well as comp.lang.perl.misc. My apologies for
the faulty assumption.

Paul Lalli

 

[Syl]

Paul - THANK YOU for such a quick reply.

I suspected that at first.
Postgres seems to have changed the naming convention of the columns. In
version 7 the integer column is defined as : int4 while in 8.0 the
integer column simply states : integer
I built the new table using a structure dump of the old - and received
no errors.

Thanks you for helping me isolate the error - it obviously makes it
much easy to know what/where I should focusing on!

 

[Ch Lamprecht]

Hi group - not sure if my error is coming from the database install,
or from the Perl DBI version

This is my query :
$query="select distinct dossier,sdolink from table where dossier is
not null";
$sth = $dbh_std->prepare($query);
$sth->execute();
$data = $sth->fetchall_arrayref;
$sth->finish;

When I kick off the script from the command line, I am getting this
error :
DBD:g::st execute failed: ERROR: invalid input syntax for
integer: ""
DBD:g::st fetchall_arrayref failed: no statement executing

Any ideas ?

Hi,
did you try to run the very same query from psql?

Christoph

 

[Syl]

Hi Christoph - The select syntax is correct. Yes - I didn run the query
from psql and received no errors.