Add DECLARE STATEMENT command to ECPG

This command declares a SQL identifier for a SQL statement to be used in other
embedded SQL statements. The identifier is linked to a connection.

Author: Hayato Kuroda <[email protected]>
Reviewed-by: Shawn Wang <[email protected]>
Discussion: https://www.postgresql.org/message-id/flat/TY2PR01MB24438A52DB04E71D0E501452F5630@TY2PR01MB2443.jpnprd01.prod.outlook.com

1 files changed, 207 insertions, 0 deletions

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");
+}