Parameters not working.

[ffw_rude]

 Hi,

I'm new to Jasper and iReports and have an issue. I have a request like 

SELECT * FROM test WHERE id=$P{param_test}

i declared the parameter in the "report inspector" and when i try to preview my report it's says : 

Error filling print... Error preparing statement for executing the report query : 

SELECT * FROM test WHERE id=?

 

I read a lot about the subject but nothing seems to work. 

 

If anyone have an idea ;)

 

Rude

[cbarlow3]

Maybe your parameter is the wrong datatype?  I just wrote a report, created a parameter (String in my case) called param_test, and used the following SQL statement:

SELECT * FROM CORE.SHARE AS SHARE
WHERE ID=$P{param_test}

and it worked just fine.  I attached a screen shot of my design view and the result (I entered."0100" as my input for $P{param_test} during this run).

I've also pasted the JRXML below, although it's a little lengthy, since the asterisk pulls all fields, and even though they don't appear individually in the SQL section, they do of course get defined individually as fields that are available to iReport.

Carl

Code:

<?xml version="1.0" encoding="UTF-8"?><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="ParamTest" language="groovy" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">	<property name="ireport.zoom" value="1.0"/>	<property name="ireport.x" value="0"/>	<property name="ireport.y" value="0"/>	<style name="BandedTable" isDefault="false">		<conditionalStyle>			<conditionExpression><![CDATA[$V{REPORT_COUNT}%2==1]]></conditionExpression>			<style isDefault="false" style="BandedTable" backcolor="#99FF99"/>		</conditionalStyle>	</style>	<parameter name="param_test" class="java.lang.String"/>	<queryString>		<![CDATA[sELECT * FROM CORE.SHARE AS SHAREWHERE ID=$P{param_test}]]>	</queryString>	<field name="SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="PARENT_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="STORED_ACCESS_KEY" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="ORDINAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="LAST_FM_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="ID" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="DESCRIPTION" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="TYPE_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="PROCESSING" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="BRANCH_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="OPEN_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CLOSE_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CHARGE_OFF_TYPE_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CHARGE_OFF_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CHARGE_OFF_AMOUNT" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="MONETARY_PURGE_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="LAST_MONETARY_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="LAST_ACTIVITY_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="BALANCE" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="COURTESY_PAY_LIMIT" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="OPENING_BALANCE" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="ORIGINAL_AMOUNT" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="ORIGINAL_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CERT_NUMBER" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CERT_DIVIDENDS_AVAILABLE" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CERT_OID_YIELD_TO_MATURITY" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CERT_OID_BALANCE" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="MATURITY_POSTING" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="MATURITY_FREQUENCY" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="MATURITY_PERIOD" class="java.lang.Integer">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="MATURITY_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="MATURITY_LAST_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="STMT_MAIL_GROUP_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="STMT_CUTOFF_GROUP_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="STMT_CUTOFF_LAST_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="STMT_CUTOFF_LAST_TRAN_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="STMT_CUTOFF_PRIOR_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="STMT_CUTOFF_PRIOR_TRAN_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="STMT_REG_E_COUNT" class="java.lang.Integer">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="TAX_PLAN_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="DIVIDEND_POSTING" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="DIVIDEND_CUSTOM_RATE" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="DIVIDEND_LAST_AMOUNT" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="DIVIDEND_LAST_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="DIVIDEND_ACCRUED_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="DIVIDEND_ACCRUED_AMOUNT" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="DIVIDEND_APYE_START_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="DIVIDEND_APYE_DAILY_BAL" class="java.math.BigDecimal">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="REG_D_THIRD_PARTY_COUNT" class="java.lang.Integer">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="REG_D_TRANSFER_COUNT" class="java.lang.Integer">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="MAIL_PERSON_ADDR_LINK_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="TAX_PERSON_SERIAL" class="java.lang.Long">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CRED_REP_PRIMARY_ECOA_CODE" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CRED_REP_PRIMARY_CONS_INFO_IND" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CRED_REP_ACCOUNT_TYPE" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CRED_REP_SPEC_COMM_CODE" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CRED_REP_COMP_COND_CODE" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CRED_REP_BANKRUPTCY_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CRED_REP_FIRST_DQ_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CRED_REP_ACCOUNT_STATUS" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="CRED_REP_LAST_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="COLLECTION_STATUS" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="COLLECTION_HANDLING" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="COLLECTION_NOTICE_COUNT" class="java.lang.Integer">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="COLLECTION_NOTICE_DATE" class="java.sql.Date">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<background>		<band splitType="Stretch"/>	</background>	<detail>		<band height="20" splitType="Stretch">			<textField>				<reportElement x="0" y="0" width="72" height="20"/>				<textElement/>				<textFieldExpression class="java.lang.String"><![CDATA[$F{ID}]]></textFieldExpression>			</textField>			<textField>				<reportElement x="72" y="0" width="197" height="20"/>				<textElement/>				<textFieldExpression class="java.lang.String"><![CDATA[$F{DESCRIPTION}]]></textFieldExpression>			</textField>			<textField pattern="¤ #,##0.00">				<reportElement x="269" y="0" width="126" height="20"/>				<textElement textAlignment="Right"/>				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{BALANCE}]]></textFieldExpression>			</textField>		</band>	</detail></jasperReport>

[ffw_rude]

 Hi and thx for your answer. In fact i just found what was the problem and it was a concatenation problem. I tried to to a

$P{Date}+" 00:00:00" and ireport was not happy about it.

Now my report works fine in iReports with the params with prompt but jasper seems to tell me there's a null Pointer exeception when i try to run it.

 

I made sure it was the JRXML of the report who works but i can't get it working in Jasper

[cbarlow3]

Make sure you have a valid default value for your parameters.  In JasperServer--unlike iReport--it won't automatically prompt you for the parameters before it attempts to run the report, so try hardcoding a sensible default value, redeploy, and see if that fixes the problem.  If it does, your next step is to make your default something sensible but maybe not hardcoded (like new Date() instead of hardcoding a specific date that you know has good data associated with it) and create input controls for each of your parameters.  Then, when you run the report in JasperServer, if you want to change the parameters from the default values, you have to click the small "Report Options" icon in the upper left to get the dialog to come up, fill in the parameters, and the report will run again with the new values.  I wish there was a way in JasperServer to force the parameters prompt prior to running the report, but I don't know of a way.

Hope that's all the problem is.  Good luck.

Carl

[ffw_rude]

 I'm not sure i understood so let me reformulate

 

I open iReport and set Default values to my parameters.

Save in Ireport

Edit and replace the JRXML in jasper.

Then i don't understand what's next. I don't see the Report options... 

[cbarlow3]

In JasperServer (not iReport), the way the user inputs parameters when they run a report is they click on the "Report Options" icon (my first screenshot has it outlined), and that will bring up a dialog like the one in my second screenshot that groups together your various parameter input controls.  BUT...you won't even see this icon in JasperServer unless the report you've run has Input Controls defined.  This you do in iReport in the Repository Navigator by right clicking on Input Controls for your report and choosing "Create a local input control" and then defining the name (make it the same as your parameter), the data type, etc.  My third screenshot shows how these look in the repository navigator of iReport after you've created them.

Carl

P.S.: I have no idea if that's what's causing your problem, by the way...I just know that you have an input parameter, your report works in iReport but not after deployment to JasperServer, and this is one obvious difference between the two runtime environments:  iReport prompts you before running the report, while JasperServer runs the report with whatever default values (if any) you've defined and only then can you change the parameters and re-run the report...and only if you've defined input controls.

 

[ffw_rude]

 Thx for you help.

I already defined my 4 inputs controls (have two ints and two dates). Thing is that when i execute i have a nullpointerexception even BEFORE i can see the prompt box forom jasper parameters.

[cbarlow3]

Well, again, my theory about the null pointer (and it's a half-baked theory, I admit) is that these parameters might not have default values appropriate defined.  Since JasperServer attempts to run the report FIRST wtih the default values before you as a user get any chance to specify other values, I was thinking that might be the cause of the exception.  If you make sure you've defined valid default values and you still get the null pointer exception, then we know it's something else.

Carl

[ffw_rude]

 Okay so i'll try to add values to the default values in irepot. I'll let you know how it goes

[ffw_rude]

 Re.

 

I tried to put a default value but that didn't work. I have the same null pointer exeption when i try to launch the jasper report

[ruudheemskerk]

You get nullpointer exception when you define an inputcontrol but forget to create the parameter within the report for this input control.

As stated: the name of the inputcontrol must be exactly the same (case sensitive) as the pareameter.

Suppose: Input control: DATE_UNTILL label Date Untill then you have to create a parameter: DATE_UNTILL

Regards Ruud

 

[mdahlman]

The details of the problem aren't really clear to me.

$P{Date}+" 00:00:00"

Is the parameter called Date a Date? If so, then this addition doesn't make sense. If the Date is really a string then that line make sense... but treating dates as string opens up lots of other problems.

Do you no longer have a problem with the parameter Param_test? The report works in iReport? Is this more of a JasperServer question?

I think with a more systematic description of the issue people will be more likely to have suggestions.

Regards,
Matt

[ffw_rude]

 Hi.

So the report works well in iReport and indeed the date parameter is a date. I tried with a string but that's not working either.

 (thought i wrote this sorry).

 

But i think you may have pointed the good thing. 

 

I have to put a DATE into the parameter but the informix field is a DATETIME. That's why i used this. But how can i do ? Because iReport work and jasper don't. Do i don't think it's a java problem (but i might be wrong).

 

[ffw_rude]

 I got it ! my bad. I did a rookie mistake. When putting the parameters in Jasperserver i use a "P" for "Parameters" and in iReport it was "parameters"

 

So ... my bad and thx you all for your help

[mdahlman]

Thanks for posting the resolution!

Good job tracking down the problem.

Regards,
Matt

[peter_pon]

Hi,

I have a strange problem:

my input control is                          multiselect query

the query to populate it is like         select id, name from myfunction($P{user});

the default value is supplied

when report runs the multiselect list appears for the split of a second (properly poulated)

then dissapears.

I can using reset button to select some item while report is being refreshed and then the list stays as it supposed to be.

I tested the same control without a function call just a simple sql. It behaves exactly as with the function call.

What could be the problem?

Thanks,

Peter.