[MDEV-12167] CONNECT ENGINE table type=JDBC won't allow spaces in remote column names Created: 2017-02-25 Updated: 2017-03-04 Resolved: 2017-03-04 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Storage Engine - Connect |
| Affects Version/s: | 10.1.21 |
| Fix Version/s: | N/A |
| Type: | Bug | Priority: | Major |
| Reporter: | Robert Dyas | Assignee: | Olivier Bertrand |
| Resolution: | Not a Bug | Votes: | 0 |
| Labels: | None | ||
| Environment: |
CentOS7 |
||
| Description |
|
When trying to connect to a remote table (in this case the remote server is a MySQL 5.6 server with ANSI qutoes both enabled and disabled during test) any column name with a space in it generates an error as follows: Got error 174 'ExecuteQuery: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Provider' in 'field list'' from CONNECT Query is: SELECT `rm_VA_GLOBAL_December2016_CMS_Submission`.`Provider ID`, `rm_VA_GLOBAL_December2016_CMS_Submission`.`Hospital Name`, `rm_VA_GLOBAL_December2016_CMS_Submission`.`Address`, `rm_VA_GLOBAL_December2016_CMS_Submission`.`City`, `rm_VA_GLOBAL_December2016_CMS_Submission`.`State`, `rm_VA_GLOBAL_December2016_CMS_Submission`.`ZIP Code`, `rm_VA_GLOBAL_December2016_CMS_Submission`.`County Name` FROM `rm_VA_GLOBAL_December2016_CMS_Submission` WHERE ( ( TRUE ) AND ( TRUE ) ) LIMIT 0,50 |
| Comments |
| Comment by Robert Dyas [ 2017-03-03 ] |
|
I think this was supposed to be assigned to Olibia Bertrand (sp?) because this is a CONNECT ENGINE issue not a JDBC driver issue |
| Comment by Olivier Bertrand [ 2017-03-04 ] |
|
Sorry, the documentation is not explicit enough but as said in the "Table options" list, the QUOTED (and sometimes the QCHAR) options must be specified when column or table names must be quoted in the query sent to the data source. In your case, just adding QUOTED=1 to the create table should be enough. I shall update the documentation for future releases. |
| Comment by Robert Dyas [ 2017-03-04 ] |
|
Is there a reason why all queries don't quote the table and column names by default? |
| Comment by Olivier Bertrand [ 2017-03-04 ] |
|
Data sources don't use all the same quoting character. |
| Comment by Robert Dyas [ 2017-03-04 ] |
|
To make sure I understand correctly, for JDBC table type: QUOTED=1 QCHAR='"' |
| Comment by Olivier Bertrand [ 2017-03-04 ] |
|
Yes. As a matter of facts, QCHAR is only useful for data sources that cannot tell what quoting char they use. |