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


Regards
Ryo Matsumura