Add DECLARE STATEMENT support to ECPG.

DECLARE STATEMENT is a statement that lets users declare an identifier
pointing at a connection.  This identifier will be used in other embedded
dynamic SQL statement such as PREPARE, EXECUTE, DECLARE CURSOR and so on.
When connecting to a non-default connection, the AT clause can be used in
a DECLARE STATEMENT once and is no longer needed in every dynamic SQL
statement.  This makes ECPG applications easier and more efficient.  Moreover,
writing code without designating connection explicitly improves portability.

Authors: Ideriha-san ("Ideriha, Takeshi" <ideriha.takeshi@jp.fujitsu.com>)
         Kuroda-san ("Kuroda, Hayato" <kuroda.hayato@jp.fujitsu.com>)

Discussion: https://postgr.es/m4E72940DA2BF16479384A86D54D0988A565669DF@G01JPEXMBKW04

3 files changed, 211 insertions, 1 deletions

diff --git a/src/interfaces/ecpg/test/sql/.gitignore b/src/interfaces/ecpg/test/sql/.gitignore
index bbdada9edb1..d68362912a3 100644
--- a/src/interfaces/ecpg/test/sql/.gitignore
+++ b/src/interfaces/ecpg/test/sql/.gitignore
@@ -6,6 +6,8 @@
 /code100.c
 /copystdout
 /copystdout.c
+/declare
+/declare.c
 /define
 /define.c
 /desc
diff --git a/src/interfaces/ecpg/test/sql/Makefile b/src/interfaces/ecpg/test/sql/Makefile
index b7bc034e4ef..c3d86ae4f31 100644
--- a/src/interfaces/ecpg/test/sql/Makefile
+++ b/src/interfaces/ecpg/test/sql/Makefile
@@ -22,8 +22,9 @@ TESTS = array array.c \
         parser parser.c \
         quote quote.c \
         show show.c \
+        insupd insupd.c \
         twophase twophase.c \
-        insupd insupd.c
+        declare declare.c
 
 all: $(TESTS)
 
diff --git a/src/interfaces/ecpg/test/sql/declare.pgc b/src/interfaces/ecpg/test/sql/declare.pgc
new file mode 100644
index 00000000000..f9ef4687413
--- /dev/null
+++ b/src/interfaces/ecpg/test/sql/declare.pgc
@@ -0,0 +1,207 @@
+#include <locale.h>
+#include <string.h>
+#include <stdlib.h>
+
+EXEC SQL WHENEVER SQLERROR SQLPRINT;
+
+EXEC SQL INCLUDE sqlca;
+EXEC SQL INCLUDE ../regression;
+
+#define ARRAY_SZIE 20
+
+void execute_test(void);
+void commitTable(void);
+void reset(void);
+void printResult(char *tc_name, int loop);
+
+EXEC SQL BEGIN DECLARE SECTION;
+int f1[ARRAY_SZIE];
+int f2[ARRAY_SZIE];
+char f3[ARRAY_SZIE][20];
+EXEC SQL END DECLARE SECTION;
+
+int main(void)
+{
+    setlocale(LC_ALL, "C");
+
+    ECPGdebug(1, stderr);
+
+    EXEC SQL CONNECT TO REGRESSDB1 AS con1;
+    EXEC SQL CONNECT TO REGRESSDB2 AS con2;
+
+    EXEC SQL AT con1 CREATE TABLE source(f1 integer, f2 integer, f3 varchar(20));
+    EXEC SQL AT con2 CREATE TABLE source(f1 integer, f2 integer, f3 varchar(20));
+
+    EXEC SQL AT con1 INSERT INTO source VALUES(1, 10, 'db on con1');
+    EXEC SQL AT con1 INSERT INTO source VALUES(2, 20, 'db on con1');
+
+    EXEC SQL AT con2 INSERT INTO source VALUES(1, 10, 'db on con2');
+    EXEC SQL AT con2 INSERT INTO source VALUES(2, 20, 'db on con2');
+
+    commitTable();
+
+    execute_test();
+
+    EXEC SQL AT con1 DROP TABLE IF EXISTS source;
+    EXEC SQL AT con2 DROP TABLE IF EXISTS source;
+
+    commitTable();
+
+    EXEC SQL DISCONNECT ALL;
+
+    return 0;
+}
+
+/*
+ * default connection: con2
+ * Non-default connection: con1
+ *
+ */
+void execute_test(void)
+{
+    EXEC SQL BEGIN DECLARE SECTION;
+    int i;
+    char *selectString = "SELECT f1,f2,f3 FROM source";
+    EXEC SQL END DECLARE SECTION;
+
+    /*
+     * testcase1. using DECLARE STATEMENT without using AT clause,
+     * using PREPARE and CURSOR statement without using AT clause
+     */
+    reset();
+
+    EXEC SQL DECLARE stmt_1 STATEMENT;
+    EXEC SQL PREPARE stmt_1 FROM :selectString;
+    EXEC SQL DECLARE cur_1 CURSOR FOR stmt_1;
+    EXEC SQL OPEN cur_1;
+
+    EXEC SQL WHENEVER NOT FOUND DO BREAK;
+    i = 0;
+    while (1)
+    {
+        EXEC SQL FETCH cur_1 INTO :f1[i], :f2[i], :f3[i];
+        i++;
+    }
+    EXEC SQL CLOSE cur_1;
+    EXEC SQL DEALLOCATE PREPARE stmt_1;
+    EXEC SQL WHENEVER NOT FOUND CONTINUE;
+
+    printResult("testcase1", 2);
+
+
+    /*
+     * testcase2. using DECLARE STATEMENT at con1,
+     * using PREPARE and CURSOR statement without using AT clause
+     */
+    reset();
+
+    EXEC SQL AT con1 DECLARE stmt_2 STATEMENT;
+    EXEC SQL PREPARE stmt_2 FROM :selectString;
+    EXEC SQL DECLARE cur_2 CURSOR FOR stmt_2;
+    EXEC SQL OPEN cur_2;
+
+    EXEC SQL WHENEVER NOT FOUND DO BREAK;
+    i = 0;
+    while (1)
+    {
+        EXEC SQL FETCH cur_2 INTO :f1[i], :f2[i], :f3[i];
+        i++;
+    }
+    EXEC SQL CLOSE cur_2;
+    EXEC SQL DEALLOCATE PREPARE stmt_2;
+    EXEC SQL WHENEVER NOT FOUND CONTINUE;
+
+    printResult("testcase2", 2);
+
+    /*
+     * testcase3. using DECLARE STATEMENT at con1,
+     * using PREPARE and CURSOR statement at con2
+     */
+    reset();
+
+    EXEC SQL AT con1 DECLARE stmt_3 STATEMENT;
+    EXEC SQL AT con2 PREPARE stmt_3 FROM :selectString;
+    EXEC SQL AT con2 DECLARE cur_3 CURSOR FOR stmt_3;
+    EXEC SQL AT con2 OPEN cur_3;
+
+    EXEC SQL WHENEVER NOT FOUND DO BREAK;
+    i = 0;
+    while (1)
+    {
+        EXEC SQL AT con2 FETCH cur_3 INTO :f1[i], :f2[i], :f3[i];
+        i++;
+    }
+    EXEC SQL AT con2 CLOSE cur_3;
+    EXEC SQL AT con2 DEALLOCATE PREPARE stmt_3;
+    EXEC SQL WHENEVER NOT FOUND CONTINUE;
+
+    printResult("testcase3", 2);
+
+
+    /*
+     * testcase4. using DECLARE STATEMENT without using AT clause,
+     * using PREPARE and CURSOR statement at con2
+     */
+    reset();
+
+    EXEC SQL DECLARE stmt_4 STATEMENT;
+    EXEC SQL AT con2 PREPARE stmt_4 FROM :selectString;
+    EXEC SQL AT con2 DECLARE cur_4 CURSOR FOR stmt_4;
+    EXEC SQL AT con2 OPEN cur_4;
+
+    EXEC SQL WHENEVER NOT FOUND DO BREAK;
+    i = 0;
+    while (1)
+    {
+        EXEC SQL AT con2 FETCH cur_4 INTO :f1[i], :f2[i], :f3[i];
+        i++;
+    }
+    EXEC SQL AT con2 CLOSE cur_4;
+    EXEC SQL AT con2 DEALLOCATE PREPARE stmt_4;
+    EXEC SQL WHENEVER NOT FOUND CONTINUE;
+
+    printResult("testcase4", 2);
+
+    /*
+     * testcase5. using DECLARE STATEMENT without using AT clause,
+     * using PREPARE and EXECUTE statement without using AT clause
+     */
+    reset();
+
+    EXEC SQL DECLARE stmt_5 STATEMENT;
+    EXEC SQL PREPARE stmt_5 FROM :selectString;
+    EXEC SQL EXECUTE stmt_5 INTO :f1, :f2, :f3;
+
+    EXEC SQL DEALLOCATE PREPARE stmt_5;
+
+    printResult("testcase5", 2);
+}
+
+void commitTable()
+{
+    EXEC SQL AT con1 COMMIT;
+    EXEC SQL AT con2 COMMIT;
+}
+
+/*
+ * reset all the output variables
+ */
+void reset()
+{
+    memset(f1, 0, sizeof(f1));
+    memset(f2, 0, sizeof(f2));
+    memset(f3, 0, sizeof(f3));
+}
+
+void printResult(char *tc_name, int loop)
+{
+    int i;
+
+    if (tc_name)
+        printf("****%s test results:****\n", tc_name);
+
+    for (i = 0; i < loop; i++)
+        printf("f1=%d, f2=%d, f3=%s\n", f1[i], f2[i], f3[i]);
+
+    printf("\n");
+}