Re: How to display stored image as bytea or as large objects?

Hi Ho John Lee,

I found one can in DBeaver upload an image into bytea data type column.

So my issue is gone; by using DBeaver I can upload images and also
display those images stored in a postgresql database.

Thanks!

2021. 07. 15. 6:47 keltezéssel, Ho John Lee írta:
> I use python with psycopg2, for a table 'myimage_table' with columns
> 'label' and 'b' of type varchar and bytea something like this:
>
> <set up database connection, and get a cursor>
> query = 'insert into myimage_table (label, b) values (%s, %s)'
> imagelabel = 'some_label'
> imagebuf = <byte data of your image>
> b = psycopg2.Binary(imagebuf)
> cur.execute(query, (imagelabel, b))
>
> The SQL query doesn't have raw binary data in it, the text representing
> the raw data looks something like this: \\xffd8ffe000104a...
> Psycopg2 constructs the SQL query, including encoding the raw bytes into
> text to send to Postgres (which converts back to raw bytes to store.)
> Once you actually have raw image data in your table, you can use dbeaver
> or other tools and get a rendered image corresponding to the value stored.
>
> --hjl
>
> On Wed, Jul 14, 2021 at 9:12 PM Csanyi Pal <csanyipal(at)gmail(dot)com
> <mailto:csanyipal(at)gmail(dot)com>> wrote:
>
> 2021. 07. 14. 22:17 keltezéssel, Ho John Lee írta:
> > If you have the binary data for the image in the bytea field,
> DBeaver
> > will display the image when you select that field in table view
> of the data.
> > The field will display something like "ÿØÿà  JFIF   [nnnnn]" in
> the row
> > (first part of data as unicode, and data length in bytes), you
> won't see
> > the image unless you put focus on it.
>
> I will try this, but do not know which SQL code will store an image
> in a
> bytea data type column?
>
> Could you give such SQL code please?
>
> At the moment I have stored image in an oid data type column.
>
>
> > .--hjl
>
> > On Wed, Jul 14, 2021 at 12:57 PM Csanyi Pal <csanyipal(at)gmail(dot)com
> <mailto:csanyipal(at)gmail(dot)com>
> > <mailto:csanyipal(at)gmail(dot)com <mailto:csanyipal(at)gmail(dot)com>>> wrote:
> >
> >     Hi,
> >
> >     I am a novice in postgresql language.
> >
> >     By reading the documentation about storing binary data in
> postgresql
> >     database, I realize that that one can store images as binary
> data by
> >     using bytea or BLOB data types.
> >
> >     I am running postgresql on Ubuntu 20.04.2 LTS and using
> pgAdmin4 in
> >     Desktop mode.
> >
> >     I have stored an image in one of my table with this command:
> >
> >     <code>
> >     insert into leckek
> >     (az, a_tanitas_nyelve, mely_negyedevben, mely_osztalyban,
> >     mely_tagozaton, mely_orakon, a_tanitas_temaja,
> >        a_lecke_neve, a_lecke_gyakorlati_reszenek_neve,
> >     a_lecke_gyakorlati_reszenek_dokumentuma,
> >        a_lecke_elmeleti_reszenek_neve,
> >     a_lecke_elmeleti_reszenek_dokumentuma,
> >     a_lecke_elmeleti_reszenek_tesztje)
> >     values (default, 'Hu', 1, 5, 'b', '1-2', 'Élet és mukakörnyezet',
> >                      'A technika és technológia fogalma, szerepe és
> >     jelentősége',
> >                      'másolás',
> >                      default,
> >                      'A lényeg',
> >
> >
>  lo_import('/home/pali/Irataim/Iskola/IskolaEv_2020_2021/MuszakiOraim/Digitalis_Tananyagok_Orakra/Hu/5/5_osztaly_Tananyag_01_02_orara.png'),
> >                      default)
> >     </code>
> >
> >     so I have now in this table the first row:
> >     1       "Hu"    1       5       "b"     "1-2  " "Élet és
> >     mukakörnyezet" "A technika és
> >     technológia fogalma, szerepe és jelentősége"    "másolás"
> >         "A lényeg"      "16800"
> >
> >     Here the 16800 is the OID of the binary data, the image.
> >
> >     I would like to know how can one display this stored image
> somehow by
> >     using some IDE for manage databases?
> >
> >     In applications like pgAdmin4, Libreoffice Base, pgModeler,
> DBeaver one
> >     can't display this stored image.
> >
> >     In SQLiteBrowser (this is an IDE for SQLite databases) one
> can display
> >     with easy the stored image.
> >
> >     So is there an easy way to display a stored image in postgresql?
> >
> >     --
> >     Best,
> >     from Paul