Difference between revisions of "Accessing Firebird with ODBC"

From LSDevLinux
Jump to: navigation, search
(Names)
(Names)
Line 4: Line 4:
 
A few names will be used throughout this guide. These are for you to pick.
 
A few names will be used throughout this guide. These are for you to pick.
 
* <code>&lt;sysdbapassword&gt;</code> The administrator password for this install of Firebird.
 
* <code>&lt;sysdbapassword&gt;</code> The administrator password for this install of Firebird.
 +
* <code>&lt;database_path&gt;</code> Path to the database, eg. <code>/usr/share/fieldworks/</code>
 
* <code>&lt;database_name&gt;</code> Filename for the database, eg. <code>database.fdb</code>
 
* <code>&lt;database_name&gt;</code> Filename for the database, eg. <code>database.fdb</code>
 
* <code>&lt;username&gt;</code> Name for a regular database user.
 
* <code>&lt;username&gt;</code> Name for a regular database user.

Revision as of 12:03, 27 April 2007

A guide to setting up Firebird and ODBC on a Linux system. This guide was created using Debian Etch, some details may vary for other distributions.

Names

A few names will be used throughout this guide. These are for you to pick.

  • <sysdbapassword> The administrator password for this install of Firebird.
  • <database_path> Path to the database, eg. /usr/share/fieldworks/
  • <database_name> Filename for the database, eg. database.fdb
  • <username> Name for a regular database user.
  • <password> Password for the regular database user.
  • <DSN_name> Name for the ODBC description of the database.

Installing software

  • Install unixodbc* packages
  • Install firebird2-classic-server (set SYSDBA password)
  • Install firebird2-dev
  • Optional: Install firebird2-utils-classic

Note: It's also possible to use firebird2-super-server. I don't fully understand the difference.

Creating a database

Note: The commands run as root here would be better run as the 'firebird' user.

Note 2: Retrospectively, using this guide for Ubuntu would have probably been better. Aside from one or two package names, it should work on Debian.

# gsec -user sysdba -pass <sysdbapassword> -add <username> -pw <password>
$ cd /path/to/database-directory
# /usr/lib/firebird2/bin/isql (NOT the same as /bin/isql)
SQL> create database '<datbase_name>' user '<username>' password '<password>';
SQL> connect test.fdb user <username> password <password>;

Data can then be added with normal SQL commands

Setting up unixODBC

Drivers

Database

Use ODBCConfig. Create new DSN (user or system).

Source: The unixODBC User Manual.

Driver:

  • Driver: /usr/lib/odbc/libOdbcFb32.so
  • Setup: /usr/lib/odbc/libOdbcFb32.so

Data source:

First, select the driver just created and click 'OK'.

  • Name: <DSN_name>
  • Dbname: localhost:/path/to/database
  • User: <username>
  • Password: <password>

(All of the above can be done with an .ini file.)

Warning: Putting the name and password in the DSN is a bit of a security risk. It is done in this example for convenience.

Connecting to the database

isql -v <DSN_name>

(It may be required to use LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/odbc)

Running the above command as either root or firebird will provide an ODBC SQL console.

OpenOffice.org Base can also connect to ODBC databases.

Programming (C)

Reference