3 Replies Latest reply on Sep 13, 2004 12:33 PM by godboles

    Declared SQL question

    godboles

      Hi all,

      I have written the following declared SQL. I am just giving the XDOCLET tags to keep the posting short.

      *@jboss.declared-sql
      * signature="java.util.Collection ejbSelectCitiesForCountyList(java.lang.String statecode, java.lang.String countyList)"
      * distinct="true"
      * ejb-name="UsaZipCode"
      * field-name="city"
      * where="statecode = {0} AND county IN ({1}) AND citytype = 'D'"
      * order="city"
      * strategy="on-find"
      * page-size="50"

      I call this method with two parameters. The first parameter is the statecode (eg NJ). The second parameter is the list of counties which are comma separated (eg. Morris,Essex). When the method executes, the server logs show correct SQL being execute:

      SELECT DISTINCT city FROM UsaZipCode WHERE statecode = ? AND county IN (?) AND citytype = 'D' ORDER BY city

      In the second parameter for the IN(?) clause , if I pass a comma separated list of counties, I get NOTHING back. But if I pass only one county (eg. Morris), I do get a list back.

      Does anyone know why this is happening. I was hoping that if I pass a comma separated list of counties in the second parameter, I will have a nice 'IN' clause.

      I hope my posing is not confusing. Any help would be greately appreciated.

      Thanks.

      HG

        • 1. Re: Declared SQL question
          bartvh

          The substituted values get quoted, so the query actually will read as

          SELECT DISTINCT city
          FROM UsaZipCode
          WHERE statecode = 'NJ' AND county IN ('Morris,Essex') AND citytype = 'D'
          ORDER BY city;

          (note the position of the quotes in the substitution of the second '?')

          This explains why a single element works.

          • 2. Re: Declared SQL question
            godboles

            bartvh,

            Thanks a lot for your response. It explains why a single value works.

            I am not sure how should I get past this issue. I would really like to pass a comma separated list of counties. I guess I can play with the positioning of the quotes in my strings.

            Do you have any suggestions?

            Once again, thanks for a quick response.

            HG

            • 3. Re: Declared SQL question
              godboles

              Hi all,

              If any one is interested and facing a similar situation, I got around this issue by using the JBoss dynamic SQL.

              HG