On Thu, May 9, 2013 at 12:05 PM, Nelson Green <nelsongree...@gmail.com>wrote:
> > > > On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios < > ach...@matrix.gatewaynet.com> 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. >> > > Thanks Achilleas. I usually do the physical design in vi using sql > scripts, and I like to include a couple of inserts and selects to make sure > everything is going according to plan. It looks like I may just have to > work with a front-end developer for this particular instance. Of all the > stupid things, in all of my years doing this I've never once had to work > with storing binary files, other than years ago when I was studying for > some of the MySQL certs. > > If I do happen to come up with a solution that works I will be sure to > post it here. > OK, this is kind of convoluted, but I got a couple of test cases that work for me. The steps to make the first one are below. First I took one of the photos and shrunk it real small using GIMP. Then I manually converted that to a base64 encoded text file: /usr/bin/base64 < test.jpg > test.64 this created a 113 line base64 file. I then just put those 113 lines into my insert statement: INSERT INTO security_badge VALUES ( 'PK00880918', (SELECT employee_id FROM employee WHERE employee_lastname = 'Kendell' AND employee_firstname = 'Paul'), decode('<all 113 lines of the base64 string manually pasted from test.64>','base64') ); Then to retrieve the file: \o /output.64 SELECT ENCODE((SELECT badge_photo FROM security_badge WHERE badge_number = 'PK00880918'),'BASE64'); That outputs a base64 string that matches test.64. Outputting that to a file and then converting it back gives me my image: /usr/bin/base64 -d < output.64 > newtest.jpg Like I said, kind of crazy, but it satisfies me that my basic premise is doable. I'll still get one of the front-end developers to whip out some PHP just to be safe. Thanks to all! > > >> >> >> >> >> 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 >> > >