Heyas,
i have some trouble executing a report query containing a converted Date parameter as input. Basically, the query is:
SELECT * FROM STAT.PODQ
WHERE ZSTYEAR=cast(year($P{DATE_SEL}) as char(4))
AND ZSTMONTH=cast(month($P{DATE_SEL}) as char(2))
AND ZSTDAY=cast(day($P{DATE_SEL}) as char(2))
with $P{DATE_SEL} is an java.util.Date typed Parameter (populated by an input control). I would love to see an option to log the query that is acutally executed on the database.
Any help on this?
regards
Marc
3 Answers:
Marc,
Depending on the version of JasperServer you are using, you should find this already appears in log4j.properties:
### JasperReports loggers
#log4j.logger.net.sf.jasperreports.engine.query.JRJdbcQueryExecuter=debug
Uncomment it and you should be get what you need in the log.
Regards,
Matt
For the latest releases of Jaspersoft Studio...
You can print out information to the console as described in How to get Jaspersoft Studio logging output from within scriptlet.
Or you probably want to enable FINE-level logging for the specific class you're interested in. See Configuring Apache Commons Logging and Log4J in Jaspersoft Studio and https://community.jaspersoft.com/questions/841628/it-possible-log-genera... for an example.
You can create a log4j.properties file like this one:
<span style="font-size:10pt"><span style="font-size:8.0pt">#############################################</span></span>
<span style="font-size:10pt"><span style="font-size:8.0pt">log4j.appender.fileout=org.apache.log4j.RollingFileAppender</span></span>
<span style="font-size:10pt"><span style="font-size:8.0pt">log4j.appender.fileout.File=<span style="background-color:yellow">C:/tmp/jasperstudio.log</span></span></span>
<span style="font-size:10pt"><span style="font-size:8.0pt">log4j.appender.fileout.MaxFileSize=16000KB</span></span>
<span style="font-size:10pt"><span style="font-size:8.0pt">log4j.appender.fileout.MaxBackupIndex=2</span></span>
<span style="font-size:10pt"><span style="font-size:8.0pt">log4j.appender.fileout.layout=org.apache.log4j.PatternLayout</span></span>
<span style="font-size:10pt"><span style="font-size:8.0pt">log4j.appender.fileout.layout.conversionPattern=%d{ABSOLUTE} %5p %c{1},%t:%L - %m%n</span></span>
<span style="font-size:10pt"><span style="font-size:8.0pt">log4j.rootLogger=<span style="background-color:yellow">WARN</span>, fileout</span></span>
<span style="font-size:10pt"><span style="font-size:8.0pt">log4j.logger.net.sf.jasperreports.engine.query=<span style="background-color:yellow">FINE</span></span></span>
<span style="font-size:10pt"><span style="font-size:8.0pt">log4j.logger.net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.level=<span style="background-color:yellow">FINE</span></span></span>
<span style="font-size:10pt"><span style="font-size:8.0pt">#############################################</span></span>Reference this file in your Jaspersoft Studio[ Professionnal].ini file (which is located in the same directory as jasper studio' exe):
<span style="font-size:10.6667px">-Dlog4j.configuration=file:/c:/path%to%log4j.properties%file/log4j.properties</span>
Then restart Jaspersoft Studio.
Im using jasper studio 5.6 and there is no log4j.properties. Is there any other solution?
Hello Matt,
Thanks for the quick reply. I am using JS 3.1, so i guess it will work. Is there a documentation about the logger facilities (properties), as your mentioned code wasn't in my property file?
Anyways: Is it possilble to pipe the SQL queries to a dedicated log file (eg "sqlquery.log")? That would help me a bunch!
regards
Marc
Oooookay,
i am learning ALOT here. Thats the excerpt from my log:
---------------------
09:24:31,416 DEBUG JRJdbcQueryExecuter,http-8080-Processor22:155 - SQL query string: SELECT * FROM STAT.PODQ
WHERE ZSTYEAR=cast(year(?) as char(4))
AND ZSTMONTH=cast(month(?) as char(2))
AND ZSTDAY=cast(day(?) as char(2))
09:24:31,418 DEBUG JRJdbcQueryExecuter,http-8080-Processor22:206 - Parameter #1 (DATE_SEL of type java.util.Date): Thu Jan 22 00:00:00 CET 2009
09:24:31,419 DEBUG JRJdbcQueryExecuter,http-8080-Processor22:206 - Parameter #2 (DATE_SEL of type java.util.Date): Thu Jan 22 00:00:00 CET 2009
09:24:31,420 DEBUG JRJdbcQueryExecuter,http-8080-Processor22:206 - Parameter #3 (DATE_SEL of type java.util.Date): Thu Jan 22 00:00:00 CET 2009
---------------------
Is it really that weird? Do i have to take the java.util.Date object, and turn it into a formatted string to feed it to the database as she would expect it? Is there a more elegant way to achivie this?
As far as i know it, bound parameters can be typed.
Any suggestions on that?
regards
Marc
JDBC handles all the translations. You just need to have the parameter from the input control be defined as java.util.Date, which it looks like you do.
Sherman
Jaspersoft
Heyas,
yeah, one would expect that, Sherman. Perhaps i should have added the following lines of the logs as well:
--------------------------------------------
09:24:31,430 ERROR EngineServiceImpl,http-8080-Processor22:678 - Error while filling report
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ZAS_SUMMARY_INTIME_jrxml_1233303783421
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141)
at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:127)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:668)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:588)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1212)
...
--------------------------------------------
Halp?
regards
Marc
Could you post a full stacktrace (incl. nested exceptions = "Caused by ..")?
Regards,
Lucian