You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by Umesh Wagle <Um...@sos.sungard.com> on 2006/09/19 14:49:20 UTC
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.
*******************
RE: Sybase LIKE clause underscore wildcard problem
Posted by Greg Monroe <Gr...@DukeCE.com>.
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
RE: Sybase LIKE clause underscore wildcard problem
Posted by Greg Monroe <Gr...@DukeCE.com>.
(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
RE: Sybase LIKE clause underscore wildcard problem
Posted by Greg Monroe <Gr...@DukeCE.com>.
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