Bug #2737
closedException when attributeName contains SQL keywords
0%
Description
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:
http://sbcdata.lternet.edu/catalog/metacat?action=read&qformat=sbclter&docid=knb-lter-sbc.17.2&displaymodule=entity&entitytype=dataTable&entityindex=1
Updated by ben leinfelder almost 15 years ago
We should be able to wrap the attribute names in quotes to escape the reserved words. Not sure if it's standard across all RDBMS, but it seems like each DB adaptor could handle this.
Updated by Duane Costa almost 13 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:
Author: costa
Date: 2012-01-23 15:27:10 -0800 (Mon, 23 Jan 2012)
New Revision: 2221
Modified:
trunk/src/org/ecoinformatics/datamanager/database/DatabaseAdapter.java
trunk/src/org/ecoinformatics/datamanager/database/TableMonitor.java
trunk/test/org/ecoinformatics/datamanager/DataManagerTest.java
Log:
Fix for Bug #2737: Exception when attributeName contains SQL keywords.
Updated by Duane Costa almost 13 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)
...
"adcp_wave_measurment" FLOAT,
"adcp_wave_measurment"_Prime FLOAT,
"adcp_wave_measurment"_Prime_Prime FLOAT,
"adcp_wave_measurment"_Prime_Prime_Prime FLOAT,
"adcp_wave_measurment"_Prime_Prime_Prime_Prime FLOAT,
"Temp_01m_HeightAboveBottom" FLOAT,
...
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.