Difference between revisions of "Accessing Firebird with ODBC"

Latest revision as of 22:18, 4 August 2012

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

Download the Firebird ODBC version 2 source for Linux
In OdbcJdbc/Builds/Gcc.lin run make -f makefile.linux
Copy the resulting Release/libOdbcFb32.so into /usr/lib/odbc

Database

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

Graphical setup

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

DSN:

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

Name: <DSN_name>
Dbname: localhost:<database_path><database_name>
User: <username>
Password: <password>

Config file setup

The above can also be created adding an entries to unixODBC's ini files, such as in these minimal examples. (The graphical tool does the same thing, but adds a lot more fields to the files.

Driver: /etc/odbcinst.ini

[Firebird]
Driver		= /usr/lib/odbc/libOdbcFb32.so
Setup		= /usr/lib/odbc/libOdbcFb32.so

DSN: /home/user/.odbc.ini<code> for a single user or <code>/etc/odbc.ini for all users)

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

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

The unixODBC site has a bunch of good reference. Click 'Manuals' on the left. (The ODBC Programming Tutorial worked fine for me, but the Easysoft ones do look more professional).
The MSDN ODBC Programmer's Reference is the authoritative reference for the ODBC API, but there may be some differences between this and unixODBC.

@@ Line 3: / Line 3: @@
 == Names ==
 A few names will be used throughout this guide. These are for you to pick.
-* <code><sysdbapassword></code> The administrator password for this install of Firebird.
+* <code>&lt;sysdbapassword&gt;</code> The administrator password for this install of Firebird.
-* <code><username></code> Name for a regular database user.
+* <code>&lt;database_path&gt;</code> Path to the database, eg. <code>/usr/share/fieldworks/</code>
-* <code><password></code> Password for the regular database user.
+* <code>&lt;database_name&gt;</code> Filename for the database, eg. <code>database.fdb</code>
-* <code><DSN_name></code> Name for the ODBC description of the database.
+* <code>&lt;username&gt;</code> Name for a 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.
 == Installing software ==
@@ Line 13: / Line 16: @@
 * Install firebird2-classic-server (set SYSDBA password)
 * Install firebird2-dev
-* Optional: Install firebird2-utils-classic, flamerobin
+* Optional: Install firebird2-utils-classic
 '''Note:''' It's also possible to use firebird2-super-server. I don't fully understand the difference.
@@ Line 22: / 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.
-  # 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 somewhere/nice
+  $ cd &lt;database_path&gt;
   # /usr/lib/firebird2/bin/isql (NOT the same as /bin/isql)
-  SQL> create database 'test.fdb' user '<username>' password '<password>';
+  SQL> create database '&lt;datbase_name&gt;' user '&lt;username&gt;' password '&lt;password&gt;';
-  SQL> connect test.fdb user <username> password <password>;
+  SQL> connect test.fdb user &lt;username&gt; password &lt;password&gt;;
 Data can then be added with normal SQL commands
@@ Line 37: / Line 40: @@
 === Database ===
-Use ODBCConfig. Create new DSN (user or system).
+<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.
+==== Graphical setup ====
+Use ODBCConfig. Create a new Driver, then a new user DSN.
+Source: The [http://www.unixodbc.org/doc/UserManual/ unixODBC User Manual].
 '''Driver:'''
+* Name: <code>&lt;driver_name&gt;</code>
 * Driver: <code>/usr/lib/odbc/libOdbcFb32.so</code>
 * Setup: <code>/usr/lib/odbc/libOdbcFb32.so</code>
-'''Data source:'''
+'''DSN:'''
 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.)
+==== Config file setup ====
+The above can also be created adding an entries to unixODBC's ini files, such as in these '''minimal''' examples. (The graphical tool does the same thing, but adds a lot more fields to the files.
+'''Driver:'''
+<code>/etc/odbcinst.ini</code>
+ [Firebird]
+ Driver		= /usr/lib/odbc/libOdbcFb32.so
+ Setup		= /usr/lib/odbc/libOdbcFb32.so
+'''DSN:'''
+<code>/home/user/.odbc.ini<code> for a single user or <code>/etc/odbc.ini</code> for all users)
+ [&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;
 == 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>)
-Running the above command as either root or firebird will provide an ODBC SQL console
+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 ===
+* The [http://www.unixodbc.org/ unixODBC site] has a bunch of good reference. Click 'Manuals' on the left. (The [http://www.unixodbc.org/doc/ProgrammerManual/Tutorial/ ODBC Programming Tutorial] worked fine for me, but the Easysoft ones do look more professional).
+* The [http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcabout_this_manual.asp MSDN ODBC Programmer's Reference] is the authoritative reference for the ODBC API, but there may be some differences between this and unixODBC.
+[[Category:Database]]

Difference between revisions of "Accessing Firebird with ODBC"

Latest revision as of 22:18, 4 August 2012

Contents

Names

Installing software

Creating a database

Setting up unixODBC

Drivers

Database

Graphical setup

Config file setup

Connecting to the database

Programming (C)

Reference

Navigation menu

Views

Personal tools

Navigation

site

Search

Tools