Connecting Openoffice.org's Writer and MySQL on Debian GNU/Linux

This is a quick description of how to use Openoffice.org's Writer to modify MySQL databases. The Openoffice.org used in this example is from 2005 so it might become dated but here it is anyway.

Install OpenOffice.org

apt-get install openoffice.org

Install MySQL

apt-get install mysql

To turn on networking in MySQL (make it listen to incomming connections) you have to edit the /etc/mysql/my.cnf file.Comment out the line "skip-networking". To make it listen to local loopback only (just your machine) you need to put in the line "bind-address = 127.0.0.1". In the section "[mysqld]". Save it.

Then restart MySQL

/etc/init.d/mysql restart

After that install the grant tables.

mysql_install_db --user=mysql

Then login to MySQL.

mysql -u root -p

By default the password is blank so hit enter when asked for one. Then after you login set the MySQL root users password with the following commands.

use mysql;

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('passwordhere');

Then create a database with the following command.

create database databasenamehere;

Then create a table.

create table blah (unique_name varchar(100) not null primary key);

I've read if you don't create a primary key the database stays read-only. So heed that warning. To connect to MySQL with OpenOffice Writer you have to install the packages "unixodbc", "libmyodbc", and "myodbc" in Debian. Do this with the following commands.

apt-get install unixodbc libmyodbc libmyodbc

Then configure the following odbc file /etc/odbcinst.ini to look like this:

[MySQL]

Description = ODBC Driver for MySQL
Driver      = /usr/lib/libmyodbc.so
Setup       = /usr/lib/libodbcmyS.so
FileUsage   = 1
CPTimeout   =
CPReuse     =

Then configure the next odbc file /etc/odbc.ini to look like the following example. Don't forget to replace the "databasenamegoeshere" with your actual database name.

[databasenamegoeshere]
Description  = MySQL db test
Driver       = MySQL
Server       = localhost
Database     = databasenamegoeshere
Port         = 3306
Socket       =
Option       =
Stmt         =

To test if you can connect to the MySQL database with ODBC use the program "isql". It should have come with one of the packages above. To connect to the MySQL database use the command:

isql databasename root mysqlrootspassword -v

If it connects and brings you to a SQL prompt then it should work fine in Openoffice. If not then you'll have to look at the error message and determine what your unique problem is and how to make it connect correctly.

Now open OpenOffice.org's Writer program and click "Tools" -> "Data Sources". In the "database" tab select "MySQL" as the database type. Click the datasource url button and select your database. Then in the "MySQL" tab click select "Use existing myodbc datasource". In the datasource url section click the button and make sure your database is selected. Under username put the users name that has access to the DB (mine is root). Check the password box. Then click the "ok" button and you'll be back at the main Writer screen. Hit the "F4" key and it will bring up a database view. From there you can right click on the "tables" area to create and edit or make new tables.

Reddit!

Related stories