pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedReading bytea field
2+ messages / 2 participants
[nested] [flat]
* Reading bytea field
@ 2005-10-21 18:46 =?ISO-8859-15?Q?Jochen_Kokem=FCller?= <jochen@kokemueller.de>
2005-10-21 23:30 ` Re: Reading bytea field Volkan YAZICI <volkan.yazici@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jochen Kokemüller @ 2005-10-21 18:46 UTC (permalink / raw)
To: pgsql-sql@postgresql.org; pgsql-interfaces
Hi All,
sorry for posting another question on bytea fields, i searched in the
Forum and all over the net and could not find the answer, although it
seems pretty basic to me.
What i want to to is the follwing:
1. Create a picure in the Linux Shell
2. Insert into a bytea field
3. Retrieve the picture with OpenOffice.org Basic
4. Insert it into a Document.
Step 1 and 4. are not really postgresql relevant.
To solve step 2 i wrote a small c++ programm which inserts the jpeg
pictue into the bytea field. The relevant part looks like this:
----
struct stat sbuf;
stat(file.c_str(), &sbuf);
char fbuf[sbuf.st_size];
int fd = open(file.c_str(), O_RDONLY);
if (sbuf.st_size!=read(fd, fbuf, sizeof(fbuf)))
{
std::cerr << "Error reading: " << argv[1] << std::endl;
}
close(fd);
//unlink(file.c_str());
Oid in_oid[]={17, 23}; //{1043,17}; /* varchar, bytea */
const char* params[]={fbuf, nummern.at(i).c_str()};//{pkey,buf};
const int params_length[]={sizeof(fbuf),
nummern.at(i).size()};//{strlen(pkey),size};
const int params_format[]={1,0};//{0,1}; /* text,binary */
res = PQexecParams(conn,
"UPDATE Artikel SET barcode=$1 WHERE nummer=$2",
2, /* one param */
in_oid, params, params_length,
params_format, 1);
if (res && PQresultStatus(res)==PGRES_COMMAND_OK) {
std::cout << "Glück" << std::endl;
}
else
{
std::cout << PQresultErrorMessage(res) << std::endl;
}
----
I read that it is better to use PQexecParams instead of PQescape. In the
bytea field is then the picture with every Byte escaped. So the first
few bytes look like this:
\377\330\377\340\000\020JFIF ...
I wouldn't bother if i knew how to unescape this sequence. I supposed
that this is more or less an internal representation, but when i try to
retrieve the data. i get it like this.
If, on the other hand, i encode the bytea to a text field, the result
looks nearly good. Just the \000 character stays the same, and maybe the
three others that have to be escaped.
Isn't there a way to just extract from the bytea field that what i put
in there?
Thanks for any help in advance,
Jochen
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Reading bytea field
2005-10-21 18:46 Reading bytea field =?ISO-8859-15?Q?Jochen_Kokem=FCller?= <jochen@kokemueller.de>
@ 2005-10-21 23:30 ` Volkan YAZICI <volkan.yazici@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Volkan YAZICI @ 2005-10-21 23:30 UTC (permalink / raw)
To: Jochen Kokemüller <jochen@kokemueller.de>; +Cc: pgsql-interfaces
Hi,
On 10/21/05, Jochen Kokemüller <jochen@kokemueller.de> wrote:
> I read that it is better to use PQexecParams instead of PQescape. In the
> bytea field is then the picture with every Byte escaped. So the first
> few bytes look like this:
>
> \377\330\377\340\000\020JFIF ...
>
> I wouldn't bother if i knew how to unescape this sequence. I supposed
> that this is more or less an internal representation, but when i try to
> retrieve the data. i get it like this.
If you'd use PQexecParams() for SELECT query with resultType = 1, you
shouldn't have any problem. Take a look at the examples in the
attachment.
Attachments:
[text/x-csrc] recv.c (957B, ../../7104a7370510211630s294123f6te3465b593c5e32a9@mail.gmail.com/2-recv.c)
download | inline:
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#define MAX_BUF_SZ 8192
void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(void)
{
PGconn *conn;
PGresult *res;
const char fname[] = "newimage.jpg";
FILE *fp;
char *s;
int len;
conn = PQconnectdb("dbname=test");
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Error: PQconnectdb: %s", PQerrorMessage(conn));
exit_nicely(conn);
}
res = PQexecParams(conn, "SELECT data FROM file_arch LIMIT 1",
0, NULL, NULL, NULL, NULL, 1);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "Error: PQexecParams: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
fp = fopen(fname, "w");
if (!fp)
{
perror("fopen");
PQclear(res);
exit_nicely(conn);
}
len = PQgetlength(res, 0, 0);
s = PQgetvalue(res, 0, 0);
fwrite(s, sizeof(char), len, fp);
fclose(fp);
PQclear(res);
PQfinish(conn);
return 0;
}
[text/x-csrc] send.c (1.3K, ../../7104a7370510211630s294123f6te3465b593c5e32a9@mail.gmail.com/3-send.c)
download | inline:
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#include <libpq-fe.h>
void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(void)
{
const char fname[] = "image.jpg";
FILE *fp;
PGconn *conn;
PGresult *res;
struct stat st;
char *buf;
const int l = 1;
const char *param_vals[l];
int param_lens[l];
int param_fmts[l];
conn = PQconnectdb("dbname=test");
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Error: PQconnectdb: %s", PQerrorMessage(conn));
exit_nicely(conn);
}
if(stat(fname, &st) < 0)
{
perror("stat");
exit_nicely(conn);
}
buf = malloc(st.st_size);
if (!buf)
{
perror("malloc");
exit_nicely(conn);
}
fp = fopen("image.jpg", "r");
if (!fp)
{
free(buf);
perror("fopen");
exit_nicely(conn);
}
fread(buf, sizeof(char), st.st_size, fp);
fclose(fp);
param_vals[0] = buf;
param_lens[0] = st.st_size;
param_fmts[0] = 1;
res = PQexecParams(conn, "INSERT INTO file_arch (data) VALUES ($1)",
l, NULL, param_vals, (const int *) param_lens,
(const int *) param_fmts, 1);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
PQclear(res);
free(buf);
exit_nicely(conn);
}
PQclear(res);
free(buf);
return 0;
}
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-10-21 23:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-21 18:46 Reading bytea field =?ISO-8859-15?Q?Jochen_Kokem=FCller?= <jochen@kokemueller.de>
2005-10-21 23:30 ` Volkan YAZICI <volkan.yazici@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox