Apache OpenOffice Community Forum

OpenOffice.org v2.3.0
Windows XP SP2 Build 2600

In an OpenOffice Calc spreadsheet I have numerous cells which have live data being updated via DDE links to another application. What I want to do is, when a macro is run dump the data into a MySQL database.

The MySQL database is operational on a different host. I have locally installed the MySQL ODBC 3.51 driver, and linked this to my MySQL database. Test connections are successful.

The problem I am having is finding UNO/OpenOffice.org Basic documentation which will enable me to write a macro which will open an ODBC (or direct) link to the database, send it MySQL queries inserting table records, then close the connection.

I have tried some comparable Visual Basic (sorry for uttering that term if it is an f-word around these parts) ODBC code, but the OpenOffice.org UNO interpreter doesn't seem to like some of the object names.

Any help is appreciated!

I move your thread in the UNO API forum, perhaps it will get more attention.

Have you seen the link to the Basic guide in the Macro forum?

VBA is for Microsoft applications alone. Even if the used languages where exactly the same, you would talk to completely different APIs. You can access the OOo-API via it's own StarBasic language and several other script languages, Win-COM, C++ and Java via bridges.
Writing from spreadsheet cells into a database can be a nightmare. There are many exceptions to catch due to wrong type, missing values, key violations and other constraints on the database side.
Why not using a database form if you have your MySQL in Base already?
Anywas, I had some success with the following approach:
Link all the cells which make up one record into one row and name it "export" or something: Hide/protect this range from users.
I converted the date to ISO-String. Spreadsheet date/times use to be days since 1899-12-30

First row has the field names and the second aggregates the record in one row. So you don't have to rewrite everything next time.
StarBasic: aValues() may contain:
"" for empty cells
Basic type Null for errors
"strings"
or numbers of Basic type Double.

- Get a new record of your result set (assuming insertion of new records, which is complicated enough)
- Test for valid field names in aFieldNames()
- Determine the field types
- Map strings and doubles to the respective types (Dates as ISO-string may help here)
- Write the record and catch SQL errors
Base - Copy column and copy row functions
I would recommend any other language but StarBasic because of the error handling. Creating your own class for the recordset could be useful as well.

I found the solution to my problem (actually it came from a MySQL webpage) under 24.1.7.2.1.6. Microsoft ADO.
(http://dev.mysql.com/doc/refman/5.0/en/ ... isualbasic)

The code I am using is:
This is really all I needed.

Why not using a database form if you have your MySQL in Base already?

The reason I need to use this system is that I have DDE links in OpenOffice Calc which connect to a separate application to collect realtime data. I don't have a method of doing this in Base (or if there is one, I haven't come across it), but still need to pump the data into an external MySQL database for other reasons (mainly to be able to access the data via the web without the requirement of an ODBC driver, OpenOffice, and the myriad of other expensive software needed to collect the data).

Take care of the dates, times and date/times. They may import wrong or not at all.

I'm not collecting date-data from the spreadsheet; timestamps are controlled by the database alone (i.e. when records are input into the data tables, they are timestamped by the related MySQL function 'CURRENT_TIMESTAMP' rather than relying on the external application).

OK, OK. I use to read this type of question in another context: "How can I use my spreadsheet as an "easy" database form for arbitrary data?"