Jump to content
  • Support for $P!{} icw Prepared Statements

    Assigned User teodord
    CategoryFeature request

    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[]}




    -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.






    I posted this also on the JasperReport forum: http://www.jasperforge.org/index.php?option=com_joomlaboard&Itemid=215&func=view&id=33253&catid=8

    User Feedback

    Recommended Comments

    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? :)



    Link to comment
    Share on other sites

    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[]}




    Link to comment
    Share on other sites

    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.




    Link to comment
    Share on other sites

    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?





    Link to comment
    Share on other sites

    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

  • Create New...