CODEWITHSUNDEEP
mysqlsqljoinlibreofficelibreoffice-base
Dycius
Dycius

Reputation: 19

Advice to join LibreOffice Base Table with a MySQL Table

My situation is the following: There are two databases in two different offices linked together by the corporate network. On of the databases is MySQL, which holds data that can change, but the field types never do. This is standard information for the whole company to access. The second database is LibreOffice Base. It holds extra bits and bobs that that changes, including the number of fields and the types of fields because the company can change their mind about what extra information they want for the products in the main MySQL database.

I only need to join one table in the MySQL database to one in the LO Base database. Both these tables have one field in common called "Code". Currently, we manually enter a large chunk of the data in MySQL into LO to use the "Report Builder" to make a pretty report with all the extra bits and bobs about the products. This is extremely counterproductive, and would be easier if I could just perform a join on these two tables and then use that join in the report builder. It would save a lot of time and be less error prone.

The trouble is that I don't know how to proceed with this. LO Base is a very limited system to work with, but it's all I have to work with as security doesn't allow anything more. I was thinking that I need to create another LO Base database on the computer and have it connect to the MySQL one and I would be able to somehow connect the two LO Base files together???? Is this the right line of thinking? Can't I just use the ODBC/JDBC/Direct Connect feature of LO and do the join?

Also, if I had to make a separate LO Base file, how would the join look? I have never done one between two files like that.

Thirdly, would it be possible to have a query that displays all records in my LO Base database that don't Join with the main DB as this would mean I have imputed the wrong "Code"? This Query would need to be done in LO Base, which is why I ask. I have done a very simple join that didn't cause LO to explode, but this is a bit more complex.

Upvotes: 1

Views: 569

Answers (1)

Doug0
Doug0

Reputation: 341

Don't think LibreOffice Base natively supports mixed data sources using different drivers. Each LibreOffice database uses just one connector/driver or it uses the HSQLDB engine, but not a mix.

Python could be used to bridge the databases; the many options for getting into LO from Python are discussed in another topic/answer (How to access ODB files in Python 2.7). Python can be used independently, or from within LibreOffice under the Macro tools. Also may be possible to build the connection using LibreOffice Basic see API reference but not aware of any code examples.

Python also can use MySQL Connector. After bridging the databases, most likely option would be to port/insert data into one database using Python SQL command, transferring all data into the same location.

Upvotes: 0

Related Questions