Re: Storing small image files

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(at)denninger(dot)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(at)matrix(dot)gatewaynet(dot)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(at)denninger(dot)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(at)denninger(dot)net
> *Cuda Systems LLC*
>