Bug in AddGeometryColumn?

[Milton]

Hi there Justin

I've been playing a little with GeoDB for a side project, and I've run
into something that looks like a bug, but I think I may be missing
something. If I run something like:
CALL AddGeometryColumn(NULL, 'my_table', 'my_column', -1,
'geometry', 2);

then the Java code will try to execute this statement:
ALTER TABLE "my_table" ADD "my_column" GEOMETRY COMMENT 'GEOMETRY'

Note the quotes around my_table and my_column. This results in a H2
error that reads:
Table "my_table" not found

Without the quotes, the query runs fine. The quotes are added by
explicitly calling the esc() method to do just that. So, it seems
there should be no esc() method, right? If I change the code then it
runs just fine. I've tried this using GeoDB 0.6, 0.7-RC1 and 0.7-RC2.

Any ideas? I suppose that this method is called routinely from Java,
or is it normally not called at all?

Thanks
Milton

[Justin Deoliveira]

Hi Milton,

Unfortunately I think is just how case is handled with h2, which defaults to upper case. When you create the table with a statement like:

CREATE TABLE my_table (my_column ... ) what actually gets created is this:

MY_TABLE(MY_COLUMN)

So the update command should look like:

CALL AddGeometryColumn(NULL, 'MY_TABLE', 'MY_COLUMN', -1,
'geometry', 2);

If you were instead to create the table like:

CREATE TABLE "my_table" ("my_column" ... )

Using quotes to force the names the lower case, then your original call should work.

Hope I understood the issue correctly, let me know if that works for you.

-Justin

--
Justin Deoliveira

OpenGeo - http://opengeo.org

Enterprise support for open source geospatial.

[Justin Deoliveira]

Hi Milton,

Yeah, it is a tough call. On the one side if you don't quote then you are more forgiving to case sensitivity, but on the other hand if you don't you can potentially not allow any way to match table and column names that are lower case.

We have this same problem in geotools jdbc datastores... and generally we have found it is just less of a mess to be explicit, which means users have to match case exactly.

-Justin

On Wed, Jan 11, 2012 at 6:07 AM, Milton Jonathan <mil...@tecgraf.puc-rio.br> wrote:

Perfect Justin!

Yeah, that was the issue: upper-case vs. lower-case! Thanks a bunch :)

Hey, just wondering now: why call that esc() method to use quotes? If you didn't use it, wouldn't it work as case-insensitive? (of course, you probably had a good reason to implement this in the first place, I'm just thinking loud here)

Thanks again
Milton

--

Milton Jonathan
Grupo GIS e Meio Ambiente
Tecgraf/PUC-Rio
Tel: +55-21-3527-2502