Base Help Anyone

users
#1

Hi
Libre Office
Version: 4.2.7.2
Build ID: 420m0(Build:2)
English
Base
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=14.04
DISTRIB_CODENAME=trusty
DISTRIB_DESCRIPTION="Ubuntu 14.04.1 LTS"

I am new to Linux and therefore Libre Base and have been searching for help !!
I posted this on the Libre Office THe Document Foundataion but am still waiting for a reply.......

I should be grateful if you could give me an answer to my question before I waste any more time..

I
have been using Base to provide a database for my paintings via a
simple form. I recorded 200 records and then added the facility to
include a photograph .jpg file and this is when the gremlins started to
appear.

It constantly issues error messages when adding data
and causes me to endlessly loose work as it has failed to write to the
table from a form for no obvious reason I can see, sometimes I can add a
new data or several set/records on the form OK and then it will fail
with the error message as follows where the Table1 is my Table

S1000 General error java.lang.NullPointerException in statement [update "Table1" SET "Photo"=? Where ID=?}

Is is a know issue that it is unstable ? In addition I set auto save and backup yet fail to find them anywhere.
Any help will be very appreciated and I am ok to provide my database for analysis if required by genuine helper and would pay on results if necessary.

cheers
alan

Alan Pedder
Mb. 0781 844 5906
alanswatercolours.co.uk

#2

Hello Alan. I haven't used Base to store binaries however I have monitored
this list for a while and used Base for a few small projects.

I expect the two primary recommendations you will get are...
1. Don't store data in Base, use a separate database back end because the
embedded HSQL is not very stable. To that end I've recently found the
native MySQL connector works well for me (never had reliable connections
with the Java or ODBC connectors).
2. Don't store binaries in Base, store a path/pointer to the binary. Keeps
the table size down, minimizes loss if the db becomes corrupted.

#3

Hi :slight_smile:
It is only the internal HSqlDb back-end that loses data. If you get the
proper HSqlDb from their website then it works completely fine. It's
designed to be extremely fast for fairly tiny databases, like almost any
address book.

So, it's only Base that loses data. Outside of LibreOffice and OpenOffice
losing data is a heinous crime, especially in a database!!

So the main thing is to migrate existing tables into an external back-end
and pretty much any external back-end will do. Generally any back-end is
better than the Base one. Postgresql might be the easiest. MySql/MariaDb
are often used.

Regards from
Tom :slight_smile:

#4

Hi Alan,

Is is a know issue that it is unstable ? In addition I set auto save and backup yet fail to find them anywhere.

Yes, unfortunately a known and frequent phenomenon with the default
provided embedded hsqldb implementation.

You might get more mileage out of it by upping the memory available to
the Java process that is used - there is a setting for this that you
would have to manually add to the script or properties file (these files
are to be found within the ODB file, which is simply a zipped container
for the database setup). Read the hsqldb 1.8 documentation before
fiddling with these though.

Autosave doesn't work for Base documents, if it produces anything at
all, then it will be a series of 0 byte files.

This is currently bug 71550.

Any help will be very appreciated and I am ok to provide my database for analysis if required by genuine helper and would pay on results if necessary.

No need to provide it here, your database is probably corrupt judging by
the symptoms you are experiencing. I hope you have some kind of stable
backup or copy of the ODB file somewhere else that you can work from, or
at least re-purpose, in order to connect your front end (forms, queries,
etc) to a more reliable backend db system.

HSQLDB is reliable enough outside of the embedded scenario defined as
the default in LO Base. There are tutorials in the OpenOffice.org forum
and the LO Base Handbook which explain how to set up a split hsqldb
database.

Alternatively, people use a variety of other db backend engines :

SQLite
H2
Postgresql
MySQL / MariaDB
Firebird (not the embedded version that ships experimentally with LO,
but the standaloe server version)

Obviously, not all engines will be suited to what you want to do, or are
willing to administrate, etc, so it would be best to do your homework
beforehand to make up your mind.

Alex

#5

Hi,

Store all picture files in the same directory as your Base file.

Call menu:Tools>SQL and execute:
ALTER TABLE "PicTable" DROP COLUMN "Pics";
ALTER TABLE "PicTable" ADD COLUMN "Pics" VARCHAR(100);
SHUTDOWN COMPACT;

where "PicTable" should be replaced with the actual name of your table
and "Pics" with the actual name of the field in that table.

The database will be defragmented and is no longer accessible. Restart
the office suite, open the database and your form. A double-click on the
picture control still opens a file picker dialog. But now it stores only
the file name in the database field and loads the actual picture data
from that file.

If you want to convert this embedded HSQL database to a "real" database
follow this description:

https://forum.openoffice.org/en/forum/viewtopic.php?f=6&t=62905&p=278742#p278742

Hope this helps.

#6

Hi again Alan.

Sorry to jump in the deep end with my reply. A database is typically used
by a very small number of people compared to word processing, spreadsheet,
etc. As a result I didn't even think about holding back the jargon in my
reply.

Seems a number of people have given answers that should help you on your
way. Regarding your questions here...
1. Yes
2. Yes, that is one way the table can be viewed. Like looking at the table
in your database. Just keep in mind it is not a spread sheet and so even
though it looks similar it does not work the same way.
3. MySQL *is* the database. LibreOffice Base is the program that shows you
the table that is MySQL gives it. Kind of like your web browser lets you
look at web pages. Each web site has its own web server and gives the web
page to your browser to display for you. MySQL is the server and gives the
table to LibreOffice Base to display for you.
4. There may be. I don't know of them. Hopefully other respondents can give
an idea what options there are and how stable they might be. (Presuming you
envision a "single product" answer where the whole thing can be managed by
a single program as with Base and Access.)
5. Andreas provided that answer. If you missed it he suggests VARCHAR(100).

#7

Tom, I'm not entirely sure, when you said

So, it's only Base that loses data

that you properly comunicated what you meant. What did you mean?

I would absolutely agree with the comments made about not storing binaries in
the database. It's not a good idea, whatever database you use.

And don't use the standard "HSQLdb included" setup, it's really too prone to
errors.

Regards
Mark

#8

Hi :slight_smile:
When people use Base with the internal back-end they have sometimes
reported data-loss. Other database programs do their utmost to avoid any
data-loss.

On this mailing list we tend to push people into using external back-ends,
as a priority, even if their specific Base-related question has nothing to
do with that issue. People here have helped at least one individual
migrate their tables from the internal back-end to an external one in
step-by-step detail. I gather people now have a faster route, or
documentation to help people with that migration, making it much less
painful. The last couple of times the individuals seemed to find it
surprisingly easy.

One of the massive benefits of Base, imo, that makes it vastly more
powerful than Access is that it makes it so easy to connect to a wide
variety of external back-ends and tries to do that by default. Access can
be twisted into doing it too but makes it much more difficult. This makes
Base highly scalable and suitable for a huge range of very different
scenarios and much more future-proof than Access could hope for. On the
other hand it makes it a little more difficult to understand.

The marketing team don't seem to quite grasp that simply 'selling' Base as
being much like Access is actually quite damaging and completely misses the
huge advantages that Base offers.
Regards from
Tom :slight_smile: