In my case, I needed a way to pump data from dbf files to MySQL. I could have used a number of other programs, but I thought that this would be a good excuse to learn another.

By the way, there are a number of not free programs that claim to do the same thing. Just use a search engine to find one.

The solution was to download the MySQL JDBC driver (Connect/J) and install it as instructed on the JDBC Connection help page.

• From the menu, select Tools / Options... / OpenOffice.org / Java
• There will be a list of available Java runtime environments (JRE), select one
• Click the Class Path... button
• Add the path to either an archive (jar or zip file) or the directory where JDBC was unpacked

When writing this page (ie, after I had a working system), I discovered another option - the MySQL Connector for OpenOffice.org. I am running OpenOffice 3.1.1 - which is supported by this connector. However, the local OpenOffice help does not mention it.

• The MySQL Connection help page suggests only ODBC and JDBC.
• The Database Wizard help page has many suggestions, but not that.

Since I was using 2 systems, one with OpenOffice and separate with MySQL, I finally decided to check the firewall on the MySQL system.

When I opened port 3306, it fixed the problem.

On the MySQL system,

• Use the Windows Control Panel / Windows Firewall
• On the Exceptions tab, select Add Port...
• Name = MySQL (or anything you want)
• Port number = 3306
• Leave TCP selected
• Select OK

If I had had experience with working system, I might have found this much sooner. Basically, with the port blocked, when you click Test it takes a long time before a dialog box indicates no response. However, when it is open, failed login attempts are reported immediately.

Note that the default root user id will not work - so create another for this.

• File / New / Database opens the Wizard
• Select Connect to an existing database and JDBC
• Datasource URL - mysql://hostname/databasename
• JDBC driver class - com.mysql.jdbc.Driver (case sensitive, no extra spaces before or after) - test the driver (class)
• Click Next and enter the username and password (must check the box to enter it) - be sure to test the connection
• Let the Wizard "register" the database for you - it creates a short alias for the full *.odb path - Tools / Options... / OpenOffice.org Base / Databases
• Clicking Finish creates an odb file - this stores the information you just entered

By default, the root username works only on the localhost and will not work over a network. This is a good thing - create a separate username and password for remote access.

Security fail - Reopening an *.odb file does not required either a username or a password.

• File / New / Database opens the Wizard
• Select Connect to an existing database and dBase
• On the Next page, enter (locate) the directory that contains the dbf files
  If the directory is not found, there will be an option to create one
• Click Next and let the Wizard "register" the database for you
• Clicking Finish creates an *.odb file - this stores the information you just entered

Edit		See the table structure - columns and their data types
Open		See the data
Copy		Used with Paste to convert the table to MySQL

• In mysql.exe (or another method of your choice), create a new database (directory) - I did not find a way to do this in OpenOffice
• In OpenOffice, open 2 databases
  • One has the dbf tables you want to copy
  • The other is the empty MySQL database
• In the dbf window, right click an existing table and select Copy
• In the MySQL window, right click in the table area and select Paste
• In the dialog box
  • Select Definition and data
  • On the next page, select which fields to copy
  • The next page provides the option to change the field types - you will need this later
  • Let it run (click Create)
• Locate and fix multiple import problems

An error has occurred? Do you want to continue copying?

select count(*) from tablename;

I don't get it - a number of fields created by this process were one character smaller than the originals. Specifically,

number(9,4) decimal(10,0) - these are what the data was number(8,4) decimal( 9,0) - these are what was created

Decimal fields are stored in dbf files as ascii values. It is possible that the database is defined wrong (but still holds the data) or that OpenOffice has a fairly major design problem. (I am pretty sure that OpenOffice has the problem .. but you never know.)

At any rate, when pasting a table (ie, making a copy), there is an option to modify the proposed data types .. so I did. At that point, the data was transferred without any problems.

• The program provides a number of tools that might help to make database development easier - in particular, the query building tool.

• I tried to save a data form as html - totally worthless. Each data field was saved as a gif - formatted to be unreadable. In the following table, the sample image is inside the green border. As you can see, the text was severely clipped. In addition, there is a huge amount of dead space (which is why I added the green background .. so you can see just how crappy this is).

  Image created via Save as html

URL: http:// mc-computing.com / Databases / MySQL / OpenOffice.html