13

We'd like to delve a bit deeper into the Content Delivery API to see some of the raw SQL queries that are being built and executed when using the API.

We've got the standard Content Delivery logging running in debug mode, but this doesn't provide the sort of detail i'm looking for.

I'm thinking of adding a trace on the development database then performing some fetches via the API, but I thought I'd ask the community first if there's a better way to go about doing this?

Thanks in advance

Improve this question
3
  • 7
    In log back change ' <root level="OFF">' to ON and set loglevel to TRACE. It dumps hell lot of stuff including hybrrnate queries. SQL Trace may be your best bet though for DB.
    – Ram G
    Commented Sep 12, 2013 at 23:33
  • 1
    Ram - don't be shy. Setting up TRACE level logging is a "full" answer to John's question. Why just a comment?
    – Dominic Cronin
    Commented Sep 13, 2013 at 5:33
  • MS SQL or Oracle? Frank van Puffelen details broker query troubleshooting steps in this related SO answer for MSSQL.
    – Alvin Reyes
    Commented Sep 13, 2013 at 16:58

3 Answers 3

Reset to default
10

You can try this to enable debug level log at Hibernate.

<logger name="org.hibernate" level=”debug”>
<appender-ref ref="rollingHibernateLog"/>
</logger>

rollingHibernateLog –must be also defined in logback.xml

Improve this answer
9

Tridion uses the Hibernate framework for it's database persistence, so Hibernate's logging is what needs to be set up.

In logback.xml define the following:

1) Define an appender to put the DB log statements into a separate file. Simply paste this into your logback.xml:

<appender name="rollingHibernateLog" class="ch.qos.logback.core.rolling.RollingFileAppender">
    <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
        <fileNamePattern>${log.folder}/cd_hibernate.%d{yyyy-MM-dd}.log</fileNamePattern>
        <maxHistory>${log.history}</maxHistory>
    </rollingPolicy>
    <encoder>
        <pattern>${log.pattern}</pattern>
    </encoder>
    <prudent>true</prudent>
</appender>

2) Next add the following loggers:

<logger name="org.hibernate.SQL" level="debug">
    <appender-ref ref="rollingHibernateLog"/>
</logger>
<logger name="org.hibernate.type" level="trace">
    <appender-ref ref="rollingHibernateLog"/>
</logger>

Beware that the new log file will grow to hundreds of megs in size very fast.

Improve this answer
3

Once root logging in enabled on trace level, you can confirm the hibernate trace is working by looking for statements such as this:

2014-03-14 05:29:50,977 DEBUG ConnectionManager - opening JDBC connection

2014-03-14 05:29:50,977 DEBUG SQL - select custommeta0_.PUBLICATION_ID as PUBLICAT8_1_, 
custommeta0_.ITEM_ID as ITEM4_1_, custommeta0_.CUSTOM_META_VALUE_ID as CUSTOM1_1_, 
custommeta0_.CUSTOM_META_VALUE_ID as CUSTOM1_10_0_, 
custommeta0_.KEY_DATE_VALUE as KEY2_10_0_, 
custommeta0_.KEY_FLOAT_VALUE as KEY3_10_0_, 
custommeta0_.ITEM_ID as ITEM4_10_0_,     
custommeta0_.ITEM_TYPE as ITEM5_10_0_, 
custommeta0_.KEY_NAME as KEY6_10_0_, 
custommeta0_.KEY_TYPE as KEY7_10_0_, 
custommeta0_.PUBLICATION_ID as PUBLICAT8_10_0_, 
custommeta0_.KEY_STRING_VALUE as KEY9_10_0_ from CUSTOM_META custommeta0_ 
where custommeta0_.PUBLICATION_ID=? and custommeta0_.ITEM_ID=?
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.