[GRASS-user] GRASS and ODBC help wanted

GRASS GRASS user
1

Hi all,

I need some help.

OS: Debian Etch

I try to connect GRASS [1] via odbc to my local PostgreSQL database. The
case is for a training lession about this gis. I connect to Postgres
because i have no other db.

The direct contact to the PostgresQL runs without problems.

I have this

1.
odbc.ini

[grass_via_odbc]
Description = Test to Postgres
Driver = PostgreSQL
Trace = Yes
TraceFile = sql.log
Database = grass_nc
Servername = localhost
UserName = verysecret
Password = verysecret
Port = 5432
Protocol = 8.1
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
odbc.ini (END)

2.
odbcinst.ini

[PostgreSQL]
Description = PostgreSQL driver for Linux & Win32
Driver = /usr/lib/odbc/libodbcpsqlS.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
Threading = 2
UsageCount = 1

both data located in my local home

/home/georg/.odbc.ini
/home/georg/.odbcinst.ini

Postgres runs

ok now comes some grass stuff

set connection

GRASS 6.2.1 (solothurn):~/solothurn > db.login driver=odbc
database=grass_via_odbc user=secret password=secret
WARNING: The password was stored in file.

check connection

GRASS 6.2.1 (solothurn):~/solothurn > db.connect -p
driver:odbc
database:grass_via_odbc
schema:(null)
group:(null)

and then when i try this

GRASS 6.2.1 (solothurn):~/solothurn > g.copy
vect=avdpool_liegen,liegen_odbc1
COPY [avdpool_liegen@PERMANENT] to current mapset as [liegen_odbc1]
DBMI-ODBC driver error: SQLConnect():
[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV
failed (0)
WARNING: Cannot open database 'grass_via_odbc'
no database is open
WARNING: Cannot copy table
WARNING: Cannot copy avdpool_liegen@PERMANENT to current mapset as
         liegen_odbc1

Any idea where the problem is?
Any hint?

Thanks in advance

greeting Georg

--
-----------------------------------------------
georg loesel : garnstrasse 12 : 14482 potsdam

fon ++49 (0) 331 2438 770
mobil ++49 (0) 176 233 244 11
mail georg@de-schorsch.de
skype/wengo georgloesel
internet www.de-schorsch.de
latlon 52,3944; 13,0894
gpg look for 05C6733F
-----------------------------------------------

2

Hi Georg,

working with Postgres I would suggest you also use the postgres (pg) driver
instead of ODBC. If you need to use ODBC, because you want to show it in
your lesson, there are some hints in the GRASS wiki:

http://grass.gdf-hannover.de/wiki/Vector_Database_Management#ODBC

regards,
  Otto

On Thu, 29 Nov 2007 18:40:32 +0100
"Georg Lösel (privat)" <georg@de-schorsch.de> wrote:

Hi all,

I need some help.

OS: Debian Etch

I try to connect GRASS [1] via odbc to my local PostgreSQL database. The
case is for a training lession about this gis. I connect to Postgres
because i have no other db.

The direct contact to the PostgresQL runs without problems.

I have this

1.
odbc.ini

[grass_via_odbc]
Description = Test to Postgres
Driver = PostgreSQL
Trace = Yes
TraceFile = sql.log
Database = grass_nc
Servername = localhost
UserName = verysecret
Password = verysecret
Port = 5432
Protocol = 8.1
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
odbc.ini (END)

2.
odbcinst.ini

[PostgreSQL]
Description = PostgreSQL driver for Linux & Win32
Driver = /usr/lib/odbc/libodbcpsqlS.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
Threading = 2
UsageCount = 1

both data located in my local home

/home/georg/.odbc.ini
/home/georg/.odbcinst.ini

Postgres runs

ok now comes some grass stuff

set connection

GRASS 6.2.1 (solothurn):~/solothurn > db.login driver=odbc
database=grass_via_odbc user=secret password=secret
WARNING: The password was stored in file.

check connection

GRASS 6.2.1 (solothurn):~/solothurn > db.connect -p
driver:odbc
database:grass_via_odbc
schema:(null)
group:(null)

and then when i try this

GRASS 6.2.1 (solothurn):~/solothurn > g.copy
vect=avdpool_liegen,liegen_odbc1
COPY [avdpool_liegen@PERMANENT] to current mapset as [liegen_odbc1]
DBMI-ODBC driver error: SQLConnect():
[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV
failed (0)
WARNING: Cannot open database 'grass_via_odbc'
no database is open
WARNING: Cannot copy table
WARNING: Cannot copy avdpool_liegen@PERMANENT to current mapset as
         liegen_odbc1

Any idea where the problem is?
Any hint?

Thanks in advance

greeting Georg

3

Georg Lösel wrote:

I try to connect GRASS [1] via odbc to my local PostgreSQL database.

I'm not the one to help you with that, but ...

I connect to Postgres because i have no other db.

SQLite may be an easier choice?

You say you are running Debian/Etch. Do you use the official debian grass
package (6.0.2) or the updated version (6.2.x) from the DebianGIS project at
backports.org? Or self compiled? If you use the 6.0 version I highly recommend
installing the updated version from backports.org. That may solve many
problems.

http://packages.debian.org/grass
http://wiki.debian.org/DebianGis

Hamish

      ____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs

4

"Georg Lösel (privat)" wrote on 11/29/2007 06:40 PM:

Hi all,

I need some help.

OS: Debian Etch

I try to connect GRASS [1] via odbc to my local PostgreSQL database. The
case is for a training lession about this gis. I connect to Postgres
because i have no other db.

The direct contact to the PostgresQL runs without problems.
  

...

2.
odbcinst.ini

[PostgreSQL]
Description = PostgreSQL driver for Linux & Win32
Driver = /usr/lib/odbc/libodbcpsqlS.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
Threading = 2
UsageCount = 1
  
The "Driver" setting above isn't right: It should be (take out 'S'):

Driver = /usr/lib/odbc/libodbcpsql.so

See: http://www.unixodbc.org/odbcinst.html

Note that ODBC is sloooooooow. Direct connection to PG is
way faster.

Markus