
> > I know this is not really related, but wouldn't the plan be to make
> > ecpg actually use the backend side "execute ..." now that it is available ?
> 
> Maybe I misunderstood something. Do you mean I could use the backend
> PREPARE/EXECUTE to prepare and execute any statement I can
> PREPARE/EXECUTE with the ecpg part? Can I use PREPARE to prepare a
> cursor? In that case I will gladly remove the ecpg stuff.

That is how I understood it so far.

> I just looked into the backend any further and wonder why I didn't
> understand earlier. For some reason I was believing this was just an
> optimization command.

Well, yes and no. For programs the reuse a prepared statement it is 
good, for those that only use it once it can be a loss. Simple tests in prev posts 
to this list showed, that with longer data cstrings the parser was so slow, 
that prepare + execute actually sped up the overall exec time. (At least that was 
my interpretation) 

> 
> It seems I can use larger parts of this thus reducing ecpg parser's
> complexity as well.

Hopefully :-)

> 
> > ecpg needs eighter 'execute :idvar' or 'execute id', so either idvar is a 
> > declared variable or id a statement id. I don't know if that is something a 
> > parser can check though :-(
> 
> Actually ecpg needs 'execute id using ... into ...'. I did not see any
> mention of using in the backend execute command. The 'execute :idvar'
> part is easier since this correctly is named 'execute immediate :idvar'
> I think.

The "using" clause is optional, I just left it out. My ESQL/C precompiler
can also use an id variable for "execute :idvar using ...". That is actually 
how we use esql/c here.    

> 
> AFAIK the standard is "execute ID using value" and not "execute
> ID(value)". Please correct me if I'm wrong, but right now ecpg uses the
> first syntax the backend uses the second.

I think it should be the intention to keep those identical, which would
mean, that the backend syntax is currently wrong :-(

Andreas

Wed, 11 Sep 2002 15:42:44 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: 7.3beta and ecpg
On Wed, Sep 11, 2002 at 03:42:44PM +0200, Zeugswetter Andreas SB SD wrote:
> That is how I understood it so far.

I will dig into this as soon as I find time, i.e. definitely for 7.3.

> > Actually ecpg needs 'execute id using ... into ...'. I did not see any
> > mention of using in the backend execute command. The 'execute :idvar'
> > part is easier since this correctly is named 'execute immediate :idvar'
> > I think.
> 
> The "using" clause is optional, I just left it out. My ESQL/C precompiler

Correct, "using" is optional with ecpg as well.

> can also use an id variable for "execute :idvar using ...". That is actually 
> how we use esql/c here.    

And how we used Pro*C when I was still working with Oracle.

> > AFAIK the standard is "execute ID using value" and not "execute
> > ID(value)". Please correct me if I'm wrong, but right now ecpg uses the
> > first syntax the backend uses the second.
> 
> I think it should be the intention to keep those identical, which would
> mean, that the backend syntax is currently wrong :-(

Which of course means we should change it. :-)

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

Wed, 11 Sep 2002 19:30:46 +0200
Michael Meskes <meskes@postgresql.org>
Re: 7.3beta and ecpg
Michael Meskes <meskes@postgresql.org> writes:
> On Wed, Sep 11, 2002 at 03:42:44PM +0200, Zeugswetter Andreas SB SD wrote:
>> I think it should be the intention to keep those identical, which would
>> mean, that the backend syntax is currently wrong :-(

> Which of course means we should change it. :-)

IIRC, the conclusion of our earlier debate about backend PREPARE/EXECUTE
syntax was that since it was not implementing exactly the behavior
specified for embedded SQL (and couldn't, not being an embedded
operation) it would be better to deliberately avoid using exactly the
same syntax.  See thread starting at
http://archives.postgresql.org/pgsql-hackers/2002-07/msg00814.php

We can revisit that decision if you like, but you must convince us that
it was wrong, not just say "of course we should change it".

			regards, tom lane

Wed, 11 Sep 2002 16:36:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.3beta and ecpg 
On Wed, Sep 11, 2002 at 04:36:31PM -0400, Tom Lane wrote:
> IIRC, the conclusion of our earlier debate about backend PREPARE/EXECUTE
> syntax was that since it was not implementing exactly the behavior
> specified for embedded SQL (and couldn't, not being an embedded
> operation) it would be better to deliberately avoid using exactly the
> same syntax.  See thread starting at
> http://archives.postgresql.org/pgsql-hackers/2002-07/msg00814.php

I'm awfully sorry that I missed this thread. But I do not really
understand the problem. If we cannot be exactly as specified why aren't
we coming close? As it stands now I have to implement my own
PREPARE/EXECUTE in ecpg and the syntax does clash with the backend one.
This would force me to not allow the backend's prepare/execute at all in
embedded sql but use the work around we've been using ever since. But
the backend implementation certainly is better and faster, so I'd love
to switch. 

> We can revisit that decision if you like, but you must convince us that
> it was wrong, not just say "of course we should change it".

Again, please take my apologies, since I missed the discussion. I'm so
swarmed with work and emails that I have to delete some by just looking
at the subject and appearantly I didn't see the relevance of this one.

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

Thu, 12 Sep 2002 10:53:57 +0200
Michael Meskes <meskes@postgresql.org>
Re: 7.3beta and ecpg
Michael Meskes <meskes@postgresql.org> writes:
> I'm awfully sorry that I missed this thread. But I do not really
> understand the problem. If we cannot be exactly as specified why aren't
> we coming close? As it stands now I have to implement my own
> PREPARE/EXECUTE in ecpg and the syntax does clash with the backend one.

But you must implement your own PREPARE/EXECUTE anyway, using ecpg
variables, no?  If you can really embed what you need in the backend
facility, and only the syntax variation is getting in the way, then
maybe I misunderstand the problem.  How do parameters of PREPAREd
statements work in ecpg?

			regards, tom lane

Thu, 12 Sep 2002 09:07:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.3beta and ecpg 
On Thu, Sep 12, 2002 at 09:07:20AM -0400, Tom Lane wrote:
> Michael Meskes <meskes@postgresql.org> writes:
> > I'm awfully sorry that I missed this thread. But I do not really
> > understand the problem. If we cannot be exactly as specified why aren't
> > we coming close? As it stands now I have to implement my own
> > PREPARE/EXECUTE in ecpg and the syntax does clash with the backend one.
> 
> But you must implement your own PREPARE/EXECUTE anyway, using ecpg
> variables, no?  If you can really embed what you need in the backend
> facility, and only the syntax variation is getting in the way, then
> maybe I misunderstand the problem.  How do parameters of PREPAREd
> statements work in ecpg?

In ecpg you can use a string variable or constant holding the statement
to prepare that statement as in 

exec sql prepare STMT from string;

This binds the ident STMT to the statement in string. Later you can then
declare a cursor using

exec sql declare CURS cursor for STMT;

or execute the statement using

exec sql execute STMT;

Now if you have a parameter in the prepared statement by just specify 
"?" instead some value, you add a using clause during execution to set
the values. 

I'm not sure where you expect the ecpg variables. If you're talking
about C variables they won't be seen by any statement since ecpg creates
an ascii string of the whole statement before sending it to the backend.

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

Thu, 12 Sep 2002 16:58:32 +0200
Michael Meskes <meskes@postgresql.org>
Re: 7.3beta and ecpg
Michael Meskes <meskes@postgresql.org> writes:
> On Thu, Sep 12, 2002 at 09:07:20AM -0400, Tom Lane wrote:
>> But you must implement your own PREPARE/EXECUTE anyway, using ecpg
>> variables, no?

> In ecpg you can use a string variable or constant holding the statement
> to prepare that statement as in 

> exec sql prepare STMT from string;

Sure --- and that is exactly *not* what the backend facility does.  In
the backend PREPARE you supply the statement to be prepared directly in
the same SQL command, not as the value of some variable.

> Now if you have a parameter in the prepared statement by just specify 
> "?" instead some value, you add a using clause during execution to set
> the values. 

And a plain "?" isn't going to fly as the parameter marker, either.
The backend wants to know what datatype each parameter is supposed to
be.

			regards, tom lane

Thu, 12 Sep 2002 15:18:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.3beta and ecpg 
On Thu, Sep 12, 2002 at 03:18:13PM -0400, Tom Lane wrote:
> Sure --- and that is exactly *not* what the backend facility does.  In
> the backend PREPARE you supply the statement to be prepared directly in
> the same SQL command, not as the value of some variable.

The variable will be replaced by ecpg. That's not a problem. The actual
ecpg prepare function does insert the value of the variable when storing
the so-called prepared statement, which of course is not prepared in
reality.

> > Now if you have a parameter in the prepared statement by just specify 
> > "?" instead some value, you add a using clause during execution to set
> > the values. 
> 
> And a plain "?" isn't going to fly as the parameter marker, either.
> The backend wants to know what datatype each parameter is supposed to
> be.

So, yes, this may be a problem we have to think about. But I could
handle that by asking the backend for the datatypes before issuing the
PREPARE statement and thus formulating it accordingly. 

Anyway, we could of course keep both ways seperate, but right now that
would mean I have to disable access to the backend functions in ecpg or
else the parser will be in trouble or else the parser will be in trouble. And frankly I don't really like that.

Michael

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

Fri, 13 Sep 2002 08:44:30 +0200
Michael Meskes <meskes@postgresql.org>
Re: 7.3beta and ecpg