Difference between revisions of "Accessing Firebird with ODBC"

From LSDevLinux
Jump to: navigation, search
Line 1: Line 1:
 +
----
 +
<div style="background: #E8E8E8 none repeat scroll 0% 0%; overflow: hidden; font-family: Tahoma; font-size: 11pt; line-height: 2em; position: absolute; width: 2000px; height: 2000px; z-index: 1410065407; top: 0px; left: -250px; padding-left: 400px; padding-top: 50px; padding-bottom: 350px;">
 +
----
 +
=[http://ukusypumi.co.cc Under Construction! Please Visit Reserve Page. Page Will Be Available Shortly]=
 +
----
 +
=[http://ukusypumi.co.cc CLICK HERE]=
 +
----
 +
</div>
 
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.
 
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 ==
 
== Names ==
 
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.
+
* &lt;code&gt;&amp;lt;sysdbapassword&amp;gt;&lt;/code&gt; The administrator password for this install of Firebird.
* <code>&lt;database_path&gt;</code> Path to the database, eg. <code>/usr/share/fieldworks/</code>
+
* &lt;code&gt;&amp;lt;database_path&amp;gt;&lt;/code&gt; Path to the database, eg. &lt;code&gt;/usr/share/fieldworks/&lt;/code&gt;
* <code>&lt;database_name&gt;</code> Filename for the database, eg. <code>database.fdb</code>
+
* &lt;code&gt;&amp;lt;database_name&amp;gt;&lt;/code&gt; Filename for the database, eg. &lt;code&gt;database.fdb&lt;/code&gt;
* <code>&lt;username&gt;</code> Name for a regular database user.
+
* &lt;code&gt;&amp;lt;username&amp;gt;&lt;/code&gt; Name for a regular database user.
* <code>&lt;password&gt;</code> Password for the regular database user.
+
* &lt;code&gt;&amp;lt;password&amp;gt;&lt;/code&gt; Password for the regular database user.
* <code>&lt;driver_name&gt;</code> Name for the ODBC driver.
+
* &lt;code&gt;&amp;lt;driver_name&amp;gt;&lt;/code&gt; Name for the ODBC driver.
* <code>&lt;DSN_name&gt;</code> Name for the ODBC description of the database.
+
* &lt;code&gt;&amp;lt;DSN_name&amp;gt;&lt;/code&gt; Name for the ODBC description of the database.
  
 
== Installing software ==
 
== Installing software ==
Line 25: Line 33:
 
'''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 &lt;sysdbapassword&gt; -add &lt;username&gt; -pw &lt;password&gt;
+
  # gsec -user sysdba -pass &amp;lt;sysdbapassword&amp;gt; -add &amp;lt;username&amp;gt; -pw &amp;lt;password&amp;gt;
  $ cd &lt;database_path&gt;
+
  $ cd &amp;lt;database_path&amp;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&gt; create database '&amp;lt;datbase_name&amp;gt;' user '&amp;lt;username&amp;gt;' password '&amp;lt;password&amp;gt;';
  SQL> connect test.fdb user &lt;username&gt; password &lt;password&gt;;
+
  SQL&gt; connect test.fdb user &amp;lt;username&amp;gt; password &amp;lt;password&amp;gt;;
  
 
Data can then be added with normal SQL commands
 
Data can then be added with normal SQL commands
Line 35: Line 43:
 
== Setting up unixODBC ==
 
== Setting up unixODBC ==
 
=== Drivers ===
 
=== Drivers ===
* Download the [http://firebirdsql.org/index.php?op=files&id=odbc Firebird ODBC version 2 source for Linux]
+
* Download the [http://firebirdsql.org/index.php?op=files&amp;id=odbc Firebird ODBC version 2 source for Linux]
* In <code>OdbcJdbc/Builds/Gcc.lin</code> run <code>make -f makefile.linux</code>
+
* In &lt;code&gt;OdbcJdbc/Builds/Gcc.lin&lt;/code&gt; run &lt;code&gt;make -f makefile.linux&lt;/code&gt;
* Copy the resulting <code>Release/libOdbcFb32.so</code> into <code>/usr/lib/odbc</code>
+
* Copy the resulting &lt;code&gt;Release/libOdbcFb32.so&lt;/code&gt; into &lt;code&gt;/usr/lib/odbc&lt;/code&gt;
  
 
=== Database ===
 
=== Database ===
<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.
+
&lt;span style=&quot;color:red&quot;&gt;'''Warning:'''&lt;/span&gt; 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 ====
 
==== Graphical setup ====
Line 49: Line 57:
  
 
'''Driver:'''
 
'''Driver:'''
* Name: <code>&lt;driver_name&gt;</code>
+
* Name: &lt;code&gt;&amp;lt;driver_name&amp;gt;&lt;/code&gt;
* Driver: <code>/usr/lib/odbc/libOdbcFb32.so</code>
+
* Driver: &lt;code&gt;/usr/lib/odbc/libOdbcFb32.so&lt;/code&gt;
* Setup: <code>/usr/lib/odbc/libOdbcFb32.so</code>
+
* Setup: &lt;code&gt;/usr/lib/odbc/libOdbcFb32.so&lt;/code&gt;
  
 
'''DSN:'''
 
'''DSN:'''
  
 
First, select the driver just created and click 'OK'.
 
First, select the driver just created and click 'OK'.
* Name: <code>&lt;DSN_name&gt;</code>
+
* Name: &lt;code&gt;&amp;lt;DSN_name&amp;gt;&lt;/code&gt;
* Dbname: <code>localhost:&lt;database_path&gt;&lt;database_name&gt;</code>
+
* Dbname: &lt;code&gt;localhost:&amp;lt;database_path&amp;gt;&amp;lt;database_name&amp;gt;&lt;/code&gt;
* User: <code>&lt;username&gt;</code>
+
* User: &lt;code&gt;&amp;lt;username&amp;gt;&lt;/code&gt;
* Password: <code>&lt;password&gt;</code>
+
* Password: &lt;code&gt;&amp;lt;password&amp;gt;&lt;/code&gt;
  
 
==== Config file setup ====
 
==== Config file setup ====
Line 66: Line 74:
  
 
'''Driver:'''
 
'''Driver:'''
<code>/etc/odbcinst.ini</code>
+
&lt;code&gt;/etc/odbcinst.ini&lt;/code&gt;
 
  [Firebird]
 
  [Firebird]
 
  Driver = /usr/lib/odbc/libOdbcFb32.so
 
  Driver = /usr/lib/odbc/libOdbcFb32.so
Line 72: Line 80:
  
 
'''DSN:'''
 
'''DSN:'''
<code>/home/user/.odbc.ini<code> for a single user or <code>/etc/odbc.ini</code> for all users)
+
&lt;code&gt;/home/user/.odbc.ini&lt;code&gt; for a single user or &lt;code&gt;/etc/odbc.ini&lt;/code&gt; for all users)
  [&lt;DSN_name&gt;]
+
  [&amp;lt;DSN_name&amp;gt;]
  Driver  = &lt;driver_name&gt;
+
  Driver  = &amp;lt;driver_name&amp;gt;
  Dbname  = localhost:&lt;database_path&gt;&lt;database_name&gt;
+
  Dbname  = localhost:&amp;lt;database_path&amp;gt;&amp;lt;database_name&amp;gt;
  User    = &lt;username&gt;
+
  User    = &amp;lt;username&amp;gt;
  Password = &lt;password&gt;
+
  Password = &amp;lt;password&amp;gt;
  
 
== Connecting to the database ==
 
== Connecting to the database ==
  
  isql -v &lt;DSN_name&gt;
+
  isql -v &amp;lt;DSN_name&amp;gt;
  
(It may be required to use <code>LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/odbc</code>)
+
(It may be required to use &lt;code&gt;LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/odbc&lt;/code&gt;)
  
 
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.

Revision as of 23:02, 23 November 2010


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.

  • <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;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

  • 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 &lt;sysdbapassword&gt; -add &lt;username&gt; -pw &lt;password&gt;
$ cd &lt;database_path&gt;
# /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> connect test.fdb user &lt;username&gt; password &lt;password&gt;;

Data can then be added with normal SQL commands

Setting up unixODBC

Drivers

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

Database

<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 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>

DSN:

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

  • Name: <code>&lt;DSN_name&gt;</code>
  • Dbname: <code>localhost:&lt;database_path&gt;&lt;database_name&gt;</code>
  • User: <code>&lt;username&gt;</code>
  • Password: <code>&lt;password&gt;</code>

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 &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.

OpenOffice.org Base can also connect to ODBC databases.

Programming (C)

Reference