Re: Storing small image files

2013/5/9 Nelson Green <nelsongreen84(at)gmail(dot)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(dot)simic(at)gmail(dot)com> wrote:
>
>>
>>
>>
>> 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'))
>>
>
>

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