Re: The escape clause in the SELECT statement in PostgreSQL 8.4

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