EJB-QL: Wrong SQL query generated for TRUE on MySQL using JB

[mrauch]

My EJB-QL query

SELECT pa.stationNumber FROM PostAssignment AS pa
WHERE pa.courseCombination.race.id = ?1 AND
pa.rT = TRUE

gets translated into the following SQL statement
2003-07-12 21:20:00,279 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.PostAssignment.ejbSelectRTStationNumbersByRace] Executing SQL: SELECT DISTINCT t0_pa.stationNumber FROM PostAssignment t0_pa, CourseCombination t2_pa_courseCombination, Race t1_pa_courseCombination_race WHERE (t1_pa_courseCombination_race.id = ? AND t0_pa.rT = true) AND (t0_pa.combination_fk=t2_pa_courseCombination.id AND t2_pa_courseCombination.race_fk=t1_pa_courseCombination_race.id)

On MySQL, executing this query results in a "Unknown column 'true' in 'where clause'", because the TRUE literal is not known. I figured out that I could fix this by changing the type-mapping for MySQL to use (1=1) as boolean true value, so I modified standardjbosscmp-jdbc.xml with

<type-mapping>
mySQL
<true-mapping>(1=1)</true-mapping>
<false-mapping>(1=0)</false-mapping>

but that didn't change anything, the generated SQL looks still the same. I'm running JBoss 3.0.0 so I'm wondering if my approach to fix the problem isn't supported in this version. If possible, I would like to stick to 3.0.0 because our software was tested on this version and we don't have the resources to run tests on another version before the system goes in production.

Any suggestions on how to solve this problem?

[vashistvishal]

Now, For one parameter u are passing argument through the method which will implemt this query that is "?1" but for another you are assigning value in query "true", why dont u pass the arguement same as argument one as
"?2" and see what happens.
I cant see anything wrong in the query.
Another point to make is if you are using MySql, then what i understand is you have to cretae a another file similar to standardjbosscmp-jdbc.xml with a different name of course and then do ur customized changes for MySql in that file, rather then changing yr default file.

I hope this helps.

[mrauch]

I wasn't passing the boolean value in as parameter because I'm only interested in the objects that have this flag set to true, I don't have a use-case that needs the objects with the flag set to false.

I followed your suggestion to pass in this value as parameter ?2 and it worked! It seems that the conversion from Boolean objects to SQL query works, but not the parsing of the TRUE literal in the query itself. Now I get the objects back I'm interested in. The modifications of standardjbosscmp-jdbc.xml I posted aren't necessary to get it working. Thanks for the hint!!