Using backticks in column names leads to failure during INSERT with MySQL [DATAJDBC-381]

[spring-projects-issues]

Jens Schauder opened DATAJDBC-381 and commented

See DATAJDBC-380.

A property annotated as @Column(value = "virtual") in order to use keywords as column name lead to failure during insert.

Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'virtual': No value registered for key 'virtual' at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:355) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:398)

---

Issue Links:

• DATAJDBC-386 Always quote identifiers

• DATAJDBC-334 How to escape case sensitive identifiers?

• DATAJDBC-407 Escaping case-sensitive columns

Referenced from: pull request #156, and commits 1851e7f, d469324

[spring-projects-issues]

Jens Schauder commented

This is caused by the bind parameter not getting sanatized in the parameter source, resulting in the bind parmeter in the statement and in the parameter source not matching

[spring-projects-issues]

Mark Paluch commented

This is something we should consider in our dialect support so we can properly identify keywords and apply escaping

[spring-projects-issues]

Jens Schauder commented

After some internal discussion, we came up with the following plan:

1. By default always quote identifiers. This should solve the keyword problem and is also what DATAJDBC-386 is asking for.
2. Allow to not quote identifiers by configuration
3. Have a dialect specific, configurable conversion of identifiers generated by Spring Data so that the effective behavior for "normal" identifiers is the same as without quoting. E.g. for Orace we would convert identifiers to upper case which is the default in Oracle anyway. If one would specify an identifier, this conversion won't happen, so if one specifies a lower case column name the column in the database has actually to be lower case.

Code wise Identifiers should get their own class instead of being just Strings, with an additional field denoting if the identifier is to be quoted.
Quoting happens with help of the dialect, since different databases use different ways to quote identifiers

[spring-projects-issues]

Jens Schauder commented

This is fixed as a side effect of DATAJDBC-386