2 Replies Latest reply on Jul 13, 2003 9:33 AM by mrauch

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

    mrauch Newbie

      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?


        • 1. Re: EJB-QL: Wrong SQL query generated for TRUE on MySQL usin
          Vishal Sharma Expert

          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.

          • 2. Re: EJB-QL: Wrong SQL query generated for TRUE on MySQL usin
            mrauch Newbie

            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!!