Difference between revisions of "Open Database Connectivity"

From ArchWiki
Jump to: navigation, search
(Fix section level.)
(FreeTDS: Add configuration file.)
 
(16 intermediate revisions by 6 users not shown)
Line 1: Line 1:
 
[[Category:Database management systems]]
 
[[Category:Database management systems]]
 +
[[ja:Open Database Connectivity]]
 
Open Database Connectivity, commonly ODBC, is an open specification for providing application developers with a predictable API with which to access Data Sources.  
 
Open Database Connectivity, commonly ODBC, is an open specification for providing application developers with a predictable API with which to access Data Sources.  
 
An ODBC '''engine''' needs '''drivers''' to be able to interact with '''databases'''.
 
An ODBC '''engine''' needs '''drivers''' to be able to interact with '''databases'''.
Line 5: Line 6:
 
==ODBC engines==
 
==ODBC engines==
  
You have two options to chose from: [http://www.unixodbc.org/ unixODBC] and [http://en.wikipedia.org/wiki/IODBC iODBC]. Apparently unixODBC is more widely supported. This document shows how to set up unixODBC. First to access your database on your localhost and then extends the steps to configure MySQL to allow remote access through ODBC.  
+
You have two options to chose from: [http://www.unixodbc.org/ unixODBC] and [[wikipedia:IODBC|iODBC]]. Apparently unixODBC is more widely supported. This document shows how to set up unixODBC. First to access your database on your localhost and then extends the steps to configure MySQL to allow remote access through ODBC.  
 +
 
 +
Additionally you can choose from various [https://www.devart.com/odbc/ Devart ODBC drivers] for SQL Server, Oracle, MySQL, SQLite, Firebird, PostgreSQL, Interbase. 
  
 
===Installation===
 
===Installation===
# pacman -S unixodbc
+
 
 +
[[Install]] the {{Pkg|unixodbc}} package.
  
 
===Configuration===
 
===Configuration===
  
At /etc/odbcinst.ini is where drivers are declared, and /etc/odbc.ini where connections. More instruction at each driver section.
+
Driver are declared in {{ic|/etc/odbcinst.ini}}, and connections in {{ic|/etc/odbc.ini}}. More instruction at each driver section.
  
 
==Drivers==
 
==Drivers==
Line 22: Line 26:
 
====Installation====
 
====Installation====
  
pacman -S freetds
+
[[Install]] the {{Pkg|freetds}} package.
  
 
====Configuration====
 
====Configuration====
  
/etc/odbcinst.ini
+
{{hc|/etc/odbcinst.ini|<nowiki>
 +
[FreeTDS]
 +
Driver          = /usr/lib/libtdsodbc.so
 +
UsageCount      = 1
 +
</nowiki>}}
  
[FreeTDS]
+
The configuration file of FreeTDS itself is {{ic|/etc/freetds/freetds.conf}}.
Driver          = /usr/lib/libtdsodbc.so
 
UsageCount      = 1
 
  
 
=== Myodbc ===
 
=== Myodbc ===
Modbc is ODBC driver/connector for mariadb.
+
Myodbc is ODBC driver/connector for mariadb.
  
 
==== Installation ====
 
==== Installation ====
Install {{Pkg|myodbc}} from [[official repositories]].
+
Install the {{AUR|myodbc}} package.
  
 
====Configuration ====
 
====Configuration ====
  
Starting with odbcinst.ini, which lists all installed drivers. Su to root and set up your /etc/odbcinst.ini file as follows
+
Starting with {{ic|odbcinst.ini}}, which lists all installed drivers.
[MySQL]
+
{{hc|/etc/odbcinst.ini|<nowiki>
Description    = ODBC Driver for MySQL
+
[MySQL]
Driver          = /usr/lib/libmyodbc.so
+
Description    = ODBC Driver for MySQL
Setup          = /usr/lib/libodbcmyS.so
+
Driver          = /usr/lib/libmyodbc.so
FileUsage      = 1
+
Setup          = /usr/lib/libodbcmyS.so
 +
FileUsage      = 1
 +
</nowiki>}}
  
====Create A Symbolic Link====
+
====Create a symbolic link====
Next we need to create a symlink for libmyodbc.so. To do this we need to go to "/usr/lib/" and set up a symlink to libmyodbc.so
+
Next we need to create a symlink for {{ic|libmyodbc.so}}. To do this we need to go to {{ic|/usr/lib/}} and set up a symlink to {{ic|libmyodbc.so}}
  cd /usr/lib/
+
# cd /usr/lib/
  ln -s ./libmyodbc5w.so ./libmyodbc.so
+
# ln -s ./libmyodbc5w.so ./libmyodbc.so
  
 
==Databases==
 
==Databases==
Line 56: Line 64:
 
===Microsoft SQL Server 2000===
 
===Microsoft SQL Server 2000===
  
/etc/odbc.ini
+
{{hc|/etc/odbc.ini|<nowiki>
[server_name]
+
[server_name]
Driver      = FreeTDS
+
Driver      = FreeTDS
#Trace      = Yes
+
#Trace      = Yes
#TraceFile  = /tmp/odbc
+
#TraceFile  = /tmp/odbc
Servername  = server_name
+
Servername  = server_name
Database    = database_name
+
Database    = database_name
 +
</nowiki>}}
  
/etc/freetds/freetds.conf
+
{{hc|/etc/freetds/freetds.conf|<nowiki>
[server_name]
+
[server_name]
host = 192.168.0.2 # Host name or IP address.
+
host = 192.168.0.2 # Host name or IP address.
port = 1433 # Default port.
+
port = 1433 # Default port.
tds version = 7.1
+
tds version = 7.1
client charset = UTF-8
+
client charset = UTF-8
 +
</nowiki>}}
 +
 
 +
SQL Server ODBC driver connection strings and [https://www.devart.com/odbc/sqlserver/docs/driver_configuration_and_conne.htm configuration guide]
  
 
=== Mariadb ===
 
=== Mariadb ===
  
Set up your data sources in "/etc/odbc.ini" (system wide) or "~/.odbc" (current user). If a data source is defined in both of these files, the one in your home directory take precedence.  
+
Set up your data sources in {{ic|/etc/odbc.ini}} (system wide) or {{ic|~/.odbc}} (current user). If a data source is defined in both of these files, the one in your home directory take precedence.  
  
[MySQL-test]
+
{{bc|<nowiki>
Description    = MySQL database test
+
[MySQL-test]
Driver          = MySQL
+
Description    = MySQL database test
Server          = localhost
+
Driver          = MySQL
Database        = test
+
Server          = localhost
Port            = 3306
+
Database        = test
Socket          = /var/run/mysqld/mysqld.sock
+
Port            = 3306
Option          =
+
Socket          = /var/run/mysqld/mysqld.sock
Stmt            =
+
Option          =
 +
Stmt            =
 +
</nowiki>}}
  
====Create A Test Database====
+
MariaDB ODBC driver connection strings and [https://www.devart.com/odbc/mysql/docs/using_odbc_driver.htm configuration guide]
Create a new database "test". You can use one of the MySQL front-ends {{AUR|mysql-gui-tools}} {{Pkg|mysql-workbench}} or the commandline.
+
 
  mysqladmin -h localhost -u root -p create test
+
====Create a test database====
 +
Create a new database "test". You can use one of the MySQL front-ends such as {{Pkg|mysql-workbench}}, or the command-line ''mysqladmin'' command:
 +
  $ mysqladmin -h localhost -u root -p create test
  
 
====Testing the ODBC====
 
====Testing the ODBC====
 
To test the ODBC connection
 
To test the ODBC connection
  isql MySQL-test
+
  $ isql MySQL-test
  
 
If the connection is established, you will see
 
If the connection is established, you will see
Line 105: Line 121:
  
 
If you have a problem connecting then check the error message by running
 
If you have a problem connecting then check the error message by running
  isql MySQL-test -v
+
  $ isql MySQL-test -v
  
====A Couple Useful Websites====
+
====A couple useful websites====
 
http://www.unixodbc.org/doc/OOoMySQL.pdf
 
http://www.unixodbc.org/doc/OOoMySQL.pdf
  
Line 116: Line 132:
  
 
To work around error messages this URL proved helpful so here it is as well.
 
To work around error messages this URL proved helpful so here it is as well.
 +
 +
===Virtuoso / SPARQL===
 +
 +
{{hc|/etc/odbc.ini|<nowiki>
 +
[ODBC Data Sources]
 +
VOS = Virtuoso
 +
 +
[VOS]
 +
Driver = virtuoso-odbc
 +
Description = Virtuoso Open-Source Edition
 +
Address = localhost:1111
 +
</nowiki>}}
 +
 +
{{hc|/etc/odbcinst.ini|<nowiki>
 +
[virtuoso-odbc]
 +
Driver = /usr/lib/virtodbc.so
 +
</nowiki>}}
 +
 +
Opening a connection using the default credentials (username: "dba", password: "dba"):
 +
$ isql VOS dba dba

Latest revision as of 08:06, 4 December 2017

Open Database Connectivity, commonly ODBC, is an open specification for providing application developers with a predictable API with which to access Data Sources. An ODBC engine needs drivers to be able to interact with databases.

ODBC engines

You have two options to chose from: unixODBC and iODBC. Apparently unixODBC is more widely supported. This document shows how to set up unixODBC. First to access your database on your localhost and then extends the steps to configure MySQL to allow remote access through ODBC.

Additionally you can choose from various Devart ODBC drivers for SQL Server, Oracle, MySQL, SQLite, Firebird, PostgreSQL, Interbase.

Installation

Install the unixodbc package.

Configuration

Driver are declared in /etc/odbcinst.ini, and connections in /etc/odbc.ini. More instruction at each driver section.

Drivers

FreeTDS

FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases. Technically speaking, FreeTDS is an open source implementation of the TDS (Tabular Data Stream) protocol used by these databases for their own clients.

Installation

Install the freetds package.

Configuration

/etc/odbcinst.ini
[FreeTDS]
Driver          = /usr/lib/libtdsodbc.so
UsageCount      = 1

The configuration file of FreeTDS itself is /etc/freetds/freetds.conf.

Myodbc

Myodbc is ODBC driver/connector for mariadb.

Installation

Install the myodbcAUR package.

Configuration

Starting with odbcinst.ini, which lists all installed drivers.

/etc/odbcinst.ini
[MySQL]
Description     = ODBC Driver for MySQL
Driver          = /usr/lib/libmyodbc.so
Setup           = /usr/lib/libodbcmyS.so
FileUsage       = 1

Create a symbolic link

Next we need to create a symlink for libmyodbc.so. To do this we need to go to /usr/lib/ and set up a symlink to libmyodbc.so

# cd /usr/lib/
# ln -s ./libmyodbc5w.so ./libmyodbc.so

Databases

Microsoft SQL Server 2000

/etc/odbc.ini
[server_name]
Driver      = FreeTDS
#Trace       = Yes
#TraceFile   = /tmp/odbc
Servername  = server_name
Database    = database_name
/etc/freetds/freetds.conf
[server_name]
host = 192.168.0.2 # Host name or IP address.
port = 1433 # Default port.
tds version = 7.1
client charset = UTF-8

SQL Server ODBC driver connection strings and configuration guide

Mariadb

Set up your data sources in /etc/odbc.ini (system wide) or ~/.odbc (current user). If a data source is defined in both of these files, the one in your home directory take precedence.

[MySQL-test]
Description     = MySQL database test
Driver          = MySQL
Server          = localhost
Database        = test
Port            = 3306
Socket          = /var/run/mysqld/mysqld.sock
Option          =
Stmt            =

MariaDB ODBC driver connection strings and configuration guide

Create a test database

Create a new database "test". You can use one of the MySQL front-ends such as mysql-workbench, or the command-line mysqladmin command:

$ mysqladmin -h localhost -u root -p create test

Testing the ODBC

To test the ODBC connection

$ isql MySQL-test

If the connection is established, you will see

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

If you have a problem connecting then check the error message by running

$ isql MySQL-test -v

A couple useful websites

http://www.unixodbc.org/doc/OOoMySQL.pdf

This website got me going on ODBC with MySQL but left out some things that were necessary for me to get isql up and running. However this might be a good reference for the OpenOffice part.

http://mail.easysoft.com/pipermail/unixodbc-support/2004-August/000111.html

To work around error messages this URL proved helpful so here it is as well.

Virtuoso / SPARQL

/etc/odbc.ini
[ODBC Data Sources]
VOS = Virtuoso

[VOS]
Driver = virtuoso-odbc
Description = Virtuoso Open-Source Edition
Address = localhost:1111
/etc/odbcinst.ini
[virtuoso-odbc]
Driver = /usr/lib/virtodbc.so

Opening a connection using the default credentials (username: "dba", password: "dba"):

$ isql VOS dba dba