How to enable trace/debugging output with Anorm on Play 2.4.0

Question

With Play 2.4.0 Anorm got moved to an external package and logging got changed to LogBack (http://logback.qos.ch)

All well and good but nowhere are the class/package names obvious for tracing SQL calls.

The "obvious" (based on the import statements)

<logger name="anorm" level="TRACE" />

did nothing and I also tried

<logger name="anorm.SQL" level="TRACE" />

just in case it needed to be more specific.

Google searches just dead needed so I'm at a loss.

Anyone?

Abducent

And yes I tried putting the entire app in trace mode to see if I could pick up the class names from the logging output and yet still no SQL calls. – Abducent

There's one way that involves changing connection pool back to BoneCP (from the new default HikariCP): https://mcmap.net/q/405688/-where-to-see-the-logged-sql-statements-in-play2. I haven't found a solution yet that works with HikariCP. – Eudoxia

Answer 1

You can intercept calls going thru JDBC driver using

log4jdbc

I have used successfully with JPA/hibernate and Hikary on Play 2.4, the setup should be identically since this influences the JDBC layer.

Add the library to your build.sbt:

"org.bgee.log4jdbc-log4j2" % "log4jdbc-log4j2-jdbc4" % "1.12"

Adjust the config. Add log4jdbc, the log4jdbc automatically detects the underlying driver from the string: mysql. If you are using an obscure JDBC driver, you can configure it using config options - see docs below.

db.default.url="jdbc:log4jdbc:mysql://localhost/......"
db.default.driver=net.sf.log4jdbc.sql.jdbcapi.DriverSpy

Example of my logback.xml, relevant part:

<logger name="log4jdbc.log4j2" level="ERROR">
    <appender-ref ref="STDOUT" />
    <appender-ref ref="FILE" />
</logger>
<logger name="jdbc.sqlonly" level="INFO" >
    <appender-ref ref="DBFILE" />
</logger>

<appender name="DBFILE" class="ch.qos.logback.core.FileAppender">
    <file>${application.home}/logs/sql.log</file>
    <encoder>
        <pattern>%date - [%level] - from %logger in %thread %n%message%n%xException%n</pattern>
    </encoder>
</appender>

And, finally the log4jdbc.log4j2.properties (create it in the conf directory which is on the class path):

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

More docs: https://code.google.com/p/log4jdbc-log4j2/

Let me know if this works for you

Linette

That looks perfect - I will be checking it out - thank you! – Abducent

PS: Code has moved to GitHub: github.com/arthurblake/log4jdbc (for future reference once google code goes away) – Abducent

For what it's worth, I needed to use version "1.16" of "log4jdbc-log4j2-jdbc4" when configuring this for Postgres. Version 1.12 had a bug that caused the ResultSet to be closed prematurely. code.google.com/archive/p/log4jdbc-log4j2/issues/4 – Muco

Answer 2

Anorm doesn't log anything (and doesn't use logback), but 'output' is plain JDBC, so you can configure debug on your connection pool.

EDIT:

The debug utility from my framework Acolyte can be used to print/log the JDBC statement that would have been executed with the connection.

If you have SQL"SELECT * FROM Test WHERE id = $id", you can debug it as following.

<!-- language: scala -->

import acolyte.jdbc.AcolyteDSL

AcolyteDSL.debuging() { implicit dcon =>
  SQL"SELECT * FROM Test WHERE id = $id"
  // just print the prepared statement
  // with parameters bound
}

// really execute,
// the check the real ResultSet
SQL"SELECT * FROM Test WHERE id = $id"

Acolyte is available on Maven Central.

Tolu

Does that mean you have to duplicate the statements? PS: I've solved my problem by modifying my library to trace any calls as needed. Not optimal but fit the bill. Given that norm is likely being phased out in favour of more idiomatic sql solutions I suspect norm will just die a slow death now... – Abducent

Ah - I see that one can pass a reference to the initial stmt and not have to duplicate it (whew) – Abducent