Powered by Jitbit .Net Forum free trial version. dismiss

HomeProblems and issues

Post bug reports here. The forum is pre-moderated, your message will appear after being checked by us.

Use of Parameter Values Messages in this topic - RSS

falloutphil
falloutphil
Posts: 29


6/12/2017
falloutphil
falloutphil
Posts: 29
Hi,

When I try to use a drop down or check box in the Report Parameters, I give it an SQL query, for example:

SELECT unnest(enum_range(NULL::products)) as text, unnest(enum_range(NULL::products)) as value

(From the tooltips I assume I must provide both a display text field and a value that is returned)

This works fine, however when I come to use the resulting $selectedproduct, I cannot work out who to get this into a standard WHERE clause.

If I do:

SELECT $selectedproduct

I can see the correct returnted result - so the plumbing is working.

However what I want to do is for a drop-down or radio:

SELECT * FROM my_table WHERE product=$selectedproduct

Or for a check box I should be able to do

SELECT * FROM my_table WHERE product in $selectedProduct

I've tried several variations on the above with no luck. Can you give me a valid SQL example of using a drop down and a checkbox value generated from SQL in a subsequent WHERE clause?


Thanks!
0 link
falloutphil
falloutphil
Posts: 29


6/12/2017
falloutphil
falloutphil
Posts: 29
falloutphil wrote:

SELECT * FROM my_table WHERE product=$selectedproduct
!


To clarify the following works - hardcoding the final query:
select * from im_summary_view where product_class='Valid_Enum_Value'

However we have the situation where $selectedproduct='Valid_Enum_Value', but the following:

select * from im_summary_view where product_class='$selectedproduct'

Gives this error.
22P02: invalid input value for enum product_class: "@productClass"

So here the $selectedproduct is not being referenced - but we must use single quotes to reference an ENUM type?

So it looks like the entered values is stored as SQL variable @productClass - so the question is how do I reference it's contents in a WHERE clause as a ENUM value?
0 link
falloutphil
falloutphil
Posts: 29


6/12/2017
falloutphil
falloutphil
Posts: 29
I've worked it out, at least for drop down boxes - you need to specify the cast to enum and use no quotes:

select * from my_table where product=$selectedproduct::products

Still wondering how to do this with multiple values in check boxes?
edited by falloutphil on 6/12/2017
0 link
Gleb
Gleb
Administrator
Posts: 48


6/12/2017
Gleb
Gleb
Administrator
Posts: 48
Hi

Here is an example with IN syntax

SELECT * FROM my_table WHERE product in ($selectedProduct)


Note the brackets.

Behind the curtains, SQLwallet converts this construct into ($selectedproduct1,$selectedproduct2,....)

I did not test this with PostgreSQL enums though.
0 link
falloutphil
falloutphil
Posts: 29


6/12/2017
falloutphil
falloutphil
Posts: 29
Thanks for the reply Gleb.

Your suggestion:
SELECT * FROM my_table WHERE product in ($selectedProduct)

Will not work with Postgres ENUM types. I'm not exactly proud of the below, but it works!

SELECT * FROM my_table WHERE product in
(SELECT unnest(translate(string_to_array(selectedvals.*::text,',')::text,'()','')::products[]) FROM
(SELECT $selectedProduct) as selectedvals)


What would be good is if there was a way of getting at selected options as an array or similar rather than as row of n columns. Is there anything cleaner you could suggest?

For example if they could be returned as a comma separted string, or array a way simpler query can be imagined - where the 2 nested SELECTs and the string stripping of the brackets would be no longer required:

SELECT * FROM my_table WHERE product = ANY(
string_to_array($selectedProduct,',')::products[]
)

The problem here is that the current $selectedProduct expands to different parameters passed to string_to_array, where instead we need something similar that expands to a single string parameter that contains the comma separated values.

Any ideas?
0 link
Gleb
Gleb
Administrator
Posts: 48


6/12/2017
Gleb
Gleb
Administrator
Posts: 48
Hi

Yes, there is a way to inject the multiple-valued parameters as comma-separated values. Simply set Query type as "Stored procedure" rather than "Query".

(in fact, the "Stored procedure" mode was especially created to allow pass multi-select values as string parameters into stored procedures, as most of SQL dialects do not have support for array parameters)
edited by Gleb on 6/12/2017
0 link
falloutphil
falloutphil
Posts: 29


6/12/2017
falloutphil
falloutphil
Posts: 29
Thanks again Gleb - yes makes sense. I haven't yet played around with interacting with Stored Procedures - I'll take a look.
0 link
Gleb
Gleb
Administrator
Posts: 48


6/12/2017
Gleb
Gleb
Administrator
Posts: 48
Just to make it clear - you do not have to write a stored procedure, you can switch to "Stored Procedure" mode and still use your query as is. The only practical difference between the two modes is the way of injection of multi-valued parameters.
edited by Gleb on 6/12/2017
0 link
falloutphil
falloutphil
Posts: 29


6/12/2017
falloutphil
falloutphil
Posts: 29
Ahh got it. Thanks for pointing that out.
0 link






Powered by Jitbit Forum 8.3.8.0 © 2006-2013 Jitbit Software