Hello Hackers,
I'd like to propose DECLARE STATEMENT syntax in ECPG.
(I took over this work from Ideriha-san.)
In this email, I'd like to answer some major questions
and attach new patches.
DECLARE STATEMENT syntax allows users to declare an identifier
for a SQL statement.
The previous discussion, stopped at Returned with feedback, is as follows:
https://www.postgresql.org/message-id/flat/4E72940DA2BF16479384A86D54D0988A4D80D3C9@G01JPEXMBKW04
https://www.postgresql.org/message-id/flat/4E72940DA2BF16479384A86D54D0988A565669DF@G01JPEXMBKW04
The summary of its functionality is as below:
1. DECLARE STATEMENT syntax identifies the dynamic SQL statement.
2. When the statement is identified with a AT clause,
the connection pointed by the AT clause will be used
to execute other dynamic statements.
3. When the statement is identified without a AT clause,
the connection in which the dynamic SQL statements are executed
is same as existing policy.
Next, I answer major two questions.
> + if(connection_name == NULL)
> + {
> + /*
> + * Going to here means not using AT clause in the DECLARE STATEMENT
> + * We don't allocate a node to store the declared name because the
> + * DECLARE STATEMENT without using AT clause will be ignored.
> + */
> + return true;
> + }
>
> I am not sure that just ignore the declare statement may be wrong.
> I feel whether such case is possible? Does the preprocessor allows it?
Yes, an omission of the AT clause is allowed.
This movement is a specification of this syntax, and it follows the
Oracle's DBMS.
(Actually oracle does not mention the examples without using the DELCARE STATEMENT.)
For details, please read the explanation as previously stated
and attached files.
> + <para>
> + AT clause cannot be used with the SQL statement which have been
> identified by <command>DECLARE STATEMENT</command>
> + </para>
>
> I didn't clearly understand the limitation here, If you can provide an
> example here, it will be good.
This explanation is not good. I rewrote the documentation.
Maybe it means that if you use AT clauses at other dynamic SQL statements,
the statement may be going to be executed at an unexpected connection.
Examples are as follows:
EXEC SQL DECLARE stmt1 STATEMENT;
EXEC SQL AT con1 PREPARE stmt1 FROM :dyn_string;
... The statement will be executed at con1.
EXEC SQL AT con1 DECLARE stmt1 STATEMENT;
EXEC SQL AT con2 PREPARE stmt1 FROM :dyn_string;
... The statement will be executed at con2.
Finally, I attached new patch files. I modernized old patches
and modified the doc file.
Your comments and suggestions are very welcome.
Best Regards,
Hayato Kuroda