Support of pictures in LO Base with the direct MySQL connector

[Jean-Paul1]

Hello,

I develop an application in LibreOffice Base.
The database itself is a MySQL database.
I use MySQL WorkBench to design the tables, relationships, views and so on.
I use Base mainly to create forms. Base is connecting to MySQL with the direct MySQL connection (for performance reasons).
Multiple end users on multiple PC’s have access to the data, only through the Base forms.
Everything is working smoothly since years.

I now get the request from the end users to add pictures to one of the tables.

It appears that Base does not support pictures well, if the connector is the direct MySQL (it is supported better through the MySQL JDBC connector) : I can add/update the images (as longblob) in MySQL WB, I can see the pictures in the Base forms. But when I change them in the form, the change is not saved; even worse, the longblob is emptied !

I am running Windows 10, LO 7.2.2, MySQL 8.0.27.
Going back to the MySQL JDBC connector is not an option, beacause the performance drops enormously !

Any help ?

[Villeroy]

If you store the pictures as file in the same directory or in a dedicated subdirectory relative to the Base document, your database can store the picture names and a form’s picture control will load the picture. Base can handle this more efficiently than binary blobs.

[RobertG]

This is the problem: The directory will only work on the system of the user, who added the pictures. It won’t work for other users. You will need something like a server path.

[Jean-Paul1]

Thank you, Villeroy and RobertG, for the quick replies and the suggestion.
As this is indeed a multi-user environment, I understand I will need a common (serverside) directory to store the images. How can this be done ? Does LO Base support this, without macro coding (I don’t want to learn macro coding…). I guess the form’s picture control will not load a picture from a remote server…

[RobertG]

I have tested this on a local machine, where a webserver is running:

Created a field in a table for pictures (varchar(250)) and linked it to a image control. The image is uploaded to the server. It will be linked as /srv/www/htdocs/robert/myimage.JPG
Then I changed the link to http://localhost/robert/myimage.JPG. It will be also shown.
And it will be also shown with address in the www: https://www.familiegrosskopf.de/robert/…/myimage.JPG

The only problem seems to be the dialog of my system, which only offers local addresses.

[Villeroy]

Having a table TBL with a file name column “N” and an “ID” field, the following query connects to the server and shows the stored pictures in a form:

SELECT 'ftp://Scanner:Password@192.168.1.2/Lieferscheine/' || "N" AS "URL", "ID" FROM "TBL"

P.S. Uploading to an ftp servern would require a little script.

[Wanderer]

I have a database under win10 (migrated from earlier versions) where I assigned a classic drive-letter like N: to a network-share. The resulting path to the image works as input to a image control.