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