Difference between revisions of "Open Database Connectivity"

From ArchWiki
Jump to: navigation, search
(Move driver part and data part into their sections.)
(MySQL, ODBC with OpenOffice Setup on the Remote Server: Remove. Duplicate information. Already covered in MariaDB#Grant remote access)
Line 116: Line 116:
  
 
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.
 
==MySQL, ODBC with OpenOffice Setup on the Remote Server==
 
===Edit odbc.ini===
 
From the Client side it is necessary to edit the odbc.ini and the .odbc.ini files slightly. Here is the odbc.ini that I used to login.
 
[MySQL-test]
 
Description    = MySQL database test
 
Driver          = MySQL
 
Server          = 192.168.0.102
 
Trace          = Off
 
TraceFile      = stderr
 
Database        = test
 
Port            = 3306
 
USER            = username
 
Password        = secretpassword
 
Socket          = /var/run/mysqld/mysqld.sock
 
Option          =
 
Stmt            =
 
 
===Edit my.cnf===
 
Edit /etc/mysql/my.cnf so that port 3306 will be running to do this comment out skip-networking. You can add
 
{{ic|bind-address = IP}} with you incoming network card's IP.
 
#skip-networking
 
bind-address = 192.168.1.100
 
 
===Verify Port 3306 is Listening===
 
restart the MySQL server
 
# systemctl restart mysqld
 
Then check that port 3306 is listening on the server
 
$ ss -ant
 
If port 3306 is open, you'll see the following:
 
State      Recv-Q Send-Q                                    Local Address:Port                                      Peer Address:Port
 
LISTEN      0            50                                                    *:3306                                                  *:*
 
 
===Permissions on MySQL for Remote Connection===
 
Next it is necessary to give permissions on the server so that MySQL will allow a remote connection.
 
Type
 
mysql -h localhost -u username -p
 
and log into your MySQL database.
 
mysql> CREATE DATABASE test;
 
mysql> GRANT ALL ON test.* TO username@'192.168.1.100' IDENTIFIED BY 'PASSWORD';
 
An extensive tutorial on this subject can be found [http://www.cyberciti.biz/nixcraft/vivek/blogger/2006/03/how-do-i-enable-remote-access-to-mysql.php here].
 
 
===Testing the ODBC===
 
run the
 
isql MySQL-test
 
command and you should be able to log into your server.
 
 
===Miscellaneous===
 
 
From the client you can run the following command to verify that port 3306 is open
 
telnet 192.168.0.100 3306
 
 
Good luck and happy computing now that you can get OpenOffice to connect to you databases.
 

Revision as of 10:49, 21 February 2015

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.

Installation

# pacman -S unixodbc

Configuration

At /etc/odbcinst.ini is where drivers are declared, and /etc/odbc.ini where connections. 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

pacman -S freetds

Configuration

/etc/odbcinst.ini

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

Myodbc

Modbc is ODBC driver/connector for mariadb.

Installation

Install myodbc from official repositories.

Configuration

Starting with odbcinst.ini, which lists all installed drivers. Su to root and set up your /etc/odbcinst.ini file as follows

[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

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            =

Create A Test Database

Create a new database "test". You can use one of the MySQL front-ends mysql-gui-toolsAUR mysql-workbench or the commandline.

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.