Exception when attributeName contains SQL keywords
A dataset I'm working with has columns named "group" and "order". When trying to import the dataset into a sql database using the DataManager library I get this exception:
SQLException: ERROR: syntax error at or near "group"
The library has a problem with columns named after SQL keywords. While group may be easier to avoid, order would not when storing taxonomic info. There is a large list of reserved keywords for postgres including Year, Datestamp, etc that I haven't yet tested.
Here is a link to the problem dataset:
#2 Updated by Duane Costa about 8 years ago
I was able to test the fix suggested by Ben, wrapping the attribute names in quotes, for both Postgres and HSQL.
Completed with the following update:
Date: 2012-01-23 15:27:10 -0800 (Mon, 23 Jan 2012)
New Revision: 2221
Fix for Bug #2737: Exception when attributeName contains SQL keywords.
#3 Updated by Duane Costa about 8 years ago
Needed to rework the original bug fix because it did not work well with the logic for handling non-unique attribute names. Thanks to Gastil for catching this! (Read on for details.)
This has been fixed with update r2223.
On January 25, 2012, mgastil-buhl wrote:
The DML already had in it a method mangledName() which appended _Prime to attributeNames as many times as needed to make them unique within a table. (Of course it is bad practice to use non-unique column names but such exist.) The wrapping in double quotes needs to happen after the mangledName() method.
For example, knb-lter-mcr.31.23 returned this error (excerpt)
in its attempted sql table create statement.
Line 158 of DatabaseAdapter?.java is where mangledName() is called.
Line 154 is where getLegalDbFieldName() is called.
I can see the logic of how replacing any of a set of odd characters with underscore could make attributeNames suddenly non-unique. So, if mangle is done after legal, then at least stuff the _Prime inside the quotes, even if it means stripping the quotes, appending _Prime, and re-quoting.
I'll be uniquifying knb-lter-mcr.31.23 but there may be others out there.