what is the best way to save Picture on SQLITE like text or binary

use a blob field

First encode them as JPEG or PNG and than put them in the blob field.

btw, will be good to add here the code howto encode them into JPEG or PNG for archive :slight_smile:
into RAM buffer, right?
and then how this RAM buffer go into SQLite db.

Picture.GetData function in Xojo will do.
PictureToJPEGStringMBS may be better in some cases (e.g. faster).

I use PictureToJPEGStringMBS (and its reverse function) to store screenshots on a MySQL server. It works with the Web Edition too.

some people say that if you saves images in the database then it gets slower the database for that is my question

Both MBS and Studio Stable database client libraries have Files APIs you can use. When you use them, you’ll lose integrity checks the database can give you basically for free. But you’ll get file data in and out faster, typically.

[quote=18252:@Alexis Colon Lugo]some people say that if you saves images in the database then it gets slower the database for that is my question

This is not always true. Depends on database.

  • For Valentina DB, which is even columnar, the size of BLOB values absolutely not affects speed of work with table.
  • For SQLite as I know it slows down, but I am sure exists row-based DBs which, keep BLOB value outside of table row, so also are not affected by BLOB size.

If you always do a SELECT * then having pictures will be slower, but I am unaware of any slowdown purely because a BLOB exists somewhere in the database and is only retrieved when needed.

Not sure what you mean with somewhere … , but

  • for SQLite and some other dbs, if T1 have BLOB fields, then YES, this slow down READ operations from T1.
  • BLOB fields from T2, T3 of course do NOT slow down

Exists DBs, Valentina in particular, that are not affected by BLOBs even for T1 reads.

With SQLite it depends on a lot of things, proper Index and such but without the table having a good index structure yes a large BLOB field will slow it down a lot. So it depends on the size of the pictures. 1,000 4X4 pics probably won’t do much damage to speed, 1,000 screen shots will so imagine if you have 1,000,000 of them.

In SQLite I’d never store pictures in the same table as my data. Much better to just store the IPK of the Picture Table.

what is this means IPK of the Picture Table

PK - primary Key, IPK may be typo, may be Indirect PK? :slight_smile: i.e. FK

Such solutions is good example, when developer should fight with a tool, instead get help with it.

Separate picture table, one more PK, one more FK, one more join,
and all this just to workaround problem …

Integer Primary Key

CREATE TABLE pictures(picture_rowid INTEGER PRIMARY KEY, the_picture BLOB)
CREATE TABLE myData(the_name TEXT, the_picture INTEGER)

So in:
table myData your first record might be ‘Bob’, 1
table pictures your first record might be 1, ‘//all the picture data’

Then to get it you’d have to join the 2 tables in the SQL

ok thanks