DB Connector: SQL parser doesn't recognize parameters for LIKE

[Stefan365]

e.g.
this is ok:

DELETE FROM TODO WHERE task = :#task

but datamapper cannot recognize these parameters:

DELETE FROM TODO WHERE task LIKE ':#task'
DELETE FROM TODO WHERE task LIKE '%:#task%'

[KurtStam]

I think you got an error? This error should have been shown at the action configuration time (see also #718). So it's nothing to do with the datamapper, but with the DB connector parser instead. That said, will try to fix it.

[Stefan365]

I don't see any error in web console.
It seems parser doesn't have problem with query, but I have problem with the parser.
If I create datamapper step subsequently,
the parameter task has not been recognized (probably because located inside single quotes).
Kindly see attached pictures.

[KurtStam]

@Stefan365 When fixing this I think your second example is slightly wrong as I think the % sign needs to be part of the input param itself. So as you state in your first example it should be able to handle:

DELETE FROM TODO WHERE task LIKE ':#task'

where the task param value can be set to 'Joe%'.

[KurtStam]

@Stefan365 as far as your datamapper screenshots, you can only get at the moment because of #718. So now you arrive at the datamapper with an invalid dataShape. You can verify the error in your javascript console when saving the action config (but bc of #718, it keeps going)

[Stefan365]

@KurtStam this is my result with
DELETE FROM TODO WHERE task LIKE ':#task'
(see picture)
It is not clear to me how to infiltrate '%' in input parameter.

e.g. you want to delete all todos which tasks contains first_name (from Contact). how do you proceed?

[KurtStam]

@Stefan365 Like I said before the inputDataShape was calculated incorrectly. This is what my commit fixes. So with that fix you should be able to issue exactly what you did:
DELETE FROM TODO WHERE task LIKE ':#task'
where you can make task to firstName. I think in the datamapper you maybe able to enhance that value to concatenate it with % signs. I think you can use a padStringLeft and padStringRight in the datamapper. You cannot do it in the query.

[KurtStam]

Please reopen if this is still an issue for you.

[Stefan365]

hi @KurtStam ,
I see this behavior again.
if I put e.g. DELETE FROM TODO WHERE task LIKE ':#task' as target connection action.
I don't have option to add Data Mapper step, since ':#task' is not visible for DM. If I change it to :#task, parameter is recognized as should be.

Checked on CR1(see https://gist.github.com/dsimansk/b839cbd39e553ec4680bcd8a3b47c3ff) and Syndesis master branch.

[Stefan365]

@KurtStam one more thing,
IMHO If I look at ... LIKE '%:#param'... vs. ... LIKE ':#param' + padStringLeft '%' in datamapper
I'm finding first option more user friendly than the second one (as a user how should I know, there is some 'padStringLeft' option in DM? moreover if I'm familiar with SQL regex)

Is there any possibility to allow " '%:#param' syntax" in the future?

[KurtStam]

@Stefan365 You're right, I mistyped above, there are not supposed to be any quotes.

At the moment finding a parameter relies on the fact that it starts with :#. So if we use a prefix % we're mixing a variable name with SQL syntax. Not sure Camel itself even supports that. That said I'm sure we can sort of 'preprocess' the statement if we really want to support it, but it's not that easy.

[Stefan365]

@KurtStam In that case (no quotes only) LIKE cannot be used at all.
Just one question, did it never work? I'm quite sure it worked (LIKE ':#param') for some time period.

[KurtStam]

@Stefan365 I added this test back then:

https://github.com/syndesisio/syndesis/blob/master/app/connector/sql/src/test/java/io/syndesis/connector/sql/common/SqlParserTest.java#L148-L155

and it's tested with Oracle, Postgresql, Mysql and Derby.

[KurtStam]

@Stefan365 I would say that if the Camel SQL component starts supporting it, then we should add support for it too. Can you open a ticket with the Camel project?

[Stefan365]

hi @KurtStam since July I don't work for RedHat, contact please someone else from syndesis-qe team.