While OpenOffice.org is first and foremost a desktop office suite, it has a few powerful features that allow you to create some nifty solutions. For example, the Base application supports different database engines such as MySQL, and it can connect and manage remote databases. Moreover, the data from a remote database is available for other OpenOffice.org applications, so you can, for example, access and manipulate database records directly from within Writer. These capabilities can be put to some rather creative uses.

For example, as a writer you may need a tool to store your research notes, text snippets, useful words and expressions, etc. While you could use one of the many ready-made tools like Google Notebook, they all have one serious drawback: they provide no integration with Writer, a tool of the trade for many writers. What you need is a simple web-based notebook application that submits data to a database, which, it turn, can be accessed from Writer.

To create a web-based notebook application – let’s call it WNotes – you need to do three things: create a MySQL database, connect OpenOffice.org to the database, and create a simple web interface. To make things simple, let’s use the XAMPP package that includes two important pieces you need for this project: Apache web server and MySQL database server. Download the latest XAMPP package from www.apachefriends.org and install it using the tar xvfz xampp-linux-1.5.5a.tar.gz -C /opt command in the Terminal. Launch XAMPP by running the /opt/lampp/lampp start command. To install XAMPP on Windows, download the installer file and run it. Once XAMPP is installed, launch XAMPP Control Panel and start the Apache and MySQL servers.

Figure 1: Creating a MySQL database using phpMyAdmin

Once XAMPP is up and running, you have to create a MySQL database for use with WNotes. To do this, you can use the phpMyAdmin application bundled with XAMPP. Point your browser to http://localhost/phpmyadmin/, enter the desired name (e.g. “writer”) into the Create new database field, and press the Create button. Next, you have to add a table and populate it with fields. Since WNotes is a rather simple application, the database needs only one table (e.g. “notes”) and three fields:

• The ID field type INT that acts as a primary key and unique identifier for each record in the database
• The Notes field type LONGTEXT to store notes
• The Source field type VARCHAR to keep track of where the notes come from

To create a table, enter its name (“notes”) into the Name field, set the number of fields to 3, and press Go. When adding fields, remember to set the ID field to be the primary key and select auto_increment from the Extras drop-down list. You must also specify the length of the ID and Source fields by entering the desired values (for example, 10 and 255 respectively) in the Length/Values fields. You must also select “not null” from the Null drop-down list next to the ID and Notes fields to make them mandatory, meaning they must not be empty. Setting the Source field to null makes this field optional. If you don’t fancy creating the table manually from scratch, here is an SQL script that can do it for you:

CREATE TABLE `notes` (
 `ID` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `Notes` LONGTEXT NOT NULL ,
 `Source` VARCHAR( 255 ) NULL
 ) ENGINE=MYISAM ;

To execute the script, select the writer database, click on the SQL tab, enter the script, and press Go.

Next step is to connect OpenOffice.org to the created MySQL database. Since OpenOffice.org can’t do this on its own, you need to install and configure a piece of software called a connector or driver. MySQL offers several types of drivers, and Connector/J (JDBC Driver for MySQL) is by far the easiest one to configure and use. Before you can use it, however, you have to make sure that you have Java Runtime Environment (JRE) installed on your machine and it’s added to OpenOffice.org (choose Tools → Options → OpenOffice.org → Java, and select the installed JRE). Point your browser to mysql.com/products/connector/ and download the latest version of Connector/J. Unpack the downloaded tar.gz package and move the resulting mysql-connector-java-x.x.x-bin.jar connector into your home directory. In OpenOffice.org, choose Tools → Options → OpenOffice.org → Java, press the Class Path button, then press Add Archive, select the connector, press OK to save the settings, and restart OpenOffice.org.

Figure 2: Connecting OpenOffice.org to the MySQL database

Now you are ready to connect OpenOffice.org to the MySQL database. In OpenOffice.org, choose New → Database. In the Database Wizard, select the Connect to an existing database option, select MySQL from the drop-down list, and press Next. Select the Connect using JDBC (Java Database Connectivity) option and press Next. Specify the name of the database (“writer”) and the server address (“localhost”) using the appropriate fields. To make sure that the MySQL JDBC driver works properly, press the Test Class button. Once the driver has been loaded successfully, press Next. Enter the database user name into the User name field. If the entered user name has a password, tick the Password required check box (by default, MySQL in XAMPP uses “root” as the user name and no password).

To see whether everything works as it’s supposed to, press the Test Connection button. Press the Next button, select the Yes, register the database for me option, and press Finish. Give the database a name and save it in your home directory.

Figure 3: Accessing the data source from within Writer

To access and manage the connected database (“data source” in OpenOffice.org parlance) from within Writer, create a new Writer document or open an existing one and press F4 (or choose View → Data Sources). This opens the Data Sources pane with a list of available data sources, including the newly created MySQL database. Use the + sign to expand the tree until you reach the writer.notes table. Select the table, and you can then view, create, and modify records in the Records window. There are a lot of clever things you can do with the data in the table. For example, if the database contains a text snippet that you want to insert into the current Writer document, you can simply drag it from the Notes field onto the desired place in the document. Using the Data to Text feature, you can easily insert the records into the document as a formatted table. And, of course, you can filter, sort, and search the data without ever leaving Writer.

Finally, the created database needs a web-based interface, so you can add data from any computer using just a browser. Since the XAMPP package (as well as the vast majority of web hosting providers) includes PHP, you can easily design a PHP-based front-end for the database. While you can use any text editor to create a set of PHP-based pages that act as a web interface, there is an easier and more effective way to create a front-end for WNotes. phpMyEdit is an ingenious piece of software that can generate a perfectly functional web interface in a matter of minutes – no PHP programming skills required. Although phpMyEdit hides the complexity of generating a PHP-based interface, it still offers an easy-to-use yet powerful mechanism to customize virtually any aspect of the created front-end.

Download the latest release of phpMyEdit, unpack it, and move the resulting folder into the document root of your web server (in XAMPP, it’s the htdocs directory). Point your browser to http://localhost/phpMyEdit/phpMyEditSetup.php. If phpMyEdit works properly, you should see the database connection form. Fill out the required fields and press the Submit button. phpMyEdit then displays a list of the fields in the specified database table, and you have to select a so-called identifier (a primary key in MySQL terminology). phpMyEdit then asks you to provide the name and title you want to use for the generated page. Here you can also decide whether the page should have a header/footer and use a basic CSS stylesheet by ticking the appropriate check boxes. Once you’ve pressed Submit, phpMyEdit generates the page and writes it to the phpMyEdit folder. It also conveniently displays the generated code, which you can copy and paste into a text editor for additional tweaking.

Figure 4: The front-end generated by phpMyEdit

Now point your browser to the created page, and you should have a basic front-end to your MySQL database. Using it, you can create, modify, delete, view, sort, and filter the records. But while the default interface is rather functional as it is, phpMyEdit offers a comprehensive set of settings you can tweak to make the interface suit your needs. For example, you can easily change the number of records per page. To do this, open the generated .php file in a text editor, and locate the $opts[‘inc’]=15; line, and replace the default value with the number you want. If you want to display all the records in the table on one page, set the option to -1.

By default, the created page sorts records by their identifier, but you can also change that by modifying the $opts[‘sort_field’] option. For example, if you want to sort records by the Notes field, the option will look like this:

$opts['sort_field']=array('Notes');

You can also specify more advanced sorting based on several fields. The following sorting settings sort records by both Notes and Source fields:

$opts['sort_field']=array('Notes', 'Source');

The appearance of the front-end is controlled by a simple CSS stylesheet, and you can tweak it to your heart’s content to achieve the result you want. There is also another way to modify the overall look of the interface without fiddling with CSS: phpMyEdit allows you to customize the navigation buttons using the $opts[’navigation’] option. This option defines where on the page the navigation buttons appear (U – above the table, D – below the table) and the button type to use (B – default buttons, T – text links, G – graphics links). Using these parameters, you can specify the appearance of the navigation buttons. The following settings display the graphics buttons below the table:

 $opts['navigation']='DG';

WNotes is not the most sophisticated application out there, but it demonstrates what you can achieve by combining OpenOffice.org capabilities with other proven technologies such as MySQL and PHP. Using WNotes as a starting point you can create virtually any type of web-based application and then access and manipulate data in it directly from within Writer.

Related articles: