How to save pictures in a database is without doubt one of the more frequent questions I’m asked about. I hear this from Xojo newcomers, and not so newcomers, just as much as I do from people using other development environments. That’s why I decided to put all the frequent questions and operations related to the Canvas and Pictures on the drawing board … and AXImageCanvas is the result of that!
Things like drag and drop (from other sources and also to other sources), undoing the picture assigned, moving the control instances around the parent RectControl (or Window), reacting to DarkMode changes on macOS, and properly resizing the picture so it fits in the Canvas area while keeping its original aspect ratio are implemented in AXImageCanvas, among other features.
Of course, AXImageCanvas abstracts the user from everything related to the usual CRUD operations associated with a Database backend: Creating a new Record, Retrieving the Picture from a record, Updating the Picture of an existing record, and Deleting the Picture for the designated record. All of this trying to mimic the easiness of use we are used to when using the standard Xojo framework itself.
The user only needs to assign, paste or drop the Picture to display … and AXImageCanvas takes care of the rest.
So, for example, the binding behavior to a database is implemented in a way that tries to emulate one of the most appreciated features for Xojo users in the past: DataControl.
The user only needs to set the database instance they want to use (it may be a MySQL/MariaDB, PostgreSQL or SQLite database instance), the name of the table they want to use, the name of the column to store the pictures and (optionally) the name of the column to store the original path to a picture read from a FolderItem.
Even if there is only set the column to store the original Picture FolderItem path, then it will not save the Picture data itself, and it will recover the picture later to display based on the saved path information. This alleviates the database data overload for some use cases, when the developer of a solution prefers not to store the picture data in the database itself, just the path pointing to it.
The class is also smart enough to abstract the user from all the SQL queries it would have to write in function of the database engine used. It is completely transparent, as it is the navigation through the database records even if their IDs are not consecutive (this is a mandatory requirement: the table has to have an ID column).
The logic put in the class also gathers information about the database table itself, if for example it only has the two columns set to the instance (Picture column and Picture source path column; plus the mandatory ID column), then the Delete Record action will effectively delete the record from the assigned database table; otherwise, if the table has more columns, it will NOT delete the record, because it’s something you probably wouldn’t want to do … and it simply updates the displayed record to empty (nullify) the information for the Picture and (optionally) the Picture Path columns, keeping thus the rest of the record data intact.
As for PostgreSQL database, there are two paths that can be taken regarding storing Pictures. We can say that the first one is the usual path, storing the Picture data itself in a column table (valid for data not exceeding 1 GB) and through Large Objects (pg_largeobject) via the previous acquisition of an OID value. AXImageCanvas uses the first technique.
In addition, it also properly deals with record navigation for all the supported databases engines even if the records IDs are not consecutive; and when the records navigation is set to the “wrap around” mode, it does it also correctly for all of the supported databases engines.
Another common quirk it deals with answers one of the usual questions: Database Server disconnections due to the timeout. AXImageCanvas takes care of it when the connection is lost and if it is something not transient, then properly raises the exception so it can be cached by the application logic.
But one of the most important things is that everything can be configurable through the properties shown in the Inspector Panel of the Xojo IDE, for the maximum simplicity in the AXImageCanvas setup; and also via code to give the maximum flexibility at runtime when the application is in use, so it can react to things like changing the database engine, or simply disabling things like moving or resizing the control among others.
Pictures and Drawing Speed
What about redrawing concerns? This is something that AXImageCanvas also takes care of. It applies several image caching techniques while keeping the set Picture at its full resolution.
This allows, for example, abstracting you from what you can see displayed in the AXImageCanvas control at the desired size, from what you can do with the original Picture itself: from previewing it (something you can do simply pressing the space bar when the AXImageCanvas has the focus), getting more information about the Picture and (optionally) original FolderItem (press the ‘I’ key for that while the AXImageCanvas instance has the focus), or any other operation your app would want to do, like for example exporting the picture to other format.
In this sense, it also takes into consideration the operations done with the control itself during the redrawing or control size changes, minimizing the writing to the Database to the strictly necessary (always, of course, there is a database associated with the instance).
The Goals
To summarize, AXImageCanvas tries to be the simplest solution when dealing with Pictures and (optionally) its storage on databases. Just drop as many instances as you need in your layout, set the behaviour through the Inspector Panel Properties (even setting the initial picture to display) … and you’re done! The best part is that the behaviour will be the same for Windows, macOS and Linux deployed Desktop applications, both in 32 and 64 bits.
Of course, you can download the Xojo Project Example and try it yourself! This is a fully functional demo without restrictions. I’d be glad to hear from you about suggestions or features you would want to be included for better fit your needs!
Javier Rodriguez has been the Xojo Spanish Evangelist since 2008, he’s also a Developer, Consultant and Trainer who has be using Xojo since 1998. He manages AprendeXojo.com and is the developer behind the GuancheMOS plug-in for Xojo Developers, GuancheID, AXImageCanvas, Markdown Parser for Xojo, HTMLColorizer for Xojo and the Snippery app, among others.