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