(LinuxWorld) — The forum postings on last week's column about the installation of MySQL and ODBC to gain access to the secret power of OpenOffice.org 1.0 were a mixed bag: some good, some bad, some "so what." I was variously described as an stupid, foolish, an incompetent sysadmin and a writer of more fiction than fact.

I'll come clean about one thing: I am an incompetent sysadmin, and I have a failing score on the first LPI exam to prove it. But then so are most small businesspeople in the SOHO market. That makes my experiences a more-valuable litmus test for SOHO users than those of some yahoo guru running gentoo.

If you dismiss the name-calling and those who suggest switching to their distribution of choice in order to tame the problem, some valuable information remains to be gleaned. Among other things, I found a better, cleaner and "distribution-pure" installation method for Red Hat 8.0. It has only a couple of gotchas. Regardless of the distribution you're using, you might find help for how to get MySQL/ODBC installed and working with OpenOffice.org scattered in that forum.

If you're running Red Hat 8.0, the steps I followed might work for you. They are:

1. up2date mysql mysql-server and mysql-devel
2. /usr/bin/mysql_install_db
3. chown -R mysql.mysql /var/lib/mysql
4. chmod -R 666 /var/lib/mysql
5. /usr/bin/safe_mysql -u mysql &
6. up2date unixODBC unixODBC-devel
7. up2date --src MyODBC
8. rpmbuild --rebuild /var/spool/up2date/MyODBC-2.50.39-7.src.rpm
9. rpm -Uvh --force /usr/src/redhat/RPMS/i386/MyODBC-2.50.39-7.i386.rpm
10. Edit /etc/odbc.ini and /etc/odbcinst.ini

For whatever reasons, whenever I tried it with all the packages listed in a single up2date, it didn't work. It may have been the way I was holding my mouth, or I might not have sanitized my system completely from one of the previous failed installations. Or possibly up2date doesn't handle things correctly when there are too many packages under the tree. Hopefully it will work for you, too. As was explained in the forum, the reason for having to rebuild MyODBC is that the latest binary RPM for MyODBC does not yet match changes made in unixODBC for security problems.

There were also complaints about my suggesting a conflict of interest at MySQL AB between making money and making MySQL easy to install and configure. I would like to clarify my position. I did not say MySQL AB was failing to document MySQL more adequately in order to make more money selling support. What I said was that there is a conflict between the goals of the business and free software. It may or may not influence behavior, but it certainly exists. The goal of a business is to make money. The larger the market, the more money that can be made. Writing better documentation might very well shrink the market interested in MySQL AB products and services.

Moving right along

Enough of what happened last week. This week is all about actually using the MySQL/OpenOffice connection, not cussing while installing it or fussing about the best way to do it. For my test, I decided to do something that I had done circa 1995/1996 using both MS Access and the Lotus Approach — that being to create a table of newsletter subscribers and to use it to a mailmerge.

OpenOffice.org provides an easy-to-use tool for designing database tables. Clicking on View->DataSources brings up the Data Explorer, which lets you view and work with links, queries and tables in your data sources. I clicked on the MySQL-test data source to expand it, then selected tables and clicked the plus sign. Then a right-click on tables produced the Table Design window you see below. A couple of minutes later, I had a simple name and address table for newsletter subscriptions.

Editor's note: The above image is reduced in size to allow it to display on this page. Click on this image to see the original.

Now on to the heart of the matter: printing address labels from the table just created. I admit to getting stuck on this step for more than a full day. Relying primarily on the Help system, I followed the steps outlined but had no success. In addition to the steps being anti-intuitive, they also have the disadvantage of just not working. The best I could with them was to print every other label. I know, I know. You can say the glass is half-empty or half-full. But I wanted them all.

I asked for help using IRC in both the #openoffice.org and the #mysql channels on irc.freenode.net. But there was no help. Not even a suggestion. I Googled and read search results, but I couldn't find a mention of this problem (or even a similar one). Worse, I didn't know if I was dealing with a MySQL problem, an ODBC problem or an OpenOffice.org problem.

Finally, I joined OpenOffice.org so that I could search existing bugs. They track issues, not bugs, but it didn't help. Finally I decided to search the users mailing list archive. When I did, the problem became as plain as the nose on my face. The problem is that the Help system docs are wrong. Period. They don't work.

Success? Success!

I did find instructions there that work. I want to mention a small "gotcha" before proceeding. When I first began trying to print the address labels, I got a MySQL error message for "Too many connections." I learned that each label on the page requires a connection of its own. I did two things to resolve the problem. It's possible that only one of them was required. First, I added this line to the mysqld section of /etc/my.cnf:

set-variable = max_connections=200

After killing the running processes, I did an su mysql before restarting.0 I had found a note in a Google search from a MySQL developer who suggested not starting mysqld as root might solve the "Too many connections" problem. I restarted as user mysql instead of root passing a user argument on the command line. After the changes, database access worked. More experimentation is required to figure out which one actually cured the ill.

Here are the instructions on how to print the labels.

1. Choose File->New->Labels
2. Select the Labels tab and pick the columns from the table you want printed on the label, left to right, line by line. Be sure to enter space between adjacent columns on the same line and to press Enter to continue on a new line.
3. Select the Format tab and define the way you want the labels to print: the size, margins and number of columns and rows you want per page.
4. Select the Options tab and unselect Synchronize
5. Click on New Document
6. Press F4 to select the data source
7. Select the rows from the table that you want included by clicking on the button to the left of the leftmost column of the table display. Use the Shift and CTL keys as you normally do for selecting multiple items.
8. Click on the "Data to Fields" icon on the database toolbar. Just to the left of this icon is a very similar one. You want the one on the right, which is immediately adjacent to the Form Letter icon. When you click it, the document fields will be populated from the database.
9. Click on the Form Letter icon and follow the dialog to print the labels.

Final impressions? OpenOffice.org is more sophisticated and powerful than I realized. It can be made to work with various database-management systems, and it can do the mailmerge functions required by many SOHO types. Ease of use is not up to what I remember with either Access or Approach six or seven years ago, but I'm sure that it will continue to improve in that regard. The good news to me is that Linux is making strong strides not just in the enterprise, and not just on the desktop, but in a place where it seems to lag most of all: the SOHO market.

If you need database access and manipulation for your SOHO office environment, I would not recommend an immediate switch to OpenOffice.org. I would very strongly recommend that you begin beta tests of it — perhaps with more than one DBMS — to see if it meets your needs. Exercise it fully. You don't want to get to the tail-end of an implementation only to find another "gotcha" that makes the whole thing unsuitable. As with all software, there are bound to be bugs in both the code and the documentation. I'll continue my own investigations and compare my experiences with both PostgreSLQ and the OpenOffice.org default, dBase.

• Trail of tears: MySQL, ODBC & OpenOffice 1.0
• Codeweaver's CrossOver Office is the best way to do the wrong thing
• How to spyce up your data
• Two stupid PHP tricks
• More stupid PHP tricks
• Even more stupid PHP tricks
• Practical XML with Linux, Part 3: XML database tools for Linux
• "Open Source Is In Our Blood," Says Sun's Jonathan Schwartz

• Is Joe Barr certifiable?
• MySQL AB
• OpenOffice.org
• OpenOffice/MySQL How-to
• unixODBC