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
thenumber of parameters in EXECUTE statement as zero.
I don't have any other idea...
Regards
Ryo Matsumura
