db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: Postgres escaping issues
Date Mon, 21 Jun 2004 17:30:16 GMT
hi,

mysql has the same problem because it also uses '\' as the default escape character

SELECT A0.LASTNAME,A0.FIRSTNAME,A0.ID FROM PERSON A0 WHERE A0.FIRSTNAME LIKE 
'h%\%' escape '\'

results in

  Syntax error or access violation,  message from server: "You have an error in 
your SQL syntax.  Check the manual that corresponds to your MySQL server version 
for the right syntax to use near ''\'' at line 1"

ojb also uses '\' as default. but for mysql and postgresql it should be '\\' for 
sapdb and hsqldb '\' is ok . hsql also works with '\\'.

jakob

Jakob Braeuchi wrote:

> hi,
> 
> i think we'd better change the escape character in the example to make 
> it work with postgresql.
> 
> jakob
> 
> Robert S. Sfeir wrote:
> 
>>
>> Tried adding the code to the test, but now the error looks like this:
>>
>> Changed like test to this:
>> '%h\\\\%'
>>
>> I think this is a bug in OJB now since the above string works in psql 
>> (then
>> again psql is kinda whacked).
>>
>> org.apache.ojb.broker.PersistenceBrokerSQLException:
>> org.postgresql.util.PSQLException: ERROR: unterminated quoted string 
>> at or
>> near "'\'"
>> org.apache.ojb.broker.PersistenceBrokerException:
>> org.apache.ojb.broker.PersistenceBrokerSQLException:
>> org.postgresql.util.PSQLException: ERROR: unterminated quoted string 
>> at or
>> near "'\'" at 
>> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe 
>>
>> ferenceBroker.java:240) at
>> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe 
>>
>> ferenceBroker.java:252) at
>> org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(Persis 
>>
>> tenceBrokerImpl.java:1238) at
>> org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery( 
>>
>> DelegatingPersistenceBroker.java:332) at
>> org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery( 
>>
>> DelegatingPersistenceBroker.java:332) at
>> org.apache.ojb.broker.QueryTest.testLikeEscapedCriteria1(QueryTest.java:285) 
>>
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39 
>>
>> ) at 
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl 
>>
>> .java:25)Caused by: org.apache.ojb.broker.PersistenceBrokerSQLException:
>> org.postgresql.util.PSQLException: ERROR: unterminated quoted string 
>> at or
>> near "'\'" at 
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl 
>>
>> .java:318) at 
>> org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.j 
>>
>> ava:74) at 
>> org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:183) 
>> at
>> org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(RsIterator 
>>
>> FactoryImpl.java:58) at
>> org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(Pers 
>>
>> istenceBrokerImpl.java:1824) at
>> org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(Persis 
>>
>> tenceBrokerImpl.java:1354) at
>> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe 
>>
>> ferenceBroker.java:110) at
>> org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryRe 
>>
>> ferenceBroker.java:232) ... 21 moreCaused by:
>> org.postgresql.util.PSQLException: ERROR: unterminated quoted string 
>> at or
>> near "'\'" at 
>> org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139) 
>>
>> at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152) at
>> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100) at
>> org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43) at
>> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j 
>>
>> ava:517) at 
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j 
>>
>> ava:50) at 
>> org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statem 
>>
>> ent.java:233) at 
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl 
>>
>> .java:283) ... 28 more
>>
>>
>> On 6/20/04 8:38 AM, "Robert S. Sfeir" <robert@codepuccino.com> wrote:
>>
>>
>>> Huh... Check this out...
>>>
>>> mytest=# select * from person where firstname LIKE 'r%\\\\%';
>>> id | lastname |  firstname
>>> ----+----------+--------------
>>>  4 | sfeir    | robert%\dude
>>>  5 | sfeir    | robert\dude
>>> (2 rows)
>>>
>>> Seems you need to double escape???
>>>
>>> R
>>>
>>>
>>> On 6/20/04 8:32 AM, "Thomas Dudziak" <tomdz@first.fhg.de> wrote:
>>>
>>>
>>>> Robert, could you also run test with a different escape character ?
>>>> Also, have you checked the postgresql mailing list ? Perhaps this is a
>>>> known behavior with a workaround that we can use. I'd prefer we delay
>>>> the release for a day or two if we can fix this in an easy way (eg.
>>>> without changing OJB too much)
>>>>
>>>> Tom
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message