Odbc
Contents
MySQL, ODBC with OpenOffice Setup on LocalHost
This document shows how to set up ODBC in Arch, first to access you database on your localhost and then extends the steps to configure MySQL to allow remote access through ODBC.
Packages To Install
Start by installing the unixodbc and myodbc
# pacman -Sy unixodbc # pacman -Sy myodbc
Configure Your ini Files
Configure your "odbc.ini", ".odbc.ini", and "odbcinst.ini" file. Startinig with odbcinst.ini, 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
Next set up your "odbc.ini" and ".odbc.ini" file. It should be pointed out here that the ".odbc.ini" file is just like the "odbc.ini" file except that it is a file that gets saved in the user's home dir (i.e a hidden file).
[MySQL-test] Description = MySQL database test Driver = MySQL Server = localhost atabase = test Port = 3306 Socket = /tmp/mysql.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/myodbc3-3.51.12.so or whatever the version of the module is and set up a symlink to libmyodbc.so
ln -s /usr/lib/libmyodbc3-3.51.12.so ./libmyodbc.so
Renamed Modules
OK, for this step I cheap-skated it a little and simply copy these files to agree with version of ODBC that Arch was looking for.
cp libssl.so.0.9.8 libssl.so.0.9.7 cp libcrypto.so.0.9.8 libcrypto.so.0.9.7
Create A Test Datbase
On my machine I run phpmyadmin so I already had a test database to use. If you don't have a test database then create one in MySQL.
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.
MySQL, ODBC with OpenOffice Setup on the Remote Server
Edit odbc.ini
From the Client side it is neccessary 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 = /tmp/mysql.sock Option = Stmt =
Edit my.cnf
Edit /etc/my.cnf so that port 3306 will be running to do this comment out skip-networking and also add bind-address = your IP
#skip-networking bind-address =192.168.0.100
Edit hosts.allow
Next add a line in "hosts.allow"
mysqld:ALL
It is recommended to do something like mysql:192.168.0.102 or whatever your client machine would be.
Verify Port 3360 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 0.0.0.0:3306 0.0.0.0:* LISTEN
Permissions on MySQL for Remote Connection
Next it is neccesary to give permissions on the server so that MySQL will allow a remote connection. Type "mysql -u "username" -p" and log into you MySQL
mysql> CREATE DATABASE test; mysql> GRANT ALL ON test.* TO username@'192.168.0.101' IDENTIFIED BY 'PASSWORD';
http://www.cyberciti.biz/nixcraft/vivek/blogger/2006/03/how-do-i-enable-remote-access-to-mysql.php
Here was a helpful URL for this step.
Testing the ODBC
run the "isql MySQL-test" command and you should be able to log into your server.
Miscellaneous
The main error message that took a while for me to figure out was the error message "Lost connection to MySQL server during query". This was being driven from the hosts.allow not being added.
Also from the client you can run the following command to verify that port 3306 is open
telnet 192.168.0.102 3306
Good luck and happy computing now that you can get OpenOffice to connect to you datbases. Alex & HW