User’s experience with Openoffice (Libre) Database called “Base”

As a thoroughly convinced supporter of most products of Openoffice I regularly use the spreadsheet for all kinds of things. So, when I wanted to analyse some historical sites I naturally started by importing a CSV (comma separated variable) file into a simple spreadsheet. This gave me the basics of name, location, etc.

However, I realised very soon that because I could not afford to visit all the sites, I was severely limited in what information I could get. And therefore I had to rely on: images available on the web, descriptions (often only a few words about each location), maps, aerial photography and streetview.

Most of this information is visual, so for each site I wanted to have stored alongside the text descriptions and “yes/no” about what features were at a site, the original source photos. And whilst I have copy-n-pasted images to a table many times before, it has always been problematic.

Paradoxically, although you’d think storing an image with data would be easy in calc (spreadsheet), my experience is that it is best stored in openoffice writer, as it copes much better with resizing to fit images than openoffice calc . In writer (when you remember how?) they stay in the table, but in calc, it seems like the images have a life of their own and very quickly I find odd images all over the place that no longer “belong” to any record. (Maybe that’s because you inevitably try to sort tables – and the images get “left behind”).

However, openoffice tables may be better formatted and easier to work with when images are present, but they are much more difficult to use when the data extents over several screen widths. You gain because the tables expand – but that means that very quickly the table is absolutely enormous if any of the fields are large. And that is what I had, images + several screen widths of columns. Also, it’s a real pain when you have odd data for example data that is really a choice of “OPTIONS” (small, medium, big, mega).

BASE

Realising that the project to analyse the data could take up to a month, I reasoned that it was worth spending some time optimising the method of recording the data. From my previous experience with Access & MySQL that was clearly a form based approach with a database “table” (aka spreadsheet) sitting in the background being populated by the form.

So, whilst I’d tried and failed before, I thought it worth taking a look at the Openoffice base product because my application was SO SIMPLE. My reasoning: “how could it fail to do something so simple?” (Well it did)

Obviously, I’d had a look at base before, but I wasn’t impressed because it seemed to lack all the features that I had found so useful in Access. However, because I didn’t need complex features and it was mainly a question of “scale” and efficiency of data entry and I didn’t need a lot of complex features, I decided to give it another go.

Now, the reality is that when you open base, there isn’t a lot there which is immediately off putting. Indeed, there is no option for importing or exporting data – which was the first thing I had to do. And it drives you potty when you can’t even start a project without reading loads of help forums to find the answer.

It turned out that “all I had to do” was copy and paste. I put that in quotation marks, because it’s almost unusable as a feature. Yesterday (as I tried to migrate out of base) the only way I could copy my data out of base was to another version of base connected with MySQL, was that I had to copy each table, close down base, open it up again, then paste, and close it down for each and every table (it keeps trying to paste the same data no matter what you do until you force it to clear it’s memory by closing it down).

And why was I trying to store my data in MySQL – because base crashed the first day I used it, wiping out my entire database (and there is no automatic backup as in other products) and it has crashed regularly ever since. Fortunately, I managed to recover the form, which was paradoxically the main man-hours of effort. But it has crashed almost every day I’ve used it. And for the simplest of things … like just dragging a box around on the form designer …. CRASH … and not only Based but the whole of open office goes and at that time it included work that had not been saved in writer.

So, in case you want the short answer to whether to use base, I can’t honestly recommend it to anyone for any purpose.

Recommendation

NO – DON’T USE IT

The main reasons:

  1. It crashes so frequently that you couldn’t use it for “ephemeral” data – data that is lost unless recorded, like recording who has called on telephones (unless it was also recorded on paper or could be recalled in some other way).
  2. It crashes so frequently that you ought to be backing it up EXTREMELY frequently. So frequently that you have to regularly stop entering data purely to close down base completely to ensure the data is up to date (save does not save – instead the data is not secure until base is closed down). And then copy and paste the old database to a new file (outside base). Indeed … I’m so sceptical about Base destroying data, that I even keep copies in other folders.
  3. There is no proper way to import & export data from Openoffice/Libre** base database. Yes, you CAN copy and paste, but that is not what most reasonable people mean by export/import, and even if it did, the software is so ropey & buggy that you couldn’t hand it to your computer illiterate person with instructions and have any hope they could import or export data using copy and paste. And for example, when I did copy and paste to excel – I ended up with a complete chaotic mess of formatting. You know there’s something severely wrong with a product, when after several hours trying to work out why you can’t copy and paste, you are elated to finally get your data out of base into something safe.
  4. I assumed I could easily connect base to other products. For example, I assumed that I could easily use a calc spreadsheet as a table. That may be possible (so opaque is the help), but I’ve not found out how. Moreover, given the way base destroys its own files, I wouldn’t trust it to retain the integrity of anything it was connected to.
  5. There is no meaningful help – and issues that stop users being able to use the software are routinely and quite ridiculously marked “SOLVED” when there is clearly no solution for the user.

Biggest bugbear – is that every time you have a problem and search online, you’ll find some idiot has marked it solved

It’s not just annoying – it’s actually clearly the reason why base has never got beyond a rather ropey “try-out”. I’ve been looking online, for solutions to the various problems I’ve encountered, and in almost every instance, I’ve found posts marked “SOLVED” when in fact, the problem still exists, but some idiot software programmer has decided that because it doesn’t affect them (they live in some ephemeral world where such mundane issues don’t affect them), it’s not a problem. Despite the fact, that these problems are blocking people like me using base.

And that’s why it’s not maturing – because any reasonable person (i.e. not a software engineer) who tries to use base, will very quickly encounter a problem which is a blocker to them using base, but which some ignorant software programmer has deemed to be “not a problem therefore SOLVED” – and therefore never will get solved.

SOLVED MEANS THERE IS A SOLUTION

And it’s that attitude why I’ve not bothered to raise any of the numerous issues I have on the openoffice problem reporting system. And if I feel like that, then clearly many other people will feel the same. In short, there’s no point spending the time documenting issues, because they will inevitably be marked “SOLVED” as if the user’s quirky needs to have a working product is the real problem.

It’s pretty clear that I’d be wasting my time, because each and every issue blocking my use has ALREADY been marked “SOLVED”. The reality is there’s no solution, it isn’t solved, it’s just that marking an issue “SOLVED” means they’ve said they’re not going to work on it.

And do I sound cross! Sure I am … mainly because it’s nearly a pretty good bit of software, which but for the attitude of those dealing with the serious problems blocking users, would have turned into a fantastic product long ago.

The simplest issues are the biggest issues

To explain, I finally had something working and was starting to add locations, and as I process each location I’d realise there was some additional data that I could be capturing. As a result, I was constantly adding new fields.

Well, as you can imagine, the database was a bit of a mess. So, naturally when I was starting to lose fields, I went to tidy them to for example put all the tick box fields together, likewise the short text descriptions.

But no!

After several hours trying to work out what I was doing wrong and why I couldn’t change the order of the fields I read some snotty comment saying “ISSUE SOLVED” -in which they basically said “we don’t think you should be changing the order (because we don’t work this way) so we’re not doing anything about it”. Well, it was a major issue for me. I’m not the tidiest worker, but when I start losing fields, I NEED to tidy up and there was no way to do it within base.

(You can copy and paste – but I can’t tell you the process because it usually goes wrong when I try doing it and I have to repeat it many times before I get something I need. The big issue is “auto-increment” fields. I think my strategy is to create an empty table, then change the id to auto-increment – BUT KEEP SAVING – because closing without saving causes issues. And then I past in the data – and live with the fact that the ids no longer match if I deleted ANY records)

It clearly wasn’t solved. Instead it was an open issue that someone (for their own reasons) had decided they didn’t want to work on so even though there was not solution they falsely marked it “SOLVED”. I can tell you as a user IT IS A MAJOR EVEN CRITICAL ISSUE. I say critical because except for the fact I had multiple images, I would have given up at that point and gone back to the spreadsheet (as I have several times before when I gave it a shot years go).

And it crashed, and crashed and CRASHED. So, when I realised that I might need to duplicate the database because the locations were of two distinct types, before starting the new database I thought it would be much better to start it on a stable platform like MySQL. That way, the vital data would be SAFE outside base and base should be a lot more stable if it only contains the forms which were not changing.

So …I tried to migrate the data to MySQL. And that seemed to work a charm (except for needing a 32bit driver not the 64bit that was default), until I tried to run the form with a subform.

Let me reiterate what I’m doing … I look at each location, I add the data for a location (often just a sentence and a few tick boxes for features I can see) , then I add the pictures into a sub-form (which are stored as a simple filename – so really really simple).

But after around 6 hours searching last night – it appears that this absolutely simple application – involving just two table (just one more than a spreadsheet) is not possible in base. A form with a subform will not work with MySQL.

Now, I could easily do this on a webpage with PHP & MySQL – create a form with a subform that automatically adds new records for each new image. But I know that before I can store the data, I have to explicitly create the necessary new record to hold the image data.

So when I read numerous problem reports on the issue, each one marked “SOLVED” suggesting that it’s “not a base issue”, I knew this was TOTAL TWADDLE.

The issue is clearly and unequivocally with base, because if I were writing a Php form on a website and MySQL wasn’t doing what I wanted it to do … I wouldn’t even dream of saying “ISSUE SOLVED BECAUSE IT’S NOT MY WEBSITE PROGRAM THAT’S WON’T WORK WITH MYSQL“. The issue remains, and instead LIKE ANY DECENT PROGRAMMER I have to find a way to get my PHP code to work with MySQL. And likewise OpenOffice/Libre base has to find a way to work with MySQL and not the other way around. And until it does work properly, the issue is in no sense of the word “SOLVED“.

(Skip if you want)
To explain, the issue is this. In the table of locations, I have an entry for a location. But in the table of images, until I add an image for that location, there is no entry for that location. In base, you “simply”(?) add the image to the subform (using an obscure right click … which took hours to find). However, it does work when you store the data in MySQL. And the reason is because the base “backend” will implicitly create an entry in the image table to store the data for the image, whereas MySQL needs to be told explicitly to create a new record.

To use a very simple analogy … if a teacher says “now write down in your text book the following”, it is as if base is saying “not possible … because you’ve not told me to go to empty page”. The teacher doesn’t explicitly tell the students to find an empty page, because that instruction is implicit. However, it also means that when base has a problem, because it implicitly creates a new entry – I once found 30 “images” which had been created when the form froze and I kept hitting buttons trying to make it do something.

So, MySQL needs to be told explicitly to create a new record for each image, whereas base will do it implicitly if you try to save data from a image subform where there isn’t a corresponding record for that location in the image table. In other words, they get away with not implicitly telling the database what to do, because on their own ROPEY database it liberally creates records (… andeven when not desired).

Other issues.

There are just the quirks – many times when editing the form … boxes would move for no apparent reason. Or it proved impossible to place icons on the form except next to the left side.

And it struggles with just 2000 entries in the table. I open a table and move to number 937 and it sits there with a whole page of a table apparently of the same entry. I don’t know if it’s crashed, but I’ve never been able to wait long enough to see any change.

When I had the numerous extra entries in the table, I tried getting rid of them by copying and pasting rows that had been entered later (so that the indexes would be sequential). You can’t copy and paste rows. Ok, this reflects the behaviour of most databases using SQL – so to a database nerd this might seem obvious it won’t do that, but from a user’s point of view where you’re used to NORMAL behaviour in spreadsheets this is unacceptable.

I’m going to restate the REALLY ODD way of entering image data of right clicking. That’s so not standard that it’s totally unituitive.

Queries -I expected to be able to create a query that simulated an insert without actually doing the insert new data (to check it was picking the right records) and then make a minor adjustments to the form based query to cause it to insert the same data. It turned out that not only was it impossible to use the form based design for this, but the SQL command was so different that I was “coding blind”.

I could go on …

Pros

If only it weren’t impossible to use practically, it has the potential to be a fantastic bit of software. The form wizard is good, I love being able to display and image on the form with (almost) ease.

If it weren’t for the fact that no sensible person should ever consider using it because of the crashes, the failure to provide the basic features for ordinary users and the inability to the simplest database application (form+subform) to work with my MySQL I’d say it’s a fantastic piece of software.

Summary

If I knew what I knew before I started I wouldn’t have started using Base. The database (or perhaps associated code) within Base is a load of crap, being unable to cope with a fairly small database of 2000 entries. It constantly crashed requiring EXTREMELY FREQUENT closing of base (you cannot save from within base) and then copy and pasting of the file. Despite assurances to the contrary, I cannot see any viable way to use another stable database like MySQL and retain any functionality for subforms – which means that for almost all users it has no more functionality than a spreadsheet. And if you ever did get to the stage of storing a lot of data – it is extremely difficult & quirky to get it out of Base.

And the reason there’s such a potentially fantastic bit of software that is practically shite is clear – it’s because the people/person who controls the “issues” system for the software dismisses anything and everything they personally can’t be bothered to deal with as “SOLVED” leaving users with no solution for issue that block their use resulting in a crap piece of unusable software.

So, what is the alternative?

As someone who thinks Micro$oft is a load of shite and what I think is their illegal monopoly is the main reason that computers have hardly progressed for the last 40years and I avoid them like the plague (well I always look for an alternative and will use it in preference) … unless like me you have the option of using MySQL and php. The problem for most ordinary people is that most “databases” seem to be written by nerds for use by nerds. Whereas what I think most ordinary people need is software that has the bells and whistles you don’t have in spreadsheets and wordprocessors … and I’ve yet to find that anywhere but access. (That’s all I’m saying)

As for me … I’ve got about 50 locations to finish in Scotland. But if I were to go further I’ve got about another 2000 elsewhere and perhaps several thousand more if I were prepared to extend it even further. I will live with the constant crashes for the Scottish 50, but that is the end. And before I consider going further, I’m moving to Php-MySQL. Because it will be quicker & more reliable to create my OWN APPLICATION rather than using base.


**Openoffice/Libre in the hope that it was only an openoffice problem, I downloaded a version of libre. As far as I can see they are EXACTLY the same product with only minor cosmetic differences (changes to icons).

 ADDENDUM

Just as an indication of how potty this software and “help” is – I found a forum where people were regularly posting “help my database has crashed and I’ve lost everything”. Someone would then say “please Private message me and I may be able to help”. But rather that post any SOLUTION – the people running the “HELP” forum told the only individual who was actually providing any help to anyone to stop offering their help to individuals. And it’s even such a common problem that there are also software packages designed to recover data. So, in a way I was lucky my system crashed so early, because I wasn’t led up the garden path to have 100s of manhours at jeopardy when it inevitably crashed WITH NO HELP ON OFFER.

ADDADDENDUM

And as if to prove me entirely right that each and every actual issue raised is just automatically marked as resolved:

https://bz.apache.org/ooo/show_bug.cgi?id=127622

Peter <leginee@gmail.com> changed:

What |Removed |Added
—————————————————————————-
Resolution|— |NOT_AN_OOO_ISSUE
CC| |leginee@gmail.com
Status|UNCONFIRMED |RESOLVED

— Comment #1 from Peter <leginee@gmail.com> —
Hi Mike,

This is a developer tool with developer focus. So the focus is on code change. Often we can not reproduce the issue. We request then that if you have the issue that you improve your analysis. If you lack the skill we have a forum and a user mailing list that help you. You can also query the dev mailing list for analysis. But the person that must do this is you. Because we do not have the issue, we massively lack information to improve our code. Plus we do not have the resources to handle all these cases here. So we close the issue and ask people to go to the forum and or the users mailing list. You can always reopen an issue if you have additional information. We welcome this. Now I will set this issue to resolved and not_an_ooo_issue because wherever the discussion goes it will not end in a code change. And this is the tool that tracks mostly only code . I hope you understand this. (Resolved is just a mark we think we can not do anything on this now. The second state is far more important. Fixed means we think this should not appear again, irreproduceable means we acknowledge the issue butare unable to fix, not_an_ooo_issue means solution is beyond the scope of this tool, won’t fix – we will not change something. Obsolete- this issue is old and the report is out of date. Please renew the information. If you still have the need to talk you can write a mail to dev@openoffice.apache.org . In order to improve the analysis:forums you find at forum.openoffice.org and userlist users@openoffice.apache.org. If you want to help write a mail to recruitment@openoffice.apache.org I know this is sometimes frustrating.

You have also not given your case, so I did not have a second look.

All the best Peter


You are receiving this mail because:
You reported the issue.

This entry was posted in Climate. Bookmark the permalink.

8 Responses to User’s experience with Openoffice (Libre) Database called “Base”

  1. Peter Kovacs says:

    Hi Mike,

    I am no native speaker, so please take my apologies for any misunderstandings.

    Would you please post examples which issues have been unjustified resolved. The word resolved is not good, but so far we have no better word for it. And we need one that describes all cases. Succesfull or not we need to mark that we can not support the request.
    If you have an idea for a better description then please let us know. (Email: dev@openoffice.apache.org)

    I understand now your issues. I will check if we have those issues already noted. If so I post the links so you see how stuff is addressed in bugzilla. Please bear with me I need to find some free time for this.

    You just addressed the wrong place for your request.

    All the best
    Peter

    • Scottish-Sceptic says:

      Peter thanks for the post. First, I appreciate that there’s an awful lot of hard work goes into software like this. However, it’s a real shame that consistently whenever I’ve had a look I’ve found I couldn’t use it.

      Anyone can have specific issues – so the specific issues are not the “blocker” – but when I began reading time and time again the same kind of response “resolved” to issues that were still current for me, it was clear that there was a mismatch between what the software guys consider resolved, and what is actually still real issues for users. From there is was a short step to realising that if I raised the same issues WHICH ARE CAUSING ME TO GIVE UP ON BASE – I would very clearly get the same response of “Resolved” – and be wasting my time.

      And I know how difficult it can be to see the software from the user’s point of view. I used to work for a major photocopier company on a team dealing with the software faults. And one day, an issue came in which I recognised as having caused me great angst before I joined the company (the MD had asked to to try to work out how to deal with a paper jam – but following the instructions didn’t work). And it had simply been dismissed out of hand by the software guys as “not something we think is important because there’s an easy work around”. Yet despite the fact the company had probably lost orders because of this fault and I could testify it was a problem – the software guys just would not accept it was a major issue.

      However, since you have replied, I will try to document what I can.

  2. oooforum says:

    Where is the problem? If you want use MySQL, use it.
    Indeed, you can connect Base with this extension:
    https://extensions.openoffice.org/fr/project/mysql-driver-apache-openoffice

    • Scottish-Sceptic says:

      The problem is that I would never have considered migrating to MySQL if openoffice base wasn’t constantly crashing and destroying my data.

      I did eventually get the subforms to work – but I’ve no idea how. Like Harry potter against the troll it was shear dumb luck. I don’t like software that relies on luck to work.

      • oooforum says:

        Why do you talking about migrating?
        Just using Base with database like MySQL or another one.
        I don’t reproduce what you described. Base works with no crash for me.
        Instead of this long diatribe, you should provide an ODB to reproduce.

        • Scottish-Sceptic says:

          I’ve said why, it kept crashing and destroying the file so the data was unrecoverable. Even things as simple as dragging a text box around a form would cause a crash – not only on base but it would bring down the rest of openoffice.

          The problem with MySQL miraculously disappeared. I’ve no idea why, so I don’t know whether it will miraculously reappear. That means that it is always possible that at some point I’ll be locked out again

          I can’t put a lot of effort into using something that at any point I could suddenly find I’m unable to use it.

          I can’t easily export data, I can’t see any way to automatically backup data. It’s extremely quirky about when it does and doesn’t save – so the only way I can be sure everything is saved is to totally shut down base.

          The database is now a mess because of a lot of spurious entries at the end. I can’t find a way to easily remove them. I can’t easily export the table in SQL to rebuild it. If I export it in other formats for some strange reason it doesn’t use the same datatype.

          I was struggling to cope with importing and exporting data and dealing with all the multitude of quirks and constantly having to remember to backup … when I was working at it day in day out. There is no way I’m going to remember all I need to come back and use it effectively in six months time. It is INCONCEIVABLE for me to make up something for a friend with no idea about databases – it would almost certainly end up with them phoning one night to say they’d lost weeks of work and could I do anything to help. AND the answer is almost certainly no.

          So the present position is this: I’ve already spent too much time just trying to get the database working. I finally managed to get a copy to Mysql – but by then the time and enthusiasm I had for that bit of the project had disappeared.

          And the main reason for the above “diatribe”, was firstly to document my problems, but mainly to try and work out whether I had any viable options that would allow me to proceed with what I was doing.

          ALSO ON RESTORING DATA BY COPY AND PASTING – **WHICH IS ALL YOU CAN DO WITHIN BASE***
          It seems impossible to restore something with an auto-number id. It would often just refuse with no explanation. Then it seemed that if you saved it at some point that it might do it … but I’ve no idea whether it was always the same point or it was a quirky bug because I couldn’t consistently add auto-numbers, except by creating an entirely new id.

          That kind of thing might be acceptable for a database of a couple of dozen people – but I needed to have a consistent id to link tables of many thousands.

          If I could save it as SQL – then unless the software is totally crap – I should be able to restore the database through base knowing it was exactly the same.

          And I can’t organise the fields in the database so they are grouped in any sensible fashion. I gave up documenting the field because I was having to recreate new tables so often. So, if I come back to this in even a few months the whole structure is such a mess with weird datatypes, no documentation, a complete disagree of where fields are so there’s no rhyme or reason – I won’t have a clue what I’ve done or the exact meaning I was intending for the fields.

  3. Scottish-Sceptic says:

    To ooodrum … I take it you have a fairly low opinion of users.

    If I were in your place I’d
    1) express a bit of sympathy for the huge problems the software caused, but explain it’s all volunteers, etc.
    2) have instructed the user how to for example turn on the error log
    3) encouraged them to have another go … perhaps focussing on some aspect or trying some change suggested by your vast knowledge of the software … to help you out.

    However, whilst I know users and their problems are a real pain in the backside, I have found that insulting users … whilst it might make your life easier by making them go away and shut up … it does nothing to make your software any better.

Leave a Reply to Scottish-Sceptic Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>