[Solved] Calc + UNO + ODBC

The Application Programming Interface and the OASIS Open Document Format

[Solved] Calc + UNO + ODBC

Postby pkingsbury » Tue Mar 25, 2008 8:06 pm

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!
Last edited by pkingsbury on Thu Mar 27, 2008 2:01 pm, edited 1 time in total.
pkingsbury
 
Posts: 3
Joined: Tue Mar 25, 2008 7:53 pm

Re: Calc + UNO + ODBC

Postby Hagar Delest » Thu Mar 27, 2008 12:39 pm

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?
AOO 4.1.6 on Xubuntu 18.10 and 4.1.5 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28383
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Calc + UNO + ODBC

Postby Villeroy » Thu Mar 27, 2008 1:30 pm

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:
Code: Select all   Expand viewCollapse view
Date Company Article Price
=TEXT(B2;"YYYY-MM-DD")  =D2   =F2  =NP+Tax

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:
Code: Select all   Expand viewCollapse view
REM getting the data:
oName = thisComponent.NamedRanges.getByName(strRangeName)
oRange = oName.getReferredCells()
aData() = oRange.getDataArray() ' array of 2 row-arrays (list of lists)
aFieldNames() = aData(0)
aValues() = aData(1)

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26375
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED] Calc + UNO + ODBC

Postby pkingsbury » Thu Mar 27, 2008 2:04 pm

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/myodbc-usagenotes-apptips.html#myodbc-usagenotes-apptips-microsoft-visualbasic)

The code I am using is:

Code: Select all   Expand viewCollapse view
Dim Connection As New ADODB.Connection
Connection.Open "DSN=FactoryTalk"
Query = "INSERT INTO some_table (id, name) VALUES ('', 'somename');"
Connection.Execute(Query)
Connection.Close


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).
pkingsbury
 
Posts: 3
Joined: Tue Mar 25, 2008 7:53 pm

Re: [SOLVED] Calc + UNO + ODBC

Postby Villeroy » Thu Mar 27, 2008 2:10 pm

Take care of the dates, times and date/times. They may import wrong or not at all.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26375
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [SOLVED] Calc + UNO + ODBC

Postby pkingsbury » Thu Mar 27, 2008 2:12 pm

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).
pkingsbury
 
Posts: 3
Joined: Tue Mar 25, 2008 7:53 pm

Re: [SOLVED] Calc + UNO + ODBC

Postby Villeroy » Thu Mar 27, 2008 2:20 pm

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?"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26375
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 2 guests