custom query executer with hyperlink

[ict-programmer]

I have a custom query executer which extends JRAbstractQueryExecuter.  Everything seems to be working beautifully with that--meaning:

1. I created a new query in the repository
2. I attached that query to an input control
3. I attached the input control to a report
4. When the report runs, the expected items are in the input control dropdown, and
5. Changing the selection changes the report as expected.

The problem comes when I go to that report via a hyperlink from another report.  Let's say I have a report showing all my bank accounts.  Then I want to click on an account, and it opens another report with all the transactions for that account.  In the input control, all those bank accounts are listed, so that on the transactions report I can switch between accounts without having to go back to the overview report.

If I use my custom query executer, when I click on the hyperlink to see the transactions for an account, it always only shows the first item from the input control dropdown, no matter which account is clicked.  If I create a dummy database that uses SQL, make a query that produces the same results as my custom query, and make the input control draw from that query, then my hyperlinks all work exactly as expected.

So I know that my hyperlink parameters are all correct, and I know that once the report is opened, I can switch to anything in the dropdown successfully.

Is it possible that there is something in the timing of how a report is executed that is different when using a custom query executer?  Why won't the hyperlink pull up the correct report parameters right away?

I'm using JasperServer 5.5.  If I need to post some portion of code, let me know what would be helpful to see.  Thanks!

UPDATE:

Thanks, marianol, for the suggestions.  I switched the logging to DEBUG, and this is the output in jasperserver.log:

Note that in both of the cases below, the url is: server:8443/jasperserver/flow.html?_flowId=viewReportFlow&
reportUnit=%2FPersonal%2FAccountStatement-DD&deb_accountcode=10001&PeriodStartDate=2015-04-01&PeriodEndDate=2015-04-18&
_eventId_drillReport=&_flowExecutionKey=e1s2&reportLocale=en_US

Using my custom query executer for the input control:

• 2015-04-18 12:01:32,363 DEBUG valueQueryLog,http-bio-8443-exec-6:2133 - query took 54 ms: personal
• 2015-04-18 12:01:32,366 DEBUG valueQueryLog,http-bio-8443-exec-6:2134 - params: {LoggedInUser=MetadataUserDetails: my_user, LoggedInUsername=my_user, datasourceURI=/BackEndResources/DataSources/JDB_UserAccounts, REPORT_DATA_SOURCE=org.test.UserDataSource@310e6e9c}

Note that the lines above are for my custom query on the input control, where "personal" is the complete text of the query.

 

• 2015-04-18 12:01:32,798 DEBUG JRJdbcQueryExecuter,pool-4-thread-37:254 - SQL query string: EXECUTE FUNCTION getPersonalAccountData( ? , ? , ? );
• 2015-04-18 12:01:32,799 DEBUG JRJdbcQueryExecuter,pool-4-thread-37:396 - Parameter #1 (deb_accountcode of type java.lang.String): 10000    
• 2015-04-18 12:01:32,800 DEBUG JRJdbcQueryExecuter,pool-4-thread-37:396 - Parameter #2 (PeriodStartDate of type java.sql.Date): 2015-04-01
• 2015-04-18 12:01:32,801 DEBUG JRJdbcQueryExecuter,pool-4-thread-37:396 - Parameter #3 (PeriodEndDate of type java.sql.Date): 2015-04-18
   

Using a standard SQL query executer for the input control:

• 2015-04-18 12:19:12,678 DEBUG JRJdbcQueryExecuter,http-bio-8443-exec-1:254 - SQL query string: SELECT TRIM(accountcode) AS deb_accountcode, TRIM(accountcode) AS accountcode, shortname FROM deb_master WHERE dr_deb_notes LIKE '%' ||  ?  || '%' ORDER BY 1
• 2015-04-18 12:19:12,681 DEBUG JRJdbcQueryExecuter,http-bio-8443-exec-1:396 - Parameter #1 (LoggedInUsername of type java.lang.String): my_user
• 2015-04-18 12:19:12,702 DEBUG valueQueryLog,http-bio-8443-exec-1:2133 - query took 25 ms: SELECT TRIM(accountcode) AS deb_accountcode, TRIM(accountcode) AS accountcode, shortname FROM deb_master WHERE dr_deb_notes LIKE '%' ||  $P{LoggedInUsername}  || '%' ORDER BY 1
• 2015-04-18 12:19:12,705 DEBUG valueQueryLog,http-bio-8443-exec-1:2134 - params: {DATABASE_TIMEZONE=sun.util.calendar.ZoneInfo[id="Pacific/Port_Moresby",offset=36000000,dstSavings=0,useDaylight=false,transitions=0,lastRule=null], LoggedInUser=MetadataUserDetails: my_user, LoggedInUsername=my_user, REPORT_CONNECTION=org.apache.commons.dbcp.PoolableConnection@68be9898, datasourceURI=/BackEndResources/DataSources/InformixPronto}

Note that all of the above relates to the input control query.

 

• 2015-04-18 12:19:13,141 DEBUG JRJdbcQueryExecuter,pool-4-thread-39:254 - SQL query string: EXECUTE FUNCTION getPersonalAccountData( ? , ? , ? );
• 2015-04-18 12:19:13,142 DEBUG JRJdbcQueryExecuter,pool-4-thread-39:396 - Parameter #1 (deb_accountcode of type java.lang.String): 10001
• 2015-04-18 12:19:13,143 DEBUG JRJdbcQueryExecuter,pool-4-thread-39:396 - Parameter #2 (PeriodStartDate of type java.sql.Date): 2015-04-01
• 2015-04-18 12:19:13,144 DEBUG JRJdbcQueryExecuter,pool-4-thread-39:396 - Parameter #3 (PeriodEndDate of type java.sql.Date): 2015-04-18

I don't see any indications of why in one case, Parameter #1 for the report query would be "10000" and it the other case it would be the correct "10001".  If I just input the URL above, I get the same behavior as I do with the hyperlink.  There is no default value set for that report parameter.

[marianol]

Oops this is a strange one... so I have more questions than answers :) this may point you in the right direction until somebody more knowledgable can help more.
I think you are in the right path thinking on a timing issue, you can add net.sf.jasperreports.engine.query.JRJdbcQueryExecuter to DEBUG to your server log (login as superuser and go to Server Settings -> Log Settings) here you have some info on the JRS logs https://community.jaspersoft.com/wiki/log4jproperties-jasperreports-server

You can also add some logging in your cusotm query executor to see if there is something going on there,

Do you see anything in the jasperserver.log?
What happends if you send the parameter to the report vis URL Get request (HTTP API) ?
Does the report has a devault value for that parameter? is that default value the first item on the list?

 

[ict-programmer]

@marianol - Thanks for the thoughts.  See my update.