Connecting the pieces: OpenOffice – MySQL – More.Groupware

Connecting OpenOffice to More.Groupware through ODBC

I detailed how I got my office to work with Open Source tools like OpenOffice, more.groupware, MySQL and others. What I will tell you here is now I integrated OpenOffice templates to get address details directly from the more.Groupware database via OpenOffice’s connection tools based on ODBC. This will work with both 1.0 and 1.1b.

After successfully installing More.Groupware and starting to use it, you will probably end up with hundreds of companies and thousands of names. So if you’re writing a letter or fax, would you want to enter the address details every single time? I doubt it!

OpenOffice provides very useful database connection methods that are more fully explained on their site, however I will briefly show you how I did it for Computer Point’s printed communications.

Required base applications:
1.OpenOffice
2.MySQL
3.More.Groupware

Required utilities:
1.MyODBC connector from http://www.mysql.com
2.UnixODBC from http://www.unixodbc.org

Further reading and instructions:
1.OpenOffice.org 1.0, ODBC and MySQL �How-To� by John McCreesh (http://www.unixodbc.org/doc/OOoMySQL.pdf) this is an extensive detailed guide that you should read. It also details several work-arounds and �gotchas�.

The very first thing you need to do is install the MyODBC package on your system as well as the MySQL shared client and development binaries. This is the same requirement for both Windows and Linux, although Linux is more involved and is what I will conentrate on here. Next install the UnixODBC either from source or rpm package.

The latest MyODBC package (from http://www.mysql.com) just does not work for me. I had to use the older 2.50 release on Linux and it works quite well.

Now create your odbc.ini and odbcinst.ini as detailed in McCreesh’s document. What I got cought at is the necessity of copying /etc/odbc.ini file to my own directory to get it to work:

cp /etc/odbc.ini ~/.odbc.ini

(note that is a hidden file, so don’t forget the �.�)

My odbc.ini file looks like this:

[groupware]
Description = MoreGroupware
Driver = MySQL
Server = server
Database = mgw
Port =
Socket =
Option =
Stmt =

(note: obviously you should define your server in the hosts file, otherwise enter the server’s IP address above)

Once everything is installed, test your installation with isql:

isql groupware username password -v

the username and passwords of course are defined in MySQL to access the database. I set up an account for everyone in the office through phpMyAdmin where you should define the �host� parameter for every user as �%� in order for everyone to access the database from where ever they are (home, office, etc).

1.Assuming that everything is successful so far, start up OpenOffice and go to the Tools > Data Sources menu item
2.In the general tab click on the �New Data Source� and enter the data source as defined in the odbc.ini file.
3.Select ODBC if you’re using OpenOffice 1.0, or MySQL for OpenOffice 1.1 in the Database type field.
4.Click on the Data Source URL and select your source from the pop-up list.
5.Click the �MySQL� tab, select �Use existing MyODBC data source�, enter your username and click on the �password required� button if a password is required to access the groupware database.
6.Click the �Queries� tab, then SQL to enter your SQL statement to select the address details you need. Here’s mine if it’s any help to you:

SELECT DISTINCT `mgw_contacts`.`firstname` AS `FirstName`, `mgw_contacts`.`lastname` AS `LastName`, `mgw_contacts`.`function` AS `Title`, `mgw_companies`.`name1` AS `Company`, `mgw_companies`.`street` AS `Address1`, `mgw_companies`.`additional` AS `Address2`, `mgw_companies`.`city` AS `City`, `mgw_companies`.`zip` AS `PostCode`, `mgw_countries`.`name` AS `Country`, `mgw_companies`.`telephone` AS `Tel`, `mgw_companies`.`fax` AS `Fax` FROM `mgw_contacts` `mgw_contacts`, `mgw_countries` `mgw_countries`, `mgw_companies` `mgw_companies` WHERE ( `mgw_contacts`.`priv_countrycode` = `mgw_countries`.`code` AND `mgw_companies`.`countrycode` = `mgw_countries`.`code` AND `mgw_companies`.`id` = `mgw_contacts`.`companyid` ) ORDER BY `LastName` ASC

7.Click OK to save your settings. Now you’re ready to get the address details directly into your OpenOffice documents from More.Groupware!
8.Please feel free to download a template that uses the above statement, please modify it as you see fit to use it in your own office.

Regards
Mahmood Al-Yousif