Re: Storing small image files

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