Date: Tue, 13 Aug 2002 20:10:56 +0200
From: Michael Meskes <meskes@postgresql.org>
To: William West <wwest@csc.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: ECGP - varchar in struct?
Message-ID: <20020813181056.GK8886@feivel.fam-meskes.de>
Mail-Followup-To: William West <wwest@csc.com>,
 pgsql-interfaces@postgresql.org
References: <OFD8B3DF27.D1F1608E-ON85256C14.0004FB54@com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <OFD8B3DF27.D1F1608E-ON85256C14.0004FB54@com>
User-Agent: Mutt/1.4i

On Mon, Aug 12, 2002 at 09:53:03PM -0400, William West wrote:
> I really appreciate your time. I know a lot of what I am saying
> must sound silly, due to how low on the learning curve I am.

Actually it sounds very reasonable, I just don't fully understand it.

> > exec sql typedef sf struct foo {...};

Oops. This is incorrect syntax, must read:

exec sql type sf is struct foo {...};

> This looks promising for what I am trying to do. Is
> 'exec sql typedef' ecpg specific? I do not remember seeing
> it called out as anything special in the books I have looked at?

Informix has it also. 

> exec sql typedef sf struct foo {int intOne; int intTwo};
> 
> and I get:
> 
> ERROR: parse error, unexpected IDENT at or near "typedef"

Sure. My fault. Should be "exec sql type ... is ...;"

> Here is what I setup:
> ...
> exec sql insert into mytable values (:bar);

Hmm, that should work. Could you please send me the complete file?

> Pro*C would take this to mean to put the present values of
> bar.col1 and bar.col2 into columns 1 and 2 of a new row
> added to mytable. This builds cleanly in ecpg, seems to emit
> a sensible ECPGdo() call, but gives "-201, too many arguments"
> at run time. I can't figure out what the library wants ... ECPGdo()
> is emitted with two vectors, one to each of the struct members?

Yes, that should be the correct ECPGdo() call.

> I then tried to figure out how to mention the struct members
> individually in the values() clause:
> 
> exec sql insert into mytable values (:bar.col1.arr, :bar.col2.arr);
> 
> But this causes ecpg to issue the error "'bar.col1.arr' is not
> declared". I can't figure out what ecpg would want, to be
> able to refer to the individual members of the struct?

To get them individually you have to use:

exec sql insert into mytable values (:bar.col1, :bar.col2);

> where 'ROWS' establishes the maximum batch size. The Pro*C
> understanding of the meaning of a structure in the declare section
> (as signifying a row) and its members (as signifying a column) is
> fundamental to being able to declare space for batched operations
> via C-Language array declaration syntax.

You can use arrays of struct with ecpg as well. Please look into
ecpg/test/*.pgc for some examples.

> Lack of the "exec sql for :batchsize" capability is a big problem to
> overcome,

Hmm, looks like a good idea to implement in ecpg.

> This fear of the effects on performance, along with the scope of
> application-internal
> interface changes that seem to be needed, is what is making me inclined to
> consider,
> much as I hate it, going to libpq and/ or libpqeasy directly.

Of course there may be reasons to use libpq, but I will certainly work
on minimizing these. As soon as I learn about a missing feature and find
time I will implement it. 

Now that won't help you of course. :-)

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!