[
https://issues.apache.org/jira/browse/CAMEL-25039?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18119700#comment-18119700
]
Claus Ibsen commented on CAMEL-25039:
-------------------------------------
Correcting the evidence I filed this on.
I checked the benchmark traces properly and the `:name` mistake is **already
handled**, by CAMEL-24869: `SourceValidator.validateYamlEndpoints` detects it
at authoring time, with casts and time literals covered, and `camel_edit_file`
refuses the write. The model was told, in those words, and acted on it:
```
camel_edit_file -> {"status":"invalid","errors":["Line 29: sql: :customer is
not a camel-sql named parameter
(the JDBC driver gets it as written and fails with a syntax error): write
:#customer for a header or a key
of a Map body, or :#${body[customer]} with a Simple expression (also
:country)"]}
```
and the next model turn: *"I see - for named parameters in SQL, I need to use
`:#` prefix. Let me fix that"*.
So the claim in this issue that the parameter syntax is why all 20 attempts of
that benchmark step failed is **wrong**. Those attempts failed on the SQL
dialect — `MERGE INTO ... ON DUPLICATE KEY UPDATE` (MySQL) and `ON CONFLICT`
(Postgres) against H2, where the standard `MERGE INTO ... USING (VALUES ...)
WHEN MATCHED` form is needed. That is database knowledge, not Camel's concern.
What remains, and is why I would still keep the change: CAMEL-24869 lives in
the authoring tooling, so it helps only an author who validates. Someone
hand-writing a route and running `camel run` gets nothing today — the query
reaches the database and comes back as the database's own syntax complaint. The
warning at endpoint creation covers that path.
It is therefore a smaller, optional improvement rather than the fix I
described, and it duplicates detection that already exists at another layer
(camel-sql cannot depend on camel-jbang-core, so the logic cannot be shared).
Closing this is a reasonable call if the duplication is not worth the reach.
> camel-sql - say when a query uses a named parameter without the # placeholder
> instead of passing it to the database
> -------------------------------------------------------------------------------------------------------------------
>
> Key: CAMEL-25039
> URL: https://issues.apache.org/jira/browse/CAMEL-25039
> Project: Camel
> Issue Type: Improvement
> Components: camel-sql
> Reporter: Claus Ibsen
> Priority: Major
>
> The sql component takes named parameters as {{:#name}} or {{:#${simple}}}. A
> query that uses any other shape is passed through to the database untouched,
> and the database's complaint names neither Camel nor the right syntax.
> {{DefaultSqlPrepareStatementStrategy.prepareQuery}} asks
> {{hasNamedParameters(query)}} and, when the answer is no, returns the query
> unchanged:
> {code:java}
> if (allowNamedParameters && hasNamedParameters(query)) {
> ...
> answer = replaceParams(query);
> } else {
> answer = query;
> }
> {code}
> The patterns it parses are {{:?name}} / {{:?${...}}} -- {{SqlComponent}} has
> already regex-replaced the {{placeholder}} character ({{#}} by default) with
> {{?}}. So {{:customer}} and {{:${body[customer]}}} match nothing, reach H2
> verbatim, and come back as:
> {noformat}
> Syntax error in SQL statement "INSERT INTO customers (id, country, orders)
> VALUES ([*]:$ body[customer] , :$ body[country] , 1) ..."
> {noformat}
> (the braces are blanked by H2's own JDBC-escape handling, which makes the
> message harder still to connect back to the query as written).
> Suggestion: when the query is passed through because nothing was recognised,
> but it contains a {{:}} followed by a letter or {{$}}, say so once -- either
> a WARN when the endpoint starts or a hint added to the failure -- naming the
> supported form. It should stay a hint and never reject a query: a colon
> appears legitimately in a Postgres {{::}} cast and in time literals, so the
> check must skip a doubled colon and must not fail the endpoint.
> Evidence: measuring how well a local model writes Camel routes, a step that
> registers a row with named parameters was failed by all 20 attempts across
> two 10-pass runs. The attempts were {{:customer}} (Spring JDBC style, no
> {{#}}), {{:${body[customer]}}} (no {{#}}), and the correct
> {{:#${body[customer]}}} paired with a MySQL-dialect upsert. Only the last of
> those is the author's own fault; the first two are shapes Camel could
> recognise as a mistake and name.
> See also the sibling issue about the catalog documentation of a component not
> carrying the syntax at all, only the option list.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
