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

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
-------------------------------------------------------------