Re: Storing small image files

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(dot)simic(at)gmail(dot)com> wrote:

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