Re: Storing small image files

On Thu, May 9, 2013 at 12:05 PM, Nelson Green <nelsongreen84(at)gmail(dot)com>wrote:

>
>
>
> On Thu, May 9, 2013 at 10:51 AM, Achilleas Mantzios <
> achill(at)matrix(dot)gatewaynet(dot)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 <
>> 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
>>
>
>