2 Replies Latest reply on Feb 6, 2004 10:51 AM by George Taylor

    Cross Datasource finder

    George Taylor Newbie

      I have a class that has the following finder

      <![CDATA[finds all content files of a specified content Id and user agent]]>
      <query-method>
      <method-name>findByContentId</method-name>
      <method-params>
      <method-param>java.lang.Integer</method-param>
      <method-param>java.lang.String</method-param>
      </method-params>
      </query-method>
      <result-type-mapping>Local</result-type-mapping>
      <ejb-ql><![CDATA[SELECT DISTINCT crf.contentFile FROM com_afl_content_entity_Content c, in (c.contentRuleFiles) as crf, in (crf.contentTypeRule.devices) as d, in (d.userAgents) as da WHERE c.id = ?1 and da.userAgent = ?2]]></ejb-ql>



      I have a problem that the objects generated as d and da are from a different datasource.

      the sql that jboss generates is:
      SELECT DISTINCT
      t0_crf_contentFile.ID
      FROM
      CONTENT t1_c,
      CONTENT_RULE_FILE t3_crf,
      DEVICE t4_d,
      DEVICE_AGENT t2_da,
      CONTENT_FILE t0_crf_contentFile,
      CONTENT_TYPE_RULE t5_crf_contentTypeRule
      WHERE
      (t1_c.ID = ? AND t2_da.USER_AGENT = ?) AND
      (
      t3_crf.CONTENT_FILE_ID=t0_crf_contentFile.ID AND
      t4_d.ID=t2_da.DERVICE_ID AND t1_c.ID=t3_crf.CONTENT_ID AND
      t5_crf_contentTypeRule.ID=t4_d.DEVICE_ID AND
      t3_crf.CONTENT_TYPE_RULE_ID=t5_crf_contentTypeRule.ID
      )


      most of the files have
      java:/ContentDS
      <datasource-mapping>Oracle9i
      in the jbosscmp-jdbc, but for the device and deviceAgent beans it has
      java:/AflDS
      <datasource-mapping>Oracle9i</datasource-mapping>

      As you can see the sql that is generated gets all the data from the same datasource (I am presuming from ContentDS), even though in the setup for the device and deviceAgent beans is set specificly to the AflDS datasource.

      does anyone know of a way to get jboss to take the data from the correct datasource.

      I am developing the code in eclipse 3.0.0 (M5) using xdoclets 2.0 to generate the finders, and running on jboss 3.2.2

      any help would be VERY much appreciated.

      thanks

        • 1. Re: Cross Datasource finder
          Alexey Loubyansky Master

          You should use the same datasource for all the entities involved in the query. And you can't select fields in the finder, you must use ejbSelect instead.

          • 2. Re: Cross Datasource finder
            George Taylor Newbie

            the reason for using multiple datasources is because the tables are in seperate schemas, and due to security restrictions, they must have different user names to access them. If i can do this in a single datasouce, please let me know.

            I will change it to an ejbSelect instead of an ejbFind, but won't this mean that i will have to have an object of type ContentFile to be able to call the select ??? I am trying to find a record of type ContentFile that represents the query.