Passing SQL to a Query Parameter

[bob_8]

I am using the PHP Client. I want to dynamically create a table at run time and pass the name of that table to a report. My Query syntax looks like:

<parameter name="x1" class="java.lang.String" isForPrompting="false">

  <defaultValueExpression><![CDATA["foo"]]></defaultValueExpression>

</parameter>

 

<queryString>

   <![CDATA[sELECT a, b, c FROM $P!{x1}]]>

</queryString>

 

If I set the isForPrompting to true and run this iStudio, it will prompt me for a table name and work just fine.

 

If I try to use the same in my PHP Code, I crash. 

 

Here is the call in PHP (Note: This code works fine if I don't include the query variable. In other words, I know I can pass "normal" parameters to the report)

 

 $controls = array ('Header1'=>array ($Header1),

  'Footer1'=>array ($Footer1),

  'Footer2'=>array ($Footer2),

  'SegVar' =>array ($SegVar),

  'SegId'  =>array ($SegId),

  'Image'  =>array ($logo),

  'x1' =>array ("foo")

    );

 

$report = $c->reportService()->runReport($reportName, 'pdf', null, null, $controls);

 

This is the error I get

 

Fatal error: Uncaught exception 'JaspersoftExceptionRESTRequestException' with message 'Resource URI:/reports/KIZVA/CUST-REV-ANALYSIS Type:com.jaspersoft.jasperserver.api.metadata.common.domain.InputControlsContainer not found' in /opt/lampp/htdocs/pam/lib/jrs-rest-php-client-master/src/Jaspersoft/Tool/RESTRequest.php:380 Stack trace: #0 /opt/lampp/htdocs/pam/lib/jrs-rest-php-client-master/src/Jaspersoft/Tool/RESTRequest.php(464): JaspersoftToolRESTRequest->handleError(404, Array, '{"message":"Res...') #1 /opt/lampp/htdocs/pam/lib/jrs-rest-php-client-master/src/Jaspersoft/Service/ReportService.php(40): JaspersoftToolRESTRequest->prepAndSend('http://107.191....', Array, 'GET', NULL, true) #2 /opt/lampp/htdocs/pam/JobRunner.php(486): JaspersoftServiceReportService->runReport('/reports/KIZVA/...', 'pdf', NULL, NULL, Array) #3 /opt/lampp/htdocs/pam/JobRunner.php(553): RunJob(Array) #4 {main} thrown in /opt/lampp/htdocs/pam/lib/jrs-rest-php-client-master/src/Jaspersoft/Tool/RESTRequest.php on line 380

 

Does anyone have any suggestions? I've tried this multiple ways (with $P and $P!) and any combination I could think of.

 

Any and all ideas would be greatly appreciated.

 

[bob_8]

UPDATE:

****** INCASE SOMEONE FROM JASPER/TIBCO SEES THIS - THIS IS A REALLY STOOPID BUG AND SHOULD BE FIXED ******

The problem had nothing to do with the parameter. The problems ended up being the report NAME - if the report name contains a dash, you'll get a resource

not found error. As soon as I removed the dash, everything worked just fine.

 

Hey, I know this (the community version) is free but....

[rajesh.sirsikar]

Hi Bob,

check my blog http://www.rajeshsirsikar.com/conditional-query-using-parameter-value-in-the-query-to-fetch-data/

you will get a better idea to resolve your issue.

Thanks,

Rajesh S

[bob_8]

Hi Rajesh, THANK YOU for your comment. I will look at your blog but my problem turned out to be that I had a dash in my report name. Quite a bit of time wasted before I figured it out. I do believe it is a bug in the PHP Client since the report works when run from iStudio.

 

Anyway... Thanks again for taking time to respond.

 

Bob