Hi,
we update Mashzone from 9.12 to 10.3 and now we search how to build entire SQL query for JDBC.
I explain in my query if a user choose in input list All, we need a condition like this
APP LIKE ‘%’ OR APP IS NULL
If he choose value
APP LIKE ‘VALUE’
if he choose multi
APP IN (‘V1’, ‘V2’)
In 9.12 we can do it with parameter [PARAM] but now with :PARAM is impossible
Can you help us ?
Hi,
there are some clarifications needed.
First, I assume by “input list” you mean the “input list widget” that has a built-in functionality for single-, multi- and all-selection (see attached screenshot for comparison). The “all” you mention is then the button at the top of the widget where you can choose everything?
Kind regards,
Helmut
No the list can come from many way like other dataFeed.
I mean juste for query in JDBC on data feed
I don t have in my Mashzone version a widget like your SS
Then, in this case, the “All” value you mention is a specific value in the list? Or does this mean that “all values” are selected?
Additionally, you use “LIKE” in one of the queries, but “IN” (which looks for exact matches) in the other. Do you need exact matches, or do you only require a similarity (user chooses “ab”, and the values “abc”, “abcd” and “xab” are also valid then)?
My question is how is possible to build a dynamic SQL request in datafeed ?
In some case i need to get all values and no condition where (my solution is like with % but is possible is not the good way), in other case a condition where with a list (list come from data feed or widget or …)
And in other case i need juste a where condition with one value (my solution is LIKE ‘val’ but is possible is not the good way)
In SQL :
SELECT * FROM TABLE WHERE col LIKE ‘%’
SELECT * FROM TABLE WHERE col IN (‘val1’, ‘val2’ …)
SELECT * FROM TABLE WHERE col LIKE ‘val’
But it can be
SELECT * FROM TABLE
SELECT * FROM TABLE WHERE col IN (‘val1’, ‘val2’ …)
SELECT * FROM TABLE WHERE col =‘val’
the request depend of selection or not and other thing
Ok, now I understand your question.
We added SQL injection prevention in MZNG 10.x. Therefore, no SQL keywords, table or column names can be added to or changed in the statement dynamically - only values can now be replaced dynamically. So if you need changing parts regarding SQL keywords (LIKE, IN, WHERE etc), you will have to try to do that statically. I was asking my questions in order to help you create such a static statement.
Example: In the below query, the only thing that can be changed dynamically using a parameter is the value “test”.
SELECT a, b, x FROM table WHERE a = ‘test’ ORDER BY x