How escape parameter on quoted query

[Al3tz]

Hi,
I have the next query

SELECT payment_form_name,
       sum(COALESCE("01",0.00)) AS "01",
       sum(COALESCE("02",0.00)) AS "02",
       sum(COALESCE("03",0.00)) AS "03",
       sum(COALESCE("04",0.00)) AS "04",
       sum(COALESCE("05",0.00)) AS "05",
       sum(COALESCE("06",0.00)) AS "06",
       sum(COALESCE("07",0.00)) AS "07",
       sum(COALESCE("08",0.00)) AS "08",
       sum(COALESCE("09",0.00)) AS "09",
       sum(COALESCE("10",0.00)) AS "10",
       sum(COALESCE("11",0.00)) AS "11",
       sum(COALESCE("12",0.00)) AS "12"
FROM crosstab( 
            'SELECT payform_name,MONTH,amount
              FROM payments_forms_by_month AS pfm
              WHERE pfm.shop_id= 9
                AND YEAR=''2015''', 
            'SELECT to_char(generate_series( 
            CONCAT(''2015'',''-01-01'')::TIMESTAMP, 
            CONCAT(''2015'',''-12-31'')::TIMESTAMP, ''1 MONTH''),''MM'') m') 
        AS ct(payment_form_name text, 
              "01" numeric, 
              "02" numeric, 
              "03" numeric, 
              "04" numeric, 
              "05" numeric, 
              "06" numeric, 
              "07" numeric, 
              "08" numeric, 
              "09" numeric, 
              "10" numeric, 
              "11" numeric, 
              "12" numeric)
GROUP BY payment_form_name

This query run ok, but when i try use variables, query fail

SELECT payment_form_name,
       sum(COALESCE("01",0.00)) AS "01",
       sum(COALESCE("02",0.00)) AS "02",
       sum(COALESCE("03",0.00)) AS "03",
       sum(COALESCE("04",0.00)) AS "04",
       sum(COALESCE("05",0.00)) AS "05",
       sum(COALESCE("06",0.00)) AS "06",
       sum(COALESCE("07",0.00)) AS "07",
       sum(COALESCE("08",0.00)) AS "08",
       sum(COALESCE("09",0.00)) AS "09",
       sum(COALESCE("10",0.00)) AS "10",
       sum(COALESCE("11",0.00)) AS "11",
       sum(COALESCE("12",0.00)) AS "12"
FROM crosstab( 
            'SELECT payform_name,MONTH,amount
              FROM payments_forms_by_month AS pfm
              WHERE pfm.shop_id= 

$X{IN, pfm.shop_id,shop_id}

                AND YEAR=''2015''', 
            'SELECT to_char(generate_series( 
            CONCAT(''2015'',''-01-01'')::TIMESTAMP, 
            CONCAT(''2015'',''-12-31'')::TIMESTAMP, ''1 MONTH''),''MM'') m') 
        AS ct(payment_form_name text, 
              "01" numeric, 
              "02" numeric, 
              "03" numeric, 
              "04" numeric, 
              "05" numeric, 
              "06" numeric, 
              "07" numeric, 
              "08" numeric, 
              "09" numeric, 
              "10" numeric, 
              "11" numeric, 
              "12" numeric)
GROUP BY payment_form_name

Same when i try put

${year}

parameter

¿What is the correct way to achieve this?
Regards

[Arno_Mittelbach]

Hi Al3tz,

the $X{IN, pfm.shop_id,shop_id} replacement needs to be used without any additional “= or IN” clause. For example, the query could be

SELECT * FROM X WHERE $X{IN, pfm.shop_id,shop_id}

The parameter needed here needs to be a list parameter (i.e., a datasource parameter with multi selection). What do the error messages say?

Cheers,
Arno

[Al3tz]

Hi Arno,

The traceback is

Error
Consulta no pudo ser preparado: Error preparing statement for executing the report query : SELECT * FROM (SELECT * FROM (SELECT payment_form_name, sum(COALESCE(“01”,0.00)) AS “01”, sum(COALESCE(“02”,0.00)) AS “02”, sum(COALESCE(“03”,0.00)) AS “03”, sum(COALESCE(“04”,0.00)) AS “04”, sum(COALESCE(“05”,0.00)) AS “05”, sum(COALESCE(“06”,0.00)) AS “06”, sum(COALESCE(“07”,0.00)) AS “07”, sum(COALESCE(“08”,0.00)) AS “08”, sum(COALESCE(“09”,0.00)) AS “09”, sum(COALESCE(“10”,0.00)) AS “10”, sum(COALESCE(“11”,0.00)) AS “11”, sum(COALESCE(“12”,0.00)) AS “12” FROM crosstab( $$SELECT payform_name,MONTH,amount FROM payments_forms_by_month AS pfm WHERE pfm.shop_id= 9 AND YEAR=? $$, ‘SELECT to_char(generate_series( CONCAT(’‘2015’‘,’‘-01-01’‘)::TIMESTAMP, CONCAT(’‘2015’‘,’‘-12-31’‘)::TIMESTAMP, ‘‘1 MONTH’’),’‘MM’‘) m’) AS ct(payment_form_name text, “01” numeric, “02” numeric, “03” numeric, “04” numeric, “05” numeric, “06” numeric, “07” numeric, “08” numeric, “09” numeric, “10” numeric, “11” numeric, “12” numeric) GROUP BY payment_form_name) wrappedQry) limitQry WHERE 0 = 1 /* user: 6 / / report: 3771197 / / token: 388ea373-44fc-4ef9-a703-041a62fa5507 / / currentuser: 6 */

The problem is when i put the parameter, parameters is configured previusly in the correct way.

Example using ${year} (text paramater) in the same query, will be fail

SELECT payment_form_name,
       sum(COALESCE("01",0.00)) AS "01",
       sum(COALESCE("02",0.00)) AS "02",
       sum(COALESCE("03",0.00)) AS "03",
       sum(COALESCE("04",0.00)) AS "04",
       sum(COALESCE("05",0.00)) AS "05",
       sum(COALESCE("06",0.00)) AS "06",
       sum(COALESCE("07",0.00)) AS "07",
       sum(COALESCE("08",0.00)) AS "08",
       sum(COALESCE("09",0.00)) AS "09",
       sum(COALESCE("10",0.00)) AS "10",
       sum(COALESCE("11",0.00)) AS "11",
       sum(COALESCE("12",0.00)) AS "12"
FROM crosstab( 
            'SELECT payform_name,MONTH,amount
              FROM payments_forms_by_month AS pfm
              WHERE pfm.shop_id= 9
                AND YEAR=${year}', 
            'SELECT to_char(generate_series( 
            CONCAT(''2015'',''-01-01'')::TIMESTAMP, 
            CONCAT(''2015'',''-12-31'')::TIMESTAMP, ''1 MONTH''),''MM'') m') 
        AS ct(payment_form_name text, 
              "01" numeric, 
              "02" numeric, 
              "03" numeric, 
              "04" numeric, 
              "05" numeric, 
              "06" numeric, 
              "07" numeric, 
              "08" numeric, 
              "09" numeric, 
              "10" numeric, 
              "11" numeric, 
              "12" numeric)
GROUP BY payment_form_name

Thanks

[Arno_Mittelbach]

What report type are you using? Dynamic Lists?

[Al3tz]

Yes,
Dynamic List

[Arno_Mittelbach]

Ah .. I think I found the problem. You are using the parameter within a string, that is:

... FROM crosstab( 
            'SELECT payform_name,MONTH,amount
              FROM payments_forms_by_month AS pfm
              WHERE pfm.shop_id= 9
                AND YEAR=${year}', ...

here you would need to use $!{year} as ReportServer (or rather the jdbc driver) cannot correctly identify the type the parameter should have.

Arno

[Al3tz]

Arno,
In the case of ${year} parameter your guess is correct, now only left the problem with function in the form $X{}.

Now query fail using $X{IN, pfm.shop_id,shop_id} parameter

SELECT payment_form_name,
       sum(COALESCE("01",0.00)) AS "01",
       sum(COALESCE("02",0.00)) AS "02",
       sum(COALESCE("03",0.00)) AS "03",
       sum(COALESCE("04",0.00)) AS "04",
       sum(COALESCE("05",0.00)) AS "05",
       sum(COALESCE("06",0.00)) AS "06",
       sum(COALESCE("07",0.00)) AS "07",
       sum(COALESCE("08",0.00)) AS "08",
       sum(COALESCE("09",0.00)) AS "09",
       sum(COALESCE("10",0.00)) AS "10",
       sum(COALESCE("11",0.00)) AS "11",
       sum(COALESCE("12",0.00)) AS "12"
FROM crosstab( 
            'SELECT payform_name,MONTH,amount
              FROM payments_forms_by_month AS pfm
              WHERE pfm.shop_id= $X{IN, pfm.shop_id,shop_id}
                AND YEAR=$!{year}::text', 
            'SELECT to_char(generate_series( 
            CONCAT(''2015'',''-01-01'')::TIMESTAMP, 
            CONCAT(''2015'',''-12-31'')::TIMESTAMP, ''1 MONTH''),''MM'') m') 
        AS ct(payment_form_name text, 
              "01" numeric, 
              "02" numeric, 
              "03" numeric, 
              "04" numeric, 
              "05" numeric, 
              "06" numeric, 
              "07" numeric, 
              "08" numeric, 
              "09" numeric, 
              "10" numeric, 
              "11" numeric, 
              "12" numeric)
GROUP BY payment_form_name

Traceback

Error
Consulta no pudo ser preparado: Error preparing statement for executing the report query : SELECT * FROM (SELECT * FROM (SELECT payment_form_name, sum(COALESCE(“01”,0.00)) AS “01”, sum(COALESCE(“02”,0.00)) AS “02”, sum(COALESCE(“03”,0.00)) AS “03”, sum(COALESCE(“04”,0.00)) AS “04”, sum(COALESCE(“05”,0.00)) AS “05”, sum(COALESCE(“06”,0.00)) AS “06”, sum(COALESCE(“07”,0.00)) AS “07”, sum(COALESCE(“08”,0.00)) AS “08”, sum(COALESCE(“09”,0.00)) AS “09”, sum(COALESCE(“10”,0.00)) AS “10”, sum(COALESCE(“11”,0.00)) AS “11”, sum(COALESCE(“12”,0.00)) AS “12” FROM crosstab( ‘SELECT payform_name,MONTH,amount FROM payments_forms_by_month AS pfm WHERE pfm.shop_id= pfm.shop_id IN (?) AND YEAR=2016::text’, ‘SELECT to_char(generate_series( CONCAT(’‘2015’‘,’‘-01-01’‘)::TIMESTAMP, CONCAT(’‘2015’‘,’‘-12-31’‘)::TIMESTAMP, ‘‘1 MONTH’’),’‘MM’‘) m’) AS ct(payment_form_name text, “01” numeric, “02” numeric, “03” numeric, “04” numeric, “05” numeric, “06” numeric, “07” numeric, “08” numeric, “09” numeric, “10” numeric, “11” numeric, “12” numeric) GROUP BY payment_form_name) wrappedQry) limitQry WHERE 0 = 1 /* user: 6 / / report: 3771197 / / token: 65f42047-e9e4-4d3c-8199-8d23243438da / / currentuser: 6 */

The trick $!{} is not working!.

[Arno_Mittelbach]

Could you try the following

SELECT payment_form_name,
       sum(COALESCE("01",0.00)) AS "01",
       sum(COALESCE("02",0.00)) AS "02",
       sum(COALESCE("03",0.00)) AS "03",
       sum(COALESCE("04",0.00)) AS "04",
       sum(COALESCE("05",0.00)) AS "05",
       sum(COALESCE("06",0.00)) AS "06",
       sum(COALESCE("07",0.00)) AS "07",
       sum(COALESCE("08",0.00)) AS "08",
       sum(COALESCE("09",0.00)) AS "09",
       sum(COALESCE("10",0.00)) AS "10",
       sum(COALESCE("11",0.00)) AS "11",
       sum(COALESCE("12",0.00)) AS "12"
FROM crosstab( 
            'SELECT payform_name,MONTH,amount
              FROM payments_forms_by_month AS pfm
              WHERE pfm.shop_id IN ($!{shop_id.toString()}) 
                AND YEAR=$!{year}::text', 
            'SELECT to_char(generate_series( 
            CONCAT(''2015'',''-01-01'')::TIMESTAMP, 
            CONCAT(''2015'',''-12-31'')::TIMESTAMP, ''1 MONTH''),''MM'') m') 
        AS ct(payment_form_name text, 
              "01" numeric, 
              "02" numeric, 
              "03" numeric, 
              "04" numeric, 
              "05" numeric, 
              "06" numeric, 
              "07" numeric, 
              "08" numeric, 
              "09" numeric, 
              "10" numeric, 
              "11" numeric, 
              "12" numeric)
GROUP BY payment_form_name

[Al3tz]

Well,
If i try using the form

WHERE pfm.shop_id IN ($!{shop_id.toString()})

, this fail
Message error

Error
Consulta no pudo ser ejecutado: ERROR: syntax error at or near “[”

Try with form

WHERE pfm.shop_id IN ($!{shop_id[0].toString()})

, is success

But only take the first element in List, now the question is how to cast List Array in to integer or string values, for use with IN clausule.

Thanks

[Arno_Mittelbach]

Hm, this should have worked, but apparently there is a small bug that makes this fail, if the parameter has not yet been changed. So here is a different solution. Note, however, that this one is potentially dangerous since it might be used for SQL injection attacks:

WHERE pfm.shop_id IN ($!{shop_id.toString().startsWith("[") ? shop_id.toString().replace("[","\"").replace("]","\"").replaceAll(", ","\", \"") : shop_id.toString()})

what this does is the following: It first checks if the parameter is a normal list (which it should not but due to the bug this is the case, when having the default value). If so, it rewrites the string to be of the proper form. Otherwise, it uses the preferred mechanism.

Hope this helps
Arno