Automating OpenOffice.org - Part 3

From LXF Wiki

AUTOMATING OPENOFFICE.ORG BASIC SCRIPTING SERIES

Part 1: OOo Basic: Use macros in Writer
Part 2: OOo Basic: Use macros in Calc
Part 3: OOo Basic: Query databases

(Original version written by Mark Bain for Linux Format magazine issue 82.)

Table of contents

OOo Basic: Query databases

PART 3 More time-saving cleverness with Mark Bain – his macros and database tips will help you run queries, create reports in an OpenOffice.org application and keep track of your books and CDs.

I really love OpenOffice.org. Especially when I use it with OOo Basic. Not so much because it gets me out of the clutches of ProprietarySoft Inc, but more because it's really, really good. We've already seen in this series how easy it is to manipulate text documents and spreadsheets with OOo Basic, so let's do more of the same—but this time extract information from databases.

The aim here is to be as lazy as possible. Imagine, for instance, that you're just about to prepare an invoice for a very popular Linux magazine that you write for. Why spend time retyping stuff that you've already got stored? This tutorial will give you the tools to stop up many more time-leaking jobs—and enjoy exploring Unix at the same time, naturally.

Ingredient #1: a database server

First things first. You'll need a database—after all, this is all about using macros to get information out of one. However, I'm not going to go into setting up a database—that's outside the scope of the tutorial. Of course, if you were to say that you hadn't got a database and didn't even know where to start, I'd say, "Don't panic, this is going to be easy." I'd then suggest that you wanted a database server. You could use just any old PC that you've got, connect it to your network and then install Debian (if you didn't have a second PC, you could run the server on your own machine). You might create yourself a minimal boot installation CD from http://www.debian.org, stick it in your CD drive, reboot and follow the instructions. You wouldn't bother installing any of the extras (desktop, file server, web server and so on) as you'd just want a bare bones setup.

Now I would tell you to turn this into a database server by typing apt-get install mysql-server, and then to edit the /etc/mysql/my.cnf file to hash out the line 'bind-address = 127.0.0.1' (so that it looked like '#bind-address = 127.0.0.1'). This would allow you to access the server from any other PC on your network.

As you'd want a database and a user to access it, I'd probably tell you to do the following:

mysql -uroot mysql  
set password for 'root'@'localhost' = password('put your own password here');   
create database accounts;  
grant all privileges on accounts.* to 'your user'@'%'  
identified by 'your user password';  
exit;     

Finally, I would suggest you gave your new server a static IP address by editing the /etc/network/interfaces file so that the end of it looked something like

#iface eth0 inet dhcp  
iface eth0 inet static     
      address 192.168.1.3         
      netmask 255.255.255.0         
      gateway 192.168.1.1     
      

At that point, I'd probably say, you could reboot and log on to the PC on which you'd be running OpenOffice.org.

But since this tutorial is all about using OOo Basic and not creating databases, I won't be doing any of that.

Accessing your database

Don't fire up OpenOffice.org just yet. To make life even easier for ourselves (which is what macros are all about) we'll be using UnixODBC, an API for accessing data sources. This will save us the nitty-gritty of making connections to server and databases—the protocols, signals sent, blah, blah, blah. Instead, the hardest thing that you'll have to do is install UnixODBC and its MySQL libraries on to the machine where you're going to be using OOo. On Debian this is as easy as

apt-get install unixodbc  
apt-get install libmyodbc     

Obviously, if you're using another distro you'll have to check the process for that—have a look at the UnixODBC website at http://www.unixODBC.org. However you get UnixODBC installed, you'll just need to finish off by doing two things. First, edit /etc/hosts so that it includes a reference to your database server, eg 192.168.1.3 acamas. Then edit /etc/odbc.ini to include something like:

[accounts]  
Description    = MySQL db test  
Driver         = MySQL  
Server         = acamas  
Database       = accounts  
Port           = 3306     

Now take a deep breath, count to five, slowly exhale, and we're done—no more command-line stuff.

Sort out your database

Open up OpenOffice.org. It doesn't matter which type of document, but let's say a Writer document for now. In Tools, you'll see that one of the sub-menus says Data Sources. Click on that and you'll notice the Data Source Administration form.

The easy bit: click on New Data Source and set the database type to MySQL in the General tab. Then go to the MySQL tab, add the database name to Data Source URL and put in your username (remember to create the empty database and a user before trying to access it from OOo). Next click on the Tables tab. There won't be anything there (because you haven't created any tables yet). Guess what we're going to do now? That's right—we're down the pub and it's your round. No? OK, save that for later—in the meantime it's time to create the data.

If you're a command-line freak (and I must admit I am), go back to your database server, log on to the database and create the tables. Don't forget that you can log on directly from your current server.

bainm@hector:~/ooobasic3$ mysql -hacamas -ubainm -pmypassword accounts  
mysql > create database accounts;  
create table accounts.customer (id int auto_increment,   
surname varchar(50), firstname varchar(50),  
address1 varchar(50), address2 varchar(50), city varchar(50), county varchar(50),  
country varchar(50), postcode varchar(50),primary key (id));  
create table accounts.invoice (id int auto_increment,customer_id int,  
sent_date date,paid_date date,primary key (id));  
create table accounts.item (id int auto_increment,customer_id int,  
invoice_id int,title varchar(50),details varchar(255),value double,  
primary key (id));  
insert into accounts.customer  
(surname,firstname,address1, address2,city,county,country,postcode) values
('Smith','John','The Big House','1 The Street','Thistown','Thisshire','UK','TH1 1HT');
insert into accounts.customer  
(surname,firstname,address1, address2,city,county,country,postcode) values
('Jones','Mary','Building A','Industrial Est.', Hereton','Herehire','UK','HE1 1EH');  
insert into accounts.item (customer_id,title,value) values (1,'A  fine piece of work',500);  
insert into accounts.item (customer_id,title,value) values (1,'A great job',500);  
insert into accounts.item (customer_id,title,value) values (2,'Day 1',1500);  
insert into accounts.item (customer_id,title,value) values (2,'Day 2',1600);      

If that doesn't appeal, just use the Data Source Administration form, go to the Tables tab and click on the New Table Design button. You can use the Table Design form to easily create any tables that you need.

Using your new tables

We've spent quite a bit of time on the database side of things—simply because that has to be right before you start. Everything else will just fall into place. Now we can take a look at our first database-using macro. If you followed last month's installment you'll be used to the OOo CreateUnoService function (you have been practising, haven't you?). We're going to be using it again here, this time to get access to OOo's data RowSet. This is an OOo name for the record set, and it allows you to run queries on the database and retrieve information from it.

RowSet = createUnoService("com.sun.star.sdb.RowSet")      

Now, all you have to do is to tell the RowSet about the database that you want to connect to (ie the one you set up in Data Source Administration), give it your username, password and the query that you want to run. The RowSet then obtains the result of the query and makes it available for you to use. Therefore you probably want to do something like

sub main    
  sql1   
end sub   
Sub sql1    
  Dim RowSet      
  RowSet = createUnoService("com.sun.star.sdb.RowSet")      
  RowSet.DataSourceName = "Accounts"      
  RowSet.User="bainm"      
  RowSet.Password = "password"      
  RowSet.Command = "SELECT count(*) c FROM item"      
  RowSet.execute()      
  RowSet.next()      
  MsgBox "There are " + rowSet.getString(1) + " items"   
End Sub      

This is fine, but have a look at the next example:

Dim RowSet   
Sub Main    
     connectToDatabase ("Accounts", "bainm", "kawasaki")           
     sql1   
End Sub   
Sub connectToDatabase(database as string, username as string, password as string)
  RowSet = createUnoService("com.sun.star.sdb.RowSet")      
  RowSet.DataSourceName = database      
  RowSet.User = username      
  RowSet.Password = password   
End Sub   
Sub updateRowSet(sql as string)      
  RowSet.Command = sql      
  RowSet.execute()    
End Sub   
Sub sql1   
  updateRowSet("SELECT count(*) c FROM item")    
  RowSet.next()     
  MsgBox "There are " + rowSet.getString(1) + " items"   
End Sub  

With the second example, it becomes very easy to extend the functionality of the macro. Take a look:

Sub sql2      
         updateRowSet("SELECT id, surname, firstname FROM customer")                
         while RowSet.Next()           
                  MsgBox "Customer No. " + rowSet.getString(1) + " " + rowSet.getString(2) + _                           
                  " " + rowSet.getString(3)                
         wend   
End Sub

Writer reports

So far we've seen just how easy it is to access a database from a macro and to display the results. But we haven't really seen anything that you couldn't do as easily directly from the command line. If you cast your mind back to Automating OpenOffice.org - Part 1 I hope you'll recall that we were writing directly to OOo Writer documents. That seems the sensible thing to do now, using information from our database.

The great thing is that we can start doing very impressive things with very little new code. We've already got the loadNewFile subroutine (we set off with that in Automating OOo - Part 1 and modified it in Part 2) for creating a new Writer document, and we have the add_paragraph subroutine for writing to the document. All we have to do is add simple subroutines to create reports from the information in the database. Here's a simple way to create a document containing a list of all of the customers in the 'Accounts' database:

Dim RowSet  
Sub Main      
         connectToDatabase ("Accounts", "bainm", "kawasaki")              
         loadNewFile              
         createCustomerReport  
End Sub  
Sub createCustomerReport     
         updateRowSet("SELECT id, surname, firstname  FROM customer")              
         while RowSet.Next()                
                  add_paragraph("Customer No. " + _                           
                  rowSet.getString(1) + " " + rowSet.  getString(2) + " " + rowSet.getString(3))              
         wend  
End Sub     

Really, that's all there is to it. The process is as simple as that: send your query to the database, then display the result in a document. End of story. Well, not quite. As we identified in Automating OOo - Part 1 you don't really want to have to change the Main subroutine every time you want to run a new report unless you're a masochist. Again, the key thing here is to build yourself a dialog box to control the jobs that need to be done.

This time you won't hard code the contents of elements such as list boxes. No, this time you'll load them directly from the database. Let's say that you've added a list box and called it lstCustomers in a dialog called dlgAccounts. What to load it with? You're there ahead of me: we can just send a query to the database requesting a list of customers:

          updateRowSet("SELECT surname, firstname FROM  customer")     
          

Then you can loop through the record set, loading the list box with info as you go:

          lstCustomers.AddItem(rowSet.getString(2) + " " +  rowSet.getString(1), i) 
          

You can use this new list box as the filter for any reports that you wish to great. For example, if you wanted to see all of the items bought by a particular customer you would use the selectedItem property of the list box to obtain the text that's been selected. You could then use that to build an SQL statement, like this:

sql = " select title,value from customer, item " + _      
           " where cutomer.id = item.customer_id " + _              
           " and concat(customer.firstname,concat(' ',customer.  surname)) = '" + _              
           lstCustomers.selectedItem +"'"     
           

Even better—build the SQL into a function. Why? This way you can use the query in any of the subroutines that you write without having to rewrite any code. Now you can add a button to the dialog box, associate a subroutine to the button and start making use of this. For a start, get the subroutine to output a message box so that you can see the SQL statement that you've built. When you're happy with that, use the SQL to load a new record set, then write this all to a Writer document.

I'm sure that you can see just how easy this all is (and that's the key thing to remember—this is easy), and that automating the extraction from a database into an OOo Writer document is pretty simple. It probably won't surprise you to learn that it's just as easy with the spreadsheet program Calc. The interactions with the database are just the same. The only difference is that you have to write to individual cells rather than paragraphs—if anything this gives you even more flexibility in the way that you can lay out your information.

So I'll leave it to you to work out what to do now—we've discussed all that you need in this and the first two parts of Automating OpenOffice.org.

A media library

To finish, we'll just look at a simple application—one in which you can store and view a library of all of your CDs, DVDs, LPs or books.

Start by creating the tables in your database. You'll have to ask yourself a question: do I put all of my tables in my original database or do I create a new database for each project that I'm working on? I'd recommend the latter—you'll find it much easier to manage all of your information this way. However, if you do choose this method don't forget to add a reference for your new database to your /etc/odbc.ini file, then add it as a new data source into OpenOffice.org. You'll also need to instruct your macro to use the new database by changing connectToDatabase ("Accounts", "bainm", "kawasaki") to connectToDatabase ("library", "bainm", "kawasaki").

Next thing: don't be tempted to try to shove everything into a single table—you're just asking for problems if you do. What kind of problems? Well, let's look at a simple example—a field containing a name. You know that 'Bill Gates', 'William Gates', 'B Gates', and 'Evil Overlord of Darkness' all refer to the same person, but your computer doesn't. This can make querying the data very difficult. Look at this table:

Table: item

Title Author
Cat's Cradle Kurt Vonnegut
Slaughterhouse 5 K Vonnegut

Instead of this, you could use two tables—one with the item details, the second with the Author details:

Table: item

Title Author ID
Bagombo Snuff Box 1
The Sirens of Titan 1

Table: author

ID Name
1 Kurt Vonnegut Jr.

This way, instead of having to remember every possible spelling of the author's name, all you need is the author's ID number. Similarly, you don't want to store the words 'cd' or 'lp' or 'book' in the table containing the title. Instead use something like:

Table: item

Title Media ID
Mind Bomb 2
Zen and the Art of Motorcycle Maintenance 1

Table: media

ID Type
1 Book
2 CD

Now, with a little SQL you can get useful information out of the database:

select item.title, author.name, media.type
from item, author, media
where item.author_id = author.id
and item.media_id = media.id;

Next use this SQL in a subroutine to fill a spreadsheet with the results from the query—look at showFullLibrary on the coverdisc to see just how this works (you'll also find the SQL to create your new database as well as an example /etc/odbc.ini). If you examine the code you'll find that the macro does not include hard-coded column numbers when writing to the spreadsheet; rather, the RowSet.Columns.Count property is used to create a loop. So what? Well, this means that it doesn't matter if you change the number of records obtained from a query—the macro will automatically insert the correct number of columns into the spreadsheet.

Filtering data

"But I don't want to see everything in the database!" I hear you cry, "I want to be able to see only CDs or only books, or just a single artist's work." Easily done—if you create a new form you can add list boxes and populate them from the author and media tables (just like we've already done in the accounting example). These list boxes can now be used build filters for the query. On the coverdisc, showFilteredLibrary shows you how to use optional inputs to build such a filter and then to display the results in the spreadsheet.

To add new items, authors or media types to the database you need an insert statement, such as

insert into library.author (name) values ('Hawkwind');   
insert into library.item (title,author_id,media_id) values ('The Ambient Anarchists',4,1);      

This can easily be done on the command line, but with the knowledge that you've picked up you can build forms to do the job for you.