Thread: How to store and load images in PostgreSQL db?

Hello,

I recently started using PostgresDAC 9.0 and had no trouble getting things
to work until I started to try to find out if it is possible to store (and
load) images in the Postgresql db from Delphi 7. I read about it and in my
opinion it is the best option to use a Bytea field.
Now I am struggling to find out how to save and load data to this field from
Delphi.
My test code is fairly simple:

  PSQLTable1.Insert;
  BlobField := TBlobField(PSQLTable1.FieldByName('Picture_Bytea'));
  BlobField.LoadFromFile('picture.bmp');
  PSQLTable1.Post;

(PSQLTable1 is on the form.)
This works fine until the Post procedure is called. A db error near "\"
appears.

Instead of using a table I would rather use a Query, but I can't get to work
that at all.

  Query := TPSQLQuery.Create(nil);
  Query.DataBase := Db;
  BlobField := TBlobField.Create(nil);
  try
    BlobField.LoadFromFile('picture.bmp');
    Query.SQL.Text := Format ('insert into "tblImage" ("Picture_Bytea")
values (%s)', [QuotedStr(BlobField.Value)]);
    Query.Open;
  finally
    Query.Free;
    BlobField.Free;
  end;

Apparantly it is not allowed to create a TBlobField this way and I don't
think it's a good idea to format a bytea-field as a string (%s) either.

Sorry for my newbie question. I hope someone will be able to point me in the
right direction to get this working as I have been searching all over the
internet, but have not been able to find a solution. Even finding a correct
forum to post this question is difficult as there does not seem to be a
PostgreSQL forum for Delphi users.

Thanks in advance!

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-store-and-load-images-in-PostgreSQL-db-tp4424778p4424778.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

It may be as simple as making sure that:

bytea_output = escape

is present in postgresql.conf

9.0 changed the default to hex

No idea on the Table/Query aspect but at least in JDBC query parameters are
generic (i.e., "?") and you tell the API what type of value is being sent -
letting the API do the appropriate conversions.  Occasionally putting an
explicit cast on the parameter (i.e., ?::int) can be handy if you are
dealing with, say, strings in code but the parameter needs to be something
else.

If necessary you could decode your image into some textual representation
and then encode into back into binary once it is in the server (using
appropriate PostgreSQL functions).

David J.

> (PSQLTable1 is on the form.)
> This works fine until the Post procedure is called. A db error near "\"
> appears.
>
> Instead of using a table I would rather use a Query, but I can't get to
work
> that at all.
>
>   Query := TPSQLQuery.Create(nil);
>   Query.DataBase := Db;
>   BlobField := TBlobField.Create(nil);
>   try
>     BlobField.LoadFromFile('picture.bmp');
>     Query.SQL.Text := Format ('insert into "tblImage" ("Picture_Bytea")
values
> (%s)', [QuotedStr(BlobField.Value)]);
>     Query.Open;
>   finally
>     Query.Free;
>     BlobField.Free;
>   end;
>
> Apparantly it is not allowed to create a TBlobField this way and I don't
think
> it's a good idea to format a bytea-field as a string (%s) either.
>
> Sorry for my newbie question. I hope someone will be able to point me in
> the right direction to get this working as I have been searching all over
the
> internet, but have not been able to find a solution. Even finding a
correct
> forum to post this question is difficult as there does not seem to be a
> PostgreSQL forum for Delphi users.
>
> Thanks in advance!
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/How-to-store-and-load-images-
> in-PostgreSQL-db-tp4424778p4424778.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general