db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Greg Monroe" <Greg.Mon...@DukeCE.com>
Subject RE: Sybase LIKE clause underscore wildcard problem
Date Tue, 19 Sep 2006 19:00:40 GMT
Hmm, it looks like Sybase is probably following the 
standard better then some others.  At least a quick search 
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 
the LIKE criteria.  You could code your queries as:

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

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

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


Mime
View raw message