Difference between revisions of "Odbc"

From ArchWiki
Jump to: navigation, search
m (Miscellaneous)
(7 intermediate revisions by 4 users not shown)
Line 1: Line 1:
#REDIRECT [[Open Database Connectivity]]
[[Category:Database management systems]]
{{moveto|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
This document shows how to set up [http://www.unixodbc.org/ unixODBC] in Arch, first to access your database on your localhost and then extends the steps to configure MySQL to allow remote access through ODBC.
==MySQL, ODBC with OpenOffice Setup on LocalHost==
===Packages To Install===
Start by installing the unixodbc and myodbc
# pacman -S unixodbc myodbc
===Configure the ini Files===
Configure the "odbc.ini" and "odbcinst.ini" file.
Starting with odbcinst.ini, which lists all installed drivers. Su to root and set up your /etc/odbcinst.ini file as follows
Description    = ODBC Driver for MySQL
Driver          = /usr/lib/libmyodbc.so
Setup          = /usr/lib/libodbcmyS.so
FileUsage      = 1
Next 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.
Description    = MySQL database test
Driver          = MySQL
Server          = localhost
Database        = test
Port            = 3306
Socket          = /var/run/mysqld/mysqld.sock
Option          =
Stmt            =
===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
===Create A Test Database===
Create a new database "test". You can use one of the MySQL front-ends {{AUR|mysql-gui-tools}} {{AUR|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                                  |
|                                      |
If you have a problem connecting then check the error message by running
isql MySQL-test -v
===A Couple Useful Websites===
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.
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.
Description    = MySQL database test
Driver          = MySQL
Server          =
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.
bind-address =
===Verify Port 3306 is Listening===
restart the mysql server with the command "/etc/rc.d/mysqld restart" and then check to see that port 3306 is listening
on the server
netstat -ant
If port 3306 is open, you'll see the following
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address          Foreign Address        State
tcp        0      0*              LISTEN
===Permissions on MySQL for Remote Connection===
Next it is necessary to give permissions on the server so that MySQL will allow a remote connection.
mysql -h localhost -u username -p
and log into your MySQL database.
mysql> CREATE DATABASE test;
mysql> GRANT ALL ON test.* TO username@'' 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.
From the client you can run the following command to verify that port 3306 is open
telnet 3306
Good luck and happy computing now that you can get OpenOffice to connect to you databases.

Latest revision as of 10:32, 21 February 2015