Connecting to a web-hosted MySQL database with your local OpenOffice.org

Scenario

How you can connect to a web-hosted MySQL database with your local OpenOffice.org or other program and print mailing labels.

Update: I tested this solution in March 2007 with a friend who uses MS Access and it got him up and running successfully.

Vision

You have a database. Your organization would like to put it securely online for connection and use from far flung places.

Values

You believe in the future of Free Libre Open Source Software (FLOSS). You not only want to avail yourself of its benefits, but you want to support its success.

Resources

Web hosting is widely available for around $100 per year that is geared toward the FLOSS Linux/Apache/MySQL/PHP system. Linux is the operating system, Apache serves web pages, MySQL is a database system, and PHP is a web programming language.

The ODBC standard is a way for you to connect to a remote database from a local program running on your computer. MyODBC is a driver program that tells your computer how to connect to a remote MySQL database.

For your local computers, OpenOffice.org is available for the cost of an overnight download or a CD. Some of you also have proprietary software that can connect using the MyODBC driver.

Solution

Put your data into a MySQL database at a Linux web host. Connect to it and manage it from OpenOffice.org on your local computer using the MyODBC driver.

Steps

I am going to describe the steps I took to set up my system. Yours may vary, but the words and links I use will give you a head start if you need to adapt to another system. Some names are fictitious.

Get a Web Host

I got a domain account called MyOrg.org at a Linux web host called JaguarPC. They have a good, intuitive control panel and a responsive tech support ticket system. They are quite fast, probably among the best of their league, though sometimes when there are connection delays at my web site I wonder if a bit more money could buy me a bit more speed.

Set up an online MySQL Database for remote use

Under the Manage Site area of the JaguarPC control panel, I went to MySQL Databases. There I was able to completely set up a MySQL database.

Create Database

There was a form to let me "Create MySQL Database". I created MyDB.

Create User

There was a form to let me "Create MySQL User". I created MyUser with password MyPW.

Grant Permissions

There was a form to let me "Grant permissions on a MySQL database to a MySQL user". In MySQL you create users and give them permission levels for respective databases. So I granted MyUser all permissions on the MyDB database.

Grant remote access

There was a form to "Add Access Host". The local Linux "localhost" was already there for web applications. I added % to grant access from anywhere. This of course leaves security in the hands of my user permissions, and lets members of my organization from all over the world connect at internet cafes.

Get the MyODBC driver for My Computer

I downloaded the MyODBC driver setup program from the MySQL site and ran it. Then I followed the instructions on the MySQL web site for "Configuring a MyODBC DSN on Windows". Those instructions showed me how to add the MyODBC driver to the ODBC data sources (under Administrative Tools in Windows 2000 or later) in the Windows Control Panel.

Connect to the online MySQL database as a new data source with the MyODBC driver

When I added a User Data source under the User DSN tab, selecting the newly installed MyODBC driver, the MyODBC configuration form popped up. Into it I entered

• Data Source Name: MyOrganizationDB
• Server: MyOrg.org
• User: MyUser
• Password: MyPW

As soon as I entered the password, the form accessed MyOrg.org and populated the next list. I knew I was succeeding!

• Database: MyDB

I was connected. Heh. I'm grinning big.

Connect to the online MySQL database with ~OpenOffice.org on my computer

I downloaded and Openoffice.org version 1.9 (2.0 beta). It was a huge download, and I wish I had a CD.

Create a new ~OpenOffice.org Base document connected to the online MySQL database

I opened a new OpenOffice.org Base document, opted to "Connect to an existing database" of "ODBC" type, entered MyOrganizationDB as the "Name of the ODBC data source on your system", and omitted a user name since I had provided one in the MyODBC configuration form. The connection was established successfully. Big smiles again. I provided a document location and name, MyDB.odb, and was greeted by a new OpenOffice.org Base document.

Use OpenOffice.org to manage the online MySQL database with SQL queries

Note: After I upgraded OpenOffice to version 2.0 RC2, the actions in this section didn't work anymore. Only SELECT queries would work. Also, a safety feature was added so that the OpenOffice Base Forms wouldn't work with a table that had no primary key column defined.

I clicked on the "Queries" icon, selected to "Create Query in SQL view" (since I am dabbling in SQL after a two hour tutorial a few weeks ago), entered the following query into the SQL form, and picked Edit, Run Query.

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50))

It told me there was an error, because my query produced no results, and that is correct. Creating a table doesn't produce results. So I did the following query, which also produced the same error:

INSERT INTO customer VALUES
('Joseph',
'Smith')

And I did the following query:

SELECT * FROM customer

and got the following result:

Joseph  |  Smith

Success! After adding dozens more entries and a few more columns for addressing to the database, I'm ready for the next step.

Print mailing labels from the online MySQL database using OpenOffice.org on my computer.

See Mailing labels from OpenOffice.org?

Conclusion

That's all for the moment. I successfully entered addresses and printed mailing labels from MyDB at MyOrg.com using OpenOffice.org Base and Writer.

---