OpenOffice Base with .NET

Discuss the database features
Post Reply
UriF
Posts: 7
Joined: Sun May 16, 2021 7:43 pm

OpenOffice Base with .NET

Post by UriF »

I want to call OpenOffice Base from .NET(VB.NET/C#) applications. How to connect to Base and you have any documentation about connection string, OpenOffice Base driver or objects could be called (like Connection, Recordsets, Datasets, Datareader, Execute method) or use MS library OleDBConnection. Unfortunately, I didn't find such information.
OpenOffice 4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice Base with .NET

Post by Villeroy »

There is no such thing as a "Base database". What you most likely mean is the thing you can create newly from scratch resulting in a database in a single odb file. It is a zip container with a HSQLDB. When you are going to load the first record set, the HSQLDB is installed to a temporary directory. When you close the edit session, the zip archive is refreshed with the modified database. This is intended to be a single-file, single-user desktop database with limited functionality.
HSQL is a stand-alone product. If you want to access an HSQLDB in server mode, you have to extract it from the Base document, so the Base document treats it as a regular JDBC backend and you can access that stand-alone database just like any other JDBC database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UriF
Posts: 7
Joined: Sun May 16, 2021 7:43 pm

Re: OpenOffice Base with .NET

Post by UriF »

Sorry, I mean Base application like MS Access application. I want to connect to odb file from .NET(VB.NET or C#) application like I can connect to any database using OleDB library or library specific to database (MS SQL, MS Access, Oracle and so on). For example Oracle provided library which allow to connect through Oracle ODBC Driver. I tried OpenOffice documentation but didn't find anything like this. I develop some .NET applications just for myself. If I would have access to VBA libraries of OpenOffice analogs of MS Word, MS Excel, and MS Access from MS Visual Studio Community Edition (true .NET layer or .NET Interop like MS Office Interop) I wouldn't need MS Office anymore. I also believe that this feature would help real world developers to port production applications from MS Office to OpenOffice
OpenOffice 4 on Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: OpenOffice Base with .NET

Post by robleyd »

Similar question about LibreOffice posted on AskLibreOffice
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
UriF
Posts: 7
Joined: Sun May 16, 2021 7:43 pm

Re: OpenOffice Base with .NET

Post by UriF »

Thank you. Somebody answered me there that it is the same product. I explained that I thought that it is other product. By the way I always posted THE SAME questions about MS products at different Microsoft forums but nobody never asked me why I posted the same question at different forums. You know why? Different developers prefer different forums. If I would get proper answer at one of forums I SIMULTANEOUSLY um-subscribe.

By the way nobody answered me about .NET & OpenOffice.
OpenOffice 4 on Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: OpenOffice Base with .NET

Post by robleyd »

Somebody answered me there that it is the same product.
Just to be clear, AOO And LO aren't the same product - what was said:
AskLibreOffice wrote:LO & OO were the same at one point. LO went on its' own years ago and while other modules may have significant differences, Base remains much the same. The AOO forum and this Q&A site answer much the same questions.
By the way nobody answered me about .NET & OpenOffice.
I note it is only about six hours since your question was posted. I'm not sure what your expectations of this site are, but please note that solutions are entirely provided by volunteer users, who live in many different time zones.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
UriF
Posts: 7
Joined: Sun May 16, 2021 7:43 pm

Re: OpenOffice Base with .NET

Post by UriF »

To tell you the true before I posted this question here I checked everything at Google. The last topis was in... 2012. The answer was "NO". "No interface for .NET applications" You can open OO spreadsheet but not database I tried documentation - nothing. I wonder if developers added this feature for the last 9 yrs or not? I believe if developers in EU countries tried to replace MS Office package with OO package they experienced the same problem.
OpenOffice 4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice Base with .NET

Post by Villeroy »

Of course, you can access Base through the office API controlling any type of database through the frontend. But that was not your question. You asked for a connection URL to connect with the backend. The connection URL is displayed in the status bar. If it happens to be "embedded HSQL", then there is no URL to access the database from outside Base. If it is somethong else starting with jdbc: or odbc: then this is what you asked for.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice Base with .NET

Post by Villeroy »

UriF wrote:To tell you the true before I posted this question here I checked everything at Google. The last topis was in... 2012. The answer was "NO".
There is so much bullshit on the net. The answer is correct if you want to access the database behind the Base document by simple specification of an URL or ODBC name.
There is no connection URL like odbc:file:C:/path/MyBaseDoc.odb
Of course your program can open a Base document just like a text document or a spreadsheet as described here for VB: http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html

[Your prog] ---> [Office Base] ---> [DB backend]
But then you have a very thick additional API layer between your program and the database you want to access. That thick, fat API layer does not make things easier nor more efficient. If you want to do that, you will have a very hard and frustrating time exploring this API. You would walk this path if you are going to develop a database application with Base forms and Base reports. Particularly the input forms benefit from little code snippets. In this case you would write event driven code where the connection to the database is a given matter of fact.

Some resources on database access through office API: https://www.pitonyak.org/database/
 Edit: Right now in this minute another one comes up struggling with this API: [Python]-Get SQL Select result in a variable 
.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UriF
Posts: 7
Joined: Sun May 16, 2021 7:43 pm

Re: OpenOffice Base with .NET

Post by UriF »

Sorry, your answer irrelevant to my question. I ask you how to run SQL statements from .NET application (get cursors and modifications in database) like you do with any databases. I ask you what assembly(ies) and namespaces would be called from .NET applications. CreateObject is a late binding statement not early binding. Therefore it is a problem to debug application.

P.S. I read a first document at https://www.pitonyak.org/database/
I did not find how to get cursor to data set (Recordset, DataReader, Datatable and so on). I also did no find how to pass joined query to application (for example I want to create a view in database and get data from this view). I can do it very easy any standard database). Why do I want to connect to Base? I have MS SQL Developer edition but I have to run SQL Server services "automatic". I have my application with <100K records (it is foolish to use MS SQL for this purposes). Moreover I think to give my application to my friends but I don't want to ask them to install MS SQL. That's why I use MS Access FOR NOW. I think to replace MS Access with OO Base.
OpenOffice 4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice Base with .NET

Post by Villeroy »

The whole Base component, including several database drivers, weighs no more than 30 or 40 MB. You can not compare a tiny addition to this office suite with the multi-billion development suite MS Access. A Base document is a zip with XML data storing a connection URL to some database together with a few configuration data. Forms and reports are nothing but embedded text documents. There is one special case where the Base document stores a HSQL database folder as well but there is no way to access this in any other way than using LibreOffice or OpenOffice.

Of course you can walk through the record set of a database using the office API. Search Andrew's document for executeQuery and oResult.next(). You can do so with an MS JET database (embedded in an Access document), embedded HSQL, a list in a spreadsheet, a dBase file, PostgreSQL, anything that is connectable with a Base document.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UriF
Posts: 7
Joined: Sun May 16, 2021 7:43 pm

Re: OpenOffice Base with .NET

Post by UriF »

executeQuery and oResult.next() exactly what I am looking for. Yes, I can open spreadsheet or dBase file but you understand that you can not save joined some spreadsheets or dBase resultsets as a one query result. What I want to do. I have a data with less than 100K records. I want a permanent access to this data. Yes, I can save it as a .csv file. I have to compare some records in 2 .csv files. I can do it programmatically loading data to .Net Lists (Generics) and join and select using Linq. Moreover I will have modify this lists and save data to .csv file back.You understand that it will take a lot of time. Much more easier to save data in MS Access tables and get SQL query with Left Outer Join. I save data in MS Access from .NET application because it is a complex logic and some .NET libraries which simplify solution. Of course I will save this data in the MS Access to easier access later. Yes, I have a legal copy of MS Office but you know that MS support it's product for approx. 10yrs. If you want a new copy you have to pay for it. That's why I started to search alternatives. It is FOR NOW.
OpenOffice 4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice Base with .NET

Post by Villeroy »

UriF wrote: Yes, I can open spreadsheet or dBase file but you understand that you can not save joined some spreadsheets or dBase resultsets as a one query result.
You can also open a Base document connected to a spreadsheet or dBase directory. You have an office suite on one side and some kind of (pseudo-) database on the other side. Now you want to print labels, serial letters, invitation cards or feed your calculation model with database data. This is where Base comes into play. Base is the bridge between tabular data collections and your office suite. You connect a Base document to whatever constitutes your database and then you feed your office documents with database data. This is the main purpose of the Base component. Most database documents connect to some list in a spreadsheet in order to feed a serial letter in the word processing component Writer. If your database driver allows write access, you may even attach form controls to office documents, bind them to a record set in order to write data back into the database. A hierarchy of forms and subforms together with listboxes allows editing of one-to-one, one-to-many and many-to-many relations. Compared with MS Access all this is very primitive and far from being intuitive. And since you are talking about .NET programs, the API is a nightmare able to make a grown man cry.

Contrary to Access, each Base document can be connected to a single data source only. One dBase directory, one spreadsheet file, one database URL and in the special use case it can carry one HSQL database. This is just one of the limitations. I have not used MS Access in 20 years, but even Access of MSO 97 was ten times more powerful but not necessarily ten times much more useful.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UriF
Posts: 7
Joined: Sun May 16, 2021 7:43 pm

Re: OpenOffice Base with .NET

Post by UriF »

Thank you for your answer. I see that OO Base is not suite for my purpose. Have you heard about any open source simple DB application which could be accessible from .NET (just simple table tables, indexes, joins and SQL syntax supported)? I don't need forms, reports and so on. You understand that every table will be lesser than 50K records. I don't want a headache with .NET Linq. You understand that I don't want to run MS SQL Developer Edition (I changed service type from "automatic" to "manual") on permanent base because it just eat my CPU (if run query once or twice in a month is OK). Moreover MS SQL is not portable (I have to copy .mdf to other computer and make my friends to install MS SQL or save data in .xml or .csv and export data to friend's computer). that's why MS Access is OK FOR NOW. Thank you again for explanation.
OpenOffice 4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OpenOffice Base with .NET

Post by Villeroy »

I have no clear idea what .NET is. I left the MS universe 20 years ago.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply