![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Hi
A long unresolved problem with OpenOffice.org base was its inability to link to more than one external database at a time. See:
http://openoffice.org/bugzilla/show_bug.cgi?id=42464
It is for this reason (needing to link to more than one table) that I have had to use base's internal HQLDB database.
Does anyone know if this issue still applies in LibreOffice base?
John
Hi,
jowyta schrieb:
Hi
A long unresolved problem with OpenOffice.org base was its inability to
link to more than one external database at a time. See:
http://openoffice.org/bugzilla/show_bug.cgi?id=42464It is for this reason (needing to link to more than one table) that I
have had to use base's internal HQLDB database.
I don't understand your reasoning. The issue is about connecting to two different databases with the same odb-document. For example connecting to MySQL and PostgreSQL at the same time. That has nothing to do with more than one table. You can have as much tables in a database as you need.
What is your problem and in which way does the internal HSQLDB helps you?
Kind regards
Regina
Regina Henschel wrote
jowyta schrieb:
A long unresolved problem with OpenOffice.org base was its
inability to link to more than one external database at a time.
See: http://openoffice.org/bugzilla/show_bug.cgi?id=42464
It is for this reason (needing to link to more than one table) that
I have had to use base's internal HQLDB database.
I don't understand your reasoning. The issue is about connecting to
two different databases with the same odb-document. For example
connecting to MySQL and PostgreSQL at the same time. That has
nothing to do with more than one table. You can have as much tables
in a database as you need.
What is your problem and in which way does the internal HSQLDB helps
you?
My problem was that if I had tables in different files, even if they used the same database structure, then base could not link to more than one of them. This seems to be a facility that various people need.
It might be a solution for me if I could have several tables in the same file provided that I could manipulate them from base rather than having to use a separate database programme. If this is possible, are there some instructions somewhere as to how to extract the data to a separate file?
Kind regards
John
Hi,
jowyta schrieb:
Regina Henschel wrote
jowyta schrieb:
A long unresolved problem with OpenOffice.org base was its
inability to link to more than one external database at a time.
See: http://openoffice.org/bugzilla/show_bug.cgi?id=42464It is for this reason (needing to link to more than one table) that
I have had to use base's internal HQLDB database.I don't understand your reasoning. The issue is about connecting to
two different databases with the same odb-document. For example
connecting to MySQL and PostgreSQL at the same time. That has
nothing to do with more than one table. You can have as much tables
in a database as you need.What is your problem and in which way does the internal HSQLDB helps
you?My problem was that if I had tables in different files
?? What kind of file? A database is not a file, but normally has a lot of files.
, even if they
used the same database structure
?? 'database structure'. Which database you tried to use?
, then base could not link to more than
one of them. This seems to be a facility that various people need.
Not really. You are usually not running two databases. I guess, that there is an error in how you have organized your data.
It might be a solution for me if I could have several tables in the same
file provided that I could manipulate them from base rather than having
to use a separate database programme. If this is possible, are there
some instructions somewhere as to how to extract the data to a separate
file?
Extracting to a file depends on the database engine.
Before someone can help you, you need to describe what database kind you use at all. A database with own database management like MySQL or DBase-tables or ...?
Kind regards
Regina
John
Regina Henschel wrote
jowyta schrieb:
>> A long unresolved problem with OpenOffice.org base was its
>> inability to link to more than one external database at a time.
>> See: http://openoffice.org/bugzilla/show_bug.cgi?id=42464>> It is for this reason (needing to link to more than one table) that
>> I have had to use base's internal HQLDB database.> I don't understand your reasoning. The issue is about connecting to
> two different databases with the same odb-document. For example
> connecting to MySQL and PostgreSQL at the same time. That has
> nothing to do with more than one table. You can have as much tables
> in a database as you need.> What is your problem and in which way does the internal HSQLDB helps
> you?My problem was that if I had tables in different files, even if they
used the same database structure, then base could not link to more than
one of them. This seems to be a facility that various people need.It might be a solution for me if I could have several tables in the same
file provided that I could manipulate them from base rather than having
to use a separate database programme. If this is possible, are there
some instructions somewhere as to how to extract the data to a separate
file?Kind regards
John
If I understand your problem you have table in one database and you need
to access from another database. For example you have in MySQL/MariaDB
or PostgreSQL two databases called VendorAccounts and Inventory. Your
query/view/function/etc. must access one table in each to get the
relevant data. (MariaDB is a FOSS fork of MySQL and can easily replace
MySQL)
My first question is what is your backend?
Next how are you connecting to the backend? For example MySQL/MariaDB
have several possibilities depending on the network and database server
location. I have MySQL/MariaDB installed on my desktop and can access it
using direct connection, odbc, and jbdc - I normally use direct
connection.
Also, have you checked the documentation for your backend for the syntax
for querying tables in different databases? MySQL/MariaDB use the SQL
union to do this.
What is your OS and LO version?
Hi
OK, let me go back to square 1.
First of all I am not talking about a situation in which LibreOffice base needs to access data tables in separate files that use different database structures, e.g. one table is in MySQL and another is in PostgreSQL. This may be something that others need and I seem to recall that M$ access could do this but I do not need to.
Next, terminology because "database" can mean a number of things. Please correct me if I am wrong here. The data are contained in tables. These can be simple tables such as CSV tables or more complex tables constructed according to the rules of a particular database application. These tables can be in separate files or they may be embedded along with other information.
Then there are such things as queries, forms, reports and configurations which could, I guess, be each in separate files but are normally saved embedded together or with, say a data table. The results of running these, if saved, will be data tables or reports in separate files.
Finally there is the database application which may be in one or many files.
Now, to my situation. First of all I am using Debian GNU/Linux testing distribution. I use LibreOffice and for my database needs I use base. I use the built in HSQLDB database application and have my data tables embedded in the .odb file.
This has worked adequately (clunkily I would say, but I will keep other difficulties out of this discussion). However, base has recently become unusably slow in response to something that has changed in openjdk (I have asked about this elsewhere).
This has prompted me to revisit the question of whether to separate out my data tables. I am wondering whether it might be a workaround for the slowness problem. Also there may be advantages in backing up data separately and having it less vulnerable to anything going wrong in LibreOffice.
My data consist of a list of names, addresses etc. in one table and then some other tables that have additional information that only applies to (overlapping) subsets of the people in the names etc. table.
Way back in the past I used M$ access to handle these data. At that time my data tables were each in a separate file and everything worked very well.
When I moved over to OpenOffice.org I found that this was no longer possible. In order to run queries using data from different tables (all using the same database structure) I found I needed to embed all the tables into the same .odb files.
So my question is, is this still the same in LibreOffice?
More specifically, is it possible only using base, i.e. without running a separate database application, to access, run queries across and manipulate the data in data tables contained in separate files? I am assuming the these would probably be HSQLDB data tables.
If not, would it be possible if the data tables were embedded in the same file?
If either of these are possible then some pointers to how to separate out the data tables would be helpful.
Kind regards
John
Hi John,
Now, to my situation. First of all I am using Debian GNU/Linux testing
distribution. I use LibreOffice and for my database needs I use base. I
use the built in HSQLDB database application and have my data tables
embedded in the .odb file.This has worked adequately (clunkily I would say, but I will keep other
difficulties out of this discussion). However, base has recently become
unusably slow in response to something that has changed in openjdk (I
have asked about this elsewhere).
This is your problem. It is not LibreOffice/OpenOffice.org, but the JDK
version which is causing the problem. Furthermore, it is not even the
first time that such a change has caused the very same issue of poor
performance. So if anyone is to blame, it is Oracle. The solution is to
go back to version JDK1.6u22, u21 or even u20. The way to do this has
already been posted in this list and will be in the mailing list archives.
This has prompted me to revisit the question of whether to separate out
my data tables. I am wondering whether it might be a workaround for the
slowness problem. Also there may be advantages in backing up data
separately and having it less vulnerable to anything going wrong in
LibreOffice.
I honestly don't think that separating out your tables will make any
difference, especially if you have a large number of fields or even only
a moderate number of records(tuples) in your table - performance will be
hit nonetheless with the most current version of the JDK (u24 I believe).
Alex
I think the simple answer to your question ("Is this the same in LO")
is "yes". But, as others have suggested, it is because you haven't
quite got the right way of looking at it.
Using a database rather than a spreadsheet allows you to group things
according to the content of the data rather than where it is.
With a database you can pull out any of subset of your data by
selecting a group according to one of it's unique factors, for
example "select all phone numbers from people in the advanced group"
(for example) because you have a field in your data for "advanced
group", not because all those people are in a separate file.
I've worked on large databases written by so-called professionals
which used tables to separate data, and it is almost always just
slower and more complicated than arranging the data better.
I'd guess from some of the things you've said in your last mesage
that you've read Mariano Casanova's Step by Step guide to Base. It
has a very good section on organising data that I think will make
your life much easier.
In general it looks like Base (either LO or OOO) only accesses one
database at a time. You probably want to stop thinking so much about
"files" since the concept doesn't help very much in most of the
context of what you want to do.
As a last note, I'm using a MySQL backend under Fedora and no, it's
not fast. I'll look into why, but I wonder if it's the JSQL
interface too.
Regards
Mark Stanton
One small step for mankind...
Hi Alex
Thanks for your response.
This has worked adequately (clunkily I would say, but I will keep other
difficulties out of this discussion). However, base has recently become
unusably slow in response to something that has changed in openjdk (I
have asked about this elsewhere).This is your problem. It is not LibreOffice/OpenOffice.org, but the JDK
version which is causing the problem. Furthermore, it is not even the
first time that such a change has caused the very same issue of poor
performance. So if anyone is to blame, it is Oracle. The solution is to
go back to version JDK1.6u22, u21 or even u20. The way to do this has
already been posted in this list and will be in the mailing list archives.
I am aware of this. I am not really interested in who is to blame, it needs sorting for current versions of JDK. If you or anyone has any idea what the change is in JDK that has led to this problem please answer at http://lists.freedesktop.org/mailman/listinfo/libreoffice where I have asked this specific question.
I honestly don't think that separating out your tables will make any
difference, especially if you have a large number of fields or even only
a moderate number of records(tuples) in your table - performance will be
hit nonetheless with the most current version of the JDK (u24 I believe).
OK, that is useful to know, and there are other reasons for keeping the data separate from the .odb file.
John
Hi,
Linking tables from different database is impossible.
I know one partial work-around for this limitation:
You can create a form on a stand-alone document, say some Writer document with a "MainForm" bound to some table/query in data source "Source A".
Then you can add a subform, say "SubForm" bound to some table/query in "Source B" and link the 2 forms through common field(s) so the sub form shows all records which share the same field(s) contents with the parent form's current record.
Hi John,
I am aware of this. I am not really interested in who is to blame, it
needs sorting for current versions of JDK. If you or anyone has any
idea what the change is in JDK that has led to this problem please
answer at http://lists.freedesktop.org/mailman/listinfo/libreoffice
where I have asked this specific question.
Well, that will probably be rather hard to answer since the LibreOffice
project doesn't develop the Java runtime engine or development kit. Only
those who work on the JDK/JRE are likely to know why things suddenly
went pear-shaped, and as far as I know no-one amongst the LibreOffice
development is involved in Oracle's Java development. I imagine that the
same could be said for those former OpenOffice.org developers who worked
for Oracle and are now working at IBM, Redhat or wherever, as they were
after all, separate projects.
In the past, when similar events happened with OpenOffice.org, AFAIK it
was the JDK that Sun/Oracle had to fix, and not the other way around.
From what I remember, it was often some accessibility code change within
the JDK that caused the problems. Perhaps, and I say just perhaps, you
could check to see whether you have any accessibility options activated
(system-wide), and then turn them off to see if that brings a mild
improvement ? If not, then I know no other solution other than a
downgrade (or dual installation with an earlier version) of the JDK.
Alex
Hi 
Oracle seem to have been treating this community as a competitor since before the split away from OpenOffice. Apparently there have been a lot of attempts at co-operating with them but Oracle tend to refuse to respond. Both normal Java and openJDK are run by Oracle. The LO devs have been writing out Java dependence but that is trickier in Base.
It's possible to install Java version _21 alongside other versions and then get only LO to use the _21. The web-browsers and other stuff will pick-up on the newer version automatically but you can force LO to use the _21 by clicking on
Tools - Options - LibreOffice/General - Java
and then all versions of java that are installed should appear so just select the _21.
Note that some people say it's the older version and therefore unsafe but it's more recent than ones that appear on many systems, for example Ubuntu 10.04 LTS uses version _20.
Also, the newer ones that are supposedly safer will doubtless be found to be deeply flawed and are probably suffering security exploits right now that we will only hear about later. It seems that the only java that is really safe is one that is not installed anywhere.
Just my 2cents!
Regards from
Tom
John
Hi
OK, let me go back to square 1.
First of all I am not talking about a situation in which LibreOffice
base needs to access data tables in separate files that use different
database structures, e.g. one table is in MySQL and another is in
PostgreSQL. This may be something that others need and I seem to recall
that M$ access could do this but I do not need to.Next, terminology because "database" can mean a number of things.
Please correct me if I am wrong here. The data are contained in tables.
These can be simple tables such as CSV tables or more complex tables
constructed according to the rules of a particular database application.
These tables can be in separate files or they may be embedded along
with other information.
CSV files are normally used for importing and exporting data into or out
of a database. Databases do not use csv or txt files otherwise.
Then there are such things as queries, forms, reports and configurations
which could, I guess, be each in separate files but are normally saved
embedded together or with, say a data table. The results of running
these, if saved, will be data tables or reports in separate files.Finally there is the database application which may be in one or many files.
Now, to my situation. First of all I am using Debian GNU/Linux testing
distribution. I use LibreOffice and for my database needs I use base.
I use the built in HSQLDB database application and have my data tables
embedded in the .odb file.This has worked adequately (clunkily I would say, but I will keep other
difficulties out of this discussion). However, base has recently become
unusably slow in response to something that has changed in openjdk (I
have asked about this elsewhere).
There have been problems with various recent Java versions causing
performance problems. The work around to is use an earlier version of
Java.
This has prompted me to revisit the question of whether to separate out
my data tables. I am wondering whether it might be a workaround for the
slowness problem. Also there may be advantages in backing up data
separately and having it less vulnerable to anything going wrong in
LibreOffice.
Backing up the data in a format that allows to reconstruct the database
at worst or just open the backup is always good practice. This can be
extended to backing up all data so one can restart if there is a
hardware failure.
My data consist of a list of names, addresses etc. in one table and then
some other tables that have additional information that only applies to
(overlapping) subsets of the people in the names etc. table.
I am not sure about your design. There is procedure used with databases
called normalization. The idea is to group similar data into separate
tables with use of primary and foreign keys to allow linking. For
example, one would have a table of vendor addresses, another of vendor
contacts, another of vendor payment information, etc. Each table would
have its own primary key (often an integer) and relationships with the
other tables use a foreign key that is same as the primary key in the
other table. Appropriate normalization tries to isolate different types
of data into logical groups that allows easier data maintenance, access,
and understanding of the relationships between the data.
One can study data modeling and database design independent of the
database application, the normalization procedure is not dependent on
the applicaton. A good design for a specific situation will be
implemented similarly in any database application.
Way back in the past I used M$ access to handle these data. At that
time my data tables were each in a separate file and everything worked
very well.When I moved over to OpenOffice.org I found that this was no longer
possible. In order to run queries using data from different tables (all
using the same database structure) I found I needed to embed all the
tables into the same .odb files.So my question is, is this still the same in LibreOffice?
More specifically, is it possible only using base, i.e. without running
a separate database application, to access, run queries across and
manipulate the data in data tables contained in separate files? I am
assuming the these would probably be HSQLDB data tables.If not, would it be possible if the data tables were embedded in the
same file?If either of these are possible then some pointers to how to separate
out the data tables would be helpful.Kind regards
John
To clarify a few points, in most database applications the database
consists of a group of tables with related data with other constructs
such as views, functions, etc that are used with the database. Within
the database, data in different tables can be accessed by the use of SQL
joins, unions, and subqueries.
For example a query between the Vendors table and Invoices table in an
account payable (Accounts) database could look like:
SELECT invoiceID, Amount, DueDate, Name, Address, City, State, Zip
--these the columns from the two tables
FROM Invoices --the base table
JOIN Vendors --the external table
ON Invoices.VendorID = Vendors.ID --the relationship
between the tables (one primary key and one foreign key)
The above syntax for what is called an inner join should work in all
database applications with a database. If the tables are in different
databases, you might access the data using "schema qualified names"
where the schema name is the database (Accounts.Vendors or similar
syntax).
If you are using the GUI to construct the query, the final generated
query will be similar to above.
I think the simple answer to your question ("Is this the same in LO")
is "yes". But, as others have suggested, it is because you haven't
quite got the right way of looking at it.Using a database rather than a spreadsheet allows you to group things
according to the content of the data rather than where it is.With a database you can pull out any of subset of your data by
selecting a group according to one of it's unique factors, for
example "select all phone numbers from people in the advanced group"
(for example) because you have a field in your data for "advanced
group", not because all those people are in a separate file.
I've worked on large databases written by so-called professionals
which used tables to separate data, and it is almost always just
slower and more complicated than arranging the data better.I'd guess from some of the things you've said in your last mesage
that you've read Mariano Casanova's Step by Step guide to Base. It
has a very good section on organising data that I think will make
your life much easier.In general it looks like Base (either LO or OOO) only accesses one
database at a time. You probably want to stop thinking so much about
"files" since the concept doesn't help very much in most of the
context of what you want to do.As a last note, I'm using a MySQL backend under Fedora and no, it's
not fast. I'll look into why, but I wonder if it's the JSQL
interface too.
When I use Base with MySQL/MariaDB I use the direct connection option. I
forget which package is needed for this, I know I installed a MySQL to
LO connection from the Ubuntu repositories. The connection seems
reasonably quick and I can access all the databases/schemas on my box.
Am 13.10.2011 08:14, John Talbut wrote:
Hi
OK, let me go back to square 1.
First of all I am not talking about a situation in which LibreOffice
base needs to access data tables in separate files that use different
database structures, e.g. one table is in MySQL and another is in
PostgreSQL. This may be something that others need and I seem to recall
that M$ access could do this but I do not need to.
Yes, MS Access can bundle many types of database tables as read-only links. Base connects one database file to one database served by one database server. A "database server" may be a directory or a file in some cases.
Next, terminology because "database" can mean a number of things. Please
correct me if I am wrong here. The data are contained in tables. These
can be simple tables such as CSV tables or more complex tables
constructed according to the rules of a particular database application.
These tables can be in separate files or they may be embedded along with
other information.
The built-in HSQLDB 1.8 (not Base!) is able to fill a prepared text table with data coming from a (point decimals and ISO dates).
[Tutorial] Using csv/text files as editable data source:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=23260
[Example] Loading CSV into preformatted spreadsheets:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=23727
[refer to files #1 and #2]
Now, to my situation. First of all I am using Debian GNU/Linux testing
distribution. I use LibreOffice and for my database needs I use base. I
use the built in HSQLDB database application and have my data tables
embedded in the .odb file.
There is a serious issue with the combination of Linux +Java +Base(OOo/LibO).
[Solved] Extremely Slow search for item in base table:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=61&t=44451&hilit=Java
I'm sure this helps,
Andreas