Get sql from report with parameters filled

[postateo]

Hi,
i need to take the resulset from report with parameters.

with getquery() i get the sql text but the parameters are not compiled with data.

this is my code:

JasperDesign jasperDesign = JRXmlLoader.load(reportFile);

JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);

System.out.println(jasperReport.getQuery().getText() );

 

for example if my parameter is "RAGIONE_SOCIALE" and i input "MATTEO" now return:

SELECT.... WHERE (RagSoc LIKE '%' + $P{RAGIONE_SOCIALE} + '%' OR $P{RAGIONE_SOCIALE} IS NULL)

i want:

SELECT.... WHERE (RagSoc LIKE '%MATTEO%' OR $P{RAGIONE_SOCIALE} IS NULL)

Tanks in advance.

 

 

 

[C-Box]

Hi this is quite hard to reach as you could have let me call "calculated parameters" ... means that a parameter that is used within the sql-query could be calculated by an expression that constructs another SubSql or also the content of another parameter...(so some logic depending of the content of other input values)

We did something similiar some years ago... when I remember right, I used the JRExpressionEvaluator to get the content from the given Parameters what is working fine, if the parameter are just simple objects contents (e.g. String/Integer/Boolean etc) but as I also use calculations of nested Parameters this is not working for 100%

 

just give it a try if your queries and parameters are simple

regards from Germany

C-Box

 

[postateo]

Many thanks for your reply

I.ll try your solution. For my problem would be fine also obtain a resulset with data. Like a datatable.  I need  the sql  for this reason.

Any other suggestions ?

Matteo from italy.

[C-Box]

What about "PRE-Collecting" your data with the given parameters and creating a custom datasource out of the resultset that you pass to the fillmanager instead of the connection?!?

So you have both... the JasperPrintObject for viewing/printing/exporting.... and also the resultset in form of your CustomDataSource !??!

(We do something similiar for copy the data to clipboard that the user can work with the report based data in excel or whatever other application :-)

hth + regards to Italy

C-Box

[teodord]

Hi,

I'd say you could use a custom made SQL query executer that performs a callback and gives you the ResultSet object once it has obtained it. Such a custom query executer could be a subclass of our own JRJdbcQueryExecuter in which you overide the createDatasource() method to put the JRResultSetDataSource instance into the report parameters map for retrieval.

You need a custom query executer because scriptlets would not help in this case. By the time the first scriptlet event is called, the report would have already consumed the first record in your result set.

If once you have obtained the result set, you don't want the report to continue, you could have the query executer return an emtpy data source and the report would have nothing to iterate on and thus stop.

I see that we don't have a getResultSet() method in our JRResultSetDataSource, but we could add it if you log a RFE on trackers. In the meatime, you could add it yourself to get on with your coding.

I hope this helps.
Teodor

[postateo]

I C-Box and Teaodor,

Tanks for your suggestion. The Teodor way is my first idea, but seems very hard to implement. A small code example is my only hope. 

Thanks anyway for your time. 

Matteo 

 

[teodord]

Hi,

 

You can see how custom query executers can be registered with JR by checking the JR Ultimate Guide at: http://jasperreports.sourceforge.net/JasperReports-Ultimate-Guide-3.pdf

Also, JR comes with many built-in query executer implementations and you can follow them as examples to make your own. But again, you just need to make one that extends the one we already have in JR and register it, to take control of your reports.

 

I hope this helps.

Teodor