Difference between revisions of "Accessing Firebird with ODBC"

From LSDevLinux
Jump to: navigation, search
(Names)
Line 8: Line 8:
 
* <code>&lt;username&gt;</code> Name for a regular database user.
 
* <code>&lt;username&gt;</code> Name for a regular database user.
 
* <code>&lt;password&gt;</code> Password for the regular database user.
 
* <code>&lt;password&gt;</code> Password for the regular database user.
 +
* <code>&lt;driver_name&gt;</code> Name for the ODBC driver.
 
* <code>&lt;DSN_name&gt;</code> Name for the ODBC description of the database.
 
* <code>&lt;DSN_name&gt;</code> Name for the ODBC description of the database.
  
Line 24: Line 25:
 
'''Note 2:''' Retrospectively, using [http://www.firebirdsql.org/manual/ubusetup.html this guide for Ubuntu] would have probably been better. Aside from one or two package names, it should work on Debian.
 
'''Note 2:''' Retrospectively, using [http://www.firebirdsql.org/manual/ubusetup.html 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>
+
  # gsec -user sysdba -pass &lt;sysdbapassword&gt; -add &lt;username&gt; -pw &lt;password&gt;
  $ cd /path/to/database-directory
+
  $ cd &lt;database_path&gt;
 
  # /usr/lib/firebird2/bin/isql (NOT the same as /bin/isql)
 
  # /usr/lib/firebird2/bin/isql (NOT the same as /bin/isql)
 
  SQL> create database '&lt;datbase_name&gt;' user '&lt;username&gt;' password '&lt;password&gt;';
 
  SQL> create database '&lt;datbase_name&gt;' user '&lt;username&gt;' password '&lt;password&gt;';
Line 39: Line 40:
  
 
=== Database ===
 
=== Database ===
Use ODBCConfig. Create new DSN (user or system).
+
Use ODBCConfig. Create a new Driver, then a new user DSN.
  
 
Source: The [http://www.unixodbc.org/doc/UserManual/ unixODBC User Manual].
 
Source: The [http://www.unixodbc.org/doc/UserManual/ unixODBC User Manual].
  
 
'''Driver:'''
 
'''Driver:'''
 +
* Name: <code>&lt;driver_name&gt;</code>
 
* Driver: <code>/usr/lib/odbc/libOdbcFb32.so</code>
 
* Driver: <code>/usr/lib/odbc/libOdbcFb32.so</code>
 
* Setup: <code>/usr/lib/odbc/libOdbcFb32.so</code>
 
* Setup: <code>/usr/lib/odbc/libOdbcFb32.so</code>
Line 49: Line 51:
  
 
First, select the driver just created and click 'OK'.
 
First, select the driver just created and click 'OK'.
* Name: <code><DSN_name></code>
+
* Name: <code>&lt;DSN_name&gt;</code>
* Dbname: <code>localhost:/path/to/database</code>
+
* Dbname: <code>localhost:&lt;database_path&gt;&lt;database_name&gt;</code>
* User: <code><username></code>
+
* User: <code>&lt;username&gt;</code>
* Password: <code><password></code>
+
* Password: <code>&lt;password&gt;</code>
(All of the above can be done with an .ini file.)
 
  
 +
The DSN can also be created adding an entry to an ini file (<code>/home/user/.odbc.ini<code> for a single user or <code>/etc/odbc.ini</code> for all users), such as in this (minimal) example.
 +
[&lt;DSN_name&gt;]
 +
Driver  = &lt;driver_name&gt;
 +
Dbname  = localhost:&lt;database_path&gt;&lt;database_name&gt;
 +
User    = &lt;username&gt;
 +
Password = &lt;password&gt;
 
<span style="color:red">'''Warning:'''</span> Putting the name and password in the DSN is a bit of a security risk. It is done in this example for convenience.
 
<span style="color:red">'''Warning:'''</span> 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 ==
 
== Connecting to the database ==
  
  isql -v <DSN_name>
+
  isql -v &lt;DSN_name&gt;
  
 
(It may be required to use <code>LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/odbc</code>)
 
(It may be required to use <code>LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/odbc</code>)

Revision as of 12:19, 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.
  • <driver_name> Name for the ODBC driver.
  • <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 <database_path>
# /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 a new Driver, then a new user DSN.

Source: The unixODBC User Manual.

Driver:

  • Name: <driver_name>
  • 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:<database_path><database_name>
  • User: <username>
  • Password: <password>

The DSN can also be created adding an entry to an ini file (/home/user/.odbc.ini<code> for a single user or <code>/etc/odbc.ini for all users), such as in this (minimal) example.

[<DSN_name>]
Driver   = <driver_name>
Dbname   = localhost:<database_path><database_name>
User     = <username>
Password = <password>

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