Difference between revisions of "Odbc"

From ArchWiki
Jump to: navigation, search
m (" don't " -> " do not ")
(i18n.fix whitespace, heading levels. Remove personal signature.)
Line 1: Line 1:
 
[[Category:Networking (English)]]
 
[[Category:Networking (English)]]
=MySQL, ODBC with OpenOffice Setup on LocalHost=
+
{{i18n|Odbc}}
 +
 
 +
==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.  
 
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==
+
===Packages To Install===
 
Start by installing the unixodbc and myodbc
 
Start by installing the unixodbc and myodbc
 
  # pacman -S unixodbc myodbc
 
  # pacman -S unixodbc myodbc
  
==Configure the ini Files==
+
===Configure the ini Files===
 
Configure the "odbc.ini" and "odbcinst.ini" file.
 
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
 
Starting with odbcinst.ini, which lists all installed drivers. Su to root and set up your /etc/odbcinst.ini file as follows
Line 27: Line 29:
 
  Option          =
 
  Option          =
 
  Stmt            =
 
  Stmt            =
==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 libmyodbc.so. To do this we need to go to /usr/lib/ and set up a symlink to libmyodbc.so
 
   cd /usr/lib/
 
   cd /usr/lib/
 
   ln -s ./libmyodbc3-3.51.12.so ./libmyodbc.so
 
   ln -s ./libmyodbc3-3.51.12.so ./libmyodbc.so
  
==Renamed Modules==
+
===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
 
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.
 
was looking for.
Line 38: Line 40:
 
  cp libcrypto.so.0.9.8 libcrypto.so.0.9.7
 
  cp libcrypto.so.0.9.8 libcrypto.so.0.9.7
  
==Create A Test Database==
+
===Create A Test Database===
 
On my machine I run phpmyadmin so I already had a test database to use. If you do not have a test database then
 
On my machine I run phpmyadmin so I already had a test database to use. If you do not have a test database then
 
create one in MySQL.
 
create one in MySQL.
  
==Testing the ODBC==
+
===Testing the ODBC===
 
To test the ODBC connection
 
To test the ODBC connection
 
  isql MySQL-test
 
  isql MySQL-test
Line 56: Line 58:
 
  +---------------------------------------+
 
  +---------------------------------------+
 
  SQL>
 
  SQL>
 
  
 
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 71: Line 72:
 
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=
+
==MySQL, ODBC with OpenOffice Setup on the Remote Server==
==Edit odbc.ini==
+
===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.
 
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]
 
  [MySQL-test]
Line 88: Line 89:
 
  Stmt            =
 
  Stmt            =
  
==Edit my.cnf==
+
===Edit my.cnf===
 
Edit /etc/my.cnf so that port 3306 will be running to do this comment out skip-networking and also add
 
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
 
bind-address = your IP
Line 94: Line 95:
 
  bind-address =192.168.0.100
 
  bind-address =192.168.0.100
  
==Verify Port 3306 is Listening==
+
===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
 
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
 
on the server
Line 104: Line 105:
 
  tcp        0      0 0.0.0.0:3306          0.0.0.0:*              LISTEN
 
  tcp        0      0 0.0.0.0:3306          0.0.0.0:*              LISTEN
  
==Permissions on MySQL for Remote Connection==
+
===Permissions on MySQL for Remote Connection===
 
Next it is necessary to give permissions on the server so that MySQL will allow a remote connection.
 
Next it is necessary to give permissions on the server so that MySQL will allow a remote connection.
 
Type "mysql -u "username" -p" and log into you MySQL
 
Type "mysql -u "username" -p" and log into you MySQL
 
  mysql> CREATE DATABASE test;
 
  mysql> CREATE DATABASE test;
 
  mysql> GRANT ALL ON test.* TO username@'192.168.0.101' IDENTIFIED BY 'PASSWORD';
 
  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
 
http://www.cyberciti.biz/nixcraft/vivek/blogger/2006/03/how-do-i-enable-remote-access-to-mysql.php
Line 115: Line 115:
 
Here was a helpful URL for this step.
 
Here was a helpful URL for this step.
  
==Testing the ODBC==
+
===Testing the ODBC===
 
run the "isql MySQL-test" command and you should be able to log into your server.
 
run the "isql MySQL-test" command and you should be able to log into your server.
  
==Miscellaneous==
+
===Miscellaneous===
  
 
From the client you can run the following command to verify that port 3306 is open
 
From the client you can run the following command to verify that port 3306 is open
 
  telnet 192.168.0.102 3306
 
  telnet 192.168.0.102 3306
 
 
  
 
Good luck and happy computing now that you can get OpenOffice to connect to you databases.
 
Good luck and happy computing now that you can get OpenOffice to connect to you databases.
Alex & HW
 

Revision as of 02:28, 28 October 2011

This template has only maintenance purposes. For linking to local translations please use interlanguage links, see Help:i18n#Interlanguage links.


Local languages: Català – Dansk – English – Español – Esperanto – Hrvatski – Indonesia – Italiano – Lietuviškai – Magyar – Nederlands – Norsk Bokmål – Polski – Português – Slovenský – Česky – Ελληνικά – Български – Русский – Српски – Українська – עברית – العربية – ไทย – 日本語 – 正體中文 – 简体中文 – 한국어


External languages (all articles in these languages should be moved to the external wiki): Deutsch – Français – Română – Suomi – Svenska – Tiếng Việt – Türkçe – فارسی

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 -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

[MySQL]
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.

[MySQL-test]
Description     = MySQL database test
Driver          = MySQL
Server          = localhost
Database        = 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/ and set up a symlink to libmyodbc.so

 cd /usr/lib/
 ln -s ./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 Database

On my machine I run phpmyadmin so I already had a test database to use. If you do not 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 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          = /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

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 0.0.0.0:3306           0.0.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. 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

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 databases.