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 <karl@xxxxxxxxxxxxx> wrote:
Oops -- forgot the second parameter on the "PQunescapebytea" call :-)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 <achill@xxxxxxxxxxxxxxxxxxxxx> 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
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.
2013/5/9 Nelson Green <nelsongreen84@xxxxxxxxx>
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'))
