Jaspersoft Studio substitutes question mark for parameter value in submitted SQL

[itservices_3]

I'm getting errors in a Jasper Studio report. I added logging to Studio so I could see the SQL submitted to the server. I see for example 

(case when ? = 1 then soitem.productnum else product.num end) AS PRODUCTNUM, 

where the original source contains 

(case when $P{ckShowHistoricalProductNumber} = 1 then soitem.productnum else product.num end) AS PRODUCTNUM,

That parameter is defined as

        <parameter name="ckShowHistoricalProductNumber" class="java.lang.String" isForPrompting="false">
            <parameterDescription><![CDATA[1,0]]></parameterDescription>
            <defaultValueExpression><![CDATA[1]]></defaultValueExpression>

Any idea why this is happening?

[yama818]

I thought it was an expected result that the parameter part would be output in the log with '? in the log, which I thought was an expected result.
Your SQL seems to be well written.
I tried to copy your SQL to see if it works, but I couldn't get it to error.

If you could attach the 'full error log' or the '*.jrxml file', I might be able to notice something.

 

[jgust]

A possible issue may be that your ckShowHistoricalProductNumber is a string (class="java.lang.String") but in your example, you are comparing that to an integer.
 

[From Source](case when $P{ckShowHistoricalProductNumber} = 1 then soitem.productnum else product.num end) AS PRODUCTNUM,[Parameter Definition]<parameter name="ckShowHistoricalProductNumber" class="java.lang.String" isForPrompting="false">            <parameterDescription><![CDATA[1,0]]></parameterDescription>            <defaultValueExpression><![CDATA[1]]></defaultValueExpression>[/code]

 

[itservices_3]

@yama818: I've never seen properly formed SQL with ? symbols in them. Can you send me a link to help me understand? 
I'm working with a report authored by FishBowl. It works somehow on their server. I've attached the jrxml.

@jgust: good catch on the type mismatch. However, I changed the parameter to integer but no difference in the output.

[lucianc]

$P{..} in queries are translated into JDBC prepared statement parameters, which use ? as placeholders in the query.

You can read more about ? placeholders in the JDBC prepared statement tutorial.

Regards,

Lucian

[jgust]

This is a complicated query.  Many $P!{} parameters that are adjusting the SQL.

Two parameters that look suspect are "cbShipToState/Province" and "cbApplyDateFilter".  Both of those contain non-quoted multi-word strings.  This is typically not allowed in SQL.

cbShipToState/Province
Usage: AND COALESCE(stateconst.name,'') LIKE $P!{cbShipToState/Province}
Multi-word non-quoted Values: Australian Capital Territory, British Columbia, District of Columbia

cbApplyDateFilter
Usage: AND $P!{cbApplyDateFilter} BETWEEN $P{dateRange1} AND DATE_SUB($P{dateRange2}, INTERVAL 1 SECOND)
Multi-word non-quoted Values: Scheduled Fulfillment Date, Date Created, Date Issued, SO Date Completed, Date Last Changed, SO Item Date Fulfilled

Maybe the workflow is to prompt the user all of this information and the multi-word strings are there to assist the user to make a decision and it is their responsibility to clean it up.

My recommendation is to create another debug version where you hardcode all values and then introduce a parameter one at a time until you find the culprit.

 

[yama818]

The '?' in the log means as per lucianc's answer.

I agree with jgust's proposed solution idea.

My other points I noticed are the following.

There is area where the use of $P{xxxxxxx} and $P!{xxxxxxx} seems incorrect.
The following manual is helpful in explaining the difference in use.
https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v790/using-parameters-queries
  --> Using Parameters in a SQL Query

For example, in your SQL, the part I was wondering about is this 'ORDER BY'.

-----
ORDER BY
    groupby, locationgroup.name, IF($P!{ckOrderByCustomer} = 1,customer.name,so.num), so.num
-----

It would seem correct to describe this as follows

-----
ORDER BY
    groupby, locationgroup.name, IF($P{ckOrderByCustomer} = 1,customer.name,so.num), so.num
-----

However, I do not know of any RDBMS that allows 'IF' in SQL.
So, is it a correct SQL syntax to begin with? I doubt it. (However, this is most likely my ignorance).
If you don't mind, please let me know what RDBMS you are using.
I think you can use 'CASE' instead of 'IF'.

 

[itservices_3]

Well, this has been educational. Thanks to all.

First, I did finally get the report to compile and even produce some plausible results. Parameter "cbShipToState/Province" had default value "%", which after substituting into the SQL, became just plain % (no quotes). Changing it to "'%'" did the trick. I don't yet know why other similar parameters such as "AlternateReportTitle" don't have the same problem. What puzzles me most is why the original report compiles and runs with no problem on the Fishbowl server.

I did not know about JDBC prepared statements and ? placeholders. Thanks to @lucianc for the link. 

@jgust: Your comment on multi-word non-quoted values refers to the parameter description, not the default value expression. In Jaspersoft Studio, there is a convention that if the parameter name starts with "cb" (standing, I suppose, for "combo box"), then the parameter description consists of pairs of substitution value, description. What the user sees is the description, what gets used is the substitution value.

@yama818: Your comment about the use of $P! might be right since the parameter is just boolean 0/1 and not a piece of SQL. From a query correctness point of view, I think either usage will produce the same result.