Thread: The escape clause in the SELECT statement in PostgreSQL 8.4
Hello group,
The following is a Hibernate generated statement,
select count(*) as y0_
from view_localized_task this_
inner join TASKS nonlocaliz1_ on this_.non_localized_task_id=nonlocaliz1_.TASK_ID
where (this_.ended>=? or this_.state=? or this_.state=? or this_.state=?)
and this_.submitted<=?
and (nonlocaliz1_.TASK_ID in (select distinct this_.TASK_ID as y0_
from TASKS this_
left outer join TASK_OBJECT_HANDLES taskobject3_ on this_.TASK_ID=taskobject3_.TASK_ID
left outer join OBJECT_HANDLE taskobject1_ on taskobject3_.OBJECT_HANDLE_ID=taskobject1_.ID
where taskobject1_.ID in (select distinct this_.objecthandle_id as y0_
from OBJECT_VISIBILITY_CONTEXT this_
where this_.context_handle_id in (?)))
or nonlocaliz1_.globalTask=?
or lower(this_.userId) like ?
escape '\')
It works with PostgreSQL 8.2. But it fails after I upgrade the database to PostgreSQL 8.4, nothing else is changed.
The jdbc driver is 8.4 build 701.
Here is the error message from the code:
2010-06-07 10:31:35,541 [WARN ] JDBCExceptionReporter - SQL Error: 0, SQLState: 42601
2010-06-07 10:31:35,541 [ERROR] JDBCExceptionReporter - ERROR: unterminated quoted string at or near "'\')"
Position: 718
The value for the last parameter is "superuser".
Thanks in advance for your help.
Harry
Your Photo on Bing.ca: You Could WIN on Canada Day! Submit a Photo Now!
There have been some changes with that. SQL is going to change, too.
Backslash is the default character, no need to specify that.
scott=# select empno from emp where ename='KING';
empno
-------
7839
(1 row)
Time: 18.793 ms
scott=# update emp set ename='K\x09ING' where empno=7839;
UPDATE 1
Time: 32.694 ms
scott=# select ename from emp where empno=7839;
ename
-------------
K ING
(1 row)
Time: 0.440 ms
scott=# select ename from emp where ename like 'K\x09%';
ename
-------------
K ING
(1 row)
Time: 0.418 ms
scott=#
I have no idea how to instruct Hibernate how to generate SQL without the
escape clause. This opens some interesting possibilities. Did you see
this: http://xkcd.com/327/ ?
Sheng Hui wrote:
>
> Hello group,
>
> The following is a Hibernate generated statement,
>
> select count(*) as y0_
> from view_localized_task this_
> inner join TASKS nonlocaliz1_ on
> this_.non_localized_task_id=nonlocaliz1_.TASK_ID
> where (this_.ended>=? or this_.state=? or this_.state=? or this_.state=?)
> and this_.submitted<=?
> and (nonlocaliz1_.TASK_ID in (select distinct this_.TASK_ID as y0_
> from TASKS this_
> left outer join TASK_OBJECT_HANDLES
> taskobject3_ on this_.TASK_ID=taskobject3_.TASK_ID
> left outer join OBJECT_HANDLE
> taskobject1_ on taskobject3_.OBJECT_HANDLE_ID=taskobject1_.ID
> where taskobject1_.ID in (select
> distinct this_.objecthandle_id as y0_
> from
> OBJECT_VISIBILITY_CONTEXT this_
> where
> this_.context_handle_id in (?)))
> or nonlocaliz1_.globalTask=?
> or lower(this_.userId) like ?
> escape '\')
>
> It works with PostgreSQL 8.2. But it fails after I upgrade the
> database to PostgreSQL 8.4, nothing else is changed.
> The jdbc driver is 8.4 build 701.
>
> Here is the error message from the code:
>
> 2010-06-07 10:31:35,541 [WARN ] JDBCExceptionReporter - SQL Error: 0,
> SQLState: 42601
> 2010-06-07 10:31:35,541 [ERROR] JDBCExceptionReporter - ERROR:
> unterminated quoted string at or near "'\')"
> Position: 718
>
> The value for the last parameter is "superuser".
>
> Thanks in advance for your help.
>
> Harry
>
> <http://go.microsoft.com/?linkid=9734381>
> ------------------------------------------------------------------------
> Your Photo on Bing.ca: You Could WIN on Canada Day! Submit a Photo
> Now! <http://go.microsoft.com/?linkid=9734380>
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
Mladen Gogala <mgogala@vmsinfo.com> writes:
> There have been some changes with that.
Not since 8.2. It looks to me like the OP had
standard_conforming_strings turned on in his 8.2 installation
and forgot to duplicate that setting in 8.4.
regards, tom lane
Tom Lane wrote:
Mladen Gogala <mgogala@vmsinfo.com> writes:
There have been some changes with that.
Not since 8.2. It looks to me like the OP had
standard_conforming_strings turned on in his 8.2 installation
and forgot to duplicate that setting in 8.4.
regards, tom lane
Yup, you're right:
scott=# set
standard_conforming_strings=true;
SET
Time: 0.689 ms
scott=# select * from emp where ename like
'%' escape '\';
empno | ename | job | mgr |
hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+------+------+--------
7369 | SMITH | CLERK | 7902 |
1980-12-17 00:00:00 | 800 | | 20
7499 | ALLEN | SALESMAN | 7698 |
1981-02-20 00:00:00 | 1600 | 300 | 30
7521 | WARD | SALESMAN | 7698 |
1981-02-22 00:00:00 | 1250 | 500 | 30
7566 | JONES | MANAGER | 7839 |
1981-04-02 00:00:00 | 2975 | | 20
7654 | MARTIN | SALESMAN | 7698 |
1981-09-28 00:00:00 | 1250 | 1400 | 30
7698 | BLAKE | MANAGER | 7839 |
1981-05-01 00:00:00 | 2850 | | 30
7782 | CLARK | MANAGER | 7839 |
1981-06-09 00:00:00 | 2450 | | 10
7788 | SCOTT | ANALYST | 7566 |
1987-04-19 00:00:00 | 3000 | | 20
7839 | KING | PRESIDENT | |
1981-11-17 00:00:00 | 5000 | | 10
7844 | TURNER | SALESMAN | 7698 |
1981-09-08 00:00:00 | 1500 | 0 | 30
7876 | ADAMS | CLERK | 7788 |
1987-05-23 00:00:00 | 1100 | | 20
7900 | JAMES | CLERK | 7698 |
1981-12-03 00:00:00 | 950 | | 30
7902 | FORD | ANALYST | 7566 |
1981-12-03 00:00:00 | 3000 | | 20
7934 | MILLER | CLERK | 7782 |
1982-01-23 00:00:00 | 1300 | | 10
(14 rows)
Time: 1.197 ms
scott=# select version();
version
--------------------------------------------------------------------------------
--------------------------------
PostgreSQL 8.4.4 on i686-redhat-linux-gnu,
compiled by GCC gcc (GCC) 4.1.2 2008
0704 (Red Hat 4.1.2-46), 32-bit
(1 row)
Time: 0.736 ms
scott=#
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com