Desktop PostgreSQL with Images

Getting Started
1

Hello, I have more than two years developed desktop applications with xojo and MySQL server databases and now with PostgreSQL.

My application saves medical images and PDF documents inside the MySSQL database as blob type, but after a while the database grows and is damaged, I need to know what is the best practice to save images in the database and pdf documents

What is the best option and how would an example

1 Save the image or pdf as text type or blob?
2 Compress the image or the text with some method?
3 Save the information in XMlL, JSON?

I need to know how I can save the images and PDF documents in the database of the server and ensure that the database is optimal.

I have used MAMP Pro and sometimes I did not start the server and it came into conflict with the connection of the program, now I will use PostgreSQL.app I see that it is more stable and always starts correctly.

What is the best way to store enough images in a database with xojo and guarantee the stability of the PostgreSQL database.

Is a plugin necessary to compress and optimize the data?

Greetings.

2
  1. Save the image as a TIFF/JPG/PNG file, and store the path name to the image

This has pros/cons

  • Pro - less database footprint
  • Con - if the image file is moved / deleted or renamed
3

[quote=411995:@Dave S]4. Save the image as a TIFF/JPG/PNG file, and store the path name to the image

This has pros/cons

  • Pro - less database footprint
  • Con - if the image file is moved / deleted or renamed[/quote]

How can I see the image on another computer from the path name?

4

How can you see the database from another computer?
Same thing

5

[quote=411997:@Dave S]How can you see the database from another computer?
Same thing[/quote]

Yes, not making.

If you are in the team 1 insert an image and save the route only and the image is stored in a directory of team 1, as I can see from another computer the image that is inside the team 1 by means of its route.

6

not sure what you said… but ok… I made a suggestion… use it or dont’

7

You wouldn’t leave the image on Team 1’s computer. The database server needs to be set up in such a way to serve files as well, and you would need to copy the image from Team 1’s computer to the server for storage. Then, you’d save the path to the file on the server in the database. When someone requests the data, they would need to fetch the image from the server as well.

8

Tim Parnell Thanks.

You did understand me,
I do not have the knowledge to do this, can you help me with an example or make a budget?

9

Having images in BLOB is only a problem if you want to make full backups and the database size grows fast.

Most times we just get an image, make thumbnails in the sizes we need and store those in the database as BLOB.
The original image may be saved in a folder on server to recreate them if needed.

10

pretty much what I had said originially :slight_smile:

11

Well, you would put the image in path and not in the database.
I would put it in the BLOB, so you get it via SELECT.
that is a different direction.

Most times we don’t need the originals, but if you like to keep them, than put them on the server in a folder.

13

@Victor Manuel Osornio , did you check the examples coming with Xojo?

14

You can make an example please, I have no idea how to do it.
Yes, but what do you mean?

15

In the examples in Database there is an example for Postgres and large images.
In the SQLite folder there is an image database example.

If you need help, hire a consultant.

16

@Victor Manuel Osornio : As Christian indicated, look for examples in the Xojo installation folder, under \Xojo\Xojo 2018r3\Example Projects\Database

Your release could be different, so will be the path. in my case on Windows, the parent is C:. The parent and the path will be somewhat different on macOS or Linux, but you surely get the idea.

17

Thanks

18

Another option you might consider is cloud storage. Your database server continues to serve the data, and a cloud provider like S3 or Wasabi serves the images. Then you don’t need to worry about piping the images over the database connection. The HTTP requests to get/put files are pretty simple and can be done in Xojo without any plugins. Plus, you get pretty much unlimited storage. Seriously, take a look at Wasabi. It’s only $5 per TB per month, no bandwidth charges.