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
