v.in.db - odbc-driver - access-database: not working

[hellik]

from the dev-ML:

 http://lists.osgeo.org/pipermail/grass-dev/2012-March/058224.html

ODBC and GRASS for Windows

Have posted some weeks ago on a problem with the ODBC driver in the user list
but got no answer until now. I am working on a master thesis and would like
to use GRASS as the core of a solution with FOSGIS, but there seem to be
problems with the ODBC driver in GRASS for Windows. Beeing not a programmer
I am not really sure about this and would like to consult the developers of
GRASS for a suggestion how to proceed. Please correct me, if my point of
view should be wrong ! 

My former post in the user list is this
http://osgeo-org.1560.n6.nabble.com/ODBC-driver-in-GRASS-td4558182.html

Is there any chance to solve the problem with reading ODBC data bases
(particularly MS Access) in the near future? Havn't opened a ticket yet. 

Thanks a lot for any feedback, 
regards, Christine

attached a simple test access database (testinput.mdb - zipped) - simple x,y-point-data - for the nc-sample dataset.

tested here both with WinGRASS-6.4.3svn-r51149-195-Setup.exe and WinGRASS-7.0.svn-r51155-194-Setup.exe

the v.in.db-wxgui freezes if database (i.e. C:\wd\access\testinput.mdb) is entered in the gui

in the wxgui-commandline

v.in.db driver=odbc database="C:\wd\access\testinput.mdb" table=grassinput key=ID x=xcoor y=ycoor output=mdbvector

a error message pops up that odbc.exe isn't working.

Helmut

[hellik]

test-mdb for nc-sample data set

[hellik]

Replying to hellik:

[...] tested WinGRASS-7.0.svn-r51155-194-Setup.exe

tested with osgeo4w-wingrass7-nightly build and attached sample dataset (DEBUG=3)

GRASS 7.0.svn> v.in.db driver=odbc database="C:\dl\mdb\testinput.mdb" table=grassinput x=xcoor y=ycoor key=keyid out=vindb1

WARNING: Unable to open database <C:\dl\mdb\testinput.mdb> by driver <odbc>
ERROR: Unable to open database <$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db>
       by driver <sqlite>

GRASS 7.0.svn> g.gisenv set=DEBUG=3
GRASS 7.0.svn> v.in.db driver=odbc database="C:\dl\mdb\testinput.mdb" table=grassinput x=xcoor y=ycoor key=keyid out=vindb2
D2/3: dbDbmscap(): opendir [C:\OSGeo4W\apps\grass\grass-7.0.svn\driver\db\]
D2/3: Vect_open_new(): name = vindb2
D1/3: Vect_set_thresh(): thresh = 0.000000
D1/3: V1_open_new_nat(): name = vindb2
D1/3: Vect_get_thresh(): thresh = 0.000000
D1/3: dig__write_head()
D1/3: write coor size (0) to head
D2/3: coor body offset 18
D3/3: dig_init_plus()
D3/3: dig_cidx_init()
D1/3: Vect_open_sidx(): name = vindb2 mapset= g7 mode = new
D1/3: Memory based spatial index
D2/3: G__read_Cell_head
D2/3: G__read_Cell_head_array
D3/3: region item: proj:       99
D3/3: region item: zone:       0
D3/3: region item: north:      228500
D3/3: region item: south:      215000
D3/3: region item: east:       645000
D3/3: region item: west:       630000
D3/3: region item: cols:       1500
D3/3: region item: rows:       1350
D3/3: region item: e-w resol:  10
D3/3: region item: n-s resol:  10
D3/3: region item: top:        1
D3/3: region item: bottom:     0
D3/3: region item: cols3:      1500
D3/3: region item: rows3:      1350
D3/3: region item: depths:     1
D3/3: region item: e-w resol3: 10
D3/3: region item: n-s resol3: 10
D3/3: region item: t-b resol:  1
D3/3: Vect_hist_command()
D3/3: G_recreate_command()
D1/3: Vect_default_field_info(): map = vindb2 field = 1
D2/3: drv = sqlite db = $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db
D3/3: db_start_driver_open_database(): drvname='odbc', dbname='C:\dl\mdb\testinput.mdb'
D2/3: dbDbmscap(): opendir [C:\OSGeo4W\apps\grass\grass-7.0.svn\driver\db\]
D3/3: win_spawn: args = C:\windows\system32\cmd.exe /c "C:\OSGeo4W\apps\grass\grass-7.0.svn\driver\db\odbc.exe"
D1/3: db_d_init_error(): ODBC



WARNING: Unable to open database <C:\dl\mdb\testinput.mdb> by driver <odbc>
ERROR: Unable to open database <$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db>
       by driver <sqlite>
GRASS 7.0.svn>

[hellik]

Replying to hellik:

tested with osgeo4w-wingrass7-nightly build and attached sample dataset (DEBUG=3)

and also tested with osgeo4w-wingrass6.4.2

GRASS 6.4.2 (nc_spm_08)> g.gisenv set=DEBUG=3

GRASS 6.4.2 (nc_spm_08)> v.in.db driver=odbc database="C:\dl\mdb\testinput.mdb" table=grassinput x=xcoor y=ycoor key=keyid out=vindb5
D2/3: dbDbmscap(): opendir [C:\OSGeo4W\apps\grass\grass-6.4.2\driver\db\]
D2/3: Vect_open_new(): name = vindb5
D1/3: Vect_set_thresh(): thresh = 0.000000
D1/3: V1_open_new_nat(): name = vindb5
D1/3: Vect_get_thresh(): thresh = 0.000000
D1/3: dig__write_head()
D1/3: write coor size (0) to head
D2/3: coor body offset 14
D3/3: dig_init_plus()
D1/3: dig_spidx_init()
D3/3: dig_cidx_init()
D3/3: Vect_hist_command()
D3/3: G_recreate_command()
D1/3: Vect_default_field_info(): map = vindb5 field = 1
D2/3: drv = dbf db = $GISDBASE/$LOCATION_NAME/$MAPSET/dbf/
D3/3: db_start_driver_open_database():
  drvname = odbc, dbname = C:\dl\mdb\testinput.mdb
D2/3: dbDbmscap(): opendir [C:\OSGeo4W\apps\grass\grass-6.4.2\driver\db\]
D3/3: win_spawn: args = C:\windows\system32\cmd.exe /c "C:\OSGeo4W\apps\grass\grass-6.4.2\driver\db\odbc.exe"
DBMI-ODBC driver error: SQLConnect():
[Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben (0)

WARNUNG: Kann die Datenbank <C:\dl\mdb\testinput.mdb> nicht mit dem Treiber
         <odbc> ÷ffnen.
FEHLER: Kann Datenbank <$GISDBASE/$LOCATION_NAME/$MAPSET/dbf/> nicht mit
        dem Treiber <dbf> ÷ffnen.

[hellik]

Replying to hellik:

[Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben (0) WARNUNG: Kann die Datenbank <C:\dl\mdb\testinput.mdb> nicht mit dem Treiber <odbc> ÷ffnen.

maybe a hint (sorry, only in german)?:

 http://www.microsoft-programmierer.de/ansicht.aspx?Area=FORUM&RefNr=1923

--------< Deutsche Verbindung >--------
DSN=Microsoft Access-Datenbank;DBQ=t:\Datenbanken\Test.mdb;DefaultDir=t:\Datenbanken;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
--------</ Deutsche Verbindung >--------

--------< Standardverbindung >--------
DSN=MS Access Database;DBQ=T:\Datenbanken\Test.mdb;DefaultDir=T:\Datenbanken;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
--------</ Standardverbindung >--------

--------< alternative Zuweisung ohne DSN>-------- 
DBQ=t:\Datenbanken\Test.mdb;DefaultDir=t:\Datenbanken;Driver={Microsoft Access-Treiber (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
--------</ alternative Zuweisung ohne DSN >-------- 

Helmut

[mlennert]

Replying to hellik:

v.in.db driver=odbc database="C:\wd\access\testinput.mdb" table=grassinput key=ID x=xcoor y=ycoor output=mdbvector

AFAIK, this is wrong usage of the odbc driver. database should not be the path of the mdb file, but rather the name you give to the database in the ODBC configuration.

This is what the message in German actually says:

"[Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden", i.e. The name of the source of data could not be found. Note that it doesn't say that the _file_ hasn't been found, but the _name_.

ODBC is an intermediate layer which allows clients to communicate with database in a standardized language. This means that clients do not connect to the database directly, but via ODBC. In order to be able to connect, you have to configure this connection using dedicated tools (IIUC in MS Windows the tool is currently called "ODBC Data Source Administrator") and give a name to that connection. You then use that name as database name when accessing from a client via odbc.

I believe that unless there is more info about the ODBC access failing when used correctly, this bug should be closed as invalid.

Moritz

[mlennert]

Replying to mlennert:

I believe that unless there is more info about the ODBC access failing when used correctly, this bug should be closed as invalid.

As nothing has been posted since, I'm closing this bug as invalid.

Moritz