| Category: | Feature request |
Priority: | Urgent |
Status: | Acknowledged |
| Project: | Severity: | Critical |
Resolution: | Open |
|
| Component: | Reproducibility: | Always |
Assigned to: |
You can add a (partial) SQL ststement that is contained in a Parameter to your Report Query using the $P!{} notation.
But, if the SQL statement contained in the Parameter itself also contains variables (either in the JR syntax of "$P{}" or in the JDBC syntax of "?"), there is no way to get these variables added in properly.
Only option I see it preparsing the SQL yourself, before supplying it to JR. And parsing all teh variables in, into the string is:
- insecure (SQL injections)
- tricky to get it working cross db
- just something that should be avoinded, as JDBC can do it for us.
Therefor I was thinking int he following direction:
- give the $P!{paramName} syntax an optional second parameter for arguments, so it becomes: $P!{paramName for SQL, paramName for arguments[]}
or:
-Ability to double evaluate: $P!!{}: This way I could preprocess the question marks in my SQL to change them into the $P{} notation that Jasper understands and add all the required parameters to my parameterMap
If I missed something and this is allready possible: please point me in the right direction, because I could not find anythign on this topic.
Paul
I posted this also on the JasperReport forum: http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&f...
6 Comments:
<<But, if the SQL statement contained in the Parameter itself also contains variables (either in the JR syntax of "$P{}" or in the JDBC syntax of "?"), there is no way to get these variables added in properly.>>
Resolving $P{} placeholders in $P!{} query fragments is supported since JR 1.3.2. Have you tried it and it didn\'t work?
Regards,
Lucian
Well, I must say that I initially tested with a JR version before 1.3.2 and hadn\'t retested after upgrading to 2.0.2...
But I have now and allthough $P{} inside $P!{} is evaluated, it doesn;t work for my situation: The Prepared Statement SQL that gets added using $P!{} custom: So no way of knowing at report designtime how many "?" it will contain.
I replaced the "?" in the Prepared Statement SQL by $P{randomKey} and added the proper key/value pairs to the ParametersMap I provide the report with.
But, when trying to execute the report, it complains that the "randomKey" parameter is unknown, which I expect is true, because I did not specify it at designtime in the report (because I had no way of knowing if it would be needed).
So, eventhough the second scenario works, it doesn\'t solve the problem I\'m having.
So, that brings me back to the first option I suggested:
- give the $P!{paramName} syntax an optional second parameter for arguments, so it becomes: $P!{paramName for SQL,
paramName for arguments[]}
If this would be made possible, the JR query engine "just" has to add the arguments[] into the total list of parameters that get send to the DB with the Prepared Statement I assume the JR engine sends to the database.
This way it\'s also possible to use (bits of) Prepared Statements inside the reportQueries of subReports (since you have to specify which parameters to send to the subreport at designtime, this would be tricky with dynamically generated $P{} notations).
Am I still making sense? :)
Paul
Any comments on this?
Would like to know if better support for adding (bits of) prepared statements using the $P!{} statement could be added to JR:
- give the $P!{paramName} syntax an optional second parameter for arguments, so it becomes: $P!{paramName for SQL,
paramName for arguments[]}
Paul
A more appropriate solution would be to make use of the $X{..} query clauses introduced in 1.3.2. $X{..} can be used to introduce generic query clauses and can specify an arbitrary number of "parameters".
In this case, we could come up with something like $X{PreparedChunk, ChunkTextParameter, ValuesParameter} where PreparedChunk is a fixed ID that will be resolved to a clause handler class, ChunkTextParameter is the name of the String parameter that holds the query chunk text, and ValuesParameter is the name of the array or collection parameters that hold the values for the chunk prepared statement parameters.
One thing that we\'re not very fond of is SQL parsing. For instance, the code should ideally detect whether ? characters in the query chunk text are indeed prepared statement parameters or not. For instance, when used in String literals (e.g. WHERE column = "?") the ? character is not a prepared statement parameter.
We\'ll consider implementing this in the future. Also, you can implement this yourself external to the JasperReports vanilla code by extending the current JRJdbcQueryExecuter and providing a handler for PreparedChunk clauses.
Regards,
Lucian
Might be an obvious question, since the "Fixed in Release: " tag is not set yet, but did this make it into a 2.x or the 3.0 version?
If not, is there any timeline for implementing this feature?
Regards,
P.
This has not been implemented yet. When it will, the status will be changed to Closed.
Regards,
Lucian