[BioSQL-l] postgres 8.3 will not cast text to integer any longer

[Hilmar Lapp]

Hi Erik, thanks for the report. Given the error message, it looks  
more like the integer (which in reality is a string) can't be  
automatically converted to a string.

That would be equally interesting, though. DBI I thought used to bind  
all parameters as string by default, but maybe that has changed?

The parameter values are indeed all bound generically (and the query  
is created dynamically too), and I'm leaving it up to the DBD drivers  
to do the "Right Thing". I could obviously force everything into type  
string, but that is likely to have it's own repercussions on various  
RDBMSs.

So could you file this as a bug report on bugzilla.open-bio.org  
(category bioperl-db, this is actually not a BioSQL problem), and run  
the following test on your 8.3 instance (which minor version actually?):

CREATE TABLE t1 (a varchar(10), b text, c integer);

SELECT * from t1 WHERE a = 1;
SELECT * from t1 WHERE b = 1;
SELECT * from t1 WHERE c = '1';

INSERT INTO t1 (a,b,c) VALUES ('a','b',1);

SELECT * from t1 WHERE a = 1;
SELECT * from t1 WHERE b = 1;
SELECT * from t1 WHERE c = '1';

SELECT * from t1 WHERE a = 1::text;
SELECT * from t1 WHERE b = 1::text;
SELECT * from t1 WHERE c = integer '1';

DROP TABLE t1;

These work all fine on my 8.1.4 instance.

	-hilmar

On Mar 20, 2008, at 3:24 PM, Erik wrote:
> Hi,
>
> (latest BioSQL, bioperl-db, and bioperl-live installed.)
>
> Postgres 8.3 will not auto-cast text (='character
> varying') to integer any longer, which causes test
> t/16odba.t to fail:
>
>
> ------------- EXCEPTION: Bio::Root::Exception -------------
> MSG: error while executing query in
> Bio::DB::BioSQL::SeqAdaptor::find_by_query: ERROR:
> operator does not exist: character varying = integer
> LINE 1: ...eq.taxon_id FROM bioentry seq WHERE
> seq.identifier = 5456929
>
> It seems likely to cause many similar statements to fail;
> how should this be solved?
>
> I tried to fix it but I couldn't find the place where the
> statement/clauses are put together.
>
>
> Thanks,
>
> Erik Rijkers
>
>
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at lists.open-bio.org
> http://lists.open-bio.org/mailman/listinfo/biosql-l

-- 
===========================================================
: Hilmar Lapp  -:-  Durham, NC  -:-  hlapp at gmx dot net :
===========================================================