1 Reply Latest reply on Nov 4, 2005 5:54 AM by Florian Kammermann

    false row-name in sql-query

    Florian Kammermann Newbie

      I have following initial situation

      - JBoss Version 4.0.2
      - A Database with a table "article" column-name "artno"
      - This is mapped to the attribute "number" in the EJB (2.1)
      - the table "article" has 3 relations to the tables: articleattribute, pricing, availability.
      - the relations are mapped inside the EJB.
      - following query gets the article and the related data: SELECT OBJECT(o) FROM Article AS o WHERE o.number = ?1
      - when I log the executed SQL I see 4 querys. 1 for the "article" and 3 for the relations.

      Ok this is all well-known stuff and documented like this.

      Now I would boost the performance and add following xml-snippet to the jbosscmp-jdbc.xml:


      <query-method>
      <method-name>findByCompleteArticleNumber</method-name>
      <method-params>
      <method-param>java.lang.String</method-param>
      </method-params>
      </query-method>
      <read-ahead>
      on-find
      <page-size>4</page-size>
      <left-join cmr-field="articleAttributes"/>
      <left-join cmr-field="pricings"/>
      <left-join cmr-field="availabilities"/>
      </read-ahead>


      Here is the ejb-jar.xml-snippet:


      [CDATA[Finds an article with the specified number]]
      <query-method>
      <method-name>findByCompleteArticleNumber</method-name>
      <method-params>
      <method-param>java.lang.String</method-param>
      </method-params>
      </query-method>
      <ejb-ql>[CDATA[SELECT OBJECT(o) FROM Article AS o WHERE o.number = ?1]]</ejb-ql>


      But now the SQL-Query fails, cause it searches not for "artno" in the table "article" but for "number", which is the name used in the EJB-QL-Query.

      Here is the failed-SQL-Query:

      Executing SQL: SELECT t0_o.seq, t0_o.number, t1_o_articleAttributes.seq, t1_o_articleAttributes.artno, t1_o_articleAttributes.name, t1_o_articleAttributes.value, t1_o_articleAttributes.country, t1_o_articleAttributes.language, t1_o_articleAttributes.fk_article, t2_o_pricings.seq, t2_o_pricings.artno, t2_o_pricings.qty, t2_o_pricings.type, t2_o_pricings.pricelist, t2_o_pricings.valuetype, t2_o_pricings.valuedecimal, t2_o_pricings.valuelong, t2_o_pricings.currency, t2_o_pricings.fk_article, t3_o_availabilities.seq, t3_o_availabilities.artno, t3_o_availabilities.qty, t3_o_availabilities.whid, t3_o_availabilities.date, t3_o_availabilities.type, t3_o_availabilities.fk_article FROM article t0_o LEFT OUTER JOIN articleattribute t1_o_articleAttributes ON t0_o.seq=t1_o_articleAttributes.fk_article LEFT OUTER JOIN pricing
      t2_o_pricings ON t0_o.seq=t2_o_pricings.fk_article LEFT OUTER JOIN availability t3_o_availabilities ON t0_o.seq=t3_o_availabilities.fk_article WHERE (t0_o.number = ?)


      Here is the Exception:

      java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'number'.

        • 1. Re: false row-name in sql-query
          Florian Kammermann Newbie

          First I'm sorry about the headline, should mean column-name.

          Ok, this was not a JBoss-problem, it was a XDoclet-problem.

          I added a Xdoclet-merge-file with the name jbosscmp-jdbc-Article.xml, where I added the left-join-statments. Unfortunatly, if you add this file you must also define the colum-mapping in it. The tags in the java-file, will be overwritten. After I added the column-mapping also in the merge-file everything works.