Re: LIKE predicate and '\' character

From: "Andy Hallam" <ahm(at)exel(dot)co(dot)uk>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: LIKE predicate and '\' character
Date: 2001-11-07 12:58:00
Message-ID: 9sbb7b$sbl$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> 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(at)exel(dot)co(dot)uk

"Andy Hallam" <ahm(at)exel(dot)co(dot)uk> wrote in message
news:9sb3ek$r0k$1(at)news(dot)tht(dot)net(dot)(dot)(dot)
> 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(at)exel(dot)co(dot)uk
>
>
>

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Roberto Mello 2001-11-07 13:57:45 Re: Porting Web application written in Oracle 8 PL/SQL to Postgresql
Previous Message Andy Hallam 2001-11-07 12:56:46 LIKE predicate and '\' character