Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Apache Torque Users List" <torque-user@db.apache.org>
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: RE: Sybase LIKE clause underscore wildcard problem
Date: Tue, 19 Sep 2006 15:00:40 -0400
Message-ID: <8F5843B903F59D4C8C6806BB49A3911901841239@dukece-mail3.dukece.com>
In-Reply-To: 
 <8F5843B903F59D4C8C6806BB49A39119018411E1@dukece-mail3.dukece.com>
Thread-Topic: Sybase LIKE clause underscore wildcard problem
Thread-Index: Acbb6ganKmtNdMstTvWQpbrPZx7spwAKcbTgAABgpcAAAXZ+4A==
From: "Greg Monroe" <Greg.Monroe@DukeCE.com>
To: "Apache Torque Users List" <torque-user@db.apache.org>

Hmm, it looks like Sybase is probably following the=20
standard better then some others.  At least a quick search=20
indicated that the standard just defines the escape clause
and no default escape character.

The quick workaround is to use a CUSTOM criteria instead of=20
the LIKE criteria.  You could code your queries as:

criteria.add(CSFieldRegistryPeer.NAME,=20
      "CSFieldRegistry.Name LIKE 'CSCLIENT\\_%' escape '\\'",
      Criteria.CUSTOM);

Note: You may have to use another character then '\' because=20
some DB's may take \' as an escaped '...

Look for CUSTOM in:
http://db.apache.org/torque/releases/torque-3.2/runtime/reference/read-f
rom-db.html

In the long run, should there be a way to specify an escape character?

> -----Original Message-----
> From: Greg Monroe [mailto:Greg.Monroe@DukeCE.com]=20
> Sent: Tuesday, September 19, 2006 2:01 PM
> To: Apache Torque Users List
> Subject: RE: Sybase LIKE clause underscore wildcard problem
>=20
> (Sound of head being twacked...) D'oh, my mind's back now...=20
> _ is a match all.. I'll put some thought into this.
>=20
> > -----Original Message-----
> > From: Greg Monroe [mailto:Greg.Monroe@DukeCE.com]
> > Sent: Tuesday, September 19, 2006 1:55 PM
> > To: Apache Torque Users List
> > Subject: RE: Sybase LIKE clause underscore wildcard problem
> >=20
> > Why do you need to escape the '_'?  According to the SQL=20
> Standard (92=20
> > and on) it's a valid character in identifiers.
> >=20
> > Greg
> >=20
> > > -----Original Message-----
> > > From: Umesh Wagle [mailto:Umesh.Wagle@sos.sungard.com]
> > > Sent: Tuesday, September 19, 2006 8:49 AM
> > > To: torque-dev@db.apache.org; torque-user@db.apache.org
> > > Subject: Sybase LIKE clause underscore wildcard problem
> > >=20
> > > Hello Torque Users/Developers,
> > >=20
> > > =20
> > >=20
> > > Greetings!
> > >=20
> > > =20
> > >=20
> > > I have a workable system with MySQL 4.1  as a database and using=20
> > > Torque 3.2. Everything works fine there. I am trying to port the=20
> > > system to a Sybase 12.5 database.
> > >=20
> > > In one of my module I have a query formed as shown below:=20
> > >=20
> > > =20
> > >=20
> > > =20
> > >=20
> > > SELECT  * FROM CSFieldRegistry
> > >=20
> > > =20
> > >=20
> > > WHERE CSFieldRegistry.Name LIKE 'CSCLIENT\_%'=20
> > >=20
> > > =20
> > >=20
> > > AND CSFieldRegistry.CustomField=3D1
> > >=20
> > > AND CSFieldRegistry.Active=3D1
> > >=20
> > > AND CSFieldRegistry.Editable=3D1
> > >=20
> > > AND CSFieldRegistry.CSFirmID=3D10000021
> > >=20
> > > =20
> > >=20
> > > Please observe the LIKE clause in the above query. It=20
> works fine on=20
> > > MySQL as MySQL knows that a backslash (\) is an escape=20
> sequence for=20
> > > the underscore (_)
> > >=20
> > > =20
> > >=20
> > > But the query fails on Sybase as Sybase expects the query in the=20
> > > following form.
> > >=20
> > > =20
> > >=20
> > > SELECT  * FROM CSFieldRegistry
> > >=20
> > > =20
> > >=20
> > > WHERE CSFieldRegistry.Name LIKE 'CSCLIENT\_%'  escape '\'=20
> > >=20
> > > =20
> > >=20
> > > AND CSFieldRegistry.CustomField=3D1
> > >=20
> > > AND CSFieldRegistry.Active=3D1
> > >=20
> > > AND CSFieldRegistry.Editable=3D1
> > >=20
> > > AND CSFieldRegistry.CSFirmID=3D10000021
> > >=20
> > > =20
> > >=20
> > > Please observe the LIKE clause. It needs to suffix it=20
> with an escape=20
> > > '\'
> > > keyword.
> > >=20
> > > =20
> > >=20
> > > It becomes a little difficult to have a approach which=20
> will work on=20
> > > both the databases. Sybase expects ESCAPE and MySQL works=20
> fine with=20
> > > a backslash '\.'
> > >=20
> > > Sybase will not treat '\' as a default escape sequence=20
> whereas MySQL=20
> > > will.
> > >=20
> > > =20
> > >=20
> > > It would be of great help if you could guide me with a=20
> > > resolution/approach which will make the query work on both the=20
> > > databases (MySQL and Sybase)
> > >=20
> > > =20
> > >=20
> > > Thanks for your help.
> > >=20
> > > =20
> > >=20
> > > Cheers,
> > >=20
> > > Umesh
> > >=20
> > > =20
> > >=20
> > > =20
> > >=20
> > > ******************
> > >=20
> > > Umesh Wagle * Module Lead * SunGard Offshore services (India),=20
> > > Meridian Plaza,Sr.No. 108/8/1 + 2/1, S.B.  Road, Pune - 411053,=20
> > > India
> > >=20
> > > Direct Tel : +91-20-25606197  * Main Tel.: +91-20-25606000 *
> > > Fax.:+91-20-25606222  * umesh.wagle@SOS.SunGard.com *=20
> > > www.sungard.com <http://www.sungard.com/>
> > >=20
> > > =20
> > >=20
> > > CONFIDENTIALITY: This email (including any attachments)=20
> may contain=20
> > > confidential, proprietary and privileged information, and=20
> > > unauthorized disclosure or use is prohibited. If you=20
> received this=20
> > > email in error, please notify the sender and delete this=20
> email from=20
> > > your system. Thank you.
> > >=20
> > > *******************
> > >=20
> > > =20
> > >=20
> > >=20
> >=20
> > Duke CE Privacy Statement
> > Please be advised that this e-mail and any files=20
> transmitted with it=20
> > are confidential communication or may otherwise be privileged or=20
> > confidential and are intended solely for the individual or=20
> entity to=20
> > whom they are addressed.  If you are not the intended recipient you=20
> > may not rely on the contents of this email or any=20
> attachments, and we=20
> > ask that you  please not read, copy or retransmit this=20
> communication,=20
> > but reply to the sender and destroy the email, its=20
> contents, and all=20
> > copies thereof immediately.  Any unauthorized dissemination,=20
> > distribution or copying of this communication is strictly=20
> prohibited.
> >=20
> >=20
> >=20
> >=20
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-user-help@db.apache.org
> >=20
> >=20
>=20
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>=20
>=20

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