Opened 4 months ago

Closed 4 months ago

Last modified 4 months ago

#7242 closed defect (fixed)

GDAL ODBC (MS Access driver), can't deal with table names that have spaces

Reported by: robe Owned by: warmerdam
Priority: normal Milestone: 2.2.4
Component: default Version: unspecified
Severity: normal Keywords:
Cc:

Description

I tested this on GDAL 2.1.3, GDAL 2.2.1, and GDAL 2.2.3 and all seemed to have the same issue.

This is under windows 7 64-bit

# This correctly gives list of layers

ogrinfo "C:\Temp\test.mdb"

INFO: Open of `C:\Temp\test.mdb'
      using driver `ODBC' successful.
1: a table
2: test

#But when I try to query the table that has spaces in name, I get this

ogrinfo "S:\Temp\test.mdb" "a table"

INFO: Open of `C:\Temp\test.mdb'
      using driver `ODBC' successful.

Layer name: a table
Geometry: Unknown (any)
ERROR 1: GetFeatureCount() failed on query SELECT COUNT(*) FROM a table.
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
Feature Count: 0
Layer SRS WKT:
(unknown)
ID: Integer (10.0)
A Number: Integer (10.0)
A Text: String (255.0)

Attached Is sample database I was using to troubleshoot the problem.

Attachments (3)

test.mdb (436.0 KB) - added by robe 4 months ago.
test.mdb
ticket7242.patch (2.2 KB) - added by Even Rouault 4 months ago.
ogrodbctablelayer.cpp.patch (858 bytes) - added by robe 4 months ago.
properly quote schema qualified layers

Download all attachments as: .zip

Change History (12)

Changed 4 months ago by robe

Attachment: test.mdb added

test.mdb

comment:1 Changed 4 months ago by Even Rouault

@robe Can you try the attached patch ? (based on trunk, but should hopefully apply on 2.2 branch) Just compile-tested on my side.

Changed 4 months ago by Even Rouault

Attachment: ticket7242.patch added

comment:2 Changed 4 months ago by robe

That seems to do the trick. I'll test on other ODBC sources besides MS Access to confirm they all work. But I think this one is a winner.

comment:3 Changed 4 months ago by Even Rouault

Resolution: fixed
Status: newclosed

In 41552:

ODBC: deal with table names that require double quoting (fixes #7242)

comment:4 Changed 4 months ago by Even Rouault

In 41553:

ODBC: deal with table names that require double quoting (fixes #7242)

comment:5 Changed 4 months ago by robe

Resolution: fixed
Status: closedreopened

Even sorry when I was testing SQL Server, I had by ODBC connection look something like this:

ogrinfo "ODBC:sa/whatever@SServer,Table1,Table2" Table1

So layers didn't come thru schema qualified

However if I do it this way so all tables are brought in with their schema qualification

Then it doesn't work

ogrinfo "ODBC:sa/whatever@SServer" dbo.Table1

I get this error:

ERROR 1: GetFeatureCount() failed on query SELECT COUNT(*) FROM "dbo.Table1".
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.Table1'

When it should be doing this:

SELECT COUNT(*) FROM "dbo"."Table1"

comment:6 Changed 4 months ago by Even Rouault

I let you propose a patch

comment:7 Changed 4 months ago by robe

This isn't perfect as I guess someone could create a table with a . in it

like myspecialtable.hasadot

and it won't correctly handle that, unless we move the logic the ogrodbcdatasource.cpp -> OGRODBCDataSource::Open where it explicitly glues the schema + . + the tablename together.

That seemed a little invasive though.

Changed 4 months ago by robe

Attachment: ogrodbctablelayer.cpp.patch added

properly quote schema qualified layers

comment:8 Changed 4 months ago by Even Rouault

Resolution: fixed
Status: reopenedclosed

In 41670:

ODBC: properly quote schema qualified layers (fixes #7242, patch by robe)

comment:9 Changed 4 months ago by Even Rouault

In 41671:

ODBC: properly quote schema qualified layers (fixes #7242, patch by robe)

Note: See TracTickets for help on using tickets.