I'm trying to convince my JasperServer to spit out the SQL queries it performs to the log file so that I debug them. I've seen the following post
http://community.jaspersoft.com/questions/526007/logging-sql-query-executed
and that does cause most of the query to be posted to the log. However, the query includes many question marks in place of parameter values.
Is there some configuration option to convince Jasper to log the whole query, in a form that I can cut and paste into MySQL Workbench so that I can debug it?
2 Answers:
Hi Spierepf,
I believe you activated net.sf.jasperreports.engine.query.JRJdbcQueryExecuter
what encolding are you using in the database? Do you have non-latin symbols that are translated to questions?
In that link - all the parameters' values were resolved successfully to the dates..
What Jaspersoft version are you using?
Olga
I was referring to the log of the other thread, tat displays list of parameter and values as below:
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
Could you please confirm if you see that detailed parameter information?
Thanks and regards,
Olga
I do indeed get the additional parameter logs. However, as they are not in-line, they are not useful for a cut-and-paste into an SQL executing tool.
My query literally has more than seventy parameter entries...
I'm hoping that there is a configuration parameter to cause log4j to display parameter values in-line...
Greetings Olga,
I don't think encodings are the issue. What is happening is that the report parameters $P{...} are being translated into question marks instead of the parameter values:
SELECT * FROM table WHERE table.field = $P{Parameter}
gets logged as:
SELECT * FROM table WHERE table.field = ?
instead of:
SELECT * FROM table WHERE table.field = 123.456
I need for the log to report a valid SQL query with parameter values so that I can copy the query into MySQL Workbench and debug it. '?' symbols aren't valid SQL.
Cheers,
Peter.