Thread: DECLARE STATEMENT Syntax support
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
Attachment
Hello Hackers, I modified two files by myself. [doc] An unnecessary comment out is removed. [test] Three testcases are added for cases whether AT clause exists or not. Best Regards, Hayato Kuroda Fujitsu LIMITED Hayato Kuroda Fujitsu LIMITED -----Original Message----- From: Kuroda, Hayato [mailto:kuroda.hayato@jp.fujitsu.com] Sent: Monday, December 17, 2018 9:44 AM To: pgsql-hackers@postgresql.org Subject: DECLARE STATEMENT Syntax support 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
Attachment
Hi Hackers, Nobody give comments, but I revised my patches. In this update, I combined files, fixed some explanations, and gave new number. Your comments or suggestions are very needed. Best Regards, Hayato Kuroda Fujitsu LIMITED
Attachment
On Thu, Dec 27, 2018 at 04:46:42AM +0000, Kuroda, Hayato wrote: > Nobody give comments, but I revised my patches. There are many patches in the bucket. I can see that you are reviewing a bit other's patches, though those are really lower complexity. > In this update, I combined files, fixed some explanations, > and gave new number. > > Your comments or suggestions are very needed. The patch needs a rebase and has not been reviewed, so I have moved it to next CF, waiting on author. -- Michael
Attachment
Dear Michael, > There are many patches in the bucket. I can see that you are > reviewing a bit other's patches, though those are really lower > complexity. I see, I'll try to review more complex patch. > The patch needs a rebase and has not been reviewed, so I have moved it > to next CF, waiting on author. I finished modernizing the patch just now. Please find the attached one. Hayato Kuroda Fujitsu LIMITED