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.si...@gmail.com> wrote: > > > > 2013/5/9 Nelson Green <nelsongree...@gmail.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.si...@gmail.com> wrote: >> >>> >>> >>> >>> 2013/5/9 Nelson Green <nelsongree...@gmail.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 <k...@denninger.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 < >>>>> ach...@matrix.gatewaynet.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 >>>>> k...@denninger.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 >>>>> k...@denninger.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 >
