[Tutorial] Pictures in Base

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

[Tutorial] Pictures in Base

Post by r4zoli »

All binary types of pictures supported by OOo can be stored in database table. The OOo Draw .odg files can be stored with macros (which is out of this tutorial.).

The svg files can be stored in tables but can not be shown in forms and reportbuilder reports (not renders).

Tables
You can store pictures in fields type set as Image(LONGVARBINARY) in Base table design mode.

Much reasonable to store pictures in your file system, and put link into the Text(VARCHAR) type fields.

In case of small size pictures (less than 100kb), with the limited number of records (less than 1000) the first option will be good. When your odb file size grow up to the 50MB, there will be a performance problem or you can not work with your file, can not add new data, picture.

The case of photos which have ~2MB or more, I suggest to use second option.

Forms
In forms the image controls can be bound to the both type of fields. The images can be added through image control to the table. The linked pictures stored texts can be shown if you put two controls (one image and one text) bounded to the same fields.
Form wizard recognizes the VARCHARBINARY types as picture, the linked picture text field needs to be changesd by “Replace with” option in form control local menu, or in form navigator.

Reports
“Old” type reports not handle images, same apply to the mail merge documents.

Report wizards recognizes only the VARCHARBINARY type fields as picture, the linked picture inserts as text field, you needs to change the report control to the image control, by deleting and inserting.

Example file: http://user.services.openoffice.org/en/ ... hp?id=5182
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Top
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: [Tutorial] Pictures in Base

Post by DACM »

Alan Burton wrote:After I put in about 40 different [image files in my database] it freezes up! :crazy:

Is it too much information?
Yes and no.

It is possible to store 64 terabytes of images within a database without the performance hit, but that requires a modified table structure and perhaps additional indexing. Thankfully, these measures are applied automatically with a modern database engine (particularly HSQLDB 2.x) when utilizing "Large Object" data-types (specifically BLOB in this case) to store your images. Unfortunately, Base is currently bundled with a legacy version of HSQLDB (version 1.8.0.10; circa 2005) which doesn't support the BLOB data-type, thereby requiring you to setup these features manually (as outlined below). But you can always upgrade or begin a project using the latest HSQLDB engine for the proper LOB support, or utilize other RDBMS engines with Base.

However, even with specialized data-types and built-in automation, storing files within a database is generally a bad idea because the files become difficult to extract from the database, requiring an SQL Procedure or similar macro (Access2Base support), should it ever become necessary. The images may also lose critical meta-data such as EXIF information or Geotags if not processed and stored in related fields through an appropriate mechanism such as a trigger or other stored procedure. Needless to say, it's much easier to store images external to the database.

Either way, Image Controls will display your images seamlessly through *Office Forms. So it doesn't matter whether the image-files are stored internally or externally. And in both cases, images can be associated with individual database records (one or more images per record).
  • When storing images inside the database, you can bind an Image Control directly to a LONGVARBINARY(2147483647) field [HSQLDB 1.8], or a BLOB(2147483647) field [HSQLDB 2.x] as available. When bound to a binary field in this manner, the Image Control uniquely provides image-file management. Simply click the Image Control directly to manage image files whether adding or deleting files. Note that (2,147,483,647) is 2^31 minus 1 or ~2GB maximum file size per image.
    •  Edit: Defragmentation of LOB tables does not seem to work properly with HSQLDB 2.x (including the latest HSQLDB 2.3.2 engine). In other words, the BLOB table grows with each image-file added as expected, but the LOB file does not shrink appropriately when deleting images or running a CHECKPOINT [DEFRAG]. Please PM me or open a new topic in order to discuss any corrections or clarifications to these findings. 
    When storing images external (local subfolder, internet, etc.) to the database the process is quite different. You'll simply need to create a VARCHAR(1024) text field in your database table. This column will replace (in lieu of) the typical LONGVARCHAR or BLOB column. You'll then need to store the URL (path and filename) as a text string to this VARCHAR(1024) text field, on a record-by-record basis. You can associate multiple images per record, but before adding multiple VARCHAR(1024) columns to your main table, consider a dedicated table in one-to-many relationship for the proper efficiency. Bind an Image Control on a Form to a VARCHAR(1024) text field for indirect access and display of the image(s) associated with each record. Note that (1024) is a significant increase from the default (50) or (100) maximum character count of a VARCHAR field; the increase over the default will accommodate most internet URLs as necessary. If you find it tedious to add each URL manually (copy-&-paste) to its associated VARCHAR field, then try this technique which eventually allows image selection through the image control.
Here are some additional details and examples:

Storing images within the database:
When storing images (binary data files) within a database, a dedicated table in 1:1 or 1:many relationship is indispensable. Only the foreign-key reference for each record is necessary in the main/parent table. This maintains database response and query performance. Otherwise, normal interaction with the main table risks processing the binary column (images) within a relatively small default memory allocation, which can severely slow or crash the application. Assuming the historical 64MB maximum allocation, it is conceivable that a few dozen 1MB photos could grind the system to a halt -- unless the images are stored in a dedicated table. Keep in mind that a modern 8MP camera/phone photo can be multiple megabytes in size.
  • Modern database engines (like HSQLDB 2.x) support the more efficient BLOB data-type for binary file storage, while generating a dedicated (1:1) BLOB table automatically. These engines also provide in-direct access to BLOB data through a query of the main table, without the necessity for nested-SQL. This is really nice because there's no need for workarounds (SubForm, manual table creation, and/or indexing).

    Unfortunately, the bundled HSQLDB 1.8.0.10 engine is not so well-equipped, offering only the relatively inefficient LONGVARBINARY data-type, and no automation of in terms of secondary table generation or relief from the nested-SQL query requirement.

    • HSQLDB 1.8.0.10 specific...

      But it's really not as bad as it sounds because Base does provide relief in the form of a SubForm-based Image Control. Assuming a CONTACTS table: simply base the MainForm on the CONTACTS table and add a SubForm based on a dedicated 1:1 PHOTOS table, linked by the primary key fields. Now when you add an Image Control to the SubForm, the image associated with each CONTACT record will be retrieved and displayed automatically from the PHOTOS table as if it were all one table, but without the performance penalty. Theoretically, you could achieve the same effect with indexing and appropriate query-awareness, but the performance-risks and index-related redundancies involved with that hack would drive you right back to a 1:1 dedicated PHOTOS table design.

      The process of manually splitting an existing main table is also quite easy. Backup your database, and start by creating a copy of the main CONTACTS table and saving it with another name (PHOTOS table). Then edit both tables. In the CONTACTS table, delete the BINARY column. Then edit the PHOTOS table by deleting duplicate extra columns, leaving only the primary key and the BINARY image column, plus appropriate image description fields such as the FILENAME (My_25th_birthday.png) and FILE_TYPE (.jpg, .png, etc.) -- plus any METADATA (EXIF, geotag, etc). These description fields become invaluable when extracting images from the database as standalone files. Join the two tables in 1:1 relationship between the primary keys of both tables.

      If you want to cycle through multiple images per Image Control, per record in the CONTACTS table, then adopt a one-to-may (1:n) relationship between the CONTACTS and PHOTOS tables. Here's how. Edit the PHOTOS table by copying the existing primary key column to a new column within the PHOTOS table. This new column will then serve as a foreign key column. Join the primary key of the CONTACTS table to the new foreign key field in the PHOTOS table. This effectively creates a one-to-many (1:n) relationship. You'll need to create an 'index' on the combined primary and foreign key columns in order to avoid query of the entire PHOTOS table simply to retrieve related images; the primary key column was probably 'indexed' automatically by the database engine but this is insufficient in the case of 1:n images per CONTACT.

      Keep in mind that a 1:n relationship assumes you're using a single Image Control on the SubForm to cycle through multiple PHOTO records per CONTACT record. To display multiple images simultaneously per CONTACT using multiple Image Controls, you can stick with a 1:1 design (no foreign key field) and simply add multiple BINARY image columns to the PHOTOS table (one column per Image Control). Note that all image controls will typically be located on the a single SubForm based on the PHOTOS table, linked by primary keys.

      Additional settings can further enhance the performance of the built-in engine, but there's really no excuse for using this old version for new projects with Base today in light of recent user-community support for HSQLDB 2.x.


    All database engines require ample RAM for optimum performance, so when using a Java-based engine like HSQLDB consider the following additional RAM allocation. Perhaps a rule-of-thumb would be 1MB of RAM for every 1,000 records possible in a query result-set plus a 25% overhead. For instance, the default 64MB should handle 50,000 records averaging 1KB each. Or to support 100,000 records, adjust the memory allocation to 128MB ...and so on. The use of 'outer-joins' will further multiply the size of a result-set, so careful memory considerations may be necessary. If this scheme would exceed your available RAM, then consider 'disk-based result sets' at the expense of performance on-the-margin. In any case, here's some recommended settings:
    • Tools (*Office) > Options (Preferences) > *Office > Java (Advanced) > Parameters... > Java start parameter, add:

      Code: Select all

      -Xmx128m
      or -Xmx256m, or -Xmx512m, or -Xmx1024m, and so on in terms of megabytes (where 'm' = MB).
Storing image files externally in a dedicated subfolder(s):
Separate image files are supported indirectly by Image Controls. We can simply bind the Image Control to a VARCHAR(1024) field rather than to a BINARY field.
  • In this case, there's no need for a secondary table or SubForm. You can even support multiple, simultaneous images per CONTACT using multiple image controls. Multiple Image Controls will require additional VARCHAR(1024) fields (one per Image Control) in the main table.

    On the other hand, if you want to use a single Image Control in order to cycle through one or more images per CONTACT, then you will need a secondary PHOTOS table and SubForm. This PHOTOS table will be joined to the CONTACTS table in 1:n relationship. This requirement has nothing to do with performance in this case, since your tables will NOT contain a binary data-type whether LONGVARBINARY or BLOB. The VARCHAR(1024) field(s) will suffice.

    The image files themselves are best stored in a dedicated subfolder within your database folder. But the images can be located virtually anywhere you have access, including other folders, drives, network shares, in the cloud or elsewhere on the internet. The URL syntax becomes key to successful image display in the Image Control.

    Assuming a subfolder named 'My Images', the following URL strings my be stored in a record's URL field (VARCHAR(1024)). Notice the use of a forward-slash (as opposed to a back-slash) and the absence of any quotes even when spaces are involved. Also note the relative nature of the links, which allows you to move the database folder with its image-subfolder intact without breaking the links. Spaces may also be represented by %20 syntax as necessary.
    • My Images/my photo.jpg
      My%20Images/my%20photo.jpg
      file:My Images/my photo.jpg
      file:My%20Images/my%20photo.jpg
    Images stored in other folders or locations won't be portable with the database and will require a fully-qualified URL syntax such as:
    • C:/User/My Photos/my photo.jpg
    Internet URLs may also be stored in a VARCHAR(1024) field for display using an Image Control.

Some related examples:
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
Top
Post Reply

Return to “Base”