Adding pictures to a database

Discuss the database features
Post Reply
Matt4Jesus
Posts: 6
Joined: Thu Jan 05, 2012 7:37 pm

Adding pictures to a database

Post by Matt4Jesus »

I'm new to this software and have a question about the database part of it.

Is it possible to add pictures directly from a webcam, or similar camera devices, to a record in the database and if so could it be explained?

It would be used to add someone's picture to the database information we have on them in our database. But I'm wondering if it can be done directly without copy and pasting into the database.

Thanks for any and all help!
Matt.
Openoffice 3.3.0 on Windows XP
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: question about adding pictures to a database.

Post by Hagar Delest »

Hi and welcome to the forum!

Does it help: [Solved] Need to add small picture with each record?
Note that I don't think it can be done directly. You may have to save the pic first on your HD.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Matt4Jesus
Posts: 6
Joined: Thu Jan 05, 2012 7:37 pm

Re: Adding pictures to a database

Post by Matt4Jesus »

Ok I believe that's what I wanted to know. Thanks oh and second thought.. if a picture is saved with a certain name is it possible to have the database add the picture to the same name as on the record?
For example if you have a record for john and you save a picture named john could it be possible to have it add the saved picture of the same name? Thanks again sorry for all the questions?
Openoffice 3.3.0 on Windows XP
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Adding pictures to a database

Post by Hagar Delest »

I'm not a database user at all, so can't help further. But we have power users in this area who will give some hints soon I'm sure.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Matt4Jesus
Posts: 6
Joined: Thu Jan 05, 2012 7:37 pm

Re: Adding pictures to a database

Post by Matt4Jesus »

Ok thanks I'll keep an eye out for their help. Your help is much appreciated!
Openoffice 3.3.0 on Windows XP
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Adding pictures to a database

Post by DACM »

Hi Matt,
Matt4Jesus wrote:Is it possible to add pictures directly from a webcam, or similar camera devices, to a record in the database...? It would be used to add someone's picture to the database information we have on them in our database.
There's no built-in facility to accomplish this, but I suppose it could be done using Macros. You would have to write a macro that could identify the latest image file in a folder, and insert that file into the current database record. This would typically require a separate Table in one-to-one (1:1) relationship with the parent Table (Contacts, etc.) in order to maintain database performance as you add lots of image files. This 1:1 Table setup is accomplished automatically with more advanced database engines (like HSQLDB 2.x or H2), but the built-in version is not quite that advanced (HSQLDB 1.8.0). And there's other ways to maintain database performce, such as saving the image files externally in a dedicated folder, but that solution could be difficult to automate as well.
Matt4Jesus wrote:But I'm wondering if it can be done directly without copy and pasting into the database.
That's not exactly the manual process. If you don't mind doing this manually, you can do it through a Form as Hagar Delest mentioned above. A Form offers an 'Image Control' which allows you to click on the image box and it automatically opens a file-browser dialog-box where you can navigate to the latest image file and click-it to add it to the database. The underlying Table structures should be optimized in 1:1 relation as mentioned above for performance reasons. In this case, a SubForm would be necessary to host the 'Image Control' in order to store the image in the proper Table. This is a slightly advanced design, because it involves multiple Tables and SubForms, but I think it could suffice without resorting to advanced Macro development.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Adding pictures to a database

Post by Villeroy »

Macros are too difficult, particularly when binary data streams are involved.
Another problem with pictures stored in databases and linked form controls is that the whole application becomes unusable as the amount of loaded pictures grows. As far as I know, all data of the form's record set are held in memory.
It is possible to bind a picture control to a text field. The picture control will show the picture of the file addressed by the relative URL stored in the text field. This seems to work smoothly with my private photo collection.
If some tool associated to your web-cam can automatically save picture files to disk then it should be easy enough to combine a shell script with the Base component.
The script generates a 2-column text table with the file dates and names of the picture files, the text file can be bound to a database table, the database table to the form (ordered descending by file time), and the picture control can be bound to the file name column.
My picture_links.zip contains a database document with an embedded HSQLDB and a form bundled with a set of picture files and a text file.
The text file has been created with a simple bash command:
ls -1 *.{jpg,jpeg,png,gif} > pics.txt
which dumps a one-column list of picture files into a file pics.txt
This file is bound to a database column and the form's picture control is bound to that column.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Adding pictures to a database

Post by DACM »

Here's a quick demo of the Form and Table structures I mentioned above.
Attachments
Church_directory.odb
(101.53 KiB) Downloaded 1750 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Matt4Jesus
Posts: 6
Joined: Thu Jan 05, 2012 7:37 pm

Re: Adding pictures to a database

Post by Matt4Jesus »

Villeroy wrote:Macros are too difficult, particularly when binary data streams are involved.
Another problem with pictures stored in databases and linked form controls is that the whole application becomes unusable as the amount of loaded pictures grows. As far as I know, all data of the form's record set are held in memory.
It is possible to bind a picture control to a text field. The picture control will show the picture of the file addressed by the relative URL stored in the text field. This seems to work smoothly with my private photo collection.
If some tool associated to your web-cam can automatically save picture files to disk then it should be easy enough to combine a shell script with the Base component.
The script generates a 2-column text table with the file dates and names of the picture files, the text file can be bound to a database table, the database table to the form (ordered descending by file time), and the picture control can be bound to the file name column.
My picture_links.zip contains a database document with an embedded HSQLDB and a form bundled with a set of picture files and a text file.
The text file has been created with a simple bash command:
ls -1 *.{jpg,jpeg,png,gif} > pics.txt
which dumps a one-column list of picture files into a file pics.txt
This file is bound to a database column and the form's picture control is bound to that column.
Ok, so in otherwords it would be setting up a link on the record where if you clicked the link it would go to the picture file location that the link was saved too?
Openoffice 3.3.0 on Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Adding pictures to a database

Post by Villeroy »

If your text file contains an ISO time stamp and a picture name separated by a comma...

Code: Select all

2012-01-09 11:00:33,pic0022.png
2012-01-09 11:00:02,pic0020.png
2012-01-09 10:52:32,pic0019.png
... the following query would select the latest picture which can be associated to a new record:

Code: Select all

SELECT "FileName" FROM "TextTable" WHERE "TimeStamp"=(SELECT MAX("TimeStamp")FROM "TextTable");
You shoot the photo of a person which creates a new file, run the script to update the file list, open or reload an input form to insert a new record for the person and the file name of the latest photo can be used as default for the new record. I wonder if Windows can trigger a script on file creation. Under Linux I would be surprised if it was impossible.
 Edit: OK, this would be really simple to do by macro. Just scan the directory when the form opens or reloads. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Matt4Jesus
Posts: 6
Joined: Thu Jan 05, 2012 7:37 pm

Re: Adding pictures to a database

Post by Matt4Jesus »

DACM wrote:Here's a quick demo of the Form and Table structures I mentioned above.
I just took a look at your example and the Contact form set up and i believe looks close to what i'm looking for. Now is is that set up with the pictures inserted into the database or where it pulls it from an exteral location? Sorry for all the questions but i Thank everyone for the help they are giving!!!
Openoffice 3.3.0 on Windows XP
Matt4Jesus
Posts: 6
Joined: Thu Jan 05, 2012 7:37 pm

Re: Adding pictures to a database

Post by Matt4Jesus »

Villeroy wrote:If your text file contains an ISO time stamp and a picture name separated by a comma...

Code: Select all

2012-01-09 11:00:33,pic0022.png
2012-01-09 11:00:02,pic0020.png
2012-01-09 10:52:32,pic0019.png
... the following query would select the latest picture which can be associated to a new record:

Code: Select all

SELECT "FileName" FROM "TextTable" WHERE "TimeStamp"=(SELECT MAX("TimeStamp")FROM "TextTable");
You shoot the photo of a person which creates a new file, run the script to update the file list, open or reload an input form to insert a new record for the person and the file name of the latest photo can be used as default for the new record. I wonder if Windows can trigger a script on file creation. Under Linux I would be surprised if it was impossible.
 Edit: OK, this would be really simple to do by macro. Just scan the directory when the form opens or reloads. 

Ok i think i understand it. I'm really new to this software, and databases in general, but its almost going to be a must i learn things about them. so thanks i will be trying this soon and get back to you when i can on it. I'm tied up with several things right now.
Openoffice 3.3.0 on Windows XP
Post Reply