![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Just a short note: Base can connect to a MySql database using a connector.
External is the one and only way to go. The embedded HSQL 1.8 simply
does not work well enough. There are far too many reports of total data
losses which is inacceptable for a database product.
You can connect MySQL via ODBC, JDBC and the SDBC driver built into the
office suite.
I prefer external HSQL 2.3 via JDBC because the office frontend is
tailored around HSQL, because HSQL 2 converts formerly embedded HSQL 1.8
on the fly and because any connection to an external HSQLDB requires
only one file hsqldb.jar anywhere on the system. In server mode it takes
this file plus self made start/stop scripts and a backup script.
I just opened my oldest HSQL 2 database running in server mode on a
Windows machine, accessed during 12 hours a day by means of Writer forms
and Calc reports from 7 client machines. The first record is of
2011-Apr-28. This database never caused lost a single byte of data. Of
course we run nightly backups anyway.
Hi 
Apparently another great database program to use as a back-end is
Postgresql. Some of the Postgresql people worked with the LibreOffice
people to make a really good connector and then got that connector into
LibreOffice main trunk.
So, Postgresql has an advantage over MySql in not needing a connector,
apparently. I dunno how it gets updated though!
Also MariaDb is a drop-in replacement for MySql and i think a few places
use it but continue to claim they are using MySql. Apparently all the
MySql connectors work just the same but i've only heard from a very small
number of people about that and the person who makes/builds the MySql
connector wasn't certain they would work.
I think those are all the larger database back-ends but HSqlDb is
supposedly faster and more efficient with small databases such as almost
all address books. There are tons to choose from though so you might find
that whatever is being used somewhere already can probably be viewed and
edited through Base. I think the way Base makes it so easy to connect to
external back-ends is one of the huge advantages that Base offers. Instead
we try to cripple it by giving it an internal back-end to make it as broken
as Access.
Regards from
Tom
This is not a matter of partisanship, fanboyism nor objective evidence
of the better product. The important thing is that you are able to
connect to whatever you already have. The database of your online shop,
your business software, your accounting software, some dBase directory,
spreadsheets or csv files. The connectivity feature lets you use tabular
data without troublesome export/import.
If all you have is an embedded HSQLDB, you can convert this to HSQL 2
within minutes. Conversion into Postrgre/MySQL/whatever would require
careful editing of SQL scripts, testing and possibly adjustment of
queries, forms, reports.
Andreas ,
i must strongly confirm your vision.
We must see Base as a connector and a front end for any possible database server
We have 100 users, using LO as a front end to a MySQL server without any problems to use the MySQL data in Writer and Calc.
The front end itself is writen with LO basic mainly using Dialogs to view the data and to feed the server with new and modified data.
Greetz
Fernand
Hi everbody,
although I am not an data IT person, I've been a long time user of
openoffice/libreoffice. I'll allways wanted to use LO as an official front end
for an database; first of all because I do not know how to program html (even
with all those facilities we have today) and also I don't want to start now
learning how to program in an script language. So I always hoped to find an
GUI front end for an database, that would feel very similar to FileMaker
(Mac/win). Well those that claim, that base is already very similar probably
never used FileMaker intensly. Even Kexi which is also claimed to be as good
as FileMaker is mile away from it. Don't take me wrong here I don't want to
make any apology to this program, but they really got it quite right.
Even though still thing that LO and similar are the way to go. But I think for
sometime its being a unstable GUI crashing once and a while. After lefting it
to rest for some time, last week, I took courage to restart an small project I
wanted to move from an Calc tables to an real DB. I started using Kexi and
tested again if LO Base would connect to the MariaDB with the native mysql
driver it went flawlessly. One improvement I immediately saw (or at least as
far as I remember), was the hability to see tables not generated by LO and
read/write data as well. The last time I used base, if I remember well, base
could only read tables from an DB if it generated the tables.
But other simple things like changing the order of fields (with MariaDB as
backend) do not work in LO but in Kexi it works. It seems also that crashing
event were eliminated or reduced to a minimum. I don't want to compare GUI and
list advantages or disadvantages, I just want to point out that although big
leaps happened since I last tried to use base, what makes me very happy. On
the downside, to become an great and easy to use GUI for developing grahical
DB interfaces, if comparing with FileMaker, there is still along way to go. I
tell this because I've used FileMaker for a while. I've learned to use it in
quite a short time (~ 3-4 month) and did fairly complex reports, queries, etc
in graphic mode. I can not say the samething for SQL likes. Probably my
problem.
But I belive that base is the right way to go, because it is an multiplataform
program that links to several diferent DB programs and is open, I think two
key conditions for the success of an software, as Fernand Vanrie mentioned.
More up to date literature and how-to's would be a nice help and also an
harder development unfortunately this last one I can not help due to my
limitations.
Greetings
Hi
+1
One advantage of Base is that it can connect to such a wide range of other
database programs. It is kinda the default way of using Base. MS Access
can be twisted into using an external database but it's not as easy to
set-up that way as Base.
Kexi and other front-ends can be used either alongside Base or on other
systems by other users to use the same external back-end as the Base users
connect to. Again this "playing well with others" is a huge advantage that
Access doesn't have by default.
Sadly the marketing team, if and when they ever mention Base, focus on
using the internal back-end and never even mention the advantages that Base
has. This could be one reason why we see so many people using the internal
back-end and comparing it negatively against Access.
Unfortunately the marketing team took such strong offence to my objections
to their attempts to market Base on it's weakest points instead of it's
strength that they banned me from posting to their mailing list at all.
Sometimes i am really not a "people person"!
I think if we do mention specific back-ends, especially if they are owned
by Oracle, then it is well worth pointing out other names. It's not about
fanboyism, just about showing there are a wide range of choices - and that
people might well already have a database (or even spreadsheet) that can be
used without any export-import conversions. It is VERY good to know that
use of internal back-end can be externalised fairly easily without having
to go through all the troubles Ian Whitfield went through. On the other
hand his move away from Java-based back-ends probably gave additional
benefits!
I definitely appreciate Andreas' posts in this thread! He has cleared-up
several mysteries by explaining the problems "under the bonnet". It has
also been good to see experienced and knowledgeable people giving anecdotal
confirmation of Andreas' points.
In answer to Jay's question there was some attempt to move to using
"Firebird" rather than "HSqlDb" but i think that is still an "experimental
feature" and that we now effectively have a choice of 2 internal back-ends
neither of which work entirely as hoped for yet. With Firebird it feels
like it is "on the way" though.
Regards from
Tom
Hi Tom,
Interesting. Given similar, huge challenges, would you see areas of
cooperation with Kexi?
Much of the e-mails on Base have focussed on negative aspects. It is worth remembering that for moderate size data bases (a few thousand records, a dozen tables) it is perfectly adequate. I have now converted all of my data ase applications for MS Access to run on Base with its internal data base. All of them perform better than they did using MS Access. I would not regard either Base or Access as suitable for a large multiuser data base application. The only linux based large data base I have developed I used MySQL with a web based front end using php to interface to the data base.
Regards
Peter Goggin
Hi
I don't see this thread as having been entirely negative at all!
Base is pretty fantastic despite having so few devs and being the most
unpopular module/program to work on out of the whole suite. The devs who
are working on it are fantastic and heroic imo.
The only real problems are when people try to use Base in the way they
would use Access. Access does quite a lot of very dodgy things that most
database programs steer well away from. For a start having the front-end
and the back-end as 1 file is just asking for trouble for reasons which i
am not quite clear on but has been described in previous threads. My pet
hate is that normal users are presented with the unfamiliar interface. So
even if they just want to browse through records they kinda need training
and that training usually involves just how to design, create and build a
new database rather just how to flick through records.
Even with training it is all to easy for normal users to accidentally (or
otherwise) make a hideous mess of things. The "contacts" database at my
work-place was such a hideous mess that even printing address labels was
practically impossible. I didn't have time to go through all the hundreds
of badly named Queries to make any sense of them so i was never sure which
could be deleted and which were crucial. So i had to make yet another new
one in order to avoid getting bogged down for days in a fairly simple
task. When i got back to the database a week later someone had renamed my
Query and the Report so i had to do a bit of detective work to find them
again. Nowadays no-one uses that database at all. The company has lost
track of tons of contacts who may or may not have been useful. Nowadays we
use a simple csv to track only the email addresses and we no longer do
mail-outs at all.
Base neatly avoid ALL that can be neatly avoided by using Writer or Calc to
create "Reports" or "Forms" so that people who are not into building and
designing databases are safely in familiar programs/modules.
So normal users can still do simple edits, such as correcting spellings or
changing the company letterhead and such-like but they do so in a familiar
environment without having to learn tons of stuff they will probably never
need. They can even create new documents based on the existing ones.
At no point would they accidentally find themselves in "Desgn" views or
accidentally creating Queries.
So for me Base, Kexi and pretty much everything non-Microsoft has huge
time-saving advantages!
Regards from
Tom
Hi
Oops!! Sorry for the rant!
Also the tpyos in the 5th paragraph! (4th from the end!). I should have
deleted some of it (or the whole email tbh) down to;
"
Base neatly avoids ALL that nightmare by allowing database-designers to use
Writer or Calc to create "Reports" or "Forms" so that people who are not
into building and designing databases are safely in familiar
programs/modules.
"
Apols and regards from
Tom
Hi
I am not quite sure what level of co-operation you are asking for. There
is always a potential for more, of course.
At the moment, and hopefully on into the future, they are independent of
each other and each has their own advantages and peculiar quirks. They
gain the advantages of competition while being able to co-operate too.
Both have far too few devs imo but the devs in both are pretty heroic.
More devs in either or both might make things interesting.
At the moment both can connect to a wide range of back-ends. Also both
could be front-ends for the same back-end. Creating 2 different front-ends
might be annoying but it seems to be very possible.
Regards from
Tom
My first medium complex project with input forms to collect daily job
data used the embedded DB as well. It worked flawlessly. But the most
important preconditions is that you really do your backup every time
after closing the connection.
Nevertheless, there are far too many reports about complete data loss.
Meanwhile it is very easy to split a self-contained Base document into a
frontend and a stable backend which can be distributed and installed on
multiple machines with a tiny little bit of extra effort. Apart from
keeping your data safe and warm, HSQL 2.3 provides a lot more features
than HSQL 1.8.
Hello,
I am an "old" DB-User in the real sense of the word (I am over 70!).
In the 90ies I got into DB2 as a systems engineer at IBM. Then, around
the turn of the millenium, I set up a database for the administration of a
50-piece wind band, using Lotus-Approach (DBase...). It was fine
but I wanted to go "Open Software" and - when stumbling onto
StarOffice/OpenOffice and Base - it was clear to me to go for that
"scene". Since then I have been using MySQL as external back-end
and must say I am more than happy with it. My DB consists of some
80 interconnected tables/views with record numbers up to around
40.000. This is handled perfectly fine by MySQL (maybe MariaDB in the
near future!).
Of course - as an "old" DB-guy I have no qualms about using the
command-line mysql client directly for doing things like defining
DBs, tables, views, foreign keys etc. Therefore, if there are any limitations
in the LO-front end, it is o.k. for me.
I do feel strongly though, that if we ever want LO to become a REALLY
important player (especially within the business world!), a stable, scalable
interface to REAL databases (with sometimes millions of DB-tuples) will
have to be implemented. Internal, integrated backends are o.k. for
playing around but NOT for mission-critical, large-scale operations.
Regards
Heinz
Tom Davies schrieb:
Hi Heinrich,
I've been reluctant to join this discussion, but you comment about the need
to have "... a stable, scalable interface to REAL databases (with sometimes
millions of DB-tuples) ...", has prompted me to say that I believe one such
database already exists - it is called H2. See -
http://www.h2database.com/html/main.html.
Some will perhaps reject it out of hand, because it is Java based. However
it has a vibrant user base and from comments on the user group, some are
using H2 for very large databases. A year or so ago one user was
complaining that H2 was slowing down after his application passed the 1
billion record mark! In reply, he received several suggestions as to how
he might over come his problem.
I have migrated 6 databases from HSQL 1.8, (the largest having nearly
35,000 records - which I realise, is still quite small), but I have found
that H2 works well for me. There was a bit of work involved with the
migration, but H2 tables can be designed in LibreOffice and the process
went pretty smoothly. Perhaps the only drawback is that once tables have
been designed, they can be altered only using SQL commands. But I guess
most users who want an industrial strength database, would already be
literate in SQL.
My 2c worth,
Noel
Hello Noel,
Interesting! I will have a look at H2. The only issue for me at the moment
is that my provider has not got it installed and therefore I cannot use it.
Regards from Salzburg
Heinz
Marion & Noel Lodge schrieb:
Hi
I think Noel (& Marion)'s good experience of H2 being sooo much better than
Base is purely down to them moving away from the internal back-end in
Base. The version of HsqlDb mentioned by Noel is the 1.8 which Andreas
identified as being the version used as the internal back-end in Base.
People using HsqlDb as an external back-end would have been pushed into
upgrading many times in the last decade or so. Not upgrading would have
been a lot like sticking with Win98. So i believe it was the move to a
more modern version of a database program that gave Noel the fantastic
improvements he experienced.
Java-based back-ends do have a reputation for being much faster for the
relatively small databases that most of us probably use. The one with 1
billion records might well find that moving to something heftier such as
Postgresql or MySql/MariaDb does the trick.
Although there might be some performance advantages to moving from the
heftier back-ends to the smaller&faster ones there are several
disadvantages around doing the move. Internet facing Servers using LAMP or
WAMP and web-hosting companies tend to already have MySql/MariaDb (hence
the M i think) so it'd be a bit like installing a different Office Suite
for each document rather than trying to stick with just 1 or 2.
Regards from
Tom
Hi
One point that may have become muddled in translation is that it is only
the front-end that needs to be copied, or re-created on multiple machines.
The stable back-end could be on a server so that everyone is using the same
data. As one person adds data everyone else would be able to see that data
on their various machines.
So Base is a LOT more scalable much more easily than Access. On a single
machine you probably keep the back-end on the same machine as the
front-end. If other machines get added then they just share that same
back-end. As the company grows and eventually needs it's own internal
server that back-end might get moved to that server but all the front-ends
on all the different machines still keep using that same back-end even
though it's been moved.
Hopefully Base can even connect to back-ends hosted on WANs rather than
just LANs and even on remotely hosted websites and Clouds.
Regards from
Tom
Hello Tom...
Maybe I misunderstand your remark below, but...
My database has for years been on a WAN-accessed MySQL
server. This is only an issue resolved through DNS or the hosts file .
I haven't experienced any difficulties/problems either in defining the
database, loading or backing up data to my local machine (either
through phpMyAdmin or the native command-line client). The only
issue I have at this time is that scrolling through tables/views under
LO tends to be slow. I'll have to look at that...
Regards
Heinz
Tom Davies schrieb:
The problem is _not_ a particular version of a particular database. If
HSQL 1.8 works for you, even that version works as a rock stable, fast
and reliable backend to your Base document.
Heinrich demanded an _interface_ to handle the big irons. He was not
talking about a particular version of some particular big iron. He did
not tell us what is wrong with the current interface. From the view
point of database developers, there are several missing features. From
the view point of someone who wants to build a serial letter from his
spreadsheet, Base is the big and dark mystery to be avoided entirely and
the serial letter wizard does not mention that one of its products is a
Base document together with the serial letter.