CSV export

Creating an export usility for embedded Base databases

Currently Base has no built in support for exporting data expect to other OpenOffice.org documents.

An indirect path to exporting data that is commonly used to to first drag a table or a query to Calc sheet. Then save this sheet as a CSV file.

The HSQLDB database manager employed by OpenOffice.org as the default database format offers another even more direct path. Linked text files. Base at the moment, Release 2.0.4 and earlier, has not GUI interface to this functionality. This feature is being actively developed for a future release. Information on this activity, including the specification document, can be obtained from the Database project at the OpenOffice.org main web site.

Adding a GUI to this inherent support should be a task well suited to the scripting capability built into Base. This page will report on the efforts to implement just this using OOBasic. Where possible the utility to issue DML commands directly to HSQLDB, this should yield the best performance to writing a text file as.

The utility will support not only the export of complete tables and simple queries using a single DML commands. Performance is a critical factor, however, no more so then accuracy. For complicated parameterized queries as well may well need a different approach.

For this second approach the utility will support exporting a rowset to a csv file. This will use the HSQLDB text feature to create the link, the transfer however would be implemented in basic using batch inserts to prepared statement. This approach not only allows us to use the built in support for parameterized queries, no need to rebuild something that can be tricky and already works. It also offers the option of being reused in other contexts. For instance calling this from a button or menu on a data entry form.

The utility can be extended to support creating named export denitions. These definitons need to hold the all the necessary information to preform the export. When a paramterized query is selected as the date source it should support an option of either prompting for the parameter values or using pre-loaded values.

The first stages of the utility is being finished now. This will include support for tables, views, and simple queries. It will support a menu item added to the Base window and utilize a standard dialog box to interact with the user. Support for exporting the parameterized queries will be the next phase of the work, followed by the support for export definitions.