[GRASS5] ODBC ready

GRASS GRASS developer
1

Hi,

ODBC driver (limited functionality) is ready FOR TESTING.

Quick start:
- Download, compile, install and configure ODBC (http://www.unixodbc.org/)
- Copy ODBC include files to /usr/include/odbc
- Compile src/libes/dbmi/drivers/odbc
- Add row for ODBC driver to dbmscap file (see src/libes/dbmi/clients/man/db_connect.html)
- Configure your ODBC DSN
- Now you can run modules (examples expect driver:odbc, DSN: db,
  user:usr, table: tb(id int, val int)):

db.connect driver=odbc database=db [user=usr password=mypwd]
(see src/libes/dbmi/clients/man/db_connect.html)

db.drivers

db.tables driver=odbc database=db

db.columns driver=odbc database=db table=tb

db.describe driver=odbc database=db table=tb

db.select -hc input=sel.sql
( sel.sql contains: select * from tb)

db.execute driver=odbc database=db input=ins.sql
(ins.qsl contains: insert into tb (id, val) values ( 11, 123))

db.droptable driver=odbc database=db table=tb
(drop table!)

v.db.reclass input=map output=rmap rules=a.rcl table=tb type=area
(see src/mapdev/v.db.reclass/man/v_db_reclass.html)

That's all for now.

Anybody who want to help is welcome:
- write missing functions in driver (src/libes/dbmi/drivers/odbc/TODO)
- test various RDBMS and data types (binary not supported)
- write more modules
- translate manuals from my english to real english

I work on v.to.db (load categories, areas, lenghts, x,y, nr. of elem.,....)

Radim

----------------------------------------
If you want to unsubscribe from GRASS Development Team mailing list write to:
minordomo@geog.uni-hannover.de with
subject 'unsubscribe grass5'

2

Also, FYI, I have attached a note from the Cygnus email list
on using Oracle DLLs and the Cygnus linker.

----- Original Message -----
From: DJ Delorie <dj@delorie.com>
To: <richard.jones@ibahealthcare.com>
Cc: <cygwin@sourceware.cygnus.com>
Sent: Tuesday, May 23, 2000 8:08 AM
Subject: Re: Linking in to Windows DLL's

If you have a recent linker, it can link directly against a DLL
without needing an import library. However, this only works if the
import library doesn't include any special name mappings or extra
static code.

> Just a quick note to say that I have managed to link cygwin code against
> Oracle 8 for NT by just renaming the dll's library file to oci32.o.
> This seems to work fine (all the manuals and FAQ's require extracting

the

> symbol's out of the dll itself), and it "seems" to work correctly.

----- Original Message -----
From: Radim Blazek <Radim.Blazek@dhv.cz>
To: <grass5@geog.uni-hannover.de>
Sent: Tuesday, May 23, 2000 3:41 AM
Subject: [GRASS5] ODBC ready

Hi,

ODBC driver (limited functionality) is ready FOR TESTING.

Quick start:
- Download, compile, install and configure ODBC (http://www.unixodbc.org/)
- Copy ODBC include files to /usr/include/odbc
- Compile src/libes/dbmi/drivers/odbc
- Add row for ODBC driver to dbmscap file (see

src/libes/dbmi/clients/man/db_connect.html)

- Configure your ODBC DSN
- Now you can run modules (examples expect driver:odbc, DSN: db,
  user:usr, table: tb(id int, val int)):

db.connect driver=odbc database=db [user=usr password=mypwd]
(see src/libes/dbmi/clients/man/db_connect.html)

db.drivers

db.tables driver=odbc database=db

db.columns driver=odbc database=db table=tb

db.describe driver=odbc database=db table=tb

db.select -hc input=sel.sql
( sel.sql contains: select * from tb)

db.execute driver=odbc database=db input=ins.sql
(ins.qsl contains: insert into tb (id, val) values ( 11, 123))

db.droptable driver=odbc database=db table=tb
(drop table!)

v.db.reclass input=map output=rmap rules=a.rcl table=tb type=area
(see src/mapdev/v.db.reclass/man/v_db_reclass.html)

That's all for now.

Anybody who want to help is welcome:
- write missing functions in driver (src/libes/dbmi/drivers/odbc/TODO)
- test various RDBMS and data types (binary not supported)
- write more modules
- translate manuals from my english to real english

I work on v.to.db (load categories, areas, lenghts, x,y, nr. of

elem.,....)

Radim

----------------------------------------
If you want to unsubscribe from GRASS Development Team mailing list write

to:

minordomo@geog.uni-hannover.de with
subject 'unsubscribe grass5'

----------------------------------------
If you want to unsubscribe from GRASS Development Team mailing list write to:
minordomo@geog.uni-hannover.de with
subject 'unsubscribe grass5'

3

Hi Radim, hi all,

yesterday I could run the new ODBC driver successfully!
Many thank for your big job, Radim!

Notes for others, how I did (I am ODBC newbie..):

Tested on SuSe Linux6.4, PostgreSQL 6.5, unixODBC 1.8.8.
--------------------------------------------------
Start: Getting unixODBC: www.unixodbc.org

Compile:
--------
configure
or: configure --enable-gui # strange: on one machine I got the
                             # ODBCConfig, on another it wasn't generated
make
make install

-> Files will go to /usr/local/lib/

Instructions to set up ODBC:
file:/home/user/unixODBC-1.8.8/doc/AdministratorManual/odbcinst.html

---------------------------------------
Get it running with GRASS:

a) establish ODBC connection to PostgreSQL (Oracle, ...):

Use
  ODBCConfig
add new system DSN: means: add a new System DSN for each
database within the PostgreSQL. Only admin can add System DSN.
To add user specific DSN, use "User DSN".

Check for a list of databases:
psql -l

Example:
Assume, we have an existing database "boden" with table "humus":
  Name: Boden
  Description:Bodendatabase
  Host: localhost
  Username: neteler
  Database: boden

Use
DataManager
to watch the settings.

b) Start GRASS:

List of drivers:
db.drivers -f
You should see the odbc driver (otherwise edit /etc/dbmscap).

List of available databases:
db.databases -l

(Note: To easily import a DBase database into PostgreSQL, use
       pg.in.dbf from Alex)

Look for databases selectable through ODBC:
db.databases -l

You can preset most parameters:
db.connect driver=odbc database=boden user=neteler
db.connect -p

Look for tables:
db.tables driver=odbc database=boden

Describe a selected table:
db.describe driver=odbc database=boden table=humus
db.describe driver=odbc database=boden table=boshp

Describe the existing columns in table:
db.columns driver=odbc database=boden table=humus
db.columns driver=odbc database=boden table=boshp

Select statement (db.select can read from stdin):
db.select driver=odbc database=boden
select * from humus where humus > 1
CTRL-D twice!
- outout is zero at all.
-> Radim: what about table selection? Don't we need to select
   the table as well? I'll check the TODO list...
Or did I misuse it?

Disconnect:
db.connect driver=odbc database=

Congratulations, Radim!

Best wishes

Markus Neteler

----------------------------------------
If you want to unsubscribe from GRASS Development Team mailing list write to:
minordomo@geog.uni-hannover.de with
subject 'unsubscribe grass5'