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