Re: error when using SELECT

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(at)sss(dot)pgh(dot)pa(dot)us>
> Date: July 26, 2005 11:25:14 AM PDT
> To:
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] error when using SELECT
>
>
> Andrew Stewart <astew(at)wam(dot)umd(dot)edu> 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
-------------------------------------------------------------