Thread: error when using SELECT

I'm currently trying to install a set of Perl modules that interact with
a particular SQL schema (bioperl-db with BioSQL, if anyone is familiar
with them), and am running into a problem that appears to be emergent
with 8.0.3.

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.
CONTEXT:  SQL function "substring" statement 1
DBD::Pg::st fetchall_arrayref failed: no statement executing
not ok 32

...At first one of the bioperl-db developers thought that the error
might be from the absence of a space between the '?' and 'FOR', but this
was corrected and yet the error persists.  Can anyone tell me if any
part of that SELECT statement looks erroneous?

Thanks,
Andrew

Andrew Stewart <astew@wam.umd.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

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