Viewing sql created by ireport

Issue:

[toc on_off::hide=1]

I have a report for which the query consists of a lot of dynamic sql that is created at run time based on parameters.

I am getting a sql error in this report. I need to see the end result sql that is being generated. How can I do this?

Resolution:

Please follow the steps below to achieve what you need:

1. Create a log4j.properties (place it under ireport/etc), with the contents as follows:

   log4j.appender.fileout=org.apache.log4j.RollingFileAppender log4j.appender.fileout.File=C:/tmp/iReport.log log4j.appender.fileout.MaxFileSize=1024KB log4j.appender.fileout.MaxBackupIndex=1 log4j.appender.fileout.layout=org.apache.log4j.PatternLayout log4j.appender.fileout.layout.conversionPattern=%d{ABSOLUTE} %5p %c{1},%t:%L - %m%n log4j.rootLogger=warn, fileout log4j.logger.net.sf.jasperreports.engine.query=debug

    

   With the above, the output log will be in the iReport.log in the c:/tmp folder.

2. Edit ireport/etc/ireportpro.conf and add the following contents in the default_options parameter:

   -J-Dlog4j.configuration=file:/E:/Server/Server451/ireport/etc/log4j.properties

   So it becomes:

   default_options="-J-Xms24m -J-Xmx512m -J-Dorg.netbeans.ProxyClassLoader.level=1000 -J-XX:MaxPermSize=256m -J-Dlog4j.configuration=file:/E:/Server/Server451/ireport/etc/log4j.properties"

   Please pay attention to the above directory, you will need to adjust it to your own specific directory of iReport Designer installation.

3. Restart iReport Designer and execute reports, the generated SQL will then be output in iReport.log.

Ref. Case #00028579 -- 13:05, 20 September 2012 (UTC)