Mike Massonnet's blog My IT life, Be open

Hello,

Since OpenOffice.org2 is out, some modifications in the menu happened which has the effect I was unable to find out an up-to-date document on how to connect on mysql. I used oobase, the new one from OpenOffice.org2, to have a view on my database, the tables and also save some requests. So if you want to know how I connected me on MySQL and used the responses from a request to draw a graph, just read my post ;-)

* The MySQL and ODBC part. *

First at all we need a MySQL server of course, ODBC and its MySQL driver. With Debian type `aptitude install mysql-server libmyodbc' where I suggest version 5 for mysql-server. On other distros search the right package names with urpmq, emerge -s, ... With Windows there is an ODBC management panel, you should Google[1] it out since I'm not going to use Windows.

I granted[2] all the rights to my user on the database "test". Just connect to mysql with user root: `mysql -u root', and type: `GRANT ALL ON test.* TO your_user@localhost;'. You can check out its privileges now by typing: `SHOW GRANTS FOR your_user@localhost;'. You will see a "grant usage", and different privileges (select, insert, ...) on the database "test". You could grant only the privilege select (GRANT SELECT) instead of all.

Now we will edit the file /etc/odbc.ini (the file $HOME/.odbc.ini is another nice location). I have no idea how the configuration files are disposed on Windows, but it should be the same on most Unix based systems, you should look the FILES section from the manual: `man odbcinst'. We put the next lines in it:

[MySQL-test]
Description = MySQL database test
Driver = MySQL
Server = localhost
Database = test
Port = 3306
Socket = /var/run/mysqld/mysqld.sock
Option =
Stmt =

Note to check the socket location.

From now on we should be able to connect on MySQL with ODBC. We can test it with the command line: `isql MySQL-test'. We will get into a shell where we can type our requests. ctrl-d to disconnect.

* The OpenOffice.org2 part. *

We will create a new database and save it as an OpenDocument format with oobase. With it we can collect the values in oocalc and draw a graphic.

Actually this part is easier then the first, since there is a wizard which ask to type values and to check options. So lets get started `oobase'.

Check the third radio button and select "MySQL" in the combo list. Next we will select the radio button "Connect by using ODBC". Next we will type our ODBC Date Source Name: MySQL-test. Next we will type our username for MySQL (in my case mike) and we can test the connection by clicking the button "Test connection". Finally we will save the document by clicking the "Finish" button which should open the main window of oobase to edit the database (create tables, select values, ...).

That's it, we can save requests and use the result in oocalc. But we need a pretty example with a table in the database so I can be happy with this post :-P

I wrote something dirty and hackish in a shell script to pump my database. First we will have to write a table:
CREATE TABLE stats (id CHAR(20) NOT NULL, date DATETIME NOT NULL, value INT NOT NULL);
And now we will put something in, and we won't do it manually! So we will write this little script:
#!/bin/sh
STAT=`curl -s 'http://kde-apps.org/content/show.php?content=31856'|grep '<b>Downloads:</b>'| cut -d ':' -f5|sed 's/[</b>&nbsp;Cometr]//g'`
if [ "$STAT" ] ; then
  mysql -e "INSERT INTO stats VALUES ('txtview', NOW(), $STAT);" test
fi
We can try this script to see if it is okay. Note that you need curl. Save this latters lines into a file (txtview-st.sh), make it executable and run it: `chmod +x txtview-st.sh && ./txtview-st.sh'. To see if it has inserted a line in the table, let's run the command line `mysql -e "SELECT * FROM stats" test'. Everything should be ok.

Before we get back to OpenOffice.org2, we will do some automation around the script, so we will always have fresh datas to handle. To do so we will add an entry into our cron table[3]. This is done by running `crontab -e':
30 * * * * /home/your_username/your_scripts/txtview-st.sh

So far so good, we have a little script which regulary fill our database. Now we are going to save a request in oobase which can be taken as is by dragging/droping it on an oocalc sheet.

In oobase, we clic on "Request" in the left pan, and select "Create a request..." in the upper pan. We type the next request in the textarea:
SELECT date,value FROM stats WHERE id LIKE 'txtview' GROUP BY value;
We can have a glimpse through the menu "View". Now we save the request, save the OpenDocument base and launch `oocalc'.

We press `F4' to have the view of the Data Sources. From this view, we select in the left browser the database which were saved with oobase. It contains two directories: "Requests" and "Tables". We are going to use our saved request, so we open the "Requests" branch, drag our saved one and drop it over a cell. This outputs the dates and values on the grid.

Finally we select all the filled cells (this should already be the case) and we click into the menu "Insert" on "Diagram...". The options should be good, next we will select an "XY Diagram", next it is your choice to personalize the style of the diagram and to set the last options. We can now create the diagram and place it somewhere near the values. To refresh the values, just drag and drop the request again over a cell and don't forget to extend the data range (it is accessible in the context menu of the diagram).

We are done ! I think this can be a nice start point for some mysqlishing in OOo2.
Thank you for reading til here :-)





[1] http://www.google.com/search?q=windows+odbc+mysql
[2.1] Grant rights on accounts (version 5): http://dev.mysql.com/doc/refman/5.0/en/grant.html
[2.2] Version 4.1 http://dev.mysql.com/doc/refman/4.1/en/grant.html
[3] cron is our scheduler and there should be something equivalent under non-unix systems