search

NCEAS / z-test-issues

Test issue imports from redmine
0 stars 0 forks source link

Exception when attributeName contains SQL keywords #423

Closed mbjones closed 7 years ago

mbjones commented 7 years ago

Author Name: Chad Burt (Chad Burt) Original Redmine Issue: 2737, https://projects.ecoinformatics.org/ecoinfo/issues/2737 Original Date: 2007-01-19 Original Assignee: Duane Costa

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

mbjones commented 7 years ago

Original Redmine Comment Author Name: ben leinfelder (ben leinfelder) Original Date: 2010-01-11T18:56:29Z

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.

mbjones commented 7 years ago

Original Redmine Comment Author Name: Duane Costa (Duane Costa) Original Date: 2012-01-23T23:36:20Z

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.

mbjones commented 7 years ago

Original Redmine Comment Author Name: Duane Costa (Duane Costa) Original Date: 2012-01-27T21:38:36Z

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.

mbjones commented 7 years ago

Original Redmine Comment Author Name: Redmine Admin (Redmine Admin) Original Date: 2013-03-27T21:21:09Z

Original Bugzilla ID was 2737