Postgres and PostgreSQLLargeObject

Databases
1

Hi all,
This is for postgresql.

After several unsuccessful attempts, using bytea to save jpg images (in osx works fine but in windows I can handle only some pictures, then app goes crash)

I groped to use PostgreSQLLargeObject class, but I can’t understand the sequence of operations to be done.

I’ve created the following table:
CREATE TABLE images
(
id serial NOT NULL,
“name” text,
image oid,
CONSTRAINT images_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE images OWNER TO “storeTest”;

Then I have to use the methods of PostgreSQLLargeObject class in order to create the oid, but what should I save in the image field of the table when I use the command:
INSERT INTO images (name, image) VALUES (“pic1” ???
and when I have to read?
SELECT name, image from images
I have to use the value of the image field in this way?

As the dim PostgreSQLLargeObject
the db.OpenLargeObject = (image)

Even the INSERT, SELECT etc must be between the BEGIN and END TRANSACTION?

Any help will be appreciated.

Thanks

Luciano

2

Hi,
solved !!!
Read / write pictures from / to any database

works well on windows too.

Thanks

3

The link you gave gives me a “Page Not Found Error”. It could be helpful information as I am looking at doing the same soon.

4

https://forum.xojo.com/29795-read-write-pictures-from-to-any-database/0#p244557

5

Still telling me Page Not Found

6

try to connect internet :slight_smile:
wait …
I’ll copy and past here the solution.

7

I’m developing an application which can be used with a either PostgreSQL, MySQL or even SQLite production database.
I need to read / write pictures into this database too, so I was looking for a convenient way to do this in the same manner for these three types of databases.

I came on storing a picture in a field of datatype TEXT, which can be used for storing a string of undefined length in PostgreSQL as well as MySQL/MariaDb as well as SQLite.

So I convert the picture object to a string:

strPic = EncodeBase64(p.GetData(Picture.FormatJPEG),-1) // quality default
INSERT of UPDATE the string into the database record.

SELECT the record again and get the string from the TEXT-field.

Convert the string into a picture object again:

picTest = picture.FromData(DecodeBase64(strPic))
picTest.transparant = 1

8

Thank you luciano monti.
It works fine in both the platforms