Accessing Firebird with ODBC

From LSDevLinux
Revision as of 21:55, 4 August 2012 by Spammer (talk | contribs) (minor updates)

Jump to: navigation, search

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

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 Importance of Imagination

While growing up, Id never really considered how important it is to be imaginative. Its a childhood profession, you could say. It comes naturally. Then we hit an age when were presented with a scantron of bubble-in options, a template for a CV that we need to create, and Excel. At that point, our learning has to fit into certain parameters: within that little bubble, within the one page limit, and within a tiny digital graph. So, what happens to our imagination?It seems to fade.

[The Importance of Imagination]

[GoodvilleNews.com - good, positive news, inspirational stories, articles]

How To Speak More Wisely

It had been three weeks since my throat started to feel sore, and it wasnt getting better. The pain was most acute when I spoke. So I decided to spend a few days speaking as little as possible. Every time I had the urge to say something, I paused for a moment to question whether it was worth irritating my throat.

[How To Speak More Wisely]

[GoodvilleNews.com - good, positive news, inspirational stories, articles]

The Relationship Between Gifts & Community

Wherever I go and ask people what is missing from their lives, the most common answer (if they are not impoverished or seriously ill) is "community." What happened to community, and why dont we have it any more? There are many reasons the layout of suburbia, the disappearance of public space, the automobile and the television, the high mobility of people and jobs and, if you trace the "whys" a few levels down, they all implicate the money system.

[The Relationship Between Gifts & Community]

[GoodvilleNews.com - good, positive news, inspirational stories, articles]

Stop Using The Wrong Type of Intelligence

A man should hear a little music, read a little poetry, and see a fine picture every day of his life, in order that worldly cares may not obliterate the sense of the beautiful which God has implanted in the human soul. Johann Wolfgang von Goethe

[Stop Using The Wrong Type of Intelligence]

[GoodvilleNews.com - good, positive news, inspirational stories, articles]

Teen Told to Clean His Room Finds Winning Lotto Ticket

A teenager from Midlothian has cleaned up on the National Lottery after finally cleaning up his messy bedroom. Ryan Kitchin scooped more than 50,000 after finding a winning lottery ticket in the room that his mother had been asking him to clean for weeks.

[Teen Told to Clean His Room Finds Winning Lotto Ticket]

[GoodvilleNews.com - good, positive news, inspirational stories, articles]