6 Replies Latest reply on Apr 17, 2003 9:14 AM by benholland

    Debugging generated SQL statements

    smilie


      Hi!

      I am using JBoss-3.0.4 with CMP Entity Beans. Something goes wrong with the generated sql statement of my finder methods, but I dont know what to do to see the generated sql statements! I have changed the log4.xml and standardjaws.xml but nothing happend... here my actual log4j.xml:

      <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/" debug="false">

      <!-- ================================= -->
      <!-- Preserve messages in a local file -->
      <!-- ================================= -->

      <!-- A time/date based rolling appender -->





      <!-- Rollover at midnight each day -->
      <param name="DatePattern" value="'.'yyyy-MM-dd"/>

      <!-- Rollover at the top of each hour
      <param name="DatePattern" value="'.'yyyy-MM-dd-HH"/>
      -->


      <!-- The default pattern: Date Priority [Category] Message\n -->


      <!-- The full pattern: Date MS Priority [Category] (Thread:NDC) Message\n

      -->



      <!-- A size based file rolling appender










      -->

      <!-- ============================== -->
      <!-- Append messages to the console -->
      <!-- ============================== -->






      <!-- The default pattern: Date Priority [Category] Message\n -->





      <!-- ====================== -->
      <!-- More Appender examples -->
      <!-- ====================== -->

      <!-- Buffer events and log them asynchronously

      <appender-ref ref="FILE"/>
      <appender-ref ref="CONSOLE"/>
      <appender-ref ref="SMTP"/>

      -->

      <!-- EMail events to an administrator











      -->

      <!-- Syslog events





      -->

      <!-- Log events to JMS (requires a topic to be created)





      -->

      <!-- ================ -->
      <!-- Limit categories -->
      <!-- ================ -->














      <!-- Decrease the priority threshold for the org.jboss.varia category



      -->

      <!--
      | An example of enabling the custom TRACE level priority that is used
      | by the JBoss internals to diagnose low level details. This example
      | turns on TRACE level msgs for the org.jboss.ejb.plugins package and its
      | subpackages. This will produce A LOT of logging output.
      If you use replace the log4j 1.2 jar with a 1.1.3 jar you will need to
      change this from XLevel to XPriority.
      -->








      <!-- ======================= -->
      <!-- Setup the Root category -->
      <!-- ======================= -->


      <appender-ref ref="CONSOLE"/>
      <appender-ref ref="FILE"/>


      <!-- Clustering logging -->
      <!-- Uncomment the following to redirect the org.javagroups and
      org.jboss.ha categories to a cluster.log file.













      <appender-ref ref="CLUSTER"/>



      <appender-ref ref="CLUSTER"/>

      -->

      </log4j:configuration>



      Could someone help me?

      Regards,

      Thilko

        • 1. Wrong SQL Statement
          smilie


          Hi!

          I could activate the log... here is the wrong sql statement:

          2003-04-17 09:24:51,287 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.StatusEntity.findByName] Executing SQL: SELECT FROM STATUS t0_c WHERE t0_c.name = ?

          In my ejb-jar.xml I defined the following:

          <![CDATA[Finder method for Status Objects]]>
          <query-method>
          <method-name>findByName</method-name>
          <method-params>
          <method-param>java.lang.String</method-param>
          </method-params>
          </query-method>
          <ejb-ql><![CDATA[SELECT OBJECT(c) FROM StatusEntity c WHERE c.name=?1]]></ejb-ql>



          Do you know whats going wrong?

          Regards,

          Thilko

          • 2. Re: Wrong SQL Statement
            benholland

            Here is an entity from our project:


            <ejb-name>Customer</ejb-name>
            mvdc.ejb.account.CustomerHome
            <local-home>mvdc.ejb.account.CustomerLocalHome</local-home>
            mvdc.ejb.account.Customer
            mvdc.ejb.account.CustomerLocal
            <ejb-class>mvdc.ejb.account.CustomerBean</ejb-class>
            <persistence-type>Container</persistence-type>
            <prim-key-class>mvdc.ejb.account.CustomerPK</prim-key-class>
            False
            <cmp-version>2.x</cmp-version>
            <abstract-schema-name>CustomerBean</abstract-schema-name>
            <cmp-field>
            <field-name>CustomerID</field-name>
            </cmp-field>
            <cmp-field>
            <field-name>name</field-name>
            </cmp-field>
            <cmp-field>
            <field-name>PassWord</field-name>
            </cmp-field>

            <query-method>
            <method-name>findByName</method-name>
            <method-params>
            <method-param>java.lang.String</method-param>
            </method-params>
            </query-method>
            <ejb-ql>
            [CDATA[SELECT OBJECT(a) FROM CustomerBean AS a WHERE a.name =?1]]
            </ejb-ql>




            The select looks different to yours specially round the 'AS'. Hope that helps.

            • 3. Re: Debugging generated SQL statements
              smilie


              Thanks a lot for your answer! The changes have no effects.. her is the log:
              ...
              2003-04-17 10:46:49,459 TRACE [org.jboss.ejb.plugins.LogInterceptor] Start method=findByName

              2003-04-17 10:46:49,459 DEBUG [org.jboss.ejb.plugins.LogInterceptor] InvokeHome: findByName(Testing)

              2003-04-17 10:46:49,459 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Current transaction in MI is TransactionImpl:XidImpl [FormatId=257, GlobalId=tl-00934//2, BranchQual=]

              2003-04-17 10:46:49,459 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TX_REQUIRED for findByName

              2003-04-17 10:46:49,459 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Thread came in with tx TransactionImpl:XidImpl [FormatId=257, GlobalId=tl-00934//2, BranchQual=]

              2003-04-17 10:46:49,459 TRACE [org.jboss.ejb.plugins.EntityInstancePool] Get instance org.jboss.ejb.plugins.EntityInstancePool@1cd04f1#true#class de.talkline.picasso.server.StatusEntity

              2003-04-17 10:46:49,469 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.StatusEntity.findByName] Executing SQL: SELECT FROM STATUS t0_a WHERE t0_a.name = ?

              2003-04-17 10:46:49,519 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.StatusEntity.findByName] Find failed

              java.sql.SQLException: ORA-00936: Ausdruck fehlt
              ...

              My ejb-jar.xml:
              <!-- Entity Beans -->

              <![CDATA[]]>
              <display-name>StatusEntity</display-name>

              <ejb-name>StatusEntity</ejb-name>

              de.talkline.picasso.interfaces.StatusEntityRemoteHome
              de.talkline.picasso.interfaces.StatusEntityRemote
              <local-home>de.talkline.picasso.interfaces.StatusEntityLocalHome</local-home>
              de.talkline.picasso.interfaces.StatusEntityLocal

              <ejb-class>de.talkline.picasso.server.StatusEntity</ejb-class>
              <persistence-type>Container</persistence-type>
              <prim-key-class>java.lang.String</prim-key-class>
              False
              <cmp-version>2.x</cmp-version>
              <abstract-schema-name>StatusEntity</abstract-schema-name>
              <cmp-field >
              <![CDATA[]]>
              <field-name>name</field-name>
              </cmp-field>
              <cmp-field >
              <![CDATA[]]>
              <field-name>description</field-name>
              </cmp-field>
              <cmp-field >
              <![CDATA[]]>
              <field-name>id</field-name>
              </cmp-field>


              <![CDATA[Finder method for Status Objects]]>
              <query-method>
              <method-name>findByName</method-name>
              <method-params>
              <method-param>java.lang.String</method-param>
              </method-params>
              </query-method>
              <ejb-ql><![CDATA[SELECT OBJECT(a) FROM StatusEntity AS a WHERE a.name =?1]]></ejb-ql>

              <!-- Write a file named ejb-finders-StatusEntity.xml if you want to define extra finders. -->



              I dont know why JBoss doesnt map the method input parameter to the sql statement....


              Regards.

              Thilko

              • 4. Re: Debugging generated SQL statements
                smilidon

                hi,

                i'm sorry that i have no answer to your question, but could you tell me how to activate the log of sql statements?

                thx

                regards

                • 5. Re: Debugging generated SQL statements
                  smilie

                  Of course!

                  In JBoss 3.x you have to change the loglevel in log4j.xml. I used the fileAppender. At first, I have some problems with that, so I edited the standardjboss.xml. Ther you will find a section called <call-logging>...</call-logging> I changed this to true for the relevant parts and I get the log files. Bu b careful, after I restart the JBoss the log file was about 30MB...

                  Regards,

                  Thilko

                  • 6. Re: Debugging generated SQL statements
                    benholland

                    eh, you could always switch on logging in the db server?