8 Replies Latest reply: Apr 11, 2008 11:35 AM by 666705 RSS

    How to turn on/enable sql logging

    666705
      Our environment is Java EE 5, JPA, Weblogic 10, KODO 4.1.2, and our DB is Oracle 10gr2. We are trying to figure out what to do/what has to be set up in order to see the actual SQL going to the database. I have the following in the persistence.xml:
      <properties>
      <property name="kodo.Log" value="DefaultLevel=WARN, Runtime=INFO, Tool=INFO, SQL=TRACE"/>
      </properties>
      which I found posted somewhere while trying to resolve this, but it does nothing. There is no sql syntax whatsoever in any weblogic log or on the weblogic console. For what it's worth, we have tried both XA and the thin drivers, but that has no effect either. We have tweaked the logging options in the WL console as well, but nothing seems to have any impact on SQL logging. Any help would be greatly appreciated.
        • 1. Re: How to turn on/enable sql logging
          3004
          Russ Burkert wrote:
          Our environment is Java EE 5, JPA, Weblogic 10, KODO 4.1.2, and our DB is Oracle 10gr2. We are trying to figure out what to do/what has to be set up in order to see the actual SQL going to the database. I have the following in the persistence.xml:
          <properties>
          <property name="kodo.Log" value="DefaultLevel=WARN, Runtime=INFO, Tool=INFO, SQL=TRACE"/>
          </properties>
          which I found posted somewhere while trying to resolve this, but it does nothing. There is no sql syntax whatsoever in any weblogic log or on the weblogic console. For what it's worth, we have tried both XA and the thin drivers, but that has no effect either. We have tweaked the logging options in the WL console as well, but nothing seems to have any impact on SQL logging. Any help would be greatly appreciated.
          Hi. If you can configure WebLogic to use
          the BEA-branded driver for Oracle, we can
          then add the spyAttribute property to have
          the driver generate a spy log that will show
          every single JDBC call with every argument,
          including the SQL.
          Joe
          • 2. Re: How to turn on/enable sql logging
            666705
            using this driver:
            weblogic.jdbcx.oracle.OracleDataSource

            So, how can we add the spyAttribute property
            • 3. Re: How to turn on/enable sql logging
              3004
              Russ Burkert wrote:
              using this driver:
              weblogic.jdbcx.oracle.OracleDataSource

              So, how can we add the spyAttribute property
              In the properties panel for the datasource,
              add this property:

              spyAttributes=(log=(file)spy.log;timestamp=yes;logTName=yes)

              This will cause the driver to generate the file 'spy.log'
              in the main domain directory.

              You may have to edit your start-weblogic script to add the
              wlspy.jar to the classpath created by the script. The wlspy.jar
              is in the domain's server\lib directory.
              Joe
              • 4. Re: How to turn on/enable sql logging
                666705
                That did not work. There is nothing on the console or any of the logs that is new concerning sql logging. Added the specified properties to the driver using the console and modified the commEnv script so wlspy.jar is included in the WEBLOGIC_CLASSPATH. WebLogic was restarted.

                However, spy.log is created and at least I can now see some database errors (ORA-xxxx) coming back from Oracle (which is very helpful). But, no actual SQL in that log, either.


                Upon further scrutiny of spy.log, I can see this kind of stuff:
                PreparedStatement[35].setString(int parameterIndex, String x)
                parameterIndex = 39
                x = RN22
                OK

                All of our database transactions right now are inserts. I see how this maps to the JPA objects, but it is quite grueling to have to figure out the index/offset of each piece of data in the object and then cross-reference that to its DB table/column equivalent. Is there any way to just see a simple INSERT statement somewhere?
                --
                Edited by burkeru at 04/11/2008 7:05 AM

                --
                Edited by burkeru at 04/11/2008 7:26 AM
                • 5. Re: How to turn on/enable sql logging
                  3004
                  Russ Burkert wrote:
                  That did not work. There is nothing on the console or any of the logs that is new concerning sql logging.
                  What didn't work?
                  Added the specified properties to the driver using the console and modified the
                  commEnv script so wlspy.jar is included in the WEBLOGIC_CLASSPATH. WebLogic was restarted.

                  However, spy.log is created and at least I can now see some database errors
                  (ORA-xxxx) coming back from Oracle (which is very helpful). But, no actual SQL in that log, either.

                  Upon further scrutiny of spy.log, I can see this kind of stuff:
                  PreparedStatement[35].setString(int parameterIndex, String x)
                  parameterIndex = 39
                  x = RN22
                  OK

                  All of our database transactions right now are inserts. I see how this maps to the JPA
                  objects, but it is quite grueling to have to figure out the index/offset of each piece
                  of data in the object and then cross-reference that to its DB table/column equivalent.
                  Is there any way to just see a simple INSERT statement somewhere?
                  There has to be INSERT statements in the spy log. They may well be done by PreparedStatements,
                  which show their SQL once at the prepare, early in the log, and then re-executed indefinitely.
                  Find the SQL for a given prepared statement so you can know what failed when the XXXth execute()
                  returns a SQLException.
                  Joe
                  • 6. Re: How to turn on/enable sql logging
                    666705
                    It did not work in that there are no SQL statements in the spy.log file. I've been reading and writing SQL for 20+ years, Joe, and I assure you there are no SQL statements in that log. I can email to you the log if you want to see for yourself.

                    Well, don't I feel like an idiot now. I looked again using a real editor (vi) and now I can see the SQL inserts. So...thanks a bunch, Joe, for your help and quick turnaround on this issue. This will make things SOOOOO much easier for debugging.

                    --
                    Edited by burkeru at 04/11/2008 9:13 AM
                    • 7. Re: How to turn on/enable sql logging
                      3004
                      Russ Burkert wrote:
                      It did not work in that there are no SQL statements in the spy.log file.
                      I've been reading and writing SQL for 20+ years, Joe, and I assure you
                      there are no SQL statements in that log. I can email to you the log if
                      you want to see for yourself.

                      Well, don't I feel like an idiot now. I looked again using a real editor
                      (vi) and now I can see the SQL inserts. So...thanks a bunch, Joe, for your
                      help and quick turnaround on this issue. This will make things SOOOOO
                      much easier for debugging.
                      Glad to help! I am still confused a little. Do email me the full
                      spy log if you don't see the SQL, but the second paragraph implies
                      that you do now see them, right?
                      Joe
                      • 8. Re: How to turn on/enable sql logging
                        666705
                        Joe,
                        Correct. I see the SQL in the spy.log file for each transaction. Thanks again for your assistance.

                        Russ