PostgreSQL - 7.1.3  (installed on Linux 2.4.2-2)
PSQLODBC.DLL - 07.01.0007
Visual C++ - 6.0

I sent a previous mail with regard to using the '\' (backslash) character in
an SQL SELECT statement.
The outcome was that postgres does not escape the '\' itself - I need to do
it myself before submitting the SQL - fair enough, I now do this.

i.e
instead of
    mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
I now do
    mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';

BUT, if I use the LIKE predicate I have to escape the escape.

i.e
    mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';


Now this must be treated as a bug.
As you can see it is not an error with the PSQLODBC driver as I ran the SQL
from the command line with the same results.
I am presuming that the backend parsing logic around the LIKE prodicate is
ignoring the '\'.

Is anyone working on this ?. Can anyone send me a fix, as without this I'm
screwed.

Thanks for any help

Andy.
ahm@exel.co.uk






Wed, 7 Nov 2001 12:56:46 -0000
"Andy Hallam" <ahm@exel.co.uk>
LIKE predicate and '\' character
> I sent a previous mail with regard to using the '\' (backslash) character
in
> an SQL SELECT statement.
> The outcome was that postgres does not escape the '\' itself - I need to
do
> it myself before submitting the SQL - fair enough, I now do this.

I have just written a java app that uses a single '\' in an SQL SELECT
statement and unlike my C application that uses the PSQLODBC driver this
*DOES* return data. To me this says that the problem of having to escape the
'\' myself (as I have to do in my C++ ODBC application) has already been
addressed in the Java driver, and so I do not need to escape it myself in my
Java application.
If this problem has been addressed in the Java driver then surely (for
conformity) it should also be addressed in the ODBC driver ?.


Here is my Java code :
...
        try {
                con = DriverManager.getConnection(url, "postgres",
"postgres");
        }
        catch (Exception e) {
            MyOutput(e.getMessage());
            System.exit(1);
        }

        try {
           String strPart;

            strPart = "A\\B";
MyOutput("strPart: <" + strPart + ">");

            strSQL = "SELECT partdesc FROM partmaster WHERE partnum = ?";
MyOutput("SELECT SQL: <" + strSQL + ">");

            PreparedStatement pstmt = con.prepareStatement(strSQL);
            pstmt.setString(1, strPart);

            result = pstmt.executeQuery();

            while (result.next()) {
                data = result.getString(1);
MyOutput("DATA FETCHED: Partdesc = <" + result.getString(1) + ">");
            }
        }
        catch (Exception e) {
            MyOutput(e.getMessage());
            System.exit(1);
        }

Here is my program output:

strPart: <A\B>
SELECT SQL: <SELECT partdesc FROM partmaster WHERE partnum = ?>
DATA FETCHED: Partdesc = <AB SLASH TEST>


Java does have the same problem with the LIKE predicate however, as to
return any data I need to change my code to :

...
strPart = "A\\\\B";
...
strSQL = "SELECT partdesc FROM partmaster WHERE partnum LIKE ?";

Comments please.

Andy
ahm@exel.co.uk

"Andy Hallam" <ahm@exel.co.uk> wrote in message
news:9sb3ek$r0k$1@news.tht.net...
> PostgreSQL - 7.1.3  (installed on Linux 2.4.2-2)
> PSQLODBC.DLL - 07.01.0007
> Visual C++ - 6.0
>
> I sent a previous mail with regard to using the '\' (backslash) character
in
> an SQL SELECT statement.
> The outcome was that postgres does not escape the '\' itself - I need to
do
> it myself before submitting the SQL - fair enough, I now do this.
>
> i.e
> instead of
>     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
> I now do
>     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';
>
> BUT, if I use the LIKE predicate I have to escape the escape.
>
> i.e
>     mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';
>
>
> Now this must be treated as a bug.
> As you can see it is not an error with the PSQLODBC driver as I ran the
SQL
> from the command line with the same results.
> I am presuming that the backend parsing logic around the LIKE prodicate is
> ignoring the '\'.
>
> Is anyone working on this ?. Can anyone send me a fix, as without this I'm
> screwed.
>
> Thanks for any help
>
> Andy.
> ahm@exel.co.uk
>
>
>





Wed, 7 Nov 2001 12:58:00 -0000
"Andy Hallam" <ahm@exel.co.uk>
Re: LIKE predicate and '\' character
On Wed, Nov 07, 2001 at 12:56:46PM -0000, Andy Hallam wrote:

 The PostgreSQL parser "eat" one '\' on arbitrary place in query. You
 you want put '\' to some function (operator) you must use '\\'. 

test=# select '\\';
?column?
----------
\
(1 row)

test=# select '\\\\';
?column?
----------
\\
(1 row)

test=# select 'hello\\pg' like 'hello\\pg';
?column?
----------
f
(1 row)

test=# select 'hello\\pg' like 'hello\\\\pg';
?column?
----------
 t
 (1 row)
 
        Karel


> PostgreSQL - 7.1.3  (installed on Linux 2.4.2-2)
> PSQLODBC.DLL - 07.01.0007
> Visual C++ - 6.0
> 
> I sent a previous mail with regard to using the '\' (backslash) character in
> an SQL SELECT statement.
> The outcome was that postgres does not escape the '\' itself - I need to do
> it myself before submitting the SQL - fair enough, I now do this.
> 
> i.e
> instead of
>     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
> I now do
>     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';
> 
> BUT, if I use the LIKE predicate I have to escape the escape.
> 
> i.e
>     mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';
> 
> 
> Now this must be treated as a bug.
> As you can see it is not an error with the PSQLODBC driver as I ran the SQL
> from the command line with the same results.
> I am presuming that the backend parsing logic around the LIKE prodicate is
> ignoring the '\'.
> 
> Is anyone working on this ?. Can anyone send me a fix, as without this I'm
> screwed.
> 
> Thanks for any help
> 
> Andy.
> ahm@exel.co.uk
> 
> 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Wed, 7 Nov 2001 15:11:11 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: LIKE predicate and '\' character
"Andy Hallam" <ahm@exel.co.uk> writes:
> BUT, if I use the LIKE predicate I have to escape the escape.
> Now this must be treated as a bug.

It's not a bug, it's the defined behavior of LIKE.  See
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/functions-matching.html#FUNCTIONS-LIKE

You might find it more convenient to select a different escape
character for LIKE.

			regards, tom lane

Wed, 07 Nov 2001 12:21:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: LIKE predicate and '\' character 
On Wed, 7 Nov 2001, Andy Hallam wrote:

> PostgreSQL - 7.1.3  (installed on Linux 2.4.2-2)
> PSQLODBC.DLL - 07.01.0007
> Visual C++ - 6.0
>
> I sent a previous mail with regard to using the '\' (backslash) character in
> an SQL SELECT statement.
> The outcome was that postgres does not escape the '\' itself - I need to do
> it myself before submitting the SQL - fair enough, I now do this.
>
> i.e
> instead of
>     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
> I now do
>     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';
>
> BUT, if I use the LIKE predicate I have to escape the escape.
>
> i.e
>     mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';
>
>
> Now this must be treated as a bug.

Postgres *also* treats \ as the default LIKE escape character.
Use LIKE '<string>' ESCAPE '' (or some other character if
you want to use the like escaping for %, etc).



Wed, 7 Nov 2001 09:23:27 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: LIKE predicate and '\' character
Did you actually read my mail posting???

> I sent a previous mail with regard to using the '\' (backslash) character
in
> an SQL SELECT statement.
> The outcome was that postgres does not escape the '\' itself - I need to
do
> it myself before submitting the SQL - fair enough, I now do this.

I already know what you have just tried to explain to me.

My question is not about having to escape a '\' character but the fact that
the behaviour is not consistent when using the LIKE predicate. I.e - you
have to escape the escape when using LIKE.

Andy.






"Karel Zak" <zakkr@zf.jcu.cz> wrote in message
news:20011107151110.C6354@zf.jcu.cz...
> On Wed, Nov 07, 2001 at 12:56:46PM -0000, Andy Hallam wrote:
>
>  The PostgreSQL parser "eat" one '\' on arbitrary place in query. You
>  you want put '\' to some function (operator) you must use '\\'.
>
> test=# select '\\';
> ?column?
> ----------
> \
> (1 row)
>
> test=# select '\\\\';
> ?column?
> ----------
> \\
> (1 row)
>
> test=# select 'hello\\pg' like 'hello\\pg';
> ?column?
> ----------
> f
> (1 row)
>
> test=# select 'hello\\pg' like 'hello\\\\pg';
> ?column?
> ----------
>  t
>  (1 row)
>
>         Karel
>
>
> > PostgreSQL - 7.1.3  (installed on Linux 2.4.2-2)
> > PSQLODBC.DLL - 07.01.0007
> > Visual C++ - 6.0
> >
> > I sent a previous mail with regard to using the '\' (backslash)
character in
> > an SQL SELECT statement.
> > The outcome was that postgres does not escape the '\' itself - I need to
do
> > it myself before submitting the SQL - fair enough, I now do this.
> >
> > i.e
> > instead of
> >     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
> > I now do
> >     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';
> >
> > BUT, if I use the LIKE predicate I have to escape the escape.
> >
> > i.e
> >     mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';
> >
> >
> > Now this must be treated as a bug.
> > As you can see it is not an error with the PSQLODBC driver as I ran the
SQL
> > from the command line with the same results.
> > I am presuming that the backend parsing logic around the LIKE prodicate
is
> > ignoring the '\'.
> >
> > Is anyone working on this ?. Can anyone send me a fix, as without this
I'm
> > screwed.
> >
> > Thanks for any help
> >
> > Andy.
> > ahm@exel.co.uk
> >
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> --
>  Karel Zak  <zakkr@zf.jcu.cz>
>  http://home.zf.jcu.cz/~zakkr/
>
>  C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



Thu, 8 Nov 2001 14:10:09 -0000
"Andy Hallam" <ahm@exel.co.uk>
Re: LIKE predicate and '\' character
I have since found out that Postgres treats \ as the default LIKE escape
character. (Courtesy Stephan Szabo)

I will learn to live with this 'feature'.

Thanks.

Andy

"Andy Hallam" <ahm@exel.co.uk> wrote in message
news:9se3qn$1dju$1@news.tht.net...
> Did you actually read my mail posting???
>
> > I sent a previous mail with regard to using the '\' (backslash)
character
> in
> > an SQL SELECT statement.
> > The outcome was that postgres does not escape the '\' itself - I need to
> do
> > it myself before submitting the SQL - fair enough, I now do this.
>
> I already know what you have just tried to explain to me.
>
> My question is not about having to escape a '\' character but the fact
that
> the behaviour is not consistent when using the LIKE predicate. I.e - you
> have to escape the escape when using LIKE.
>
> Andy.
>
>
>
>
>
>
> "Karel Zak" <zakkr@zf.jcu.cz> wrote in message
> news:20011107151110.C6354@zf.jcu.cz...
> > On Wed, Nov 07, 2001 at 12:56:46PM -0000, Andy Hallam wrote:
> >
> >  The PostgreSQL parser "eat" one '\' on arbitrary place in query. You
> >  you want put '\' to some function (operator) you must use '\\'.
> >
> > test=# select '\\';
> > ?column?
> > ----------
> > \
> > (1 row)
> >
> > test=# select '\\\\';
> > ?column?
> > ----------
> > \\
> > (1 row)
> >
> > test=# select 'hello\\pg' like 'hello\\pg';
> > ?column?
> > ----------
> > f
> > (1 row)
> >
> > test=# select 'hello\\pg' like 'hello\\\\pg';
> > ?column?
> > ----------
> >  t
> >  (1 row)
> >
> >         Karel
> >
> >
> > > PostgreSQL - 7.1.3  (installed on Linux 2.4.2-2)
> > > PSQLODBC.DLL - 07.01.0007
> > > Visual C++ - 6.0
> > >
> > > I sent a previous mail with regard to using the '\' (backslash)
> character in
> > > an SQL SELECT statement.
> > > The outcome was that postgres does not escape the '\' itself - I need
to
> do
> > > it myself before submitting the SQL - fair enough, I now do this.
> > >
> > > i.e
> > > instead of
> > >     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\me';
> > > I now do
> > >     mydb=# SELECT * FROM users WHERE id = 'WORKGROUP\\me';
> > >
> > > BUT, if I use the LIKE predicate I have to escape the escape.
> > >
> > > i.e
> > >     mydb=# SELECT * FROM users WHERE id LIKE 'WORKGROUP\\\\me';
> > >
> > >
> > > Now this must be treated as a bug.
> > > As you can see it is not an error with the PSQLODBC driver as I ran
the
> SQL
> > > from the command line with the same results.
> > > I am presuming that the backend parsing logic around the LIKE
prodicate
> is
> > > ignoring the '\'.
> > >
> > > Is anyone working on this ?. Can anyone send me a fix, as without this
> I'm
> > > screwed.
> > >
> > > Thanks for any help
> > >
> > > Andy.
> > > ahm@exel.co.uk
> > >
> > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
> >
> > --
> >  Karel Zak  <zakkr@zf.jcu.cz>
> >  http://home.zf.jcu.cz/~zakkr/
> >
> >  C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>



Thu, 8 Nov 2001 14:11:56 -0000
"Andy Hallam" <ahm@exel.co.uk>
Re: LIKE predicate and '\' character