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

    Declared SQL question

    Shrihari Godbole Newbie

      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