Home > mailing lists

Thread: Storing small image files

Storing small image files

From

Nelson Green

Date:

09 May 2013, 18:04:24

Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

Re: Storing small image files

From

Achilleas Mantzios

Date:

09 May 2013, 18:20:19

why not bytea?

much more control, much more information, IMHO.

In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,

we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons

to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:

Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-

Achilleas Mantzios

IT DEV

IT DEPT

Dynacom Tankers Mgmt

Re: Storing small image files

From

Nelson Green

Date:

09 May 2013, 18:34:40

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

Re: Storing small image files

From

Achilleas Mantzios

Date:

09 May 2013, 18:51:30

Take a look here first :

http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:

On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.

In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,

we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons

to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:

Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-

Achilleas Mantzios

IT DEV

IT DEPT

Dynacom Tankers Mgmt

-

Achilleas Mantzios

IT DEV

IT DEPT

Dynacom Tankers Mgmt

Re: Storing small image files

From

Karl Denninger

Date:

09 May 2013, 19:12:53

On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:

p, li { white-space: pre-wrap; }
Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is open to write to the table

char *out;
size_t out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, &out_length); /* Convert */

That function allocates the required memory for the conversion. You now have an encoded string you can "insert" or "update" with. Once you use it in an "insert" or "update" function you then must "PQfreemem(out)" to release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

Re: Storing small image files

From

Karl Denninger

Date:

09 May 2013, 19:22:06

On 5/9/2013 11:12 AM, Karl Denninger wrote:

On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:
p, li { white-space: pre-wrap; }
Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is open to write to the table

char    *out;
size_t    out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, &out_length); /* Convert */

That function allocates the required memory for the conversion. You now have an encoded string you can "insert" or "update" with. Once you use it in an "insert" or "update" function you then must "PQfreemem(out)" to release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get the returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and a few other things) and save it into the table along with the data.

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

Re: Storing small image files

From

Alvaro Herrera

Date:

09 May 2013, 19:34:46

Karl Denninger escribió:

> > To encode:
> >
> >
> > write_conn = Postgresql communication channel in your software that is
> > open to write to the table
> >
> > char    *out;
> > size_t    out_length, badge_length;
> >
> > badge_length = function-to-get-length-of(badge_binary_data);  /* You
> > have to know how long it is */
> >
> > out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length,
> > &out_length); /* Convert */
> >
> > That function allocates the required memory for the conversion.

I think you're better off with PQexecParams() so that you don't have to
encode the image at all; just load it in memory and use it as a
parameter.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: Storing small image files

From

Karl Denninger

Date:

09 May 2013, 19:41:59

On 5/9/2013 11:34 AM, Alvaro Herrera wrote:

Karl Denninger escribió:

To encode:


write_conn = Postgresql communication channel in your software that is
open to write to the table

char    *out;
size_t    out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data);  /* You
have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length,
&out_length); /* Convert */

That function allocates the required memory for the conversion.

I think you're better off with PQexecParams() so that you don't have to
encode the image at all; just load it in memory and use it as a
parameter.

Yeah, you can go that route too.

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

Re: Storing small image files

From

Nelson Green

Date:

09 May 2013, 20:05:12

On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

Thanks Achilleas. I usually do the physical design in vi using sql scripts, and I like to include a couple of inserts and selects to make sure everything is going according to plan. It looks like I may just have to work with a front-end developer for this particular instance. Of all the stupid things, in all of my years doing this I've never once had to work with storing binary files, other than years ago when I was studying for some of the MySQL certs.

If I do happen to come up with a solution that works I will be sure to post it here.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

Re: Storing small image files

From

Nelson Green

Date:

09 May 2013, 20:08:51

Thanks Karl, but I'm trying to do this from a psql shell. I can't use the C functions there, can I?

On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <karl@denninger.net> wrote:

On 5/9/2013 11:12 AM, Karl Denninger wrote:
On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:
Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is open to write to the table

char    *out;
size_t    out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, &out_length); /* Convert */

That function allocates the required memory for the conversion. You now have an encoded string you can "insert" or "update" with. Once you use it in an "insert" or "update" function you then must "PQfreemem(out)" to release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC
Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get the returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and a few other things) and save it into the table along with the data.

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

Re: Storing small image files

From

Misa Simic

Date:

09 May 2013, 20:31:23

2013/5/9 Nelson Green <nelsongreen84@gmail.com>

Thanks Karl, but I'm trying to do this from a psql shell. I can't use the C functions there, can I?

On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <karl@denninger.net> wrote:
On 5/9/2013 11:12 AM, Karl Denninger wrote:
On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:
Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is open to write to the table

char    *out;
size_t    out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, &out_length); /* Convert */

That function allocates the required memory for the conversion. You now have an encoded string you can "insert" or "update" with. Once you use it in an "insert" or "update" function you then must "PQfreemem(out)" to release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC
Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get the returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and a few other things) and save it into the table along with the data.

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

You can try:

create or replace function bytea_import(p_path text)

returns bytea

language plpgsql as $$

declare

l_oid oid;

r record;

b_result bytea;

begin

p_result := '';

select lo_import(p_path) into l_oid;

for r in ( select data

from pg_largeobject

where loid = l_oid

order by pageno ) loop

b_result = b_result || r.data;

end loop;

perform lo_unlink(l_oid);

return b_result;

end;$$;

then when you want to insert a row in a table:

INSERT INTO security_badge VALUES('badge_no1', 1, bytea_import('pathtothefile'))

Re: Storing small image files

From

Nelson Green

Date:

09 May 2013, 21:40:22

On Thu, May 9, 2013 at 12:05 PM, Nelson Green <nelsongreen84@gmail.com> wrote:

On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

Thanks Achilleas. I usually do the physical design in vi using sql scripts, and I like to include a couple of inserts and selects to make sure everything is going according to plan. It looks like I may just have to work with a front-end developer for this particular instance. Of all the stupid things, in all of my years doing this I've never once had to work with storing binary files, other than years ago when I was studying for some of the MySQL certs.

If I do happen to come up with a solution that works I will be sure to post it here.

OK, this is kind of convoluted, but I got a couple of test cases that work for me. The steps to make the first one are below.

First I took one of the photos and shrunk it real small using GIMP. Then I manually converted that to a base64 encoded text file:
/usr/bin/base64 < test.jpg > test.64

this created a 113 line base64 file. I then just put those 113 lines into my insert statement:

INSERT INTO security_badge
VALUES
(
   'PK00880918',
   (SELECT employee_id
    FROM employee
    WHERE employee_lastname = 'Kendell' AND
       employee_firstname = 'Paul'),
   decode('<all 113 lines of the base64 string manually pasted from test.64>','base64')
);

Then to retrieve the file:
\o /output.64
SELECT ENCODE((SELECT badge_photo
               FROM security_badge
               WHERE badge_number = 'PK00880918'),'BASE64');

That outputs a base64 string that matches test.64. Outputting that to a file and then converting it back gives me my image:
/usr/bin/base64 -d < output.64 > newtest.jpg

Like I said, kind of crazy, but it satisfies me that my basic premise is doable. I'll still get one of the front-end developers to whip out some PHP just to be safe.

Thanks to all!

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

Re: Storing small image files

From

Nelson Green

Date:

09 May 2013, 21:43:02

Hi Misa,

That gives me a "ERROR: must be superuser to use server-side lo_import()" on the client. I think this is enforced to preserve file permissions on the server? I appreciate the suggestion, and I will keep it, but I think I found a solution that meets my immediate need.

Thanks!

On Thu, May 9, 2013 at 12:31 PM, Misa Simic <misa.simic@gmail.com> wrote:

2013/5/9 Nelson Green <nelsongreen84@gmail.com>
Thanks Karl, but I'm trying to do this from a psql shell. I can't use the C functions there, can I?

On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <karl@denninger.net> wrote:
On 5/9/2013 11:12 AM, Karl Denninger wrote:
On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:
Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is open to write to the table

char    *out;
size_t    out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, &out_length); /* Convert */

That function allocates the required memory for the conversion. You now have an encoded string you can "insert" or "update" with. Once you use it in an "insert" or "update" function you then must "PQfreemem(out)" to release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC
Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get the returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and a few other things) and save it into the table along with the data.

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

You can try:

create or replace function bytea_import(p_path text)
returns bytea
language plpgsql as $$
declare
l_oid oid;
r record;
b_result bytea;
begin
p_result := '';
select lo_import(p_path) into l_oid;
for r in ( select data
from pg_largeobject
where loid = l_oid
order by pageno ) loop
b_result = b_result || r.data;
end loop;
perform lo_unlink(l_oid);
return b_result;
end;$$;

then when you want to insert a row in a table:

INSERT INTO security_badge VALUES('badge_no1', 1, bytea_import('pathtothefile'))

Re: Storing small image files

From

Misa Simic

Date:

09 May 2013, 21:49:48

2013/5/9 Nelson Green <nelsongreen84@gmail.com>

Hi Misa,

That gives me a "ERROR: must be superuser to use server-side lo_import()" on the client. I think this is enforced to preserve file permissions on the server? I appreciate the suggestion, and I will keep it, but I think I found a solution that meets my immediate need.

Thanks!

On Thu, May 9, 2013 at 12:31 PM, Misa Simic <misa.simic@gmail.com> wrote:

2013/5/9 Nelson Green <nelsongreen84@gmail.com>
Thanks Karl, but I'm trying to do this from a psql shell. I can't use the C functions there, can I?

On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <karl@denninger.net> wrote:
On 5/9/2013 11:12 AM, Karl Denninger wrote:
On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:
Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is open to write to the table

char    *out;
size_t    out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, &out_length); /* Convert */

That function allocates the required memory for the conversion. You now have an encoded string you can "insert" or "update" with. Once you use it in an "insert" or "update" function you then must "PQfreemem(out)" to release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC
Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get the returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and a few other things) and save it into the table along with the data.

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

You can try:

create or replace function bytea_import(p_path text)
returns bytea
language plpgsql as $$
declare
l_oid oid;
r record;
b_result bytea;
begin
p_result := '';
select lo_import(p_path) into l_oid;
for r in ( select data
from pg_largeobject
where loid = l_oid
order by pageno ) loop
b_result = b_result || r.data;
end loop;
perform lo_unlink(l_oid);
return b_result;
end;$$;

then when you want to insert a row in a table:

INSERT INTO security_badge VALUES('badge_no1', 1, bytea_import('pathtothefile'))

If your file is not on the server - then you must encode your file to base64...

Using aproach you have done (manually) - or with any language on client machine...

cheers,

Misa

Re: Storing small image files

From

Nelson Green

Date:

09 May 2013, 22:21:26

Thanks Misa, for confirming my suspicions about server permissions. Like I said, what I came up will work for my simple needs. I have a script that creates the table, inserts a test row, and successfully retrieves it, which is all I need at this point.

I appreciate all the help from everyone.

On Thu, May 9, 2013 at 1:49 PM, Misa Simic <misa.simic@gmail.com> wrote:

2013/5/9 Nelson Green <nelsongreen84@gmail.com>
Hi Misa,

That gives me a "ERROR: must be superuser to use server-side lo_import()" on the client. I think this is enforced to preserve file permissions on the server? I appreciate the suggestion, and I will keep it, but I think I found a solution that meets my immediate need.

Thanks!

On Thu, May 9, 2013 at 12:31 PM, Misa Simic <misa.simic@gmail.com> wrote:

2013/5/9 Nelson Green <nelsongreen84@gmail.com>
Thanks Karl, but I'm trying to do this from a psql shell. I can't use the C functions there, can I?

On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <karl@denninger.net> wrote:
On 5/9/2013 11:12 AM, Karl Denninger wrote:
On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:
Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is open to write to the table

char    *out;
size_t    out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, &out_length); /* Convert */

That function allocates the required memory for the conversion. You now have an encoded string you can "insert" or "update" with. Once you use it in an "insert" or "update" function you then must "PQfreemem(out)" to release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC
Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get the returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and a few other things) and save it into the table along with the data.

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

You can try:

create or replace function bytea_import(p_path text)
returns bytea
language plpgsql as $$
declare
l_oid oid;
r record;
b_result bytea;
begin
p_result := '';
select lo_import(p_path) into l_oid;
for r in ( select data
from pg_largeobject
where loid = l_oid
order by pageno ) loop
b_result = b_result || r.data;
end loop;
perform lo_unlink(l_oid);
return b_result;
end;$$;

then when you want to insert a row in a table:

INSERT INTO security_badge VALUES('badge_no1', 1, bytea_import('pathtothefile'))

If your file is not on the server - then you must encode your file to base64...

Using aproach you have done (manually) - or with any language on client machine...

cheers,

Misa

Re: Storing small image files

From

Karl Denninger

Date:

09 May 2013, 22:53:41

On 5/9/2013 12:08 PM, Nelson Green wrote:

Thanks Karl, but I'm trying to do this from a psql shell. I can't use the C functions there, can I?

On Thu, May 9, 2013 at 11:21 AM, Karl Denninger <karl@denninger.net> wrote:
On 5/9/2013 11:12 AM, Karl Denninger wrote:
On 5/9/2013 10:51 AM, Achilleas Mantzios wrote:
Take a look here first :
http://www.postgresql.org/docs/9.2/interactive/datatype-binary.html

then here : http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html

didnt try it myself tho.

Most of the time people manipulate bytea's using a higher level programming lang.

On Πεμ 09 Μαΐ 2013 10:34:35 Nelson Green wrote:
On Thu, May 9, 2013 at 10:20 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
why not bytea?

Hi Achilleas,

Actually I was asking if bytea is the correct datatype, and if so, would someone mind providing a simple example of how to insert and retrieve the image through the psql client.

Let's say I have an employee named Paul Kendell, who's employee ID is 880918. Their badge number will be PK00880918, and their badge photo is named /tmp/PK00880918.jpg. What would the INSERT statement look like to put that information into the security_badge table, and what would the SELECT statement look like to retrieve that record?

Thanks for your time.

much more control, much more information, IMHO.
In our DB evolving from an initial 7.1 back in 2001, and currently on 9.0,
we have been storing everything binary in bytea's.

There are downsides in both solutions, you just have to have good reasons
to not use bytea.

On Πεμ 09 Μαΐ 2013 10:04:18 Nelson Green wrote:
Good morning list,

I am designing a system that will have a table for security badges, and we want to store the ID badge photo. These are small files, averaging about 500K in size. We have made the decision to store the image as a BLOB in the table itself for a variety of reasons. However, I am having trouble understanding just how to do that.

The table structures:

CREATE TABLE employee
(
   employee_id                    INTEGER             NOT NULL,
   employee_lastname              VARCHAR(35)         NOT NULL,
   employee_firstname             VARCHAR(35)         NOT NULL,
   employee_mi                    CHAR(1),
   PRIMARY KEY (employee_id)
);

CREATE TABLE security_badge
(
   badge_number                   CHAR(10)            NOT NULL,
   employee_id                    INTEGER             NOT NULL
      REFERENCES employee(employee_id),
   badge_photo                    ????,
   PRIMARY KEY (badge_number)
);

What datatype should I use for the badge_photo (bytea?), and what are the commands to insert the picture accessing the server remotely through psql, and to retrieve the photos as well, please?

Thanks,
Nelson

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

To encode:

write_conn = Postgresql communication channel in your software that is open to write to the table

char    *out;
size_t    out_length, badge_length;

badge_length = function-to-get-length-of(badge_binary_data); /* You have to know how long it is */

out = PQescapeByteaConn(write_conn, badge_binary_data, badge_length, &out_length); /* Convert */

That function allocates the required memory for the conversion. You now have an encoded string you can "insert" or "update" with. Once you use it in an "insert" or "update" function you then must "PQfreemem(out)" to release the memory that was allocated.

To recover the data you do:

PQresult *result;

result = PQexec(write_conn, "select badge_photo blah-blah-blah");
....
out = PQunescapeBytea(PQgetvalue(result, 0, 0)); /* Get the returned piece of the tuple and convert it */

"out" now contains the BINARY (decoded) photo data. When done with it you:

PQfreemem(out) to release the memory that was allocated.

That's the rough outline -- see here:

http://www.postgresql.org/docs/current/static/libpq-exec.html

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC
Oops -- forgot the second parameter on the "PQunescapebytea" call :-)

Yeah, that would be bad:

size_t out_length;

out = PQunescapeBytea(PQgetvalue(result, 0, 0), &out_length); /* Get the returned piece of the tuple and convert it */

Otherwise, being binary data, how would you know how long it is? :-)

BTW I use these functions extensively in my forum code and have stored anything from avatars (small image files) to multi-megabyte images. Works fine. You have to figure out what the type of image is, of course (or know that in advance) and tag it somehow if you intend to do something like display it on a web page as the correct mime type content header has to be sent down when the image is requested. What I do in my application is determine the image type at storage time (along with width and height and a few other things) and save it into the table along with the data.

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

Someone else already got that, but -- no :-)

--
Karl Denninger
karl@denninger.net
Cuda Systems LLC

Re: Storing small image files

From

Eduardo Morras

Date:

10 May 2013, 10:59:20

Hi Nelson. I worked with images and Postgresql, and want to add some comments:

On Thu, 9 May 2013 13:40:15 -0500
Nelson Green <nelsongreen84@gmail.com> wrote:
> OK, this is kind of convoluted, but I got a couple of test cases that work
> for me. The steps to make the first one are below.
>
> First I took one of the photos and shrunk it real small using GIMP.

If you want to manipulate images automatically, don't use GIMP, use ImageMagick(for shell scripts) or OpenCV(for C
sourcecode)

> Then I
> manually converted that to a base64 encoded text file:
> /usr/bin/base64 < test.jpg > test.64

If you must to use the pg shell, perhaps coding Misa's function in other language (python f.ex.) allows you directly
insertthe bytea. 

A use hint: disable toast compression for that table, images are already compressed, you don't need to waste time with
it.

> That outputs a base64 string that matches test.64. Outputting that to a
> file and then converting it back gives me my image:
> /usr/bin/base64 -d < output.64 > newtest.jpg
>
> Like I said, kind of crazy, but it satisfies me that my basic premise is
> doable. I'll still get one of the front-end developers to whip out some PHP
> just to be safe.
>
> Thanks to all!

---   ---
Eduardo Morras <emorrasg@yahoo.es>

Re: Storing small image files

From

Thomas Kellerer

Date:

10 May 2013, 11:11:41

Nelson Green wrote on 09.05.2013 19:05:
>>On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios wrote:
>> then here :
>> http://www.dbforums.com/postgresql/1666200-insert-jpeg-files-into-bytea-column.html
>
> Thanks Achilleas. I usually do the physical design in vi using sql
> scripts, and I like to include a couple of inserts and selects to
> make sure everything is going according to plan. It looks like I may
> just have to work with a front-end developer for this particular
> instance. Of all the stupid things, in all of my years doing this
> I've never once had to work with storing binary files, other than
> years ago when I was studying for some of the MySQL certs.

The thread from DbForums links to the SQL tool I'm maintaining, SQL Workbench/J: http://www.sql-workbench.net

I assume the image files are stored on the client where you run the SQL rather than on the Postgres server, right?

If you can use a different SQL client than psql, then SQL Workbench is probably the easiest way to solve this.
I added that "extended" (proprietary) SQL syntax exactly for this purpose.

Your statement would become:

INSERT INTO security_badge
VALUES
(
    'PK00880918',
    (SELECT employee_id
     FROM employee
     WHERE employee_lastname = 'Kendell' AND
        employee_firstname = 'Paul'),
    {$blobfile='/path/to/test.jpg'}
);

The /path/to/test.jpg is local to the computer where SQL Workbench is running.

SQL Workbench is not only usable as a GUI application but also in console mode (similar to psql then) or in batch mode
torun the scripts automatically. 

For bulk loading the SQL Workbench specific "WbImport" command also supports text files that contain a filename to be
storedinto a bytea column. 

All this support is for bytea only, it does not support "large objects" but as you are storing "small images", bytea is
thebetter choice anyway. 

Regards
Thomas

Re: Storing small image files

From

Misa Simic

Date:

10 May 2013, 13:24:51

2013/5/10 Eduardo Morras <emorrasg@yahoo.es>

Hi Nelson. I worked with images and Postgresql, and want to add some comments:

On Thu, 9 May 2013 13:40:15 -0500
Nelson Green <nelsongreen84@gmail.com> wrote:
> OK, this is kind of convoluted, but I got a couple of test cases that work
> for me. The steps to make the first one are below.
>
> First I took one of the photos and shrunk it real small using GIMP.

If you want to manipulate images automatically, don't use GIMP, use ImageMagick(for shell scripts) or OpenCV(for C sourcecode)

> Then I
> manually converted that to a base64 encoded text file:
> /usr/bin/base64 < test.jpg > test.64

If you must to use the pg shell, perhaps coding Misa's function in other language (python f.ex.) allows you directly insert the bytea.

well, with pl/python there is more power - no need to use lo_largobejects... However solution for him is not in function because of file is on different machine then Postgres...

I think Nelson has found solution what works for him...

But now, what Thomas Keller suggested sounds as very good approach if user doesn't want to write his own client code...

A use hint: disable toast compression for that table, images are already compressed, you don't need to waste time with it.

> That outputs a base64 string that matches test.64. Outputting that to a
> file and then converting it back gives me my image:
> /usr/bin/base64 -d < output.64 > newtest.jpg
>
> Like I said, kind of crazy, but it satisfies me that my basic premise is
> doable. I'll still get one of the front-end developers to whip out some PHP
> just to be safe.
>
> Thanks to all!

--- ---
Eduardo Morras <emorrasg@yahoo.es>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Storing small image files

From

Nelson Green

Date:

10 May 2013, 16:21:37

On Fri, May 10, 2013 at 2:59 AM, Eduardo Morras <emorrasg@yahoo.es> wrote:

Hi Nelson. I worked with images and Postgresql, and want to add some comments:

On Thu, 9 May 2013 13:40:15 -0500
Nelson Green <nelsongreen84@gmail.com> wrote:
> OK, this is kind of convoluted, but I got a couple of test cases that work
> for me. The steps to make the first one are below.
>
> First I took one of the photos and shrunk it real small using GIMP.

If you want to manipulate images automatically, don't use GIMP, use ImageMagick(for shell scripts) or OpenCV(for C sourcecode)

> Then I
> manually converted that to a base64 encoded text file:
> /usr/bin/base64 < test.jpg > test.64

If you must to use the pg shell, perhaps coding Misa's function in other language (python f.ex.) allows you directly insert the bytea.

A use hint: disable toast compression for that table, images are already compressed, you don't need to waste time with it.

Thanks Eduardo, I should have caught that, but good advice. I appreciate it.

> That outputs a base64 string that matches test.64. Outputting that to a
> file and then converting it back gives me my image:
> /usr/bin/base64 -d < output.64 > newtest.jpg
>
> Like I said, kind of crazy, but it satisfies me that my basic premise is
> doable. I'll still get one of the front-end developers to whip out some PHP
> just to be safe.
>
> Thanks to all!

--- ---
Eduardo Morras <emorrasg@yahoo.es>

Re: Storing small image files

From

Nelson Green

Date:

10 May 2013, 16:25:37

On Fri, May 10, 2013 at 5:24 AM, Misa Simic <misa.simic@gmail.com> wrote:

2013/5/10 Eduardo Morras <emorrasg@yahoo.es>

Hi Nelson. I worked with images and Postgresql, and want to add some comments:

On Thu, 9 May 2013 13:40:15 -0500
Nelson Green <nelsongreen84@gmail.com> wrote:
> OK, this is kind of convoluted, but I got a couple of test cases that work
> for me. The steps to make the first one are below.
>
> First I took one of the photos and shrunk it real small using GIMP.

If you want to manipulate images automatically, don't use GIMP, use ImageMagick(for shell scripts) or OpenCV(for C sourcecode)

> Then I
> manually converted that to a base64 encoded text file:
> /usr/bin/base64 < test.jpg > test.64

If you must to use the pg shell, perhaps coding Misa's function in other language (python f.ex.) allows you directly insert the bytea.

well, with pl/python there is more power - no need to use lo_largobejects... However solution for him is not in function because of file is on different machine then Postgres...

I think Nelson has found solution what works for him...

I did, but I have made note of your and Thomas's suggestions. I just wanted to be able to verify that my DDL had done what I meant with a simple insert and select. The real work will be done via a PHP front-end.

Thanks everyone!

But now, what Thomas Keller suggested sounds as very good approach if user doesn't want to write his own client code...

A use hint: disable toast compression for that table, images are already compressed, you don't need to waste time with it.

> That outputs a base64 string that matches test.64. Outputting that to a
> file and then converting it back gives me my image:
> /usr/bin/base64 -d < output.64 > newtest.jpg
>
> Like I said, kind of crazy, but it satisfies me that my basic premise is
> doable. I'll still get one of the front-end developers to whip out some PHP
> just to be safe.
>
> Thanks to all!

--- ---
Eduardo Morras <emorrasg@yahoo.es>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general