Draft installation instructions to connect GRASS 5.0 to an external DBMS
through ODBC. Please help us to improve the instructions.
-
Communication between GRASS 5.0 and database: GRASS module <-> GRASS-to-ODBC-driver
<-> ODBC <--> ODBC-to-database -driver <-> Database
-
Install the unixODBC (http://www.unixodbc.org),
your favourite database (Oracle, PostgreSQL, MySQL, ...) and ODBC driver
for the database you have chosen. However it is not necessary we recommend
to install GUI configuration tools for ODBC: ODBCConfig and DataManager
(unixODBC-kde rpm in RH distribution). Finaly you need GRASS-to-odbc-driver.
Check if this driver exists: $GISBASE/driver/db/odbc. If this driver is not
available you must compile grass/src/libes/dbmi/drivers/odbc/ (GRASS
source code).
-
Configure ODBC driver for selected database (without ODBCConfig). ODBC
drivers are defined in /etc/odbcinst.ini. Here is example:
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1
- Create DSN (data source name). DSN is used as database name in db.*
modules. Then DSN must be defined in $HOME/.odbc.ini (for this user only) or in
/etc/odbc.ini for (for all users) [watch out for the database name which
appears twice and the PostgreSQL protocol version]. Omit blanks at the beginning of
lines:
[spearfishdb]
Description = PostgreSQL
Driver = PostgreSQL
Trace = No
TraceFile =
Database = spearfishdb
Servername = localhost
UserName =
Password =
Port = 5432
Protocol = 7.0
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
Configuration of an DSN without GUI is described on http://www.unixodbc.org/odbcinst.html,
but odbc.ini and .odbc.ini may be created by GUI ODBCConfig and you can
easily view your DSN structure by DataManager. Configuration with
GUI is described on http://www.unixodbc.org/doc/UserManual/
To find out your PostgreSQL protocol, run
cat /var/lib/pgsql/data/PG_VERSION
or
psql -V
-
You need to run db.connect command.
-
Using the interface:
Before you can start with GRASS you must get isql or DataManager
working correctly on your DSN.
We explain for a link to PostgreSQL:
-
First the current user must be defined as PostgreSQL user (as user "postgres"
run "createuser yourname").
-
When using PostgreSQL, you can create an empty database now with (the name
must match the above definition in /etc/odbc.ini):
createdb spearfishdb
-
Now it's time to check the connection:
isql spearfishdb
It should return: "Connected!" and you will be in the SQL menu (leave
with quit).
-
Connect GRASS to the table:
db.connect driver=odbc database=spearfishdb location=spearfish user=neteler
-
Next we create an empty table in the spearfishdb:
echo 'create table soils ("cat" int, "soiltype" text)' | db.execute
(If you get an ODBC protocol error check /etc/odbc.ini again).
-
List existing tables in this DB:
db.tables driver=odbc database=spearfishdb location=spearfish
You will see the "soils" table.
-
Now you can upload a map (upload column-wise):
v.to.db map=soils type=area option=cat table=soils key=cat
v.to.db map=soils type=area option=label table=soils key=cat col1=soiltype
-
To interactively manage attibutes, run:
d.what.db table=soils key=cat x=cat y=cat
-
PROBLEMS? In case you face problems to install ODBC/GRASS:
a) What does the 'db.drivers' print?
b) What does 'db.connect -p' say? You should set user name (maybe password)
either in ODBCConfig or by db.connect. It's useful to set default
database by db.connect also. Then db.connect -p should print something
like:
driver:odbc
database:test
location:(null)
user:radim
password:(null)
key:(null)
c) What was the error message printed by db.* module. For example:
db.databases
DBMI-ODBC driver error: SQLConnect(): [unixODBC][Driver Manager]Data source name
not found, and no default driver specified (0)