After searching, I found that there're three issues on this problem. #977 #890 and #876
I think it would be better if we rename column 'KEY' to something else so that we can solve this once for all. A "DEFAULT_SQL" should at least support multiple databases that are widely used, such as Oracle, MySQL and MS SQL.
Although we can use spring.cloud.config.server.jdbc.sql to configure a customized SQL that is compatible to the DB we're using, but I believe this should be only be used when users have special needs such as they're using a rarely used DB engine or they have reasons to use customized table definitions.
According to this statistics, Oracle + MySQL + MS SQL, they have nearly 70% of market shares in total.
If 70% of JDBC-backend users need to write their own sql, this DEFAULT_SQL would be useless to most users.
So there're two available solutions:
Rename column 'KEY' to another name and all three databases and maybe more databases will be supported.
Change the DEFAULT_SQL and make it compatible with more databases.
I tried to write a single SQL that is compatible with MS SQL, MySQL and Oracle DB.
SQL
Oracle
MySQL
MSSQL
SELECT KEY, VALUE from PROPERTIES where APPLICATION=? and PROFILE=? and LABEL=?
Compatible
Incompatible
Incompatible
SELECT `KEY`, `VALUE` from PROPERTIES where APPLICATION=? and PROFILE=? and LABEL=?
Incompatible
Compatible
Incompatible
SELECT PROPERTIES.KEY, PROPERTIES.VALUE from PROPERTIES where APPLICATION=? and PROFILE=? and LABEL=?
Compatible
Compatible
Incompatible
SELECT [KEY], [VALUE] from PROPERTIES where APPLICATION=? and PROFILE=? and LABEL=?
Incompatible
Incompatible
Compatible
So, there seems to be no single SQL that supports all these three databases.
The best compatible one is the third line which supports MySQL and OracleDB.
Personally, I prefer Solution 1. After all, it's best practice to avoid using DB keyword in column names.
spencergibb
commented
5 years ago
After searching, I found that there're three issues on this problem. #977 #890 and #876 I think it would be better if we rename column 'KEY' to something else so that we can solve this once for all. A "DEFAULT_SQL" should at least support multiple databases that are widely used, such as Oracle, MySQL and MS SQL. Although we can use
spring.cloud.config.server.jdbc.sqlto configure a customized SQL that is compatible to the DB we're using, but I believe this should be only be used when users have special needs such as they're using a rarely used DB engine or they have reasons to use customized table definitions.According to this statistics, Oracle + MySQL + MS SQL, they have nearly 70% of market shares in total. If 70% of JDBC-backend users need to write their own sql, this DEFAULT_SQL would be useless to most users.
So there're two available solutions:
I tried to write a single SQL that is compatible with MS SQL, MySQL and Oracle DB.
So, there seems to be no single SQL that supports all these three databases. The best compatible one is the third line which supports MySQL and OracleDB.
Personally, I prefer Solution 1. After all, it's best practice to avoid using DB keyword in column names.