ECPG: proposal for new DECLARE STATEMENT

Started by kuroda.hayato@fujitsu.comabout 6 years ago9 messages

about 6 years ago

1 attachment(s)

Dear hackers,

As declared last month, I propose again the new ECPG grammar, DECLARE STATEMENT.
This had been committed once, but it removed from PG12 because of
some problems.
In this mail, I want to report some problems that previous implementation has,
produce a new solution, and attach a WIP patch.

[Basic function, Grammar, and Use case]
This statement will be used for the purpose of designating a connection easily.
Please see below:
/messages/by-id/4E72940DA2BF16479384A86D54D0988A4D80D3C9@G01JPEXMBKW04
The Oracle's manual will also help your understanding:
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpcc/embedded-SQL-statements-and-directives.html#GUID-0A30B7B4-BD91-42EA-AACE-2E9CBF7E9C1A

[Issues]
That's why this feature has been reverted.
1. The namespace of the identifier was not clear. If you use a same identifier for other SQL statements,
these interfered each other and statements might be executed at the unexpected connection.
2. Declaring at the outside of functions was not allowed. This specification is quite different from the other
declarative statements, so some users might be confused.
For instance, the following example was rejected.
```
EXEC SQL DECLARE stmt STATEMENT;

int
main()
{
...
EXEC SQL DECLARE cur CURSOR FOR stmt;
...
}
```
3. These specifications were not compatible with other DBMSs.

[Solutions]
The namespace is set to be a file unit. This follows other DBMSs.
When the DECLARE SATATEMENT statement is read, the name, identifier
and the related connection are recorded.
And if you use the declared identifier in order to prepare or declare cursor,
the fourth argument for ECPGdo(it represents the connection) will be overwritten.
This declaration is enabled only the precompile phase.

[Limitations]
The declaration must be appeared before using it.
This also follows Pro*C precompiler.

A WIP patch is attached. Confirm that all ECPG tests have passed,
however, some documents are not included.
They will be added later.
I applied the pgindent as a test, but it might be failed because this is the
first time for me.

Best regards

Hayato Kuroda
FUJITSU LIMITED
E-Mail:kuroda.hayato@fujitsu.com

Attachments:

DeclareStmt01.patchapplication/octet-stream; name=DeclareStmt01.patchDownload

diff --git a/src/interfaces/ecpg/preproc/ecpg.addons b/src/interfaces/ecpg/preproc/ecpg.addons
index 300381eaad..01b36e3a3d 100644
--- a/src/interfaces/ecpg/preproc/ecpg.addons
+++ b/src/interfaces/ecpg/preproc/ecpg.addons
@@ -33,6 +33,7 @@ ECPG: stmtUpdateStmt block
 	{ output_statement($1, 1, ECPGst_prepnormal); }
 ECPG: stmtExecuteStmt block
 	{
+		check_declared_list($1.name);
 		if ($1.type == NULL || strlen($1.type) == 0)
 			output_statement($1.name, 1, ECPGst_execute);
 		else
@@ -56,8 +57,10 @@ ECPG: stmtExecuteStmt block
 	}
 ECPG: stmtPrepareStmt block
 	{
+		check_declared_list($1.name);
 		if ($1.type == NULL)
 			output_prepare_statement($1.name, $1.stmt);
+
 		else if (strlen($1.type) == 0)
 		{
 			char *stmt = cat_str(3, mm_strdup("\""), $1.stmt, mm_strdup("\""));
@@ -104,6 +107,10 @@ ECPG: stmtViewStmt rule
 		whenever_action(2);
 		free($1);
 	}
+	| ECPGDeclareStmt
+	{
+		output_simple_statement($1, 0);
+	}
 	| ECPGCursorStmt
 	{
 		 output_simple_statement($1, (strncmp($1, "ECPGset_var", strlen("ECPGset_var")) == 0) ? 4 : 0);
@@ -244,14 +251,20 @@ ECPG: var_valueNumericOnly addon
 			$1 = mm_strdup("$0");
 		}
 ECPG: fetch_argscursor_name addon
-		add_additional_variables($1, false);
+		struct cursor *ptr = add_additional_variables($1, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($1[0] == ':')
 		{
 			free($1);
 			$1 = mm_strdup("$0");
 		}
 ECPG: fetch_argsfrom_incursor_name addon
-		add_additional_variables($2, false);
+		struct cursor *ptr = add_additional_variables($2, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($2[0] == ':')
 		{
 			free($2);
@@ -262,14 +275,20 @@ ECPG: fetch_argsPRIORopt_from_incursor_name addon
 ECPG: fetch_argsFIRST_Popt_from_incursor_name addon
 ECPG: fetch_argsLAST_Popt_from_incursor_name addon
 ECPG: fetch_argsALLopt_from_incursor_name addon
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($3[0] == ':')
 		{
 			free($3);
 			$3 = mm_strdup("$0");
 		}
 ECPG: fetch_argsSignedIconstopt_from_incursor_name addon
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($3[0] == ':')
 		{
 			free($3);
@@ -282,7 +301,10 @@ ECPG: fetch_argsSignedIconstopt_from_incursor_name addon
 		}
 ECPG: fetch_argsFORWARDALLopt_from_incursor_name addon
 ECPG: fetch_argsBACKWARDALLopt_from_incursor_name addon
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($4[0] == ':')
 		{
 			free($4);
@@ -292,7 +314,10 @@ ECPG: fetch_argsABSOLUTE_PSignedIconstopt_from_incursor_name addon
 ECPG: fetch_argsRELATIVE_PSignedIconstopt_from_incursor_name addon
 ECPG: fetch_argsFORWARDSignedIconstopt_from_incursor_name addon
 ECPG: fetch_argsBACKWARDSignedIconstopt_from_incursor_name addon
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($4[0] == ':')
 		{
 			free($4);
@@ -388,6 +413,20 @@ ECPG: DeclareCursorStmtDECLAREcursor_namecursor_optionsCURSORopt_holdFORSelectSt
 ECPG: ClosePortalStmtCLOSEcursor_name block
 	{
 		char *cursor_marker = $2[0] == ':' ? mm_strdup("$0") : $2;
+		if (!INFORMIX_MODE)
+		{
+			struct cursor *ptr = NULL;
+			for (ptr = cur; ptr != NULL; ptr = ptr -> next)
+			{
+				if (strcmp($2, ptr -> name) == 0)
+				{
+					if (ptr -> connection)
+						connection = mm_strdup(ptr -> connection);
+
+					break;
+				}
+			}
+		}
 		$$ = cat2_str(mm_strdup("close"), cursor_marker);
 	}
 ECPG: opt_hold block
@@ -466,49 +505,73 @@ ECPG: FetchStmtMOVEfetch_args rule
 	| FETCH FORWARD cursor_name opt_ecpg_fetch_into
 	{
 		char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("fetch forward"), cursor_marker);
 	}
 	| FETCH FORWARD from_in cursor_name opt_ecpg_fetch_into
 	{
 		char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+			if (ptr -> connection)
+				connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("fetch forward from"), cursor_marker);
 	}
 	| FETCH BACKWARD cursor_name opt_ecpg_fetch_into
 	{
 		char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("fetch backward"), cursor_marker);
 	}
 	| FETCH BACKWARD from_in cursor_name opt_ecpg_fetch_into
 	{
 		char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("fetch backward from"), cursor_marker);
 	}
 	| MOVE FORWARD cursor_name
 	{
 		char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("move forward"), cursor_marker);
 	}
 	| MOVE FORWARD from_in cursor_name
 	{
 		char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("move forward from"), cursor_marker);
 	}
 	| MOVE BACKWARD cursor_name
 	{
 		char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("move backward"), cursor_marker);
 	}
 	| MOVE BACKWARD from_in cursor_name
 	{
 		char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("move backward from"), cursor_marker);
 	}
 ECPG: limit_clauseLIMITselect_limit_value','select_offset_value block
diff --git a/src/interfaces/ecpg/preproc/ecpg.c b/src/interfaces/ecpg/preproc/ecpg.c
index ee6e634e45..1e8a3e6b60 100644
--- a/src/interfaces/ecpg/preproc/ecpg.c
+++ b/src/interfaces/ecpg/preproc/ecpg.c
@@ -28,6 +28,7 @@ struct _include_path *include_paths = NULL;
 struct cursor *cur = NULL;
 struct typedefs *types = NULL;
 struct _defines *defines = NULL;
+struct declared_list *g_declared_list = NULL;
 
 static void
 help(const char *progname)
@@ -346,6 +347,7 @@ main(int argc, char *const argv[])
 				struct cursor *ptr;
 				struct _defines *defptr;
 				struct typedefs *typeptr;
+				struct declared_list *list;
 
 				/* remove old cursor definitions if any are still there */
 				for (ptr = cur; ptr != NULL;)
@@ -372,6 +374,13 @@ main(int argc, char *const argv[])
 				}
 				cur = NULL;
 
+				/* remove old delared statements if any are still there */
+				for (list = g_declared_list; list != NULL;)
+				{
+					struct declared_list *this = list;
+					free(this);
+				}
+
 				/* remove non-pertinent old defines as well */
 				while (defines && !defines->pertinent)
 				{
@@ -486,6 +495,7 @@ main(int argc, char *const argv[])
 			}
 
 			free(input_filename);
+
 		}
 	}
 	return ret_value;
diff --git a/src/interfaces/ecpg/preproc/ecpg.header b/src/interfaces/ecpg/preproc/ecpg.header
index 4091ffd28b..8e931ee6e9 100644
--- a/src/interfaces/ecpg/preproc/ecpg.header
+++ b/src/interfaces/ecpg/preproc/ecpg.header
@@ -64,6 +64,8 @@ static struct ECPGtype ecpg_query = {ECPGt_char_variable, NULL, NULL, NULL, {NUL
 
 static void vmmerror(int error_code, enum errortype type, const char *error, va_list ap) pg_attribute_printf(3, 0);
 
+static void check_declared_list(const char*);
+
 /*
  * Handle parsing errors and warnings
  */
@@ -573,6 +575,28 @@ add_typedef(char *name, char *dimension, char *length, enum ECPGttype type_enum,
 		types = this;
 	}
 }
+
+/*
+ * check an SQL identifier is declared or not.
+ * If it is already declared, the global variable
+ * connection will be changed to the related connection.
+ */
+static void
+check_declared_list(const char *name)
+{
+	struct declared_list *ptr = NULL;
+	for (ptr = g_declared_list; ptr != NULL; ptr = ptr -> next)
+	{
+		if (strcmp(name, ptr -> name) == 0)
+		{
+			if (ptr -> connection)
+				{
+					connection = mm_strdup(ptr -> connection);
+					break;
+				}
+		}
+	}
+}
 %}
 
 %expect 0
diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer
index 1122f56a14..01db395c30 100644
--- a/src/interfaces/ecpg/preproc/ecpg.trailer
+++ b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -21,7 +21,7 @@ statement: ecpgstart at stmt ';' { connection = NULL; }
 			remove_typedefs(braces_open);
 			remove_variables(braces_open--);
 			if (braces_open == 0)
-			{
+		{
 				free(current_function);
 				current_function = NULL;
 			}
@@ -291,6 +291,42 @@ prepared_name: name
 		;
 
 /*
+ * Declare Statement
+ */
+ECPGDeclareStmt: DECLARE prepared_name STATEMENT
+		{
+			struct declared_list *ptr = NULL;
+			/* Check whether the declared name has been defined or not */
+			for (ptr = g_declared_list; ptr != NULL; ptr = ptr->next)
+			{
+			if (strcmp($2, ptr->name) == 0)
+			{
+				/* re-definition is a bug */
+				mmerror(PARSE_ERROR, ET_ERROR, "declared name %s is already defined", ptr->name);
+			}
+		}
+
+		/* Add a new declared name into the g_declared_list */
+		ptr = NULL;
+		ptr = (struct declared_list *)mm_alloc(sizeof(struct declared_list));
+		if (ptr)
+		{
+			/* initial definition */
+			ptr -> name = $2;
+			if (connection)
+				ptr -> connection = mm_strdup(connection);
+			else
+				ptr -> connection = NULL;
+
+			ptr -> next = g_declared_list;
+			g_declared_list = ptr;
+		}
+
+		$$ = cat_str(3 , mm_strdup("/* declare "), mm_strdup($2), mm_strdup(" as an SQL identifier */"));
+		}
+;
+
+/*
  * Declare a prepared cursor. The syntax is different from the standard
  * declare statement, so we create a new rule.
  */
@@ -300,9 +336,10 @@ ECPGCursorStmt:  DECLARE cursor_name cursor_options CURSOR opt_hold FOR prepared
 			char *cursor_marker = $2[0] == ':' ? mm_strdup("$0") : mm_strdup($2);
 			int (* strcmp_fn)(const char *, const char *) = (($2[0] == ':' || $2[0] == '"') ? strcmp : pg_strcasecmp);
 			struct variable *thisquery = (struct variable *)mm_alloc(sizeof(struct variable));
-			const char *con = connection ? connection : "NULL";
 			char *comment;
-
+			char *con;
+			check_declared_list($7);
+			con = connection ? connection : "NULL";
 			for (ptr = cur; ptr != NULL; ptr = ptr->next)
 			{
 				if (strcmp_fn($2, ptr->name) == 0)
@@ -321,7 +358,7 @@ ECPGCursorStmt:  DECLARE cursor_name cursor_options CURSOR opt_hold FOR prepared
 			this->next = cur;
 			this->name = $2;
 			this->function = (current_function ? mm_strdup(current_function) : NULL);
-			this->connection = connection;
+			this->connection = connection ? mm_strdup(connection) : NULL;
 			this->command =  cat_str(6, mm_strdup("declare"), cursor_marker, $3, mm_strdup("cursor"), $5, mm_strdup("for $1"));
 			this->argsresult = NULL;
 			this->argsresult_oos = NULL;
diff --git a/src/interfaces/ecpg/preproc/ecpg.type b/src/interfaces/ecpg/preproc/ecpg.type
index 9497b91b9d..519b737dde 100644
--- a/src/interfaces/ecpg/preproc/ecpg.type
+++ b/src/interfaces/ecpg/preproc/ecpg.type
@@ -9,6 +9,7 @@
 %type <str> ECPGDeallocateDescr
 %type <str> ECPGDeclaration
 %type <str> ECPGDeclare
+%type <str> ECPGDeclareStmt
 %type <str> ECPGDescribe
 %type <str> ECPGDisconnect
 %type <str> ECPGExecuteImmediateStmt
diff --git a/src/interfaces/ecpg/preproc/output.c b/src/interfaces/ecpg/preproc/output.c
index 65d06d5794..94bc433ed5 100644
--- a/src/interfaces/ecpg/preproc/output.c
+++ b/src/interfaces/ecpg/preproc/output.c
@@ -258,3 +258,4 @@ output_escaped_str(char *str, bool quoted)
 	if (quoted && str[0] == '"' && str[len] == '"')
 		fputs("\"", base_yyout);
 }
+
diff --git a/src/interfaces/ecpg/preproc/preproc_extern.h b/src/interfaces/ecpg/preproc/preproc_extern.h
index 323fd5c3c1..1bc6e5955f 100644
--- a/src/interfaces/ecpg/preproc/preproc_extern.h
+++ b/src/interfaces/ecpg/preproc/preproc_extern.h
@@ -48,6 +48,7 @@ extern struct _include_path *include_paths;
 extern struct cursor *cur;
 extern struct typedefs *types;
 extern struct _defines *defines;
+extern struct declared_list *g_declared_list;
 extern struct ECPGtype ecpg_no_indicator;
 extern struct variable no_indicator;
 extern struct arguments *argsinsert;
diff --git a/src/interfaces/ecpg/preproc/type.h b/src/interfaces/ecpg/preproc/type.h
index 20b279001b..01ccb74fdc 100644
--- a/src/interfaces/ecpg/preproc/type.h
+++ b/src/interfaces/ecpg/preproc/type.h
@@ -141,6 +141,13 @@ struct cursor
 	struct cursor *next;
 };
 
+struct declared_list
+{
+	char	   *name;
+	char	   *connection;
+	struct declared_list *next;
+};
+
 struct typedefs
 {
 	char	   *name;
diff --git a/src/interfaces/ecpg/test/ecpg_schedule b/src/interfaces/ecpg/test/ecpg_schedule
index 1e67d2b162..e034c5a420 100644
--- a/src/interfaces/ecpg/test/ecpg_schedule
+++ b/src/interfaces/ecpg/test/ecpg_schedule
@@ -53,6 +53,7 @@ test: sql/show
 test: sql/insupd
 test: sql/parser
 test: sql/prepareas
+test: sql/declare
 test: thread/thread
 test: thread/thread_implicit
 test: thread/prep
diff --git a/src/interfaces/ecpg/test/expected/sql-declare.c b/src/interfaces/ecpg/test/expected/sql-declare.c
new file mode 100644
index 0000000000..7ca540df28
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-declare.c
@@ -0,0 +1,580 @@
+/* Processed by ecpg (regression mode) */
+/* These include files are added by the preprocessor */
+#include <ecpglib.h>
+#include <ecpgerrno.h>
+#include <sqlca.h>
+/* End of automatic include section */
+#define ECPGdebug(X,Y) ECPGdebug((X)+100,(Y))
+
+#line 1 "declare.pgc"
+#include <locale.h>
+#include <string.h>
+#include <stdlib.h>
+
+/* exec sql whenever sqlerror  sqlprint ; */
+#line 5 "declare.pgc"
+
+
+
+#line 1 "sqlca.h"
+#ifndef POSTGRES_SQLCA_H
+#define POSTGRES_SQLCA_H
+
+#ifndef PGDLLIMPORT
+#if  defined(WIN32) || defined(__CYGWIN__)
+#define PGDLLIMPORT __declspec (dllimport)
+#else
+#define PGDLLIMPORT
+#endif							/* __CYGWIN__ */
+#endif							/* PGDLLIMPORT */
+
+#define SQLERRMC_LEN	150
+
+#ifdef __cplusplus
+extern "C"
+{
+#endif
+
+struct sqlca_t
+{
+	char		sqlcaid[8];
+	long		sqlabc;
+	long		sqlcode;
+	struct
+	{
+		int			sqlerrml;
+		char		sqlerrmc[SQLERRMC_LEN];
+	}			sqlerrm;
+	char		sqlerrp[8];
+	long		sqlerrd[6];
+	/* Element 0: empty						*/
+	/* 1: OID of processed tuple if applicable			*/
+	/* 2: number of rows processed				*/
+	/* after an INSERT, UPDATE or				*/
+	/* DELETE statement					*/
+	/* 3: empty						*/
+	/* 4: empty						*/
+	/* 5: empty						*/
+	char		sqlwarn[8];
+	/* Element 0: set to 'W' if at least one other is 'W'	*/
+	/* 1: if 'W' at least one character string		*/
+	/* value was truncated when it was			*/
+	/* stored into a host variable.             */
+
+	/*
+	 * 2: if 'W' a (hopefully) non-fatal notice occurred
+	 */	/* 3: empty */
+	/* 4: empty						*/
+	/* 5: empty						*/
+	/* 6: empty						*/
+	/* 7: empty						*/
+
+	char		sqlstate[5];
+};
+
+struct sqlca_t *ECPGget_sqlca(void);
+
+#ifndef POSTGRES_ECPG_INTERNAL
+#define sqlca (*ECPGget_sqlca())
+#endif
+
+#ifdef __cplusplus
+}
+#endif
+
+#endif
+
+#line 7 "declare.pgc"
+
+
+#line 1 "regression.h"
+
+
+
+
+
+
+#line 8 "declare.pgc"
+
+
+#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 */
+ 
+ 
+ 
+
+#line 18 "declare.pgc"
+ int f1 [ ARRAY_SZIE ] ;
+ 
+#line 19 "declare.pgc"
+ int f2 [ ARRAY_SZIE ] ;
+ 
+#line 20 "declare.pgc"
+ char f3 [ ARRAY_SZIE ] [ 20 ] ;
+/* exec sql end declare section */
+#line 21 "declare.pgc"
+
+
+int main(void)
+{
+    setlocale(LC_ALL, "C");
+
+    ECPGdebug(1, stderr);
+
+    { ECPGconnect(__LINE__, 0, "ecpg1_regression" , NULL, NULL , "con1", 0); 
+#line 29 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 29 "declare.pgc"
+
+    { ECPGconnect(__LINE__, 0, "ecpg2_regression" , NULL, NULL , "con2", 0); 
+#line 30 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 30 "declare.pgc"
+
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "create table source ( f1 integer , f2 integer , f3 varchar ( 20 ) )", ECPGt_EOIT, ECPGt_EORT);
+#line 32 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 32 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "create table source ( f1 integer , f2 integer , f3 varchar ( 20 ) )", ECPGt_EOIT, ECPGt_EORT);
+#line 33 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 33 "declare.pgc"
+
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "insert into source values ( 1 , 10 , 'db on con1' )", ECPGt_EOIT, ECPGt_EORT);
+#line 35 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 35 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "insert into source values ( 2 , 20 , 'db on con1' )", ECPGt_EOIT, ECPGt_EORT);
+#line 36 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 36 "declare.pgc"
+
+
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "insert into source values ( 1 , 10 , 'db on con2' )", ECPGt_EOIT, ECPGt_EORT);
+#line 38 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 38 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "insert into source values ( 2 , 20 , 'db on con2' )", ECPGt_EOIT, ECPGt_EORT);
+#line 39 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 39 "declare.pgc"
+
+
+    commitTable();
+
+    execute_test();
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "drop table if exists source", ECPGt_EOIT, ECPGt_EORT);
+#line 45 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 45 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "drop table if exists source", ECPGt_EOIT, ECPGt_EORT);
+#line 46 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 46 "declare.pgc"
+
+
+    commitTable();
+
+    { ECPGdisconnect(__LINE__, "ALL");
+#line 50 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 50 "declare.pgc"
+
+
+    return 0;
+}
+
+/*
+ * default connection: con2
+ * Non-default connection: con1
+ *
+ */
+void execute_test(void)
+{
+    /* exec sql begin declare section */
+     
+       
+    
+#line 63 "declare.pgc"
+ int i ;
+ 
+#line 64 "declare.pgc"
+ char * selectString = "SELECT f1,f2,f3 FROM source" ;
+/* exec sql end declare section */
+#line 65 "declare.pgc"
+
+
+    /*
+     * testcase1. using DECLARE STATEMENT without using AT clause,
+     * using PREPARE and CURSOR statement without using AT clause
+     */
+    reset();
+
+    /* declare  \"stmt_1\"  as an SQL identifier */
+#line 73 "declare.pgc"
+
+    { ECPGprepare(__LINE__, NULL, 0, "stmt_1", selectString);
+#line 74 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 74 "declare.pgc"
+
+    /* declare cur_1 cursor for $1 */
+#line 75 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare cur_1 cursor for $1", 
+	ECPGt_char_variable,(ECPGprepared_statement(NULL, "stmt_1", __LINE__)),(long)1,(long)1,(1)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
+#line 76 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 76 "declare.pgc"
+
+
+    /* exec sql whenever not found  break ; */
+#line 78 "declare.pgc"
+
+    i = 0;
+    while (1)
+    {
+        { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "fetch cur_1", ECPGt_EOIT, 
+	ECPGt_int,&(f1[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,&(f2[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(f3[i]),(long)20,(long)1,(20)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 82 "declare.pgc"
+
+if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
+#line 82 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 82 "declare.pgc"
+
+        i++;
+    }
+    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "close cur_1", ECPGt_EOIT, ECPGt_EORT);
+#line 85 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 85 "declare.pgc"
+
+    { ECPGdeallocate(__LINE__, 0, NULL, "stmt_1");
+#line 86 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 86 "declare.pgc"
+
+    /* exec sql whenever not found  continue ; */
+#line 87 "declare.pgc"
+
+
+    printResult("testcase1", 2);
+
+
+    /*
+     * testcase2. using DECLARE STATEMENT at con1,
+     * using PREPARE and CURSOR statement without using AT clause
+     */
+    reset();
+
+    /* declare  \"stmt_2\"  as an SQL identifier */
+#line 98 "declare.pgc"
+
+    { ECPGprepare(__LINE__, "con1", 0, "stmt_2", selectString);
+#line 99 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 99 "declare.pgc"
+
+    /* declare cur_2 cursor for $1 */
+#line 100 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "declare cur_2 cursor for $1", 
+	ECPGt_char_variable,(ECPGprepared_statement("con1", "stmt_2", __LINE__)),(long)1,(long)1,(1)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
+#line 101 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 101 "declare.pgc"
+
+
+    /* exec sql whenever not found  break ; */
+#line 103 "declare.pgc"
+
+    i = 0;
+    while (1)
+    {
+        { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "fetch cur_2", ECPGt_EOIT, 
+	ECPGt_int,&(f1[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,&(f2[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(f3[i]),(long)20,(long)1,(20)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 107 "declare.pgc"
+
+if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
+#line 107 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 107 "declare.pgc"
+
+        i++;
+    }
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "close cur_2", ECPGt_EOIT, ECPGt_EORT);
+#line 110 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 110 "declare.pgc"
+
+    { ECPGdeallocate(__LINE__, 0, NULL, "stmt_2");
+#line 111 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 111 "declare.pgc"
+
+    /* exec sql whenever not found  continue ; */
+#line 112 "declare.pgc"
+
+
+    printResult("testcase2", 2);
+
+    /*
+     * testcase3. using DECLARE STATEMENT at con1,
+     * using PREPARE and CURSOR statement at con2
+     */
+    reset();
+
+    /* declare  \"stmt_3\"  as an SQL identifier */
+#line 122 "declare.pgc"
+
+    { ECPGprepare(__LINE__, "con1", 0, "stmt_3", selectString);
+#line 123 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 123 "declare.pgc"
+
+    /* declare cur_3 cursor for $1 */
+#line 124 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "declare cur_3 cursor for $1", 
+	ECPGt_char_variable,(ECPGprepared_statement("con1", "stmt_3", __LINE__)),(long)1,(long)1,(1)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
+#line 125 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 125 "declare.pgc"
+
+
+    /* exec sql whenever not found  break ; */
+#line 127 "declare.pgc"
+
+    i = 0;
+    while (1)
+    {
+        { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "fetch cur_3", ECPGt_EOIT, 
+	ECPGt_int,&(f1[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,&(f2[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(f3[i]),(long)20,(long)1,(20)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 131 "declare.pgc"
+
+if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
+#line 131 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 131 "declare.pgc"
+
+        i++;
+    }
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "close cur_3", ECPGt_EOIT, ECPGt_EORT);
+#line 134 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 134 "declare.pgc"
+
+    { ECPGdeallocate(__LINE__, 0, "con2", "stmt_3");
+#line 135 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 135 "declare.pgc"
+
+    /* exec sql whenever not found  continue ; */
+#line 136 "declare.pgc"
+
+
+    printResult("testcase3", 2);
+
+
+    /*
+     * testcase4. using DECLARE STATEMENT without using AT clause,
+     * using PREPARE and CURSOR statement at con2
+     */
+    reset();
+
+    /* declare  \"stmt_4\"  as an SQL identifier */
+#line 147 "declare.pgc"
+
+    { ECPGprepare(__LINE__, "con2", 0, "stmt_4", selectString);
+#line 148 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 148 "declare.pgc"
+
+    /* declare cur_4 cursor for $1 */
+#line 149 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "declare cur_4 cursor for $1", 
+	ECPGt_char_variable,(ECPGprepared_statement("con2", "stmt_4", __LINE__)),(long)1,(long)1,(1)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
+#line 150 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 150 "declare.pgc"
+
+
+    /* exec sql whenever not found  break ; */
+#line 152 "declare.pgc"
+
+    i = 0;
+    while (1)
+    {
+        { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "fetch cur_4", ECPGt_EOIT, 
+	ECPGt_int,&(f1[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,&(f2[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(f3[i]),(long)20,(long)1,(20)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 156 "declare.pgc"
+
+if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
+#line 156 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 156 "declare.pgc"
+
+        i++;
+    }
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "close cur_4", ECPGt_EOIT, ECPGt_EORT);
+#line 159 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 159 "declare.pgc"
+
+    { ECPGdeallocate(__LINE__, 0, "con2", "stmt_4");
+#line 160 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 160 "declare.pgc"
+
+    /* exec sql whenever not found  continue ; */
+#line 161 "declare.pgc"
+
+
+    printResult("testcase4", 2);
+
+    /*
+     * testcase5. using DECLARE STATEMENT without using AT clause,
+     * using PREPARE and EXECUTE statement without using AT clause
+     */
+    reset();
+
+    /* declare  \"stmt_5\"  as an SQL identifier */
+#line 171 "declare.pgc"
+
+    { ECPGprepare(__LINE__, NULL, 0, "stmt_5", selectString);
+#line 172 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 172 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "stmt_5", ECPGt_EOIT, 
+	ECPGt_int,(f1),(long)1,(long)ARRAY_SZIE,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,(f2),(long)1,(long)ARRAY_SZIE,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(f3),(long)20,(long)ARRAY_SZIE,(20)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 173 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 173 "declare.pgc"
+
+
+    { ECPGdeallocate(__LINE__, 0, NULL, "stmt_5");
+#line 175 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 175 "declare.pgc"
+
+
+    printResult("testcase5", 2);
+}
+
+void commitTable()
+{
+    { ECPGtrans(__LINE__, "con1", "commit");
+#line 182 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 182 "declare.pgc"
+
+    { ECPGtrans(__LINE__, "con2", "commit");
+#line 183 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 183 "declare.pgc"
+
+}
+
+/*
+ * 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");
+}
diff --git a/src/interfaces/ecpg/test/expected/sql-declare.stderr b/src/interfaces/ecpg/test/expected/sql-declare.stderr
new file mode 100644
index 0000000000..71aa21ece4
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-declare.stderr
@@ -0,0 +1,286 @@
+[NO_PID]: ECPGdebug: set to 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGconnect: opening database ecpg1_regression on <DEFAULT> port <DEFAULT>  
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGconnect: opening database ecpg2_regression on <DEFAULT> port <DEFAULT>  
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: query: create table source ( f1 integer , f2 integer , f3 varchar ( 20 ) ); with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 32: OK: CREATE TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 33: query: create table source ( f1 integer , f2 integer , f3 varchar ( 20 ) ); with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 33: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 33: OK: CREATE TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 35: query: insert into source values ( 1 , 10 , 'db on con1' ); with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 35: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 35: OK: INSERT 0 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 36: query: insert into source values ( 2 , 20 , 'db on con1' ); with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 36: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 36: OK: INSERT 0 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 38: query: insert into source values ( 1 , 10 , 'db on con2' ); with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 38: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 38: OK: INSERT 0 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 39: query: insert into source values ( 2 , 20 , 'db on con2' ); with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 39: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 39: OK: INSERT 0 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 182: action "commit"; connection "con1"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 183: action "commit"; connection "con2"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: prepare_common on line 74: name stmt_1; query: "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 76: query: declare cur_1 cursor for SELECT f1,f2,f3 FROM source; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 76: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 76: OK: DECLARE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: query: fetch cur_1; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 82: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: 10 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: query: fetch cur_1; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 82: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: 20 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: query: fetch cur_1; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 82: correctly got 0 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode 100 on line 82: no data found on line 82
+[NO_PID]: sqlca: code: 100, state: 02000
+[NO_PID]: ecpg_execute on line 85: query: close cur_1; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 85: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 85: OK: CLOSE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 86: name stmt_1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: prepare_common on line 99: name stmt_2; query: "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 101: query: declare cur_2 cursor for SELECT f1,f2,f3 FROM source; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 101: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 101: OK: DECLARE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: query: fetch cur_2; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 107: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: 10 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: db on con1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: query: fetch cur_2; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 107: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: 20 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: db on con1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: query: fetch cur_2; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 107: correctly got 0 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode 100 on line 107: no data found on line 107
+[NO_PID]: sqlca: code: 100, state: 02000
+[NO_PID]: ecpg_execute on line 110: query: close cur_2; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 110: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 110: OK: CLOSE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode -230 on line 111: invalid statement name "stmt_2" on line 111
+[NO_PID]: sqlca: code: -230, state: 26000
+SQL error: invalid statement name "stmt_2" on line 111
+[NO_PID]: prepare_common on line 123: name stmt_3; query: "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 125: query: declare cur_3 cursor for SELECT f1,f2,f3 FROM source; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 125: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 125: OK: DECLARE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: query: fetch cur_3; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 131: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: 10 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: db on con1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: query: fetch cur_3; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 131: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: 20 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: db on con1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: query: fetch cur_3; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 131: correctly got 0 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode 100 on line 131: no data found on line 131
+[NO_PID]: sqlca: code: 100, state: 02000
+[NO_PID]: ecpg_execute on line 134: query: close cur_3; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 134: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 134: OK: CLOSE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode -230 on line 135: invalid statement name "stmt_3" on line 135
+[NO_PID]: sqlca: code: -230, state: 26000
+SQL error: invalid statement name "stmt_3" on line 135
+[NO_PID]: prepare_common on line 148: name stmt_4; query: "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 150: query: declare cur_4 cursor for SELECT f1,f2,f3 FROM source; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 150: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 150: OK: DECLARE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: query: fetch cur_4; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 156: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: 10 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: query: fetch cur_4; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 156: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: 20 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: query: fetch cur_4; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 156: correctly got 0 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode 100 on line 156: no data found on line 156
+[NO_PID]: sqlca: code: 100, state: 02000
+[NO_PID]: ecpg_execute on line 159: query: close cur_4; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 159: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 159: OK: CLOSE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 160: name stmt_4
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: prepare_common on line 172: name stmt_5; query: "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 173: query: SELECT f1,f2,f3 FROM source; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 173: using PQexecPrepared for "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 173: correctly got 2 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: 10 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: 20 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 175: name stmt_5
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 45: query: drop table if exists source; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 45: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 45: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 46: query: drop table if exists source; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 46: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 46: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 182: action "commit"; connection "con1"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 183: action "commit"; connection "con2"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_finish: connection con2 closed
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 0: name stmt_3
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 0: name stmt_2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_finish: connection con1 closed
+[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-declare.stdout b/src/interfaces/ecpg/test/expected/sql-declare.stdout
new file mode 100644
index 0000000000..3352d47cc4
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-declare.stdout
@@ -0,0 +1,20 @@
+****testcase1 test results:****
+f1=1, f2=10, f3=db on con2
+f1=2, f2=20, f3=db on con2
+
+****testcase2 test results:****
+f1=1, f2=10, f3=db on con1
+f1=2, f2=20, f3=db on con1
+
+****testcase3 test results:****
+f1=1, f2=10, f3=db on con1
+f1=2, f2=20, f3=db on con1
+
+****testcase4 test results:****
+f1=1, f2=10, f3=db on con2
+f1=2, f2=20, f3=db on con2
+
+****testcase5 test results:****
+f1=1, f2=10, f3=db on con2
+f1=2, f2=20, f3=db on con2
+
diff --git a/src/interfaces/ecpg/test/sql/.gitignore b/src/interfaces/ecpg/test/sql/.gitignore
index 613bdebc96..d3aaa620e0 100644
--- a/src/interfaces/ecpg/test/sql/.gitignore
+++ b/src/interfaces/ecpg/test/sql/.gitignore
@@ -10,6 +10,8 @@
 /copystdout.c
 /createtableas
 /createtableas.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 170bcd72c4..876ca8df3e 100644
--- a/src/interfaces/ecpg/test/sql/Makefile
+++ b/src/interfaces/ecpg/test/sql/Makefile
@@ -26,6 +26,7 @@ TESTS = array array.c \
         insupd insupd.c \
         twophase twophase.c \
         insupd insupd.c \
+		declare declare.c \
 		bytea bytea.c \
 		prepareas prepareas.c
 
diff --git a/src/interfaces/ecpg/test/sql/declare.pgc b/src/interfaces/ecpg/test/sql/declare.pgc
new file mode 100644
index 0000000000..f9ef468741
--- /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");
+}

Tomas Vondra

tomas.vondra@2ndquadrant.com

about 6 years ago

In reply to: kuroda.hayato@fujitsu.com (#1)

Re: ECPG: proposal for new DECLARE STATEMENT

Hi,

On Thu, Oct 31, 2019 at 12:29:30PM +0000, kuroda.hayato@fujitsu.com wrote:

Dear hackers,

As declared last month, I propose again the new ECPG grammar, DECLARE STATEMENT.
This had been committed once, but it removed from PG12 because of
some problems.
In this mail, I want to report some problems that previous implementation has,
produce a new solution, and attach a WIP patch.

[Basic function, Grammar, and Use case]
This statement will be used for the purpose of designating a connection easily.
Please see below:
/messages/by-id/4E72940DA2BF16479384A86D54D0988A4D80D3C9@G01JPEXMBKW04
The Oracle's manual will also help your understanding:
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpcc/embedded-SQL-statements-and-directives.html#GUID-0A30B7B4-BD91-42EA-AACE-2E9CBF7E9C1A

[Issues]
That's why this feature has been reverted.
1. The namespace of the identifier was not clear. If you use a same identifier for other SQL statements,
these interfered each other and statements might be executed at the unexpected connection.
2. Declaring at the outside of functions was not allowed. This specification is quite different from the other
declarative statements, so some users might be confused.
For instance, the following example was rejected.
```
EXEC SQL DECLARE stmt STATEMENT;

int
main()
{
...
EXEC SQL DECLARE cur CURSOR FOR stmt;
...
}
```
3. These specifications were not compatible with other DBMSs.

[Solutions]
The namespace is set to be a file unit. This follows other DBMSs.
When the DECLARE SATATEMENT statement is read, the name, identifier
and the related connection are recorded.
And if you use the declared identifier in order to prepare or declare cursor,
the fourth argument for ECPGdo(it represents the connection) will be overwritten.
This declaration is enabled only the precompile phase.

[Limitations]
The declaration must be appeared before using it.
This also follows Pro*C precompiler.

A WIP patch is attached. Confirm that all ECPG tests have passed,
however, some documents are not included.
They will be added later.
I applied the pgindent as a test, but it might be failed because this is the
first time for me.

I see there were no reviews of this new patch, with the feature
reimplemented after it was reverted from PG12 in September :-(

I'm not an ecpg expert (in fact I've never even used it), so my review
is pretty superficial, but I only found a couple of minor whitespace
issues (adding/removing a line/tab) - see the attached file.

Kuroda-san, you mentioned the patch is WIP. What other bits you think
are missing / need improvement? I see you mentioned some documentation
is missing - I suppose that's one of the missing pieces?

For the record, there were two threads discussing the implementation [1]/messages/by-id/1F66B161998C704BABF8989B8A2AC0A313AA41@G01JPEXMBYT05
and then the revert [2]/messages/by-id/TY2PR01MB2443EC8286995378AEB7D9F8F5B10@TY2PR01MB2443.jpnprd01.prod.outlook.com.

[1]: /messages/by-id/1F66B161998C704BABF8989B8A2AC0A313AA41@G01JPEXMBYT05
[2]: /messages/by-id/TY2PR01MB2443EC8286995378AEB7D9F8F5B10@TY2PR01MB2443.jpnprd01.prod.outlook.com

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Tomas Vondra

tomas.vondra@2ndquadrant.com

about 6 years ago

In reply to: Tomas Vondra (#2)

1 attachment(s)

Re: ECPG: proposal for new DECLARE STATEMENT

On Sun, Jan 12, 2020 at 03:52:48AM +0100, Tomas Vondra wrote:

...

I'm not an ecpg expert (in fact I've never even used it), so my review
is pretty superficial, but I only found a couple of minor whitespace
issues (adding/removing a line/tab) - see the attached file.

Meh, forgot to attach the file ...

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachments:

minor-fixes.txttext/plain; charset=us-asciiDownload

diff --git a/src/interfaces/ecpg/preproc/ecpg.addons b/src/interfaces/ecpg/preproc/ecpg.addons
index 01b36e3a3d..5fcc90dc84 100644
--- a/src/interfaces/ecpg/preproc/ecpg.addons
+++ b/src/interfaces/ecpg/preproc/ecpg.addons
@@ -60,7 +60,6 @@ ECPG: stmtPrepareStmt block
 		check_declared_list($1.name);
 		if ($1.type == NULL)
 			output_prepare_statement($1.name, $1.stmt);
-
 		else if (strlen($1.type) == 0)
 		{
 			char *stmt = cat_str(3, mm_strdup("\""), $1.stmt, mm_strdup("\""));
diff --git a/src/interfaces/ecpg/preproc/ecpg.c b/src/interfaces/ecpg/preproc/ecpg.c
index 1e8a3e6b60..93696ceb3c 100644
--- a/src/interfaces/ecpg/preproc/ecpg.c
+++ b/src/interfaces/ecpg/preproc/ecpg.c
@@ -495,7 +495,6 @@ main(int argc, char *const argv[])
 			}
 
 			free(input_filename);
-
 		}
 	}
 	return ret_value;
diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer
index 8e65d24036..052ec24077 100644
--- a/src/interfaces/ecpg/preproc/ecpg.trailer
+++ b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -21,7 +21,7 @@ statement: ecpgstart at stmt ';' { connection = NULL; }
 			remove_typedefs(braces_open);
 			remove_variables(braces_open--);
 			if (braces_open == 0)
-		{
+			{
 				free(current_function);
 				current_function = NULL;
 			}
diff --git a/src/interfaces/ecpg/preproc/output.c b/src/interfaces/ecpg/preproc/output.c
index 94bc433ed5..65d06d5794 100644
--- a/src/interfaces/ecpg/preproc/output.c
+++ b/src/interfaces/ecpg/preproc/output.c
@@ -258,4 +258,3 @@ output_escaped_str(char *str, bool quoted)
 	if (quoted && str[0] == '"' && str[len] == '"')
 		fputs("\"", base_yyout);
 }
-

David Steele

david@pgmasters.net

almost 6 years ago

In reply to: Tomas Vondra (#3)

Re: ECPG: proposal for new DECLARE STATEMENT

On 1/11/20 10:41 PM, Tomas Vondra wrote:

On Sun, Jan 12, 2020 at 03:52:48AM +0100, Tomas Vondra wrote:

...

I'm not an ecpg expert (in fact I've never even used it), so my review
is pretty superficial, but I only found a couple of minor whitespace
issues (adding/removing a line/tab) - see the attached file.

Meh, forgot to attach the file ...

Any thoughts on Tomas' comments?

A big part of moving a patch forward is keeping the thread active and
answering comments/review.

Regards,
--
-David
david@pgmasters.net

Daniel Gustafsson

daniel@yesql.se

over 5 years ago

In reply to: David Steele (#4)

Re: ECPG: proposal for new DECLARE STATEMENT

On 30 Mar 2020, at 18:53, David Steele <david@pgmasters.net> wrote:

On 1/11/20 10:41 PM, Tomas Vondra wrote:

On Sun, Jan 12, 2020 at 03:52:48AM +0100, Tomas Vondra wrote:

...

I'm not an ecpg expert (in fact I've never even used it), so my review
is pretty superficial, but I only found a couple of minor whitespace
issues (adding/removing a line/tab) - see the attached file.

Meh, forgot to attach the file ...

Any thoughts on Tomas' comments?

A big part of moving a patch forward is keeping the thread active and answering comments/review.

This patch has now been silent for quite a while, unless someone is interested
enough to bring it forward it seems about time to close it.

cheers ./daniel

Michael Meskes

meskes@postgresql.org

over 5 years ago

In reply to: Daniel Gustafsson (#5)

Re: ECPG: proposal for new DECLARE STATEMENT

This patch has now been silent for quite a while, unless someone is
interested
enough to bring it forward it seems about time to close it.

I am interested but still short on time. I will definitely look into it
as soon as I find some spare minutes.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De
Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org

kuroda.hayato@fujitsu.com

about 5 years ago

In reply to: Michael Meskes (#6)

1 attachment(s)

RE: ECPG: proposal for new DECLARE STATEMENT

Dear Tomas, Daniel, Michael,

I missed your e-mails, and I apologize the very late reply.
I want you to thank keeping the thread.

I'm not an ecpg expert (in fact I've never even used it), so my review
is pretty superficial, but I only found a couple of minor whitespace
issues (adding/removing a line/tab) - see the attached file.

Thanks, I fixed it.

Kuroda-san, you mentioned the patch is WIP. What other bits you think
are missing / need improvement? I see you mentioned some documentation
is missing - I suppose that's one of the missing pieces?

All functionalities I expect has been already implemented in the previous patch,
and I thought that only doc and reviews were needed.

Finally I attach new patch. This patch contains source changes, a test code,
and documentation changes. This one is not WIP.

I will try to review other topics on the next Commitfest.

Best regards,
Hayato Kuroda
FUJITSU LIMITED

-----Original Message-----
From: Michael Meskes <meskes@postgresql.org>
Sent: Tuesday, September 15, 2020 7:32 PM
To: pgsql-hackers@lists.postgresql.org
Subject: Re: ECPG: proposal for new DECLARE STATEMENT

This patch has now been silent for quite a while, unless someone is
interested
enough to bring it forward it seems about time to close it.

I am interested but still short on time. I will definitely look into it
as soon as I find some spare minutes.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De
Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org

Attachments:

DeclareStmt02.patchapplication/octet-stream; name=DeclareStmt02.patchDownload

diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml
index 6e3ca788f6..baa755cea7 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -249,7 +249,7 @@ EXEC SQL CONNECT TO :target USER :user USING :passwd;
    SQL statements in embedded SQL programs are by default executed on
    the current connection, that is, the most recently opened one.  If
    an application needs to manage multiple connections, then there are
-   two ways to handle this.
+   three ways to handle this.
   </para>
 
   <para>
@@ -321,6 +321,46 @@ main()
 current=testdb3 (should be testdb3)
 current=testdb2 (should be testdb2)
 current=testdb1 (should be testdb1)
+</screen>
+  </para>
+
+  <para>
+  The third option is to declare sql identifier linked to
+  the connection, for example:
+<programlisting>
+EXEC SQL AT <replaceable>connection-name</replaceable> DECLARE <replaceable>statement-name</replaceable> STATEMENT;
+EXEC SQL PREPARE <replaceable>statement-name</replaceable> FROM :<replaceable>dyn-string</replaceable>;
+</programlisting>
+   Once you link a sql identifier to a connection, you execute a dynamic SQL
+   without AT clause. Note that this option behaves like preprocessor directives,
+   therefore the link is enabled only in the file.
+  </para>
+  <para>
+   Here is an example program using this option:
+<programlisting><![CDATA[
+#include <stdio.h>
+
+EXEC SQL BEGIN DECLARE SECTION;
+char dbname[128];
+char *dym_sql = "SELECT current_database()";
+EXEC SQL END DECLARE SECTION;
+
+int main(){
+  EXEC SQL CONNECT TO postgres AS con1;
+  EXEC SQL CONNECT TO testdb AS con2;
+  EXEC SQL AT con1 DECLARE stmt STATEMENT;
+  EXEC SQL PREPARE stmt FROM :dym_sql;
+  EXEC SQL EXECUTE stmt INTO :dbname;
+  printf("%s\n", dbname);
+
+  EXEC SQL DISCONNECT ALL;
+  return 0;
+}
+]]></programlisting>
+
+   This example would produce this output, even if the default connection is testdb:
+<screen>
+postgres
 </screen>
   </para>
   </sect2>
@@ -6826,6 +6866,101 @@ EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
    </refsect1>
   </refentry>
 
+  <refentry id="ecpg-sql-declare-statement">
+   <refnamediv>
+    <refname>DECLARE STATEMENT</refname>
+    <refpurpose>declare SQL statement identifier</refpurpose>
+   </refnamediv>
+
+   <refsynopsisdiv>
+<synopsis>
+EXEC SQL [ AT <replaceable class="parameter">connection_name</replaceable> ] DECLARE <replaceable class="parameter">statement_name</replaceable> STATEMENT
+</synopsis>
+   </refsynopsisdiv>
+
+   <refsect1>
+    <title>Description</title>
+
+    <para>
+     <command>DECLARE STATEMENT</command> declares SQL statement identifier.
+     SQL statement identifier can be associated with the connection.
+     When the identifier is used by dynamic SQL statements, these SQLs are executed
+     by using the associated connection.
+     The namespace of the declaration is the precompile unit, and multiple declarations to
+     the same SQL statement identifier is not allowed.
+    </para>
+   </refsect1>
+
+   <refsect1>
+    <title>Parameters</title>
+
+    <variablelist>
+     <varlistentry>
+      <term><replaceable class="parameter">connection_name</replaceable></term>
+      <listitem>
+       <para>
+        A database connection name established by the <command>CONNECT</command> command.
+       </para>
+       <para>
+        AT clause can be omitted, but such statement has no meaning.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+
+    <variablelist>
+     <varlistentry>
+      <term><replaceable class="parameter">statement_name</replaceable></term>
+      <listitem>
+       <para>
+        The name of a SQL statement identifier, either as an SQL identifier or a host variable.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </refsect1>
+
+   <refsect1>
+    <title>Notes</title>
+    <para>
+     This association is valid only if the declaration is physically placed on top of a dynamic statement.
+    </para>
+   </refsect1>
+
+   <refsect1>
+    <title>Examples</title>
+
+<programlisting>
+EXEC SQL CONNECT TO postgres AS con1;
+EXEC SQL AT con1 DECLARE sql_stmt STATEMENT;
+EXEC SQL DECLARE cursor_name CURSOR FOR sql_stmt;
+EXEC SQL PREPARE sql_stmt FROM :dyn_string;
+EXEC SQL OPEN cursor_name;
+EXEC SQL FETCH cursor_name INTO :column1;
+EXEC SQL CLOSE cursor_name;
+</programlisting>
+   </refsect1>
+
+   <refsect1>
+    <title>Compatibility</title>
+
+    <para>
+     <command>DECLARE STATEMENT</command> is a extension of the SQL standard,
+     but can be used in famous DBMSs.
+    </para>
+   </refsect1>
+
+   <refsect1>
+    <title>See Also</title>
+
+    <simplelist type="inline">
+     <member><xref linkend="ecpg-sql-connect"/></member>
+     <member><xref linkend="ecpg-sql-declare"/></member>
+     <member><xref linkend="ecpg-sql-open"/></member>
+    </simplelist>
+   </refsect1>
+  </refentry>
+
   <refentry id="ecpg-sql-describe">
    <refnamediv>
     <refname>DESCRIBE</refname>
diff --git a/src/interfaces/ecpg/preproc/ecpg.addons b/src/interfaces/ecpg/preproc/ecpg.addons
index 300381eaad..5fcc90dc84 100644
--- a/src/interfaces/ecpg/preproc/ecpg.addons
+++ b/src/interfaces/ecpg/preproc/ecpg.addons
@@ -33,6 +33,7 @@ ECPG: stmtUpdateStmt block
 	{ output_statement($1, 1, ECPGst_prepnormal); }
 ECPG: stmtExecuteStmt block
 	{
+		check_declared_list($1.name);
 		if ($1.type == NULL || strlen($1.type) == 0)
 			output_statement($1.name, 1, ECPGst_execute);
 		else
@@ -56,6 +57,7 @@ ECPG: stmtExecuteStmt block
 	}
 ECPG: stmtPrepareStmt block
 	{
+		check_declared_list($1.name);
 		if ($1.type == NULL)
 			output_prepare_statement($1.name, $1.stmt);
 		else if (strlen($1.type) == 0)
@@ -104,6 +106,10 @@ ECPG: stmtViewStmt rule
 		whenever_action(2);
 		free($1);
 	}
+	| ECPGDeclareStmt
+	{
+		output_simple_statement($1, 0);
+	}
 	| ECPGCursorStmt
 	{
 		 output_simple_statement($1, (strncmp($1, "ECPGset_var", strlen("ECPGset_var")) == 0) ? 4 : 0);
@@ -244,14 +250,20 @@ ECPG: var_valueNumericOnly addon
 			$1 = mm_strdup("$0");
 		}
 ECPG: fetch_argscursor_name addon
-		add_additional_variables($1, false);
+		struct cursor *ptr = add_additional_variables($1, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($1[0] == ':')
 		{
 			free($1);
 			$1 = mm_strdup("$0");
 		}
 ECPG: fetch_argsfrom_incursor_name addon
-		add_additional_variables($2, false);
+		struct cursor *ptr = add_additional_variables($2, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($2[0] == ':')
 		{
 			free($2);
@@ -262,14 +274,20 @@ ECPG: fetch_argsPRIORopt_from_incursor_name addon
 ECPG: fetch_argsFIRST_Popt_from_incursor_name addon
 ECPG: fetch_argsLAST_Popt_from_incursor_name addon
 ECPG: fetch_argsALLopt_from_incursor_name addon
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($3[0] == ':')
 		{
 			free($3);
 			$3 = mm_strdup("$0");
 		}
 ECPG: fetch_argsSignedIconstopt_from_incursor_name addon
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($3[0] == ':')
 		{
 			free($3);
@@ -282,7 +300,10 @@ ECPG: fetch_argsSignedIconstopt_from_incursor_name addon
 		}
 ECPG: fetch_argsFORWARDALLopt_from_incursor_name addon
 ECPG: fetch_argsBACKWARDALLopt_from_incursor_name addon
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($4[0] == ':')
 		{
 			free($4);
@@ -292,7 +313,10 @@ ECPG: fetch_argsABSOLUTE_PSignedIconstopt_from_incursor_name addon
 ECPG: fetch_argsRELATIVE_PSignedIconstopt_from_incursor_name addon
 ECPG: fetch_argsFORWARDSignedIconstopt_from_incursor_name addon
 ECPG: fetch_argsBACKWARDSignedIconstopt_from_incursor_name addon
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		if ($4[0] == ':')
 		{
 			free($4);
@@ -388,6 +412,20 @@ ECPG: DeclareCursorStmtDECLAREcursor_namecursor_optionsCURSORopt_holdFORSelectSt
 ECPG: ClosePortalStmtCLOSEcursor_name block
 	{
 		char *cursor_marker = $2[0] == ':' ? mm_strdup("$0") : $2;
+		if (!INFORMIX_MODE)
+		{
+			struct cursor *ptr = NULL;
+			for (ptr = cur; ptr != NULL; ptr = ptr -> next)
+			{
+				if (strcmp($2, ptr -> name) == 0)
+				{
+					if (ptr -> connection)
+						connection = mm_strdup(ptr -> connection);
+
+					break;
+				}
+			}
+		}
 		$$ = cat2_str(mm_strdup("close"), cursor_marker);
 	}
 ECPG: opt_hold block
@@ -466,49 +504,73 @@ ECPG: FetchStmtMOVEfetch_args rule
 	| FETCH FORWARD cursor_name opt_ecpg_fetch_into
 	{
 		char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("fetch forward"), cursor_marker);
 	}
 	| FETCH FORWARD from_in cursor_name opt_ecpg_fetch_into
 	{
 		char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+			if (ptr -> connection)
+				connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("fetch forward from"), cursor_marker);
 	}
 	| FETCH BACKWARD cursor_name opt_ecpg_fetch_into
 	{
 		char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("fetch backward"), cursor_marker);
 	}
 	| FETCH BACKWARD from_in cursor_name opt_ecpg_fetch_into
 	{
 		char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("fetch backward from"), cursor_marker);
 	}
 	| MOVE FORWARD cursor_name
 	{
 		char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("move forward"), cursor_marker);
 	}
 	| MOVE FORWARD from_in cursor_name
 	{
 		char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("move forward from"), cursor_marker);
 	}
 	| MOVE BACKWARD cursor_name
 	{
 		char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
-		add_additional_variables($3, false);
+		struct cursor *ptr = add_additional_variables($3, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("move backward"), cursor_marker);
 	}
 	| MOVE BACKWARD from_in cursor_name
 	{
 		char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
-		add_additional_variables($4, false);
+		struct cursor *ptr = add_additional_variables($4, false);
+		if (ptr -> connection)
+			connection = mm_strdup(ptr -> connection);
+
 		$$ = cat_str(2, mm_strdup("move backward from"), cursor_marker);
 	}
 ECPG: limit_clauseLIMITselect_limit_value','select_offset_value block
diff --git a/src/interfaces/ecpg/preproc/ecpg.c b/src/interfaces/ecpg/preproc/ecpg.c
index 44a6d5119b..496c0f4a7e 100644
--- a/src/interfaces/ecpg/preproc/ecpg.c
+++ b/src/interfaces/ecpg/preproc/ecpg.c
@@ -28,6 +28,7 @@ struct _include_path *include_paths = NULL;
 struct cursor *cur = NULL;
 struct typedefs *types = NULL;
 struct _defines *defines = NULL;
+struct declared_list *g_declared_list = NULL;
 
 static void
 help(const char *progname)
@@ -347,6 +348,7 @@ main(int argc, char *const argv[])
 				struct cursor *ptr;
 				struct _defines *defptr;
 				struct typedefs *typeptr;
+				struct declared_list *list;
 
 				/* remove old cursor definitions if any are still there */
 				for (ptr = cur; ptr != NULL;)
@@ -373,6 +375,13 @@ main(int argc, char *const argv[])
 				}
 				cur = NULL;
 
+				/* remove old delared statements if any are still there */
+				for (list = g_declared_list; list != NULL;)
+				{
+					struct declared_list *this = list;
+					free(this);
+				}
+
 				/* remove non-pertinent old defines as well */
 				while (defines && !defines->pertinent)
 				{
diff --git a/src/interfaces/ecpg/preproc/ecpg.header b/src/interfaces/ecpg/preproc/ecpg.header
index f37112dd4d..b9cd5b8b28 100644
--- a/src/interfaces/ecpg/preproc/ecpg.header
+++ b/src/interfaces/ecpg/preproc/ecpg.header
@@ -64,6 +64,8 @@ static struct ECPGtype ecpg_query = {ECPGt_char_variable, NULL, NULL, NULL, {NUL
 
 static void vmmerror(int error_code, enum errortype type, const char *error, va_list ap) pg_attribute_printf(3, 0);
 
+static void check_declared_list(const char*);
+
 /*
  * Handle parsing errors and warnings
  */
@@ -576,6 +578,28 @@ add_typedef(char *name, char *dimension, char *length, enum ECPGttype type_enum,
 		types = this;
 	}
 }
+
+/*
+ * check an SQL identifier is declared or not.
+ * If it is already declared, the global variable
+ * connection will be changed to the related connection.
+ */
+static void
+check_declared_list(const char *name)
+{
+	struct declared_list *ptr = NULL;
+	for (ptr = g_declared_list; ptr != NULL; ptr = ptr -> next)
+	{
+		if (strcmp(name, ptr -> name) == 0)
+		{
+			if (ptr -> connection)
+				{
+					connection = mm_strdup(ptr -> connection);
+					break;
+				}
+		}
+	}
+}
 %}
 
 %expect 0
diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer
index 6ccc8ab916..077d893f45 100644
--- a/src/interfaces/ecpg/preproc/ecpg.trailer
+++ b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -290,6 +290,42 @@ prepared_name: name
 		| char_variable { $$ = $1; }
 		;
 
+/*
+ * Declare Statement
+ */
+ECPGDeclareStmt: DECLARE prepared_name STATEMENT
+		{
+			struct declared_list *ptr = NULL;
+			/* Check whether the declared name has been defined or not */
+			for (ptr = g_declared_list; ptr != NULL; ptr = ptr->next)
+			{
+				if (strcmp($2, ptr->name) == 0)
+				{
+					/* re-definition is not allowed */
+					mmerror(PARSE_ERROR, ET_ERROR, "declared name %s is already defined", ptr->name);
+				}
+			}
+
+			/* Add a new declared name into the g_declared_list */
+			ptr = NULL;
+			ptr = (struct declared_list *)mm_alloc(sizeof(struct declared_list));
+			if (ptr)
+			{
+				/* initial definition */
+				ptr -> name = $2;
+				if (connection)
+					ptr -> connection = mm_strdup(connection);
+				else
+					ptr -> connection = NULL;
+
+				ptr -> next = g_declared_list;
+				g_declared_list = ptr;
+			}
+
+			$$ = cat_str(3 , mm_strdup("/* declare "), mm_strdup($2), mm_strdup(" as an SQL identifier */"));
+		}
+;
+
 /*
  * Declare a prepared cursor. The syntax is different from the standard
  * declare statement, so we create a new rule.
@@ -300,9 +336,10 @@ ECPGCursorStmt:  DECLARE cursor_name cursor_options CURSOR opt_hold FOR prepared
 			char *cursor_marker = $2[0] == ':' ? mm_strdup("$0") : mm_strdup($2);
 			int (* strcmp_fn)(const char *, const char *) = (($2[0] == ':' || $2[0] == '"') ? strcmp : pg_strcasecmp);
 			struct variable *thisquery = (struct variable *)mm_alloc(sizeof(struct variable));
-			const char *con = connection ? connection : "NULL";
 			char *comment;
-
+			char *con;
+			check_declared_list($7);
+			con = connection ? connection : "NULL";
 			for (ptr = cur; ptr != NULL; ptr = ptr->next)
 			{
 				if (strcmp_fn($2, ptr->name) == 0)
@@ -321,7 +358,7 @@ ECPGCursorStmt:  DECLARE cursor_name cursor_options CURSOR opt_hold FOR prepared
 			this->next = cur;
 			this->name = $2;
 			this->function = (current_function ? mm_strdup(current_function) : NULL);
-			this->connection = connection;
+			this->connection = connection ? mm_strdup(connection) : NULL;
 			this->command =  cat_str(6, mm_strdup("declare"), cursor_marker, $3, mm_strdup("cursor"), $5, mm_strdup("for $1"));
 			this->argsresult = NULL;
 			this->argsresult_oos = NULL;
diff --git a/src/interfaces/ecpg/preproc/ecpg.type b/src/interfaces/ecpg/preproc/ecpg.type
index ffafa82af9..bee1120e30 100644
--- a/src/interfaces/ecpg/preproc/ecpg.type
+++ b/src/interfaces/ecpg/preproc/ecpg.type
@@ -9,6 +9,7 @@
 %type <str> ECPGDeallocateDescr
 %type <str> ECPGDeclaration
 %type <str> ECPGDeclare
+%type <str> ECPGDeclareStmt
 %type <str> ECPGDescribe
 %type <str> ECPGDisconnect
 %type <str> ECPGExecuteImmediateStmt
diff --git a/src/interfaces/ecpg/preproc/preproc_extern.h b/src/interfaces/ecpg/preproc/preproc_extern.h
index 51d5f94f07..992797b8bb 100644
--- a/src/interfaces/ecpg/preproc/preproc_extern.h
+++ b/src/interfaces/ecpg/preproc/preproc_extern.h
@@ -47,6 +47,7 @@ extern struct _include_path *include_paths;
 extern struct cursor *cur;
 extern struct typedefs *types;
 extern struct _defines *defines;
+extern struct declared_list *g_declared_list;
 extern struct ECPGtype ecpg_no_indicator;
 extern struct variable no_indicator;
 extern struct arguments *argsinsert;
diff --git a/src/interfaces/ecpg/preproc/type.h b/src/interfaces/ecpg/preproc/type.h
index 20b279001b..01ccb74fdc 100644
--- a/src/interfaces/ecpg/preproc/type.h
+++ b/src/interfaces/ecpg/preproc/type.h
@@ -141,6 +141,13 @@ struct cursor
 	struct cursor *next;
 };
 
+struct declared_list
+{
+	char	   *name;
+	char	   *connection;
+	struct declared_list *next;
+};
+
 struct typedefs
 {
 	char	   *name;
diff --git a/src/interfaces/ecpg/test/ecpg_schedule b/src/interfaces/ecpg/test/ecpg_schedule
index 1e67d2b162..e034c5a420 100644
--- a/src/interfaces/ecpg/test/ecpg_schedule
+++ b/src/interfaces/ecpg/test/ecpg_schedule
@@ -53,6 +53,7 @@ test: sql/show
 test: sql/insupd
 test: sql/parser
 test: sql/prepareas
+test: sql/declare
 test: thread/thread
 test: thread/thread_implicit
 test: thread/prep
diff --git a/src/interfaces/ecpg/test/expected/sql-declare.c b/src/interfaces/ecpg/test/expected/sql-declare.c
new file mode 100644
index 0000000000..7ca540df28
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-declare.c
@@ -0,0 +1,580 @@
+/* Processed by ecpg (regression mode) */
+/* These include files are added by the preprocessor */
+#include <ecpglib.h>
+#include <ecpgerrno.h>
+#include <sqlca.h>
+/* End of automatic include section */
+#define ECPGdebug(X,Y) ECPGdebug((X)+100,(Y))
+
+#line 1 "declare.pgc"
+#include <locale.h>
+#include <string.h>
+#include <stdlib.h>
+
+/* exec sql whenever sqlerror  sqlprint ; */
+#line 5 "declare.pgc"
+
+
+
+#line 1 "sqlca.h"
+#ifndef POSTGRES_SQLCA_H
+#define POSTGRES_SQLCA_H
+
+#ifndef PGDLLIMPORT
+#if  defined(WIN32) || defined(__CYGWIN__)
+#define PGDLLIMPORT __declspec (dllimport)
+#else
+#define PGDLLIMPORT
+#endif							/* __CYGWIN__ */
+#endif							/* PGDLLIMPORT */
+
+#define SQLERRMC_LEN	150
+
+#ifdef __cplusplus
+extern "C"
+{
+#endif
+
+struct sqlca_t
+{
+	char		sqlcaid[8];
+	long		sqlabc;
+	long		sqlcode;
+	struct
+	{
+		int			sqlerrml;
+		char		sqlerrmc[SQLERRMC_LEN];
+	}			sqlerrm;
+	char		sqlerrp[8];
+	long		sqlerrd[6];
+	/* Element 0: empty						*/
+	/* 1: OID of processed tuple if applicable			*/
+	/* 2: number of rows processed				*/
+	/* after an INSERT, UPDATE or				*/
+	/* DELETE statement					*/
+	/* 3: empty						*/
+	/* 4: empty						*/
+	/* 5: empty						*/
+	char		sqlwarn[8];
+	/* Element 0: set to 'W' if at least one other is 'W'	*/
+	/* 1: if 'W' at least one character string		*/
+	/* value was truncated when it was			*/
+	/* stored into a host variable.             */
+
+	/*
+	 * 2: if 'W' a (hopefully) non-fatal notice occurred
+	 */	/* 3: empty */
+	/* 4: empty						*/
+	/* 5: empty						*/
+	/* 6: empty						*/
+	/* 7: empty						*/
+
+	char		sqlstate[5];
+};
+
+struct sqlca_t *ECPGget_sqlca(void);
+
+#ifndef POSTGRES_ECPG_INTERNAL
+#define sqlca (*ECPGget_sqlca())
+#endif
+
+#ifdef __cplusplus
+}
+#endif
+
+#endif
+
+#line 7 "declare.pgc"
+
+
+#line 1 "regression.h"
+
+
+
+
+
+
+#line 8 "declare.pgc"
+
+
+#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 */
+ 
+ 
+ 
+
+#line 18 "declare.pgc"
+ int f1 [ ARRAY_SZIE ] ;
+ 
+#line 19 "declare.pgc"
+ int f2 [ ARRAY_SZIE ] ;
+ 
+#line 20 "declare.pgc"
+ char f3 [ ARRAY_SZIE ] [ 20 ] ;
+/* exec sql end declare section */
+#line 21 "declare.pgc"
+
+
+int main(void)
+{
+    setlocale(LC_ALL, "C");
+
+    ECPGdebug(1, stderr);
+
+    { ECPGconnect(__LINE__, 0, "ecpg1_regression" , NULL, NULL , "con1", 0); 
+#line 29 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 29 "declare.pgc"
+
+    { ECPGconnect(__LINE__, 0, "ecpg2_regression" , NULL, NULL , "con2", 0); 
+#line 30 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 30 "declare.pgc"
+
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "create table source ( f1 integer , f2 integer , f3 varchar ( 20 ) )", ECPGt_EOIT, ECPGt_EORT);
+#line 32 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 32 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "create table source ( f1 integer , f2 integer , f3 varchar ( 20 ) )", ECPGt_EOIT, ECPGt_EORT);
+#line 33 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 33 "declare.pgc"
+
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "insert into source values ( 1 , 10 , 'db on con1' )", ECPGt_EOIT, ECPGt_EORT);
+#line 35 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 35 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "insert into source values ( 2 , 20 , 'db on con1' )", ECPGt_EOIT, ECPGt_EORT);
+#line 36 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 36 "declare.pgc"
+
+
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "insert into source values ( 1 , 10 , 'db on con2' )", ECPGt_EOIT, ECPGt_EORT);
+#line 38 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 38 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "insert into source values ( 2 , 20 , 'db on con2' )", ECPGt_EOIT, ECPGt_EORT);
+#line 39 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 39 "declare.pgc"
+
+
+    commitTable();
+
+    execute_test();
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "drop table if exists source", ECPGt_EOIT, ECPGt_EORT);
+#line 45 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 45 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "drop table if exists source", ECPGt_EOIT, ECPGt_EORT);
+#line 46 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 46 "declare.pgc"
+
+
+    commitTable();
+
+    { ECPGdisconnect(__LINE__, "ALL");
+#line 50 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 50 "declare.pgc"
+
+
+    return 0;
+}
+
+/*
+ * default connection: con2
+ * Non-default connection: con1
+ *
+ */
+void execute_test(void)
+{
+    /* exec sql begin declare section */
+     
+       
+    
+#line 63 "declare.pgc"
+ int i ;
+ 
+#line 64 "declare.pgc"
+ char * selectString = "SELECT f1,f2,f3 FROM source" ;
+/* exec sql end declare section */
+#line 65 "declare.pgc"
+
+
+    /*
+     * testcase1. using DECLARE STATEMENT without using AT clause,
+     * using PREPARE and CURSOR statement without using AT clause
+     */
+    reset();
+
+    /* declare  \"stmt_1\"  as an SQL identifier */
+#line 73 "declare.pgc"
+
+    { ECPGprepare(__LINE__, NULL, 0, "stmt_1", selectString);
+#line 74 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 74 "declare.pgc"
+
+    /* declare cur_1 cursor for $1 */
+#line 75 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare cur_1 cursor for $1", 
+	ECPGt_char_variable,(ECPGprepared_statement(NULL, "stmt_1", __LINE__)),(long)1,(long)1,(1)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
+#line 76 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 76 "declare.pgc"
+
+
+    /* exec sql whenever not found  break ; */
+#line 78 "declare.pgc"
+
+    i = 0;
+    while (1)
+    {
+        { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "fetch cur_1", ECPGt_EOIT, 
+	ECPGt_int,&(f1[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,&(f2[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(f3[i]),(long)20,(long)1,(20)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 82 "declare.pgc"
+
+if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
+#line 82 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 82 "declare.pgc"
+
+        i++;
+    }
+    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "close cur_1", ECPGt_EOIT, ECPGt_EORT);
+#line 85 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 85 "declare.pgc"
+
+    { ECPGdeallocate(__LINE__, 0, NULL, "stmt_1");
+#line 86 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 86 "declare.pgc"
+
+    /* exec sql whenever not found  continue ; */
+#line 87 "declare.pgc"
+
+
+    printResult("testcase1", 2);
+
+
+    /*
+     * testcase2. using DECLARE STATEMENT at con1,
+     * using PREPARE and CURSOR statement without using AT clause
+     */
+    reset();
+
+    /* declare  \"stmt_2\"  as an SQL identifier */
+#line 98 "declare.pgc"
+
+    { ECPGprepare(__LINE__, "con1", 0, "stmt_2", selectString);
+#line 99 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 99 "declare.pgc"
+
+    /* declare cur_2 cursor for $1 */
+#line 100 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "declare cur_2 cursor for $1", 
+	ECPGt_char_variable,(ECPGprepared_statement("con1", "stmt_2", __LINE__)),(long)1,(long)1,(1)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
+#line 101 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 101 "declare.pgc"
+
+
+    /* exec sql whenever not found  break ; */
+#line 103 "declare.pgc"
+
+    i = 0;
+    while (1)
+    {
+        { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "fetch cur_2", ECPGt_EOIT, 
+	ECPGt_int,&(f1[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,&(f2[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(f3[i]),(long)20,(long)1,(20)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 107 "declare.pgc"
+
+if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
+#line 107 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 107 "declare.pgc"
+
+        i++;
+    }
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "close cur_2", ECPGt_EOIT, ECPGt_EORT);
+#line 110 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 110 "declare.pgc"
+
+    { ECPGdeallocate(__LINE__, 0, NULL, "stmt_2");
+#line 111 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 111 "declare.pgc"
+
+    /* exec sql whenever not found  continue ; */
+#line 112 "declare.pgc"
+
+
+    printResult("testcase2", 2);
+
+    /*
+     * testcase3. using DECLARE STATEMENT at con1,
+     * using PREPARE and CURSOR statement at con2
+     */
+    reset();
+
+    /* declare  \"stmt_3\"  as an SQL identifier */
+#line 122 "declare.pgc"
+
+    { ECPGprepare(__LINE__, "con1", 0, "stmt_3", selectString);
+#line 123 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 123 "declare.pgc"
+
+    /* declare cur_3 cursor for $1 */
+#line 124 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "declare cur_3 cursor for $1", 
+	ECPGt_char_variable,(ECPGprepared_statement("con1", "stmt_3", __LINE__)),(long)1,(long)1,(1)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
+#line 125 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 125 "declare.pgc"
+
+
+    /* exec sql whenever not found  break ; */
+#line 127 "declare.pgc"
+
+    i = 0;
+    while (1)
+    {
+        { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "fetch cur_3", ECPGt_EOIT, 
+	ECPGt_int,&(f1[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,&(f2[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(f3[i]),(long)20,(long)1,(20)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 131 "declare.pgc"
+
+if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
+#line 131 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 131 "declare.pgc"
+
+        i++;
+    }
+    { ECPGdo(__LINE__, 0, 1, "con1", 0, ECPGst_normal, "close cur_3", ECPGt_EOIT, ECPGt_EORT);
+#line 134 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 134 "declare.pgc"
+
+    { ECPGdeallocate(__LINE__, 0, "con2", "stmt_3");
+#line 135 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 135 "declare.pgc"
+
+    /* exec sql whenever not found  continue ; */
+#line 136 "declare.pgc"
+
+
+    printResult("testcase3", 2);
+
+
+    /*
+     * testcase4. using DECLARE STATEMENT without using AT clause,
+     * using PREPARE and CURSOR statement at con2
+     */
+    reset();
+
+    /* declare  \"stmt_4\"  as an SQL identifier */
+#line 147 "declare.pgc"
+
+    { ECPGprepare(__LINE__, "con2", 0, "stmt_4", selectString);
+#line 148 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 148 "declare.pgc"
+
+    /* declare cur_4 cursor for $1 */
+#line 149 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "declare cur_4 cursor for $1", 
+	ECPGt_char_variable,(ECPGprepared_statement("con2", "stmt_4", __LINE__)),(long)1,(long)1,(1)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
+#line 150 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 150 "declare.pgc"
+
+
+    /* exec sql whenever not found  break ; */
+#line 152 "declare.pgc"
+
+    i = 0;
+    while (1)
+    {
+        { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "fetch cur_4", ECPGt_EOIT, 
+	ECPGt_int,&(f1[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,&(f2[i]),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(f3[i]),(long)20,(long)1,(20)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 156 "declare.pgc"
+
+if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
+#line 156 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 156 "declare.pgc"
+
+        i++;
+    }
+    { ECPGdo(__LINE__, 0, 1, "con2", 0, ECPGst_normal, "close cur_4", ECPGt_EOIT, ECPGt_EORT);
+#line 159 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 159 "declare.pgc"
+
+    { ECPGdeallocate(__LINE__, 0, "con2", "stmt_4");
+#line 160 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 160 "declare.pgc"
+
+    /* exec sql whenever not found  continue ; */
+#line 161 "declare.pgc"
+
+
+    printResult("testcase4", 2);
+
+    /*
+     * testcase5. using DECLARE STATEMENT without using AT clause,
+     * using PREPARE and EXECUTE statement without using AT clause
+     */
+    reset();
+
+    /* declare  \"stmt_5\"  as an SQL identifier */
+#line 171 "declare.pgc"
+
+    { ECPGprepare(__LINE__, NULL, 0, "stmt_5", selectString);
+#line 172 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 172 "declare.pgc"
+
+    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "stmt_5", ECPGt_EOIT, 
+	ECPGt_int,(f1),(long)1,(long)ARRAY_SZIE,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_int,(f2),(long)1,(long)ARRAY_SZIE,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, 
+	ECPGt_char,(f3),(long)20,(long)ARRAY_SZIE,(20)*sizeof(char), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 173 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 173 "declare.pgc"
+
+
+    { ECPGdeallocate(__LINE__, 0, NULL, "stmt_5");
+#line 175 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 175 "declare.pgc"
+
+
+    printResult("testcase5", 2);
+}
+
+void commitTable()
+{
+    { ECPGtrans(__LINE__, "con1", "commit");
+#line 182 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 182 "declare.pgc"
+
+    { ECPGtrans(__LINE__, "con2", "commit");
+#line 183 "declare.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 183 "declare.pgc"
+
+}
+
+/*
+ * 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");
+}
diff --git a/src/interfaces/ecpg/test/expected/sql-declare.stderr b/src/interfaces/ecpg/test/expected/sql-declare.stderr
new file mode 100644
index 0000000000..71aa21ece4
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-declare.stderr
@@ -0,0 +1,286 @@
+[NO_PID]: ECPGdebug: set to 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGconnect: opening database ecpg1_regression on <DEFAULT> port <DEFAULT>  
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGconnect: opening database ecpg2_regression on <DEFAULT> port <DEFAULT>  
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: query: create table source ( f1 integer , f2 integer , f3 varchar ( 20 ) ); with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 32: OK: CREATE TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 33: query: create table source ( f1 integer , f2 integer , f3 varchar ( 20 ) ); with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 33: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 33: OK: CREATE TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 35: query: insert into source values ( 1 , 10 , 'db on con1' ); with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 35: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 35: OK: INSERT 0 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 36: query: insert into source values ( 2 , 20 , 'db on con1' ); with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 36: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 36: OK: INSERT 0 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 38: query: insert into source values ( 1 , 10 , 'db on con2' ); with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 38: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 38: OK: INSERT 0 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 39: query: insert into source values ( 2 , 20 , 'db on con2' ); with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 39: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 39: OK: INSERT 0 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 182: action "commit"; connection "con1"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 183: action "commit"; connection "con2"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: prepare_common on line 74: name stmt_1; query: "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 76: query: declare cur_1 cursor for SELECT f1,f2,f3 FROM source; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 76: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 76: OK: DECLARE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: query: fetch cur_1; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 82: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: 10 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: query: fetch cur_1; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 82: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: 20 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 82: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: query: fetch cur_1; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 82: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 82: correctly got 0 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode 100 on line 82: no data found on line 82
+[NO_PID]: sqlca: code: 100, state: 02000
+[NO_PID]: ecpg_execute on line 85: query: close cur_1; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 85: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 85: OK: CLOSE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 86: name stmt_1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: prepare_common on line 99: name stmt_2; query: "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 101: query: declare cur_2 cursor for SELECT f1,f2,f3 FROM source; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 101: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 101: OK: DECLARE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: query: fetch cur_2; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 107: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: 10 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: db on con1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: query: fetch cur_2; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 107: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: 20 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 107: RESULT: db on con1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: query: fetch cur_2; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 107: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 107: correctly got 0 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode 100 on line 107: no data found on line 107
+[NO_PID]: sqlca: code: 100, state: 02000
+[NO_PID]: ecpg_execute on line 110: query: close cur_2; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 110: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 110: OK: CLOSE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode -230 on line 111: invalid statement name "stmt_2" on line 111
+[NO_PID]: sqlca: code: -230, state: 26000
+SQL error: invalid statement name "stmt_2" on line 111
+[NO_PID]: prepare_common on line 123: name stmt_3; query: "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 125: query: declare cur_3 cursor for SELECT f1,f2,f3 FROM source; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 125: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 125: OK: DECLARE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: query: fetch cur_3; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 131: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: 10 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: db on con1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: query: fetch cur_3; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 131: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: 20 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 131: RESULT: db on con1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: query: fetch cur_3; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 131: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 131: correctly got 0 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode 100 on line 131: no data found on line 131
+[NO_PID]: sqlca: code: 100, state: 02000
+[NO_PID]: ecpg_execute on line 134: query: close cur_3; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 134: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 134: OK: CLOSE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode -230 on line 135: invalid statement name "stmt_3" on line 135
+[NO_PID]: sqlca: code: -230, state: 26000
+SQL error: invalid statement name "stmt_3" on line 135
+[NO_PID]: prepare_common on line 148: name stmt_4; query: "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 150: query: declare cur_4 cursor for SELECT f1,f2,f3 FROM source; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 150: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 150: OK: DECLARE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: query: fetch cur_4; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 156: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: 10 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: query: fetch cur_4; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 156: correctly got 1 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: 20 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 156: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: query: fetch cur_4; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 156: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 156: correctly got 0 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlcode 100 on line 156: no data found on line 156
+[NO_PID]: sqlca: code: 100, state: 02000
+[NO_PID]: ecpg_execute on line 159: query: close cur_4; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 159: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 159: OK: CLOSE CURSOR
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 160: name stmt_4
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: prepare_common on line 172: name stmt_5; query: "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 173: query: SELECT f1,f2,f3 FROM source; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 173: using PQexecPrepared for "SELECT f1,f2,f3 FROM source"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 173: correctly got 2 tuples with 3 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: 2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: 10 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: 20 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 173: RESULT: db on con2 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 175: name stmt_5
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 45: query: drop table if exists source; with 0 parameter(s) on connection con1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 45: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 45: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 46: query: drop table if exists source; with 0 parameter(s) on connection con2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 46: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 46: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 182: action "commit"; connection "con1"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGtrans on line 183: action "commit"; connection "con2"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_finish: connection con2 closed
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 0: name stmt_3
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: deallocate_one on line 0: name stmt_2
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_finish: connection con1 closed
+[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-declare.stdout b/src/interfaces/ecpg/test/expected/sql-declare.stdout
new file mode 100644
index 0000000000..3352d47cc4
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-declare.stdout
@@ -0,0 +1,20 @@
+****testcase1 test results:****
+f1=1, f2=10, f3=db on con2
+f1=2, f2=20, f3=db on con2
+
+****testcase2 test results:****
+f1=1, f2=10, f3=db on con1
+f1=2, f2=20, f3=db on con1
+
+****testcase3 test results:****
+f1=1, f2=10, f3=db on con1
+f1=2, f2=20, f3=db on con1
+
+****testcase4 test results:****
+f1=1, f2=10, f3=db on con2
+f1=2, f2=20, f3=db on con2
+
+****testcase5 test results:****
+f1=1, f2=10, f3=db on con2
+f1=2, f2=20, f3=db on con2
+
diff --git a/src/interfaces/ecpg/test/sql/.gitignore b/src/interfaces/ecpg/test/sql/.gitignore
index 613bdebc96..d3aaa620e0 100644
--- a/src/interfaces/ecpg/test/sql/.gitignore
+++ b/src/interfaces/ecpg/test/sql/.gitignore
@@ -10,6 +10,8 @@
 /copystdout.c
 /createtableas
 /createtableas.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 170bcd72c4..876ca8df3e 100644
--- a/src/interfaces/ecpg/test/sql/Makefile
+++ b/src/interfaces/ecpg/test/sql/Makefile
@@ -26,6 +26,7 @@ TESTS = array array.c \
         insupd insupd.c \
         twophase twophase.c \
         insupd insupd.c \
+		declare declare.c \
 		bytea bytea.c \
 		prepareas prepareas.c
 
diff --git a/src/interfaces/ecpg/test/sql/declare.pgc b/src/interfaces/ecpg/test/sql/declare.pgc
new file mode 100644
index 0000000000..f9ef468741
--- /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");
+}

Shawn Wang

shawn.wang.pg@gmail.com

about 5 years ago

In reply to: kuroda.hayato@fujitsu.com (#7)

Re: ECPG: proposal for new DECLARE STATEMENT

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: tested, passed
Documentation: tested, passed

Recently I have been doing some work on ecpg. So I review this patch. No problem was found.

The new status of this patch is: Ready for Committer

kuroda.hayato@fujitsu.com

almost 5 years ago

In reply to: Shawn Wang (#8)

RE: ECPG: proposal for new DECLARE STATEMENT

Dear Hackers,

I know I'm asking a big favor, but could you review(and commit) the patch?
The status has become RFC last Nov., but no one checked this after that.
Maybe Meskes is quite busy and have no time to review it.

The main part of the patch is about 200 lines(It means not so long), and maybe
I have reviewed other patches more than it.

I will review more, so I'm happy if this commits until the end of next CF.

Best Regards,
Hayato Kuroda
FUJITSU LIMITED