Difference between revisions of "Odbc"

From ArchWiki
Jump to: navigation, search
(MySQL and ODBC)
 
(45 intermediate revisions by 16 users not shown)
Line 1: Line 1:
=MySQL, ODBC with OpenOffice Setup on LocalHost=
+
#REDIRECT [[Open Database Connectivity]]
 
 
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
 
 
 
==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.inin that I used to login.
 
[MySQL-test]
 
Description    = MySQL database test
 
Driver          = MySQL
 
Server          = 192.168.0.101
 
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.101 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.
 
mysql -u "username" -p
 
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.
 
 
 
 
 
From remote system type command:
 
 
 
$ mysql -u webadmin –h 65.55.55.2 –p
 
You can also use telnet to connect to port 3306 for testing purpose:
 
 
 
$ telnet 65.55.55.2 3306
 

Latest revision as of 10:32, 21 February 2015