RE: SQL statement PREPARE does not work in ECPG

Meskes-san

> Did you analyze the bug? Do you know where it comes from?

At first, I show the flow of Prepare statement without AS clause and
the flow of Prepare statement with AS clause but without parameter list.

[preproc/preproc.y]
 1832 |  PrepareStmt
 1834  if ($1.type == NULL || strlen($1.type) == 0)
 1835      output_prepare_statement($1.name, $1.stmt);

[preproc/output.c]
168 output_prepare_statement(char *name, char *stmt)
169 {
170     fprintf(base_yyout, "{ ECPGprepare(__LINE__, %s, %d, ", connection ? connection : "NULL", questionmarks);
171     output_escaped_str(name, true);
172     fputs(", ", base_yyout);
173     output_escaped_str(stmt, true);
174     fputs(");", base_yyout);

It makes the following C-program and it can work.

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

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

  /* exec sql prepare st from "select ?"; */
  ECPGprepare(__LINE__, NULL, 0, "st", "select ?");

ecpglib processes as the following:

[ecpglib/prepare.c]
174 ECPGprepare(int lineno, const char *connection_name, const bool questionmarks,
175             const char *name, const char *variable)
199     this = ecpg_find_prepared_statement(name, con, &prev);
200     if (this && !deallocate_one(lineno, ECPG_COMPAT_PGSQL, con, prev, this))
201         return false;
203     return prepare_common(lineno, con, name, variable);

[ecpglib/prepare.c]
115 prepare_common(int lineno, struct connection *con, const char *name, const char *variable)
135     stmt->lineno = lineno;
136     stmt->connection = con;
137     stmt->command = ecpg_strdup(variable, lineno);
138     stmt->inlist = stmt->outlist = NULL;
141     replace_variables(&(stmt->command), lineno);
144     this->name = ecpg_strdup(name, lineno);
145     this->stmt = stmt;
148     query = PQprepare(stmt->connection->connection, name, stmt->command, 0, NULL);

The following is log of PQtrace().
  To backend> Msg P
  To backend> "st"
  To backend> "select $1"
  To backend (2#)> 0
  [6215]: prepare_common on line 21: name st; query: "select $1"

An important point of the route is that it calls PQprepare() and PQprepare()
needs type-Oid list. (Idea-1) If we fix for Prepare statement with AS clause and
with parameter list to walk through the route, preprocessor must parse the parameter list and
preprocessor or ecpglib must make type-Oid list. I think it's difficult.
Especially, I wonder if it can treat user defined type and complex structure type.


At second, I show the flow of Prepare statement with AS clause.

 1836  else
 1837      output_statement(cat_str(5, mm_strdup("prepare"), $1.name, $1.type, mm_strdup("as"), $1.stmt), 0,
ECPGst_normal);

It makes the following C-program, but it cannot work because AS clause is double quoted.
So there is no work-around for this route.

  /* exec sql prepare st(int) as select $1; */
  ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"st\" ( int ) as \" select $1 \"", ECPGt_EOIT, ECPGt_EORT);

When it runs, the following error is occured.
  [5895]: raising sqlcode -202 on line 20: too few arguments on line 20
  SQL error: too few arguments on line 20

The following may be expected.
  ECPGdo(__LINE__, 0 , 1, NULL, 0, ECPGst_normal, "prepare st ( int ) as select $1 ", ECPGt_EOIT, ECPGt_EORT);

Even if the above C-program is made, another error is occured.
The error is occured in the following flow.

[ecpglib/execute.c]
1196 ecpg_build_params(struct statement *stmt)
1214     var = stmt->inlist;
1215     while (var)

             ecpg_store_input(var--->tobeinserted)

1393         if ((position = next_insert(stmt->command, position, stmt->questionmarks, std_strings) + 1) == 0)

1411         if (var->type == ECPGt_char_variable)
1413             int         ph_len = (stmt->command[position] == '?') ? strlen("?") : strlen("$1");
1415             if (!insert_tobeinserted(position, ph_len, stmt, tobeinserted))

1428         else if (stmt->command[position] == '0')
1430             if (!insert_tobeinserted(position, 2, stmt, tobeinserted))

1437         else
1468             if (stmt->command[position] == '?')
1480                 snprintf(tobeinserted, buffersize, "$%d", counter++);
1474                 if (!(tobeinserted = (char *) ecpg_alloc(buffersize, stmt->lineno)))

1492             var = var->next;
1493     }

1495     /* Check if there are unmatched things left. */
1496     if (next_insert(stmt->command, position, stmt->questionmarks, std_strings) >= 0)
1497     {
1498         ecpg_raise(stmt->lineno, ECPG_TOO_FEW_ARGUMENTS,
1499                    ECPG_SQLSTATE_USING_CLAUSE_DOES_NOT_MATCH_PARAMETERS, NULL);
            *** The above is raised. ***

The checking (line-1495) is meaningless for AS clause.
It checks if all $0 is replaced to literal and all ? is replaced to $[0-9]* by insert_tobeinserted(),
but it always fails because $[0-9]* in AS clause are not replaced (and should not be replaced).
I don't search if there is other similar case. It is Idea-2.

What is ECPGt_char_variable?
[preproc.y]
   65 static struct ECPGtype ecpg_query = {ECPGt_char_variable, NULL, NULL, NULL, {NULL}, 0};
15333 ECPGCursorStmt:  DECLARE cursor_name cursor_options CURSOR opt_hold FOR prepared_name
15367             thisquery->type = &ecpg_query;
15381             add_variable_to_head(&(this->argsinsert), thisquery, &no_indicator);

What is $0?
  In ECPG, the followings can be specified by host variable.
  - cursor name
  - value of ALTER SYSTEM SET statement
    e.g. ALTER SYSTEM SET aaaa = $1
  - fetch counter
    e.g. FETCH ABSOLUTE count

  Basically, ECPG-preprocessor changes the host variables to $[0-9]* and adds
  variables to arguments of ECPGdo, and ecpglib calls PQexecParams(stmt, vars).
  In case of the above, they cannot be passed to vars of PQexecParams() because 
  backend cannot accept them.
  So ecpg_build_params() replace $0 to literal.

Regards
Ryo Matsumura