I'm trying to match the beginning of the column with the parameter value. Shouldn't CONCAT concatenate the parameter and the % character for which then becomes the argument for the LIKE operator? In other words if I had some jboss-ql that was "LIKE CONCAT('abc','def')", shouldn't I expect the SQL to be "LIKE 'abcdef'" rather than "LIKE ('abc' || 'def')"?

SELECT DISTINCT t0_o.id FROM address t0_o WHERE t0_o.address1 LIKE '?%' OR t0_o.city LIKE '?%' OR t0_o.state LIKE '?%' OR t0_o.country LIKE '?%' OR t0_o.postal_code LIKE '?%'

I've seen people using the substring matches, but those make for horrendous SQL debugging.
--Curt


Dain Sundstrom wrote:

And what would the correct query be?

-dain

On Thursday, February 27, 2003, at 02:37 PM, Curt Johnson wrote:

I've checked the forums, mailing list and for-pay docs, everything seems to say that this will work.
What I get in the debug statements from the CMP engine is:


2003-02-27 15:11:04,182 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.Address.findLikeAddres s] Executing SQL: SELECT DISTINCT t0_o.id FROM address t0_o WHERE t0_o.address1 LIKE (? || '%') OR t0_o.city LIKE (? || '%') OR t0_o.state LIKE (? || '%') OR t0_o.country LIKE (? || '%') OR t0_o.postal_code LIKE (? || '%')

The relevent lines from jbosscmp.xml are

</query-method>
 <jboss-ql>
   <![CDATA[
     SELECT DISTINCT OBJECT(o) FROM Address o
     WHERE o.address1 LIKE CONCAT(?1,'%')
        OR o.city LIKE CONCAT(?2,'%')
        OR o.state LIKE CONCAT(?3,'%')
        OR o.country LIKE CONCAT(?4,'%')
        OR o.postalCode LIKE CONCAT(?5,'%')
   ]]>
 </jboss-ql>
</query>


I'm using JBoss 3.06 + Tomcat 4.1.18 with PostgreSQL 7.3 on RedHat 8.0 with Sun JVM 1.4.1.


Should I file a bug report or is there something I'm missing?


Curt Johnson Tampa Open Source Java Guru




------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user




-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user





------------------------------------------------------- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user

Reply via email to