Locations of visitors to this page Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Saturday, December 09, 2006

MySQL stored routines and the command line client: No comment...Or Maybe..?!

A popular myth holds that the MySQL Database Server strips the comments from stored procedures, functions, triggers, views and events. This is not true. Or at least, it is only somewhat true.

On an earlier occasion, I have written about MySQL views and how they lose all their comments and whitespace formatting. The article also describes a workaround for it. (Interestingly, a few of the bugs I ran into at the time are now resolved, which means you can do without the kludges I had to turn to in order to get it to work.)

Apart from views, there really is no limitation in the capabilities of the MySQL Server to keep the comments inside code associated with stored routines. (With stored routines, I mean: stored procedures, functions, triggers and events).

There's a really plain and simple explanation for the endurance of the "comment-stripping-myth". The myth remains alive because a lot (if not most) people exclusively use the mysql command line client command line tool for their MySQL work. The mysql interprets the user input, and strips the comments before it sends the command to the server. So any comments never even arrive at the server side.

MySQL Command Line Client Interpreter


The mysql command line client tool implements a simple state-machine to discover when the user input should be sent to the server. For example, the ; you normally type after a statement, and the delimiter command you use in order to create a stored procedure using the command line client: those are all elements that don't really belong to the SQL language itself. They exist solely for the benefit of the mysql command line client tool, because it has to have some way of knowing when the user is done typing whatever is to be sent to the server.

The mysql command line interpreter does not limit itself to interpreting the statement delimiter. It also recognizes that the user input initiates a comment. Let's take a closer look at that behaviour:

mysql>

This is what the mysql prompt normally looks like. When it looks like this, it is ready to accept input. Let's type some input, and then skip to the new line:

mysql> select version()
->

As you can see, the client tool notices that we started to type some input. The prompt changes from mysql> to ->, notifying the user that all the subsequent input will be seen as belonging to the current statement. When we now type the default statement delimiter ;, the command line tool will send the gathered input to the server, wait to receive the result and output that to the user:

mysql> select version()
-> ;
+-----------------+
| version() |
+-----------------+
| 5.1.15-beta-log |
+-----------------+
1 row in set (0.00 sec)

mysql>

It also displays a new fresh prompt, ready to receive new input.

So what happens when we initiate a comment? Well, the mysql command line client notices that too. Single line comments are initiated by a # character or the -- (dash-dash-space) sequence, which will effectively stop interpreting the subsequent input until a newline character is seen. This can be demonstrated by typing the statement delimiter right after the comment is initiated, and then skipping to the next line:

mysql> select version() # single line comment;
-> -- dash dash space;
-> ;

As you can see, the ; does not delimit the statement if it appears on the same line after the # or -- single line comment initiators. The statement delimiter is not the only character that ceases to be given a special meaning: all input appearing on the line after the comment was initiated will not be interpreted.

Comments can also span multiple lines using the C-style comment syntax:

mysql> select /*
/*> comments;
/*> */ version();

So, the /* sequence initiates a comment, and all subsequent input, including new lines, will not be interpreted as having special meaning. That is, until the next */ sequence is encountered, delimiting the comment. You can see that the command client tool knows we're inside the multi-line comment, because the prompt changes accordingly: the second and third line have the /*> prompt rather than the usual -> prompt.

Comments are stripped before sending the command to the server


We've just seen that the mysql command line client interprets the user input until it sees a statement delimiter. When it sees one, it treats the gathered input as one command, and sends it to the server. So far, nothing strange is happening. To observe the effect of comments stripping, we need to create a little stored procedure.

Stored procedures, functions, triggers and events are also created with a single statement. However, these types of statements differ from the single SELECT statements we just observed, because these statements can themselves contain a statement sequence. So, if the mysql command line client is to be used to create them, there must be a mechanism to distinguish the delimiter that is used to separate the statements inside the procedure from the delimiter that is used to mark the end of the outer statement so it can be sent to the server.

This is achieved using a special mysql command line client command, the delimiter command. The delimiter command is used to define a word or a delimiting sequence as the statement delimiter. Let's use the delimiter command:

mysql> delimiter $$

This command tells the mysql command line client command to use the $$ sequence as a marker to gather the user input and send it to the server. It's not sent to the server - it only serves to modify the behaviour of the local command interpreter. The default statement delimiter ; may still appear at the end of a statement, but it will simply be ignored:

mysql> select version()
-> ;
-> $$
+-----------------+
| version() |
+-----------------+
| 5.1.15-beta-log |
+-----------------+
1 row in set (0.00 sec)

mysql>

Now, we can create a stored routine that contains a statement sequence:

mysql> CREATE PROCEDURE p()
-> BEGIN
-> /******************
/*> * A simple procedure
/*> *******************/
-> SELECT version();
-> SELECT schema();
-> END;
-> $$
Query OK, 0 rows affected (0.00 sec)

The actual statement sequence is contained in the BEGIN..END statement. The BEGIN..END statement is therefore also a compound statement.

The statement list itself is made up of only two SELECT statements. Each of these needs to be properly delimited using the ; statement delimiter. Here, the ; symbol is fixed, and cannot be changed. In this context, the ; is interpreted by the MySQL Server. Inside the BEGIN..END block is also a multi line comment. Technically, comments are not statements, however we did put it inside the block with the intention of keeping it there.

In order to be able to send the CREATE PROCEDURE statement as a whole to the server, the mysql command line client needs to stop interpreting the ; symbol as a statement delimiter. That's why we had to redefine the mysql command line client statement delimiter to something other than ;. In this example, the $$ was chosen. As soon as the mysql command line client sees the $$ sequence, it will gather the user input, and send it to the server as one complete statement, which tells the server to create the procedure.

To change the mysql command line client statement delimiter back to the original, simply use the DELIMITER command again:

mysql> delimiter ;

We can use the SHOW CREATE PROCEDURE statement or the ROUTINES view in the information_schema to see what the procedure looks like according to the server:

mysql> SELECT routine_definition
-> FROM information_schema.routines
-> WHERE routine_schema = schema()
-> AND routine_name = 'p'
-> ;
+-------------------------------------------------------+
| routine_definition |
+-------------------------------------------------------+
| BEGIN

SELECT version();
SELECT schema();
END |
+-------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

This result clearly shows that the comment is now gone, although we took no explicit action to remove the comment. So where did it go? How do we know for sure it is due to the mysql client tool that the comment is gone?

The simplest way to prove it, is to use the either the MySQL Query Browser or the MySQL Administrator GUI Tools. For example, using the MySQL Administrator, you can find the procedures by clicking the "Catalogs" item in the top sidebar, and then selecting the database that contains the procedure in the bottom sidebar. The procedures are all available in the "Stored Procedures" tab:
MySQLAdministratorStoredProcedures
There, you can select the procedure. Hitting the "Edit" putting pops up a small editor:
MySQLAdministratorEditStoredRoutine
By pasting the original code there, and then pushing the "Execute" button, we recreate the procedure. If we now use the mysql command line client again to see what the procedure looks like, we can see that the comment is exactly were we put it originally:

mysql> SELECT routine_definition
-> FROM information_schema.routines
-> WHERE routine_schema = schema()
-> AND routine_name = 'p'
-> ;
+---------------------+
| routine_definition |
+---------------------+
| BEGIN
/******************
* A simple procedure
*******************/
SELECT version();
SELECT schema();
END |
+---------------------+
1 row in set (0.00 sec)

You might argue that this solves the problem: if we simply switch and use these GUI tools, we will never have this problem again. Well, that's true, and maybe that works for you. However, in a lot of cases, I like the mysql command line tool better for a number of reasons (not discussed here).

Why I like code comments stored in the database


I like to write comments inside stored procedures. I use them to document the algorithm as well as it's usage. I picked up the habit when I worked as application developer writing tons of Oracle PL/SQL. Parts of the PL/SQL code of Oracle's own builtin packages (package headers, sometimes package bodies) can be queried using the data dictionary, and usually the comments inside the PL/SQL source code are enough to figure out how to use the builtin package.

Now I know a lot of people will say: "Nah, the database was not meant for all that, you should write proper documentation!" or "Why don't you use a version control system for that." Of course, I agree that proper documentation should be written, and I agree that version control systems should be used. However, the comments inside the stored procedure code serve another purpose, and I think it's a pity it's discarded by the mysql command line client tool.

  • Documentation is usually not written at the level of the implementation. Hacks, work-arounds and tricks used to implement a certain algorithm usually don't make it to the documentation. However, it is important to record their rationale, and in my opinion, there is no better place than the code itself.
  • A version control system should be in place in the development environment. However, when an application is distributed, the actual development code (including the comments) might not be readily available when it's most needed. Keeping the code and comments together inside the database server can be a life-saver if somebody suddenly runs into problems, because it enhances the possibilities to modify the code.
  • What about Licensing? Suppose I want to include, say a GPL license note with the code? I'd rather keep it as close to my stored procedure as possible.

Ok, have it your way, and try without comments


To illustrate the value of retaining the comments, consider the following function:

CREATE FUNCTION f_check_ccno(
p_ccno BIGINT
)
RETURNS TINYINT
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
BEGIN
DECLARE v_ccno VARCHAR(20)
DEFAULT reverse(
cast(p_ccno AS CHAR(20))
);
DECLARE v_len_ccno TINYINT
DEFAULT length(v_ccno);
DECLARE v_i TINYINT
DEFAULT 1;
DECLARE v_num TINYINT
DEFAULT 1;
DECLARE v_check BIGINT
DEFAULT 0;
WHILE v_i <= v_len_ccno DO
SET v_num := cast(
substr(v_ccno,v_i,1)
AS SIGNED
);
IF v_i%2=0 THEN
SET v_num := 2*v_num;
IF v_num > 9 THEN
SET v_num := v_num - 9;
END IF;
END IF;
SET v_check := v_check + v_num;
SET v_i := v_i + 1;
END WHILE;
RETURN (v_check%10)=0;
END;

So, what kind of function is this? Well, the function is called f_check_ccno, and it accepts a p_ccno parameter that is of the BIGINT type. Supposedly, ccno means something, assuming that f_ and p_ are prefixes for "function" and "parameter" respectively. The function returns a TINYINT which we assume encodes the validation result in some way.

It becomes more difficult if we want to find out what kind of validation we're dealing with. I invite you to try it: just peel it off, statement by statement and expression by expression Just guess what this thing is supposed to do.

Most people with some programming experience will have only little difficulties if at all to find out what the function does. That's hardly a suprise: the MySQL Stored Procedure language is a structured higher programming language, and it consists mostly of English keywords. However, the real problem with software program source code is to find out what it means or what was intended. Programming languages have no construct to code for purpose. All speculation on the meaning of this function so far has been derived from the identifiers. We just assume that the names have something to do with the role they play in the code.

(People: try and guess this. Post a comment here and describe what you think this function does. It is actually a useful function - I didn't just make it up. And, oh yeah...I put in a little snag - a bug. Can you find it? In one or two weeks, I'll post it as a snippet at MySQL Forge, with comments and a proper description.)

Now why do we even care? Suppose we inherit someones database and we happen to bump into this function. Of course, we assume the function is there for a reason, and it's might be used by another piece of code, say, a view, or a trigger, or maybe even a query issued by the application. But can't we just treat this function as a black box, and trust it to do whatever it's supposed to do?

Well, sure we can! And if everything is working fine, there's no reason to change anything. Of course we're just ignoring that some day, things might not work fine anymore. Bugs may suddenly occur, or we may find that the function does not scale well. Another likely scenario is a change of business, requiring that we re-evaluate or change the business rules and the software that enforces it. So one way or the other, we're going to have to deal with this function. In the worst case, we mightfind ourselves rebuilding part of the system, gathering and analyzing business requirements again. Once we're done, we might find that we've ended up with pretty much the same function we started with, the only difference being that we re-invented it ourselves.

Of course, you might get pretty fed up with the job by the time that's finished. You'll find a new opportunity and find a new occupation, leaving your successor with a new function called f_check_cc_number() that accepts a BIGINT UNSIGNED parameter called p_cc_number, and that returns a ENUM('PASS','FAIL') value. Needless to say, this is a vast improvement over the original, and has a really nifty and superior implementation. Of course, it still won't have comments because the mysql command line client has stripped them out again...

Yup. I guess you know where this is heading.

Great. Now what do we do?


Well, there are several options:

  • Switch to the GUI tools

  • Modify the mysql command line client

  • Use a little trick...


I already discussed the first option more or less. If it works for you, great. I like the GUI Tools too, but for a lot of work I just feel more comfortable with the mysql command line client.

The second option is something I'm seriously considering. It would be great to be able to turn the comment stripping on using a configuration option. The default behaviour would not be changed, and people that want to turn comment stripping off have a clean way of doing so. I explored the source code a little bit, and although I'm not a C/C++ programmer, I should be able to pull this off.

Of course, everybody can do this, and contribute the code. If you want to get started, get the source code, and hack away. To get you started, there is a very simple hack that will make mysql preserve at least the single line comments.

To do so, open the {mysql-bk}/client/mysql.cc source file and goto line 1108. There you will find something like this:

if (!in_string && (line[0] == '#' ||
(line[0] == '-' && line[1] == '-') ||
line[0] == 0))
continue; // Skip comment lines

Removing these lines will make mysql preserve all single line comments that start a line. At line 1330 a similar snippet strips the single line comments that are start within a line:

else if (!*ml_comment && (!*in_string && (inchar == '#' ||
inchar == '-' && pos[1] == '-' &&
my_isspace(charset_info,pos[2]))))
break; // comment to end of line

I tried it, and this seems to work just fine. Of course, making the patch really worth while will, put mildly, probably take a bit more overview of the source than I have now. For example, what keeps nagging me is the question "Why on earth are the comments stripped in the first place?" Anyway.

Finally: A little trick....


There is a dirty little trick that might just work fine for the time being. If you read the manual section that describes the comment syntax you might notice that there are two different forms of the multi-line comment. We already discussed the plain "C-style" comments. There is another syntax that is meant as a compatibility syntax rather than a comment syntax. It goes like this:

/*! MySQL-specific code */

The purpose of these "comments" is to be able to write portable queries. The "comment" is not really a comment; rather, it's a real command that might not be understood by all servers. The manual uses this example:

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

So, a MySQL server would understand the STRAIGHT JOIN modifier, and execute the statement accordingly, whereas other servers won't recognize this as anything else but a comment, and ignore it entirely. A special form of this syntax is frequently used in the output of mysqldump to create database dumps that are portable between MySQL versions. Here's an example from the script for the world database:

/*!40101 SET NAMES latin1 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

So, you see, a n.nn.nn scheme is used to target only a specific version of the MySQL Server. I tried a little bit, and I found out that this does the trick pretty well:

/*!999999
*
*/

So, note that I'm using six digits rather than five. Of course, it's ugly, but it works. And yes, I might have a problem as soon as MySQL 99.99.99 hits the shelves, but maybe that is just enough time for me to learn enough C/C++ to come up with a satisfactory patch for the mysql command line client. :)

Sunday, December 03, 2006

MySQL meets OpenOffice.org

A lot of MySQL software development articles focus on the server-side. This is true even end-user applications: most MySQL applications I read about, are web applications.

I decided it would be fun to write down a few simple tips on how to use MySQL in Office applications. Yup, you heard me: using MySQL as "Desktop Database", quite like MS Access is being used on many, many windows desktops.

Of course, I do not want to suggest that MySQL "...is a Desktop Database", because it's not. Rather, MySQL is a general-purpose database, so there's no reason at all to dismiss it for typical "Desktop Database" purposes.

Even though MySQL is a server product, MySQL resembles a typical Desktop Database in a number of ways. For example, MySQL is not extravagantly resource-hungry, and does not require much maintenance - (not for this purpose anyway). So, there's not much against using MySQL here.

In fact, it's probably a good idea to back your Desktop Database applications with MySQL. A lot of the typical problems (resource contention due to locking, file corruption due to crashing application programs, scale limits, and much much more) that you encounter as these applications grow larger, are either not-existent or easily solved using a server solution such as MySQL.

So - let's dive in, and see what it takes to use MySQL in the daily office. Oh yeah - I once did a write-up on connecting MS Access and MySQL. This time, we won't be using MS Office at all. We'll use OpenOffice.org instead.

Some Prerequisites


All products described here are available for most popular architectures and operating system. Both MS Windows and Linux users should be able to get this to work.

The client computer should be able to directly access a local or remote MySQL Server instance. You need to know the hostname (or IP address) of the MySQL Server host. If the MySQL Server is configured to use a non-default port, you must know the portnumber too. You also need the name of the database you want to access. Finally, you also need to know the user name and password of an existing user that has privileges to acces the database.

I'll be using the sakila sample database in the following examples. It's not absolutely necessary to install it if you already have another database to play with. If you want it, you can download it here.

Client Software


Before you start, a few things need to be in place on the client computer. It may already have these items, or you might have to download and install them:

  • A Java runtime environment (jre) or Java Development Kit (JDK, if you also want to develop Java programs). In most cases, your operating system ships with some pre-installed implementation. However, I found out that a pre-installed implementation might not be exactly what you need. Personally, I'm quite happy with Sun's own implementation.

  • The OpenOffice.org suite. You can download it here or look for a package tailored for your operating system yourself.

  • The MySQL JDBC driver, Connector/J. You can download it here.

I won't discuss the installation procedure for OpenOffice.org or the Java runtime environment. As much as I'd like to, there's just too many variety to elaborate on this in a useful way. However, I trust few if any problems will arise obtaintaining and installing these items.

As for the MySQL JDBC driver: there's not much to install. Just unpack the download in a location you're comfortable with. You will find a .jar (Java archive) in the unpacked directory, and you will need the full path and filenename of the archive.

I like to keep several versions around of JDBC drivers, and to make life easier, I created a symbolic link named mysql-connector-java.jar to the latest version of the .jar file. That way, I can easily upgrade or downgrade as needed by flipping the symbolic link to another location. You don't need to do it this way - the symbolic link is purely for my convenience.

(So what do you do with your JDBC drivers? Where do you store them? How do you upgrade?)

Configure OpenOffice.org to run Java

First, you need to enable OpenOffice.org to use Java. To do that, follow these instructions:

  1. Open one of the OpenOffice.org applications, such as Calc or Writer.

  2. Use Menu \ Tools \ Options to configure OpenOffice.org
    Enable-OpenOffice.org-java

  3. In the Options dialog, find the "Java" Category. Check the "Use a Java runtime environment" checkbox. You might see a list there of the Java runtime environments available on you system. It seems to automatically pick up all the Java runtime environments I installed in /usr/lib/jvm but I don't dare say it will be the same on your system. However, if you don't see any Java runtime environments there, and you are sure you installed one, you can use the "Add" button and browse to the corresponding directory:

    OpenOffice Preferences
    Choose your preferred Java Runtime environment by checking the radiobutton.

By completing these steps, OpenOffice.org is able to run Java applets and programs. This is a prerequisite to enable the OpenOffice.org Base application to connect to MySQL.

(Note that if you add or change a Java Runtime environment, you will be prompted to restart open office:

Restart-OpenOffice.org
Don't restart just now - we still need to configure OpenOffice.org to be aware of the MySQL JDBC driver.

Configure OpenOffice.org to use MySQL Connector/J


Although OpenOffice.org is now able to run Java, it is still unaware of the MySQL JDBC driver. Follow these instructions to enable OpenOffice.org to use the MySQL JDBC driver:

  1. Open the OpenOffice.org options dialog, and select the "Java" category as described in the previous section. If you followed the instructions in the previous section, the dialog should still be open.

  2. Use the "Class Path..." button to open the Class Path dialog.

  3. In the Class Path dialog, add a reference to the MySQL Connector/J .jar file using the "Add Archive..." button:

    Add-ConnectorJ-to-Oo-Classpath

  4. Close the OpenOffice.org options dialog, close the application if you are prompted to restart.


Ok, now we're all set. Everything discussed so far was just one-time preparation, and you won't have to go through it again (except perhaps after upgrading java or OpenOffice.org).

Registering your database with OpenOffice.org Base


OpenOffice.org 2 includes Base, a MS Access like "Desktop Database" application. OpenOffice.org Base acts as a front end for a variety of datasources commonly found in office environments. Typical examples include text documents, spreadsheets, and LDAP addressbooks.

Base can also be used to design and develop simple relational database applications using the HSQLDB database embedded in OpenOffice.org. Base also provides the means to connect to remote rdbms servers through an ODBC or JDBC connection. Right now, we will explore this last usage of Base.

Follow these instructions to register your MySQL database in OpenOffice.org Base:

  1. Start OpenOffice.org Base to enter the "Database Wizard".
    Oo-Database Wizard-connect-to-existing-MySQL
    Check the "Connect to an existing database" radiobutton, and choose "MySQL" from the dropdown listbox. Click the "Next" button.
  2. In the "Set up a MySQL Connection", select the "Connect using JDBC" radiobutton:
    Oo-Database Wizard-choose-method-jdbc
    As you can see, OpenOffice.org Base also supports ODBC connectivity (not discussed in this article), which might be more natural to Windows users. Click the "Next" button.

  3. A dialog appeart to specify the connection details for the MySQL server to which you want to connect:
    Oo-Database Wizard-before-setup-connection
    The name of the MySQL JDBC driver class and the port number are already provided by the wizard.

  4. Before filling out the remaining details, we should test if we set up the MySQL JDBC driver properly. Hit the "Test class" button to do that. If the driver can be loaded, you should see the following messagebox:
    Oo-Database Wizard-test driver
    If we are convinced that the driver can be loaded, we can fill out the remaining details to establish a connection. Confirm the messagebox and return to the Database Wizard dialog.

  5. In the "Name of the database" field, type the name of the schema (database) you want use. It is required to specify a database: you cannot access other databases that might be managed by the Server simultaneously from one "Base" database file.

    In the "Server URL" field, type the hostname or IP address of the MySQL Server host. You can use the "port number" field to specify a non-default port if necessary, but if you did not configure the MySQL Server to use another port than the default one, it should remain 3306.
    Oo-Database Wizard-Setup-MySQL-jdbc-Connection
    Hit the "Next" button

  6. Type the name of a user that can access the database. Check the "Password required" checkbox of the user is required to identify itself with a password.
    Oo-Database Wizard-setup-user

  7. Hit the "Test connection" button to verify that the connection can be established.
    Oo-Database Wizard-Test-Jdbc-Connection
    Close the message box to return to the Database Wizard.

  8. Hit the "next" button to finish off:
    Oo-Database Wizard-save
    Select the radiobutton in order to register the dabase file in OpenOffice.org. That enables you to access this database in all you OpenOffice.org documents. Check the checkbox to continue working with the database.

  9. Finally, we can hit the "Finish" button. You are prompted for a name and a location to save the "Base" database file.
    Oo-Database Wizard-Save


If you followed all the instructions, the "Base" main screen will open, and you'll see something like this:
Oo-Base-Main

On the left side, we have a sidebar that shows the four different kinds of things you kan work with in "Base":

  • Tables - here, you'll find the base tables and views defined in your database.

  • Queries - here, you can define database queries, and save them for repeated use.

  • Forms - you can use forms to create simple database applications.

  • Reports allow you to layout data for presentation purposes


The right, main area of the window shows a task pane (top) and a content pane (bottom). When you select an item in the sidebar, the task pane shows all the tasks you can perform with these types of objects, and the content pane shows all instances available for this type of object.
Great! Now what?

Working with Tables


One of the great strengths of "Desktop Database" applications is ease of use - even for non-technical users. For example, once your database is registered in Base, end-users can directly open a table and work with the data it contains. Let's try that right away.

In the sidebar, single click the "Tables" icon to select it. The "Tasks" pane shows a few taks to create new tables, and the content pane shows all tables in the "Sakila" database. In the content pane, double click the "actor" table. You should see something like this:
Oo-Base-Actor-Table


Ok, nice. This little grid gives us direct access to the table data. Navigation is quit straightforward: use the mouse to point and click an arbitrary cell in the grid. Use the "tab" key to move to the next cell. Use shift+tab to navigate to the previous cell. The "down" arrow key takes you to the next row, and the "up" arrow key to the previous row.

Changing data is easy too: just navigate to cell and type to edit the data. If you're not happy with the edits, use the "Undo" toolbar button to undo them. Navigating out of the row will permanently store the edit. If you are afraid to accidentally change the data and uncheck the "Edit Data" checkbox on the "Edit" menu.

How about adding a new row? Scrolldown to the last record in the grid, and find the empty row at the end. Navigate into it, and edit. A new empty row is automatically appended. Delete is also easy: click on the left before the first field of the row to select it, and then hit the "delete" key.

The grid also some functionality that might help the end user to work with the data in a more effective manner.

  • Filtering - only those records are retrieved from the underlying database that match a condition. Use the toolbar buttons with the funnel icon to filter data.

  • Searching - the local set of rows in the grid is searched for records that match a particular condition. Use the toolbar button with the looking glass to search for particular records.

  • Sorting - the local set of rows in the grid is reordered according to a sorting key (and order).



My impression of the "Base" data grid is that it is a very useful tool to quickly inspect or enter data. It is far from perfect, and it is also unsuitable for normal application development, but it sufficient for maintaining and entering address lists and the like. Most end-users that are used to maintaining such lists in a spreadsheet application will be able to effectively use this interface instead. Also, besides registering the database in OpenOffice.org, it requires no effort at all to obtain this interface: no coding required.

Yeah sure, nifty grid. Now what?


The "Base" application offers two more items that can be interesting for end-users in an office environment: Forms and Reports.

Forms can be developed to build small end-user applications. For a number of reasons, the data grid just is not suitable to build a genuine end-user application. A lot of the limitations you have with grids can be lifted by creating an suitable form. I will discuss this topic on another occasion more thoroughly, and demonstrate how this can be done.

Reports are useful if you want to present data to the end user in a particular layout or grouping. Reports are also useful if you want to export your data to a pdf format, or if you want to print it. Personally, I think that "Base" reports are less suitable for typical analytical BI applications.

Databases registered in "Base" can be used throughout OpenOffice.org. So, although "Base" offers some functionality which might be used in an office environment, it is really only the start. For example, if you want to perform some kind of analysis using the OpenOffice.org spreadsheet application "Calc", you can draw that from one of the registered databases.

Another typical example for the OpenOffice.org wordprocessor "Write" would be to combine data from the database with letters, or address labels to automate postal mail communication.

Right now, I won't discuss these applications in any detail, but I'll try and write up a tutorial on those subjects soon. Of course, if anybody else wants to share their story on integrating their database in open office, please do not hesitate and add a comment to this post.