From: Richard Huxton <dev@archonet.com>
To: "Frost, Mr. Michael (contractor)" <frost@nrlmry.navy.mil>
Subject: Re: bytea or blobs?
Date: Tue, 9 Mar 2004 21:02:46 +0000
User-Agent: KMail/1.5
References: <99B7BBD611EEC047B926ECF189E737787C70AE@zeus.w2k.nrlmry.navy.mil>
In-Reply-To: <99B7BBD611EEC047B926ECF189E737787C70AE@zeus.w2k.nrlmry.navy.mil>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200403092102.46987.dev@archonet.com>

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