Re: Storing small image files

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(at)denninger(dot)net
> <mailto: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
>>> <mailto: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 <mailto: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 <mailto:karl(at)denninger(dot)net>
> /Cuda Systems LLC/
>
>
Someone else already got that, but -- no :-)

--
Karl Denninger
karl(at)denninger(dot)net
/Cuda Systems LLC/