Hi Tom,
I solved the problem meanwhile. I was using the SUBSTRING function with
from/length integer arguments. DBD::Pg (this is using perl) binds all
parameters as type VARCHAR by default, so what I had to do was supply
an extra type parameter to the $sth->bind_param() calls so that they
are bound as integers.
The tricky bit was that this used to work perfectly well (i.e., without
specifying type explicitly) with the 7.3.x server I was running before,
but not with 8.x. The reason is that DBD::Pg only uses server-side
prepared statements by default if the server is 8.x or higher, and
expands the statement itself if the server is 7.3.x or lower ...
Thanks for trying to help. I thought I'd share this here since other
people might run into the same problem if they've been using DBD::Pg
since the 7.3.x times.
-hilmar
On Jul 26, 2005, at 11:43 AM, Andrew Stewart wrote:
From: Tom Lane <tgl@xxxxxxxxxxxxx>
Date: July 26, 2005 11:25:14 AM PDT
To:
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: error when using SELECT
Andrew Stewart <astew@xxxxxxxxxxx> writes:
I myself am still very new to PostgreSQL, so I'm having trouble
telling
if there is anything wrong with the postgres transaction that is being
attempted by the bioperl-db maketest. The verbose error output is as
follows...
preparing SELECT statement: SELECT SUBSTRING(seq FROM ? FOR ?) FROM
biosequence WHERE bioentry_id = ?
ok 30
ok 31
DBD::Pg::st execute failed: ERROR: invalid escape string
HINT: Escape string must be empty or one character.
According to the docs, that syntax is
The substring function with three parameters, substring(string from
pattern for escape-character), provides extraction of a substring
that
matches an SQL regular expression pattern.
It would appear that you're supplying an empty string for the second ?
which is a no-no for this particular function.
My guess is that you are trying to port code from another database that
has a different interpretation of this syntax.
regards, tom lane
--
-------------------------------------------------------------
Hilmar Lapp email: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
