how to use QL to have a case insensitive String compare?

[websel]

Hi!

I'm trying to build a finder that uses user input to lookup entries in a database. I'm using CMP and a finder. The finder is case sensitive and that is highly generating errors as the words are sometimes typed in with capitals.

Can i fix this in QL or do i need to create work around?


Wessel

[aloubyansky]

You can use ucase and lcase in JBossQL. select object(o) from O o where lcase(o.name) = lcase(?1)

[chlabreu]

I have a similar problem in the order clause. JBossQL seems to not support ucase there. Is there a workaround for it?

[aloubyansky]

It seems like there is no...

[websel]

For the XDoclet users here's my 5$

This is the finder in jboss

* @jboss.query
* query="SELECT OBJECT(o) FROM Person o WHERE LCASE(o.lastName) = LCASE(?1) ORDER BY o.foreName"
* signature="java.util.Collection findByInitialsLastNameBirthDate(java.lang.String lastName)"

As was written above the UCASE and LCASE did the trick

Thanks!
Wessel

[svadu]

"WebSel" wrote:
For the XDoclet users here's my 5$

This is the finder in jboss

* @jboss.query
* query="SELECT OBJECT(o) FROM Person o WHERE LCASE(o.lastName) = LCASE(?1) ORDER BY o.foreName"
* signature="java.util.Collection findByInitialsLastNameBirthDate(java.lang.String lastName)"

As was written above the UCASE and LCASE did the trick

Thanks!
Wessel

I confirm that this works. For some unknown to me reason UCASE didn't work but LCASE did the trick (jboss 3.2.3)...

[camel]

I like the UCASE solutions posted here better, but what I'm doing for MySQL has been working:

I changed the standardjbosscmp-jdbc.xml file, changing the java.lang.String mapping from a "VARCHAR(250) BINARY" to just "VARCHAR(250)", and MySQL does a case-insensitive compare by default I guess. It was a quick and dirty fix, but has been working fine.

[svadu]

"svadu" wrote:

"WebSel" wrote:
For the XDoclet users here's my 5$

This is the finder in jboss

* @jboss.query
* query="SELECT OBJECT(o) FROM Person o WHERE LCASE(o.lastName) = LCASE(?1) ORDER BY o.foreName"
* signature="java.util.Collection findByInitialsLastNameBirthDate(java.lang.String lastName)"

As was written above the UCASE and LCASE did the trick

Thanks!
Wessel

I confirm that this works. For some unknown to me reason UCASE didn't work but LCASE did the trick (jboss 3.2.3)...

Unfortunately I found that this works only if the fiild value in the database i s in lowercase (that's why LCASE worked for me). Unfrotunately I am not sure how else to make it working (not sure if changing the mapping is the best solution. Was anyone else successfull in this?

[aloubyansky]

How does the SQL look like? Is it correct? If so, it's not a JBoss problem.