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

From TestimonyofThomas
Jump to navigation Jump to search

Scenario

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

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

(FirstName char(50),

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

Set up the ~OpenOffice.org database as an ~OpenOffice.org datasource

I went into ~OpenOffice.org 2.0 Tools, Options, ~OpenOffice.org Base, Databases, New... and selected MyDB.odb, calling it MyDB. Now it is on the ~OpenOffice.org data sources list as MyDB.

Create labels in a Labels document

I Used File, New, Labels to create a new ~OpenOffice.org Writer Labels document. In the new Labels wizard, I left Address unchecked and selected the Customer table from MyDB. Then I picked the fields I wanted on the labels, and tried to get them in the right format. Then I picked the Avery 8160 label Brand and Type and clicked New Document. After a very long wait of more than several minutes (I went to bed) while ~OpenOffice.org read all 1300 addresses from my very long online table, a document appeared showing data field codes on a label sheet.

I selected File, Print and answered Yes, I want to print a form letter. I then selected the first 20 records from my database, and clicked OK. After several minutes a sheet of 20 mailing addresses printed from my printer. But empty fields produced blank areas in the labels.

Ideas for next step: 1. Put the Hidden Paragraph field on the same line as Address2 with the Condition set to: Not(Address2)

Hidden Paragraph is found at Insert > Fields > Other (or Ctrl+F2) > Function tab. When testing this make sure View > Hidden Paragraphs is not checked.

2. Mail addressing letters or envelopes have a different number of lines in their address. The following method will show you how to add a blank line/field via a mail merge known as a 'Hidden Paragraph', which basically hides an entire line based on the testing of a condition parameter.

1. Place cursor immediately in front of the shaded field you wish to hide if it is blank 2. Select Insert -> Fields -> Other 3. Click the Functions tab 4. Select 'Hidden Paragraph' in the 'Type' section 5. Type "NOT field-name" in the 'Condition' box, where field-name is the name of the mail merge/form letter field 6. Click the 'Insert' button 7. Click the 'Close' button 8. Do the mail merge

This will only work in OpenOffice.org 1.1, which is the current release.

3. How to eliminate the blank address lines?

Let's say that in our template, the field ‘ADDRESS2’ doesn't contain data in every record.

Place the cursor before the field ‘ADDRESS2’ in your mail template. Press the key combination CTRL+F2 to open the fields dialog window.

Switch to the ‘Functions’ tab .

Select the field style ‘Hidden Paragraph’ and insert as Condition ‘not(field name)'. In our example it will be not(ADDRESS_2). Then click on the 'Insert' button to insert your function into the document.


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.

Retrieved from "https://constructionnotesmanager.com/thomas/index.php?title=Connecting_to_a_web-hosted_MySQL_database_with_your_local_OpenOffice.org&oldid=6"