Re: Storing small image files

2013/5/9 Nelson Green <nelsongreen84(at)gmail(dot)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(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*
>>
>
>

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'))