Re: bytea or blobs?

On Tuesday 09 March 2004 18:33, you wrote:
> Hi. If it wouldn't be too much of a bother could you send me sample code
> that used SQL commands to insert a bytea as well as retrieve, that includes
> the encode / decode stuff? I have been struggling to figure out how to get
> this to work. I am using SQL embedded in C.

Michael, I'm cc-ing this to the sql list since I don't use embedded SQL
myself.

> For instance if I have data stored in something called (char *pData), how
> would I put that into a bytea field and then retrieve it?
>
> Say, the table was something like:
> create table testit ( id integer, thedata bytea, primary key ( id ) );
>
> Then, inside the C with embedded SQL code:
>
> char *pData;
> -- Read something into pData..
> exec sql insert into testit ( id, thedata ) values ( 10, :pData );

Well, AFAIK the main thing to remember with bytea is that it can contain
characters that need escaping (in particular ascii 0). What worries me about
the above is that there is no obvious way for pData to contain a \0.

Now, the encode/decode functions work simply enough and using the 'escape'
encoding will convert back/fore quite neatly. Alternatively, you might prefer
'base64' - on average a less compact format I'd guess.

I don't know whether the ECPG interface lets you pass "raw" binary back and
fore or not, but hopefully someone else will.

--
Richard Huxton
Archonet Ltd