Support for $P!{} icw Prepared Statements

<>

 

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