6 Replies Latest reply on Apr 21, 2011 12:41 PM by Yash Garg

    Log SQL statement

    Ohad Raz Newbie

      Greetings,

      I would like to know, is there a way to configure JBoss to log the SQL statements it generates and executes?

      Practically, I need it because I got this exception:

      java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
      and I do not know the source of this exception.
      If I could see the SQL JBoss tries to execute, I can figure out what is wrong.

      But beside my specific need, I think that logging the SQL statements is important (at least for beginners such as my self, to see what's going on here...)

      So, please, if anyone knows how to do it, please share this information.

      Thanks,
      Ohad

        • 1. Re: Log SQL statement
          Ohad Raz Newbie

          OK, I've found the answer:

          First of all, since my question was not clear enough, let me explain:
          JBoss logs SQL statements in the server.log (which is located in the \log directory of the server's directory (e.g., %JBOSS_HOME%\server\default\log); what I wanted was to make a separate log file for the SQL statements.

          So, how to do it?

          A modification is needed in the log4j.xml file (which is located in the \conf directory of the server's directory).
          There should be added an appender, and a category elements.

          Let's say that the I want to name of the new log file "SQLs.log", so, the modification that should be made in the log4j.xml file are as follows:

          <!-- ================================================= -->
           <!-- Append SQL generated by JBoss CMP to the log file -->
           <!-- ================================================= -->
           <appender name="CMP" class="org.jboss.logging.appender.RollingFileAppender">
           <errorHandler class="org.jboss.logging.util.OnlyOnceErrorHandler"/>
           <param name="File" value="${jboss.server.home.dir}/log/SQLs.log"/>
           <param name="Append" value="false"/>
           <param name="MaxFileSize" value="500KB"/>
           <param name="MaxBackupIndex" value="1"/>
          
           <layout class="org.apache.log4j.PatternLayout">
           <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
           </layout>
           </appender>
          
          
          
           <!-- Limit the org.jboss.ejb.plugins.cmp category to DEBUG -->
           <category name="org.jboss.ejb.plugins.cmp">
           <priority value="DEBUG" />
           <appender-ref ref="CMP"/>
           </category>


          And that does the trick!!!

          • 2. Re: Log SQL statement
            Yash Garg Newbie

            but its not logging the SQLs for Jboss Messaging? do you have any idea , how can I do that ?

            • 3. Re: Log SQL statement
              Peter Johnson Master

              Yash, you are replying to a post that is 6 years old! I doubt that the original poster was even using JBoss Messaging, probably the suggested solution was for JBossMQ. I don't think JBoss Messaging even existed back then.

              • 4. Re: Log SQL statement
                Yash Garg Newbie

                Peter, Do you know how I can see the SQLs for Jboss messaging?

                • 5. Re: Log SQL statement
                  Peter Johnson Master

                  Not offhand. You can dig thru the source code to see if Messaging logs the SQL statements. Of you can turn Messaging logging to TRACE (add a categories for org.jboss.messaging and org.jboss.jms) and see if they show up.

                   

                  If you just want to see the SQL statements used, they are in the server/xxx/deploy/messgaing/*-persistence-service.xml file.

                  • 6. Log SQL statement
                    Yash Garg Newbie

                    I have alredy added jms and messaging into log4j.xml , but its not showing the SQL Statements,

                     

                    e.g. I am trying to get the status of the Queues with the following , its working fine but wanted to see the SQL Statements

                     

                    invoke "dcm4chee.archive:service=JmsConsole" listQueueStatus