Open Office #openoffice #oobase #oocalc #odbc

Open Office has a database program (Open Office Base) and a spreadsheet program (Open Office Calc). Base can be connected via ODBC to a SQLite database in much the same way as has been described for Microsoft Access, using the same SQLite ODBC driver (See Part One of MS Access Engine). A .odb file is created, saving the connection information and stored SQL queries. For later access to the SQLite database, one can open the .odb file with Base.

Additionally, like MS Office, one can register the .odb database with Open Office so that its tables and queries can be accessed in text and spreadsheet documents using Open Office Writer and Calc, respectively.

Base has a graphic query design tool similar to that of MS Access as well as a direct SQL editor.

Single queries that have been developed using other SQLite managers work in many cases when copied into the Base SQL editor and run using the RUN SQL Direct mode; some things that do not work:

• multiple SQL commands in the one query
• runtime input parameters
• comment lines before the first SQL word
• semi-colon (;) and apostrophe or single quote (‘) in comment

With the RUN SQL Direct mode turned off, most queries that work in other SQLite managers do not work when copied into the SQL View and run. I think in this mode, the commands are OpenOffice.org syntax which may be limited to a subset of the SQLite command set. It is not readily apparent what it is that allows a query to work in the direct mode and not in the OpenOffice mode. The error messages are useless and give no clue where to look.

Extracting the query from AllFacts4Persons9.sql to return all events for the individual worked “out-of-the-box”, complete with very complex date processing, in the Run SQL Directly mode. Opposingly, that query at the foundation of shared events for a couple would not work as it comprises multiple SQL commands. I have attempted to rebuild it using the SQL Design View method and can get it to work without processing the event date field but, using this method, there seems to be no way of incorporating the complex CASE structure employed for date processing. Simply pasting that part into the code generated by Base throws up untraceable errors. Probably the SQL CASE expression is unsupported or is different in the OpenOffice.org syntax.

Base SQL Design View produces what we would call “bloated” code – double quotes around table names and field names, for example, and no short alias for a long table name. It has the virtue of being able to query not only tables but also queries stored previously in the OOBase database. The SQL direct mode cannot query stored queries while the indirect mode does not support UNION.

The inability to incorporate the querying of queries, complex expressions and the UNION operator in one mode makes it very difficult to accomplish in OOBase what one can do outside of it. Moreover, OOBase’s filtering and sorting functions for results work only in the indirect or native mode, not in the SQL direct mode – another shortcoming.

The connection from OOCalc to a registered database can help. Queries and Tables can be selected and results viewed in the Calc viewer. Results can be dragged onto a spreadsheet and filtering applied there. Even more directly, a query can be selected from the Base Queries list to a Calc spreadsheet and the results will populate the sheet.

Unfortunately, OpenOffice and the ODBC connection to the RootsMagic 4 SQLite databases is so limited and failure-prone that it cannot be recommended and prospective users are warned of the prospect of frustration and disappointment. Hence, no further detail is forthcoming from this author on how to use set up and use OpenOffice with SQLite.

Discussions & comments from Wikispaces site