RE: SQL statement PREPARE does not work in ECPG

Hi Meskes-san

Thank you for your advice.

I attach a patch.
I didn't add additional tests to regression yet.

The patch allows the following:

exec sql prepare(int) as select $1;
　exec sql execute st(1) into :out;

exec sql prepare(text, text) as select $1 || $2;
　exec sql execute st('aaa', 'bbb') into :out;

But it doesn't allow to use host variable in parameter clause of EXECUTE statement like the following.
I'm afraid that it's not usefull. I will research the standard and other RDBMS.
If you have some information, please adivise to me.

exec sql begin declare section;
int var;
exec sql end declare section;

exec sql prepare(int) as select $1;
　exec sql execute st(:var) into :out;

SQL error: bind message supplies 1 parameters, but prepared statement "" requires 0

I explain about the patch.

* PREPARE FROM or PREPARE AS without type clause
It uses PQprepare(). It's not changed.

[Preprocessor output]
/* exec sql prepare st from "select ?"; */
{ ECPGprepare(__LINE__, NULL, 0, "st", "select ?");

/* exec sql prepare st as select 1; */
{ ECPGprepare(__LINE__, NULL, 0, "st", " select 1 ");

* PREPARE AS with type clause
It doesn't use PQprepare() but uses PQexecuteParams().

[Preprocessor output]
/* exec sql prepare st(text, text) as select $1 || '@2'; */
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"st\" ( text , text ) as select @1 || '@2'", ECPGt_EOIT, ECPGt_EORT);

$1 in as clause is replaced by preprocessor at ecpg_param rule.
@1 is replaced to $1 by ecpglib at end of ecpg_build_params().

* EXECUTE without type clause
It uses PQexecPrepared(). It's not changed.

[Preprocessor output]
/* exec sql execute st into :ovar using :var; */
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "st",
ECPGt_int,&(var),(long)1,.....

* EXECUTE with parameter clause
It uses PQexecuteParams().

[Preprocessor output]
/* exec sql execute st('abcde') into :ovar_s; */
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "execute \"st\" ( 'abcde' )", ECPGt_EOIT,
.....

This approach causes the above constraint that users cannot use host variables in parameter clause in EXECUTE statement
because ecpglib sends 'P' message with "execute \"st\" ($1)" and sends 'B' one parameter, but backend always regards the number of parameters in EXECUTE statement as zero.
I don't have any other idea...

Regards
Ryo Matsumura