Thread: DECLARE doesn't set/reset sqlca after DECLARE cursor

DECLARE doesn't set/reset sqlca after DECLARE cursor

From

Boszormenyi Zoltan

Date:

12 August 2009, 17:14:06

Hi,

a customer of us complained a behavioural difference
between ESQL/C and ECPG. They check sqlca.sqlcode
almost everywhere in their application currently under
porting to PostgreSQL. Somewhere in their code
however there was a place where a statement error
was ignored and the error was reported when the code
reached the next DECLARE statement.

It seems that this pseudo-statement is the only one that
doesn't set/reset sqlca, I was thinking about adding that
in a way that a call to ecpg_init() gets emitted.

The attached patch implements this. The only downside
is that now DECLARE CURSOR cannot appear outside
of a function, a change in test/preproc/variable.pgc reflects
this fact. The largest part of the patch is the change in the
expected C source outcome of the regression tests. The only
test where the ecpg_log output has changed is variable.pgc.

Best regards,
Zoltán Böszörményi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

diff -dcrpN pgsql.describe/src/interfaces/ecpg/ecpglib/exports.txt
pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/exports.txt
*** pgsql.describe/src/interfaces/ecpg/ecpglib/exports.txt    2008-03-25 13:58:49.000000000 +0100
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/exports.txt    2009-08-12 18:13:11.000000000 +0200
*************** ECPGstatus                       23
*** 26,28 ****
--- 26,29 ----
  ECPGtrans                        24
  sqlprint                         25
  ECPGget_PGconn             26
+ ECPGreset_sqlca             27
diff -dcrpN pgsql.describe/src/interfaces/ecpg/ecpglib/misc.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/misc.c
*** pgsql.describe/src/interfaces/ecpg/ecpglib/misc.c    2009-08-07 13:06:28.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/misc.c    2009-08-12 18:22:35.000000000 +0200
*************** ecpg_gettext(const char *msgid)
*** 489,491 ****
--- 489,499 ----
  }

  #endif   /* ENABLE_NLS */
+
+ bool
+ ECPGreset_sqlca(int lineno, const char *connection_name)
+ {
+     struct connection *con = ecpg_get_connection(connection_name);
+
+     return ecpg_init(con, connection_name, lineno);
+ }
diff -dcrpN pgsql.describe/src/interfaces/ecpg/include/ecpglib.h
pgsql.declare-reset-sqlca/src/interfaces/ecpg/include/ecpglib.h
*** pgsql.describe/src/interfaces/ecpg/include/ecpglib.h    2009-08-11 14:34:03.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/include/ecpglib.h    2009-08-12 18:21:06.000000000 +0200
*************** bool        ECPGset_desc(int, const char *, in
*** 84,89 ****
--- 84,90 ----
  void        ECPGset_noind_null(enum ECPGttype, void *);
  bool        ECPGis_noind_null(enum ECPGttype, void *);
  bool        ECPGdescribe(int, bool, const char *, const char *, ...);
+ bool        ECPGreset_sqlca(int, const char *);

  /* dynamic result allocation */
  void        ECPGfree_auto_mem(void);
diff -dcrpN pgsql.describe/src/interfaces/ecpg/preproc/ecpg.addons
pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.addons
*** pgsql.describe/src/interfaces/ecpg/preproc/ecpg.addons    2009-08-11 14:34:03.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.addons    2009-08-12 18:59:22.000000000 +0200
*************** ECPG: DeclareCursorStmtDECLAREcursor_nam
*** 298,303 ****
--- 298,306 ----
      {
          struct cursor *ptr, *this;
          char *cursor_marker = $2[0] == ':' ? make_str("$0") : mm_strdup($2);
+         char *command, *comment;
+
+         const char *con = connection ? connection : "NULL";

          for (ptr = cur; ptr != NULL; ptr = ptr->next)
          {
*************** ECPG: DeclareCursorStmtDECLAREcursor_nam
*** 317,326 ****
          argsinsert = argsresult = NULL;
          cur = this;

          if (INFORMIX_MODE)
!             $$ = cat_str(5, adjust_informix(this->argsinsert), adjust_informix(this->argsresult), make_str("/*"),
mm_strdup(this->command),make_str("*/")); 
          else
!             $$ = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
      }
  ECPG: ClosePortalStmtCLOSEcursor_name block
      {
--- 320,333 ----
          argsinsert = argsresult = NULL;
          cur = this;

+         command = (char *)mm_alloc(sizeof("ECPGreset_sqlca(__LINE__, );") + strlen(con));
+         sprintf(command, "ECPGreset_sqlca(__LINE__, %s);", con);
+         comment = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
+
          if (INFORMIX_MODE)
!             $$ = cat_str(4, adjust_informix(this->argsinsert), adjust_informix(this->argsresult), command, comment);
          else
!             $$ = cat2_str(command, comment);
      }
  ECPG: ClosePortalStmtCLOSEcursor_name block
      {
diff -dcrpN pgsql.describe/src/interfaces/ecpg/preproc/ecpg.trailer
pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.trailer
*** pgsql.describe/src/interfaces/ecpg/preproc/ecpg.trailer    2009-08-11 17:18:31.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.trailer    2009-08-12 18:48:07.000000000 +0200
*************** ECPGCursorStmt:  DECLARE cursor_name cur
*** 323,328 ****
--- 323,329 ----
              char *cursor_marker = $2[0] == ':' ? make_str("$0") : mm_strdup($2);
              struct variable *thisquery = (struct variable *)mm_alloc(sizeof(struct variable));
              const char *con = connection ? connection : "NULL";
+             char *command;

              for (ptr = cur; ptr != NULL; ptr = ptr->next)
              {
*************** ECPGCursorStmt:  DECLARE cursor_name cur
*** 356,362 ****
              add_variable_to_head(&(this->argsinsert), thisquery, &no_indicator);
              cur = this;

!             $$ = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
          }
          ;

--- 357,366 ----
              add_variable_to_head(&(this->argsinsert), thisquery, &no_indicator);
              cur = this;

!             command = (char *)mm_alloc(sizeof("ECPGreset_sqlca(__LINE__, );") + strlen(con));
!             sprintf(command, "ECPGreset_sqlca(__LINE__, %s);", con);
!
!             $$ = cat_str(4, command, make_str("/*"), mm_strdup(this->command), make_str("*/"));
          }
          ;

diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-cursor.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-cursor.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-cursor.c    2009-08-10 18:06:56.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-cursor.c    2009-08-12
18:55:23.000000000+0200 
*************** if (sqlca.sqlcode < 0) exit (1);}
*** 156,162 ****

      strcpy(msg, "declare");
      ECPG_informix_set_var( 0, &( curname1 ), __LINE__);\
!  /* declare $0 cursor for select id , t from t1 */
  #line 59 "cursor.pgc"


--- 156,162 ----

      strcpy(msg, "declare");
      ECPG_informix_set_var( 0, &( curname1 ), __LINE__);\
!  ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for select id , t from t1 */
  #line 59 "cursor.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 292,298 ****
      ECPG_informix_set_var( 3, &( curname2 ), __LINE__);\
   ECPG_informix_set_var( 1, ( t ), __LINE__);\
   ECPG_informix_set_var( 2, &( id ), __LINE__);\
!  /* declare $0 cursor for select id , t from t1 */
  #line 100 "cursor.pgc"


--- 292,298 ----
      ECPG_informix_set_var( 3, &( curname2 ), __LINE__);\
   ECPG_informix_set_var( 1, ( t ), __LINE__);\
   ECPG_informix_set_var( 2, &( id ), __LINE__);\
!  ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for select id , t from t1 */
  #line 100 "cursor.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 441,447 ****


      strcpy(msg, "declare");
!     /* declare $0 cursor for $1 */
  #line 143 "cursor.pgc"


--- 441,447 ----


      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for $1 */
  #line 143 "cursor.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 596,602 ****


      strcpy(msg, "declare");
!     /* declare $0 cursor for $1 */
  #line 193 "cursor.pgc"


--- 596,602 ----


      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for $1 */
  #line 193 "cursor.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-sqlda.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-sqlda.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-sqlda.c    2009-08-11 14:24:20.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-sqlda.c    2009-08-12
18:55:25.000000000+0200 
*************** if (sqlca.sqlcode < 0) exit (1);}
*** 291,297 ****


      strcpy(msg, "declare");
!     /* declare mycur1 cursor for $1 */
  #line 100 "sqlda.pgc"


--- 291,297 ----


      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare mycur1 cursor for $1 */
  #line 100 "sqlda.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 366,372 ****


      strcpy(msg, "declare");
!     /* declare mycur2 cursor for $1 */
  #line 137 "sqlda.pgc"


--- 366,372 ----


      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare mycur2 cursor for $1 */
  #line 137 "sqlda.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-test_informix.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-test_informix.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-test_informix.c    2009-08-09 22:00:45.000000000
+0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-test_informix.c    2009-08-12
18:55:26.000000000+0200 
*************** if (sqlca.sqlcode < 0) dosqlprint ( );}
*** 147,153 ****


      ECPG_informix_set_var( 0, &( i ), __LINE__);\
!  /* declare c cursor for select * from test where i <= $1  */
  #line 48 "test_informix.pgc"

      openit();
--- 147,153 ----


      ECPG_informix_set_var( 0, &( i ), __LINE__);\
!  ECPGreset_sqlca(__LINE__, NULL); /* declare c cursor for select * from test where i <= $1  */
  #line 48 "test_informix.pgc"

      openit();
diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/preproc-autoprep.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/preproc-autoprep.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/preproc-autoprep.c    2009-05-25 12:08:49.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/preproc-autoprep.c    2009-08-12 18:55:29.000000000
+0200
*************** if (sqlca.sqlcode < 0) sqlprint();}
*** 121,127 ****
    for (i=0; i<4; i++)
        printf("item[%d] = %d\n", i, ind[i] ? -1 : item[i]);

!   /* declare C cursor for select Item1 from T */
  #line 33 "autoprep.pgc"


--- 121,127 ----
    for (i=0; i<4; i++)
        printf("item[%d] = %d\n", i, ind[i] ? -1 : item[i]);

!   ECPGreset_sqlca(__LINE__, NULL); /* declare C cursor for select Item1 from T */
  #line 33 "autoprep.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/preproc-cursor.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/preproc-cursor.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/preproc-cursor.c    2009-08-10 18:04:28.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/preproc-cursor.c    2009-08-12 18:55:30.000000000 +0200
*************** if (sqlca.sqlcode < 0) exit (1);}
*** 153,159 ****
      /* Dynamic cursorname test with INTO list in FETCH stmts */

      strcpy(msg, "declare");
!     /* declare $0 cursor for select id , t from t1 */
  #line 59 "cursor.pgc"


--- 153,159 ----
      /* Dynamic cursorname test with INTO list in FETCH stmts */

      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for select id , t from t1 */
  #line 59 "cursor.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 286,292 ****
      /* Dynamic cursorname test with INTO list in DECLARE stmt */

      strcpy(msg, "declare");
!     /* declare $0 cursor for select id , t from t1 */
  #line 100 "cursor.pgc"


--- 286,292 ----
      /* Dynamic cursorname test with INTO list in DECLARE stmt */

      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for select id , t from t1 */
  #line 100 "cursor.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 435,441 ****


      strcpy(msg, "declare");
!     /* declare $0 cursor for $1 */
  #line 143 "cursor.pgc"


--- 435,441 ----


      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for $1 */
  #line 143 "cursor.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 590,596 ****


      strcpy(msg, "declare");
!     /* declare $0 cursor for $1 */
  #line 193 "cursor.pgc"


--- 590,596 ----


      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for $1 */
  #line 193 "cursor.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/preproc-variable.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/preproc-variable.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/preproc-variable.c    2008-12-30 15:28:02.000000000 +0100
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/preproc-variable.c    2009-08-12 18:55:32.000000000
+0200
*************** typedef union { int integer; short small
*** 46,66 ****
  #line 15 "variable.pgc"


- /* declare cur cursor for select name , born , age , married , children from family */
- #line 18 "variable.pgc"
-
-
  int
  main (void)
  {
      struct birthinfo {
! #line 23 "variable.pgc"
   long born ;

! #line 23 "variable.pgc"
   short age ;
   } ;
! #line 23 "variable.pgc"

  /* exec sql begin declare section */

--- 46,62 ----
  #line 15 "variable.pgc"


  int
  main (void)
  {
      struct birthinfo {
! #line 20 "variable.pgc"
   long born ;

! #line 20 "variable.pgc"
   short age ;
   } ;
! #line 20 "variable.pgc"

  /* exec sql begin declare section */

*************** main (void)
*** 71,117 ****



! #line 27 "variable.pgc"
   struct personal_struct {
! #line 25 "variable.pgc"
!   struct varchar_name_25  { int len; char arr[ BUFFERSIZ ]; }  name ;

! #line 26 "variable.pgc"
   struct birthinfo birth ;
   } personal , * p ;

! #line 30 "variable.pgc"
   struct personal_indicator {
! #line 28 "variable.pgc"
   int ind_name ;

! #line 29 "variable.pgc"
   struct birthinfo ind_birth ;
   } ind_personal , * i ;

! #line 31 "variable.pgc"
   ind ind_children ;
  /* exec sql end declare section */
! #line 32 "variable.pgc"



! #line 34 "variable.pgc"
   char * married = NULL ;

! #line 34 "variable.pgc"


! #line 35 "variable.pgc"
   long ind_married ;

! #line 35 "variable.pgc"


! #line 36 "variable.pgc"
   ind children ;

! #line 36 "variable.pgc"


      char msg[128];
--- 67,113 ----



! #line 24 "variable.pgc"
   struct personal_struct {
! #line 22 "variable.pgc"
!   struct varchar_name_22  { int len; char arr[ BUFFERSIZ ]; }  name ;

! #line 23 "variable.pgc"
   struct birthinfo birth ;
   } personal , * p ;

! #line 27 "variable.pgc"
   struct personal_indicator {
! #line 25 "variable.pgc"
   int ind_name ;

! #line 26 "variable.pgc"
   struct birthinfo ind_birth ;
   } ind_personal , * i ;

! #line 28 "variable.pgc"
   ind ind_children ;
  /* exec sql end declare section */
! #line 29 "variable.pgc"



! #line 31 "variable.pgc"
   char * married = NULL ;

! #line 31 "variable.pgc"


! #line 32 "variable.pgc"
   long ind_married ;

! #line 32 "variable.pgc"


! #line 33 "variable.pgc"
   ind children ;

! #line 33 "variable.pgc"


      char msg[128];
*************** main (void)
*** 120,197 ****

      strcpy(msg, "connect");
      { ECPGconnect(__LINE__, 0, "regress1" , NULL, NULL , NULL, 0);
! #line 43 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 43 "variable.pgc"


      strcpy(msg, "set");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "set datestyle to iso", ECPGt_EOIT, ECPGt_EORT);
! #line 46 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 46 "variable.pgc"


      strcpy(msg, "create");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table family ( name char ( 8 ) , born integer , age
smallint, married date , children integer )", ECPGt_EOIT, ECPGt_EORT); 
! #line 49 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 49 "variable.pgc"


      strcpy(msg, "insert");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into family ( name , married , children ) values ( 'Mum'
,'19870714' , 3 )", ECPGt_EOIT, ECPGt_EORT); 
! #line 52 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 52 "variable.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into family ( name , born , married , children ) values
('Dad' , '19610721' , '19870714' , 3 )", ECPGt_EOIT, ECPGt_EORT); 
! #line 53 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 53 "variable.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into family ( name , age ) values ( 'Child 1' , 16 )",
ECPGt_EOIT,ECPGt_EORT); 
! #line 54 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 54 "variable.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into family ( name , age ) values ( 'Child 2' , 14 )",
ECPGt_EOIT,ECPGt_EORT); 
! #line 55 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 55 "variable.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into family ( name , age ) values ( 'Child 3' , 9 )",
ECPGt_EOIT,ECPGt_EORT); 
! #line 56 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 56 "variable.pgc"


      strcpy(msg, "commit");
      { ECPGtrans(__LINE__, NULL, "commit");
! #line 59 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 59 "variable.pgc"


      strcpy(msg, "open");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare cur cursor for select name , born , age , married ,
childrenfrom family", ECPGt_EOIT, ECPGt_EORT); 
! #line 62 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 62 "variable.pgc"


      /* exec sql whenever not found  break ; */
! #line 64 "variable.pgc"


      p=&personal;
--- 116,198 ----

      strcpy(msg, "connect");
      { ECPGconnect(__LINE__, 0, "regress1" , NULL, NULL , NULL, 0);
! #line 40 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 40 "variable.pgc"


      strcpy(msg, "set");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "set datestyle to iso", ECPGt_EOIT, ECPGt_EORT);
! #line 43 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 43 "variable.pgc"


      strcpy(msg, "create");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table family ( name char ( 8 ) , born integer , age
smallint, married date , children integer )", ECPGt_EOIT, ECPGt_EORT); 
! #line 46 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 46 "variable.pgc"


      strcpy(msg, "insert");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into family ( name , married , children ) values ( 'Mum'
,'19870714' , 3 )", ECPGt_EOIT, ECPGt_EORT); 
! #line 49 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 49 "variable.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into family ( name , born , married , children ) values
('Dad' , '19610721' , '19870714' , 3 )", ECPGt_EOIT, ECPGt_EORT); 
! #line 50 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 50 "variable.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into family ( name , age ) values ( 'Child 1' , 16 )",
ECPGt_EOIT,ECPGt_EORT); 
! #line 51 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 51 "variable.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into family ( name , age ) values ( 'Child 2' , 14 )",
ECPGt_EOIT,ECPGt_EORT); 
! #line 52 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 52 "variable.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into family ( name , age ) values ( 'Child 3' , 9 )",
ECPGt_EOIT,ECPGt_EORT); 
! #line 53 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 53 "variable.pgc"


      strcpy(msg, "commit");
      { ECPGtrans(__LINE__, NULL, "commit");
! #line 56 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 56 "variable.pgc"
!
!
!     strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare cur cursor for select name , born , age , married , children from
family*/ 
! #line 60 "variable.pgc"


      strcpy(msg, "open");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare cur cursor for select name , born , age , married ,
childrenfrom family", ECPGt_EOIT, ECPGt_EORT); 
! #line 63 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 63 "variable.pgc"


      /* exec sql whenever not found  break ; */
! #line 65 "variable.pgc"


      p=&personal;
*************** if (sqlca.sqlcode < 0) exit (1);}
*** 200,206 ****
      while (1) {
          strcpy(msg, "fetch");
          { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "fetch cur", ECPGt_EOIT,
!     ECPGt_varchar,&(p->name),(long)BUFFERSIZ,(long)1,sizeof(struct varchar_name_25),
      ECPGt_int,&(i->ind_name),(long)1,(long)1,sizeof(int),
      ECPGt_long,&(p->birth.born),(long)1,(long)1,sizeof(long),
      ECPGt_long,&(i->ind_birth.born),(long)1,(long)1,sizeof(long),
--- 201,207 ----
      while (1) {
          strcpy(msg, "fetch");
          { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "fetch cur", ECPGt_EOIT,
!     ECPGt_varchar,&(p->name),(long)BUFFERSIZ,(long)1,sizeof(struct varchar_name_22),
      ECPGt_int,&(i->ind_name),(long)1,(long)1,sizeof(int),
      ECPGt_long,&(p->birth.born),(long)1,(long)1,sizeof(long),
      ECPGt_long,&(i->ind_birth.born),(long)1,(long)1,sizeof(long),
*************** if (sqlca.sqlcode < 0) exit (1);}
*** 210,222 ****
      ECPGt_long,&(ind_married),(long)1,(long)1,sizeof(long),
      ECPGt_int,&(children.integer),(long)1,(long)1,sizeof(int),
      ECPGt_short,&(ind_children.smallint),(long)1,(long)1,sizeof(short), ECPGt_EORT);
! #line 71 "variable.pgc"

  if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
! #line 71 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 71 "variable.pgc"

          printf("%8.8s", personal.name.arr);
          if (i->ind_birth.born >= 0)
--- 211,223 ----
      ECPGt_long,&(ind_married),(long)1,(long)1,sizeof(long),
      ECPGt_int,&(children.integer),(long)1,(long)1,sizeof(int),
      ECPGt_short,&(ind_children.smallint),(long)1,(long)1,sizeof(short), ECPGt_EORT);
! #line 72 "variable.pgc"

  if (sqlca.sqlcode == ECPG_NOT_FOUND) break;
! #line 72 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 72 "variable.pgc"

          printf("%8.8s", personal.name.arr);
          if (i->ind_birth.born >= 0)
*************** if (sqlca.sqlcode < 0) exit (1);}
*** 235,268 ****

      strcpy(msg, "close");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "close cur", ECPGt_EOIT, ECPGt_EORT);
! #line 88 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 88 "variable.pgc"


      strcpy(msg, "drop");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table family", ECPGt_EOIT, ECPGt_EORT);
! #line 91 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 91 "variable.pgc"


      strcpy(msg, "commit");
      { ECPGtrans(__LINE__, NULL, "commit");
! #line 94 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 94 "variable.pgc"


      strcpy(msg, "disconnect");
      { ECPGdisconnect(__LINE__, "CURRENT");
! #line 97 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 97 "variable.pgc"


      return (0);
--- 236,269 ----

      strcpy(msg, "close");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "close cur", ECPGt_EOIT, ECPGt_EORT);
! #line 89 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 89 "variable.pgc"


      strcpy(msg, "drop");
      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table family", ECPGt_EOIT, ECPGt_EORT);
! #line 92 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 92 "variable.pgc"


      strcpy(msg, "commit");
      { ECPGtrans(__LINE__, NULL, "commit");
! #line 95 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 95 "variable.pgc"


      strcpy(msg, "disconnect");
      { ECPGdisconnect(__LINE__, "CURRENT");
! #line 98 "variable.pgc"

  if (sqlca.sqlcode < 0) exit (1);}
! #line 98 "variable.pgc"


      return (0);
diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/preproc-variable.stderr
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/preproc-variable.stderr
*** pgsql.describe/src/interfaces/ecpg/test/expected/preproc-variable.stderr    2008-12-30 15:28:02.000000000 +0100
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/preproc-variable.stderr    2009-08-12
18:55:54.000000000+0200 
***************
*** 2,168 ****
  [NO_PID]: sqlca: code: 0, state: 00000
  [NO_PID]: ECPGconnect: opening database regress1 on <DEFAULT> port <DEFAULT>
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 46: query: set datestyle to iso; with 0 parameter(s) on connection regress1
  [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_execute on line 46: OK: SET
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 49: query: create table family ( name char ( 8 ) , born integer , age smallint ,
marrieddate , children integer ); with 0 parameter(s) on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 49: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 49: OK: CREATE TABLE
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 52: query: insert into family ( name , married , children ) values ( 'Mum' ,
'19870714', 3 ); with 0 parameter(s) on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 52: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 52: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 53: query: insert into family ( name , born , married , children ) values ( 'Dad' ,
'19610721', '19870714' , 3 ); with 0 parameter(s) on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 53: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 53: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 54: query: insert into family ( name , age ) values ( 'Child 1' , 16 ); with 0
parameter(s)on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 54: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 54: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 55: query: insert into family ( name , age ) values ( 'Child 2' , 14 ); with 0
parameter(s)on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 55: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 55: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 56: query: insert into family ( name , age ) values ( 'Child 3' , 9 ); with 0
parameter(s)on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 56: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 56: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ECPGtrans on line 59: action "commit"; connection "regress1"
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 62: query: declare cur cursor for select name , born , age , married , children from
family;with 0 parameter(s) on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 62: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 62: OK: DECLARE CURSOR
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: correctly got 1 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: Mum      offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_store_result on line 71: allocating memory for 1 tuples
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: 1987-07-14 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: 3 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: correctly got 1 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: Dad      offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: 19610721 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_store_result on line 71: allocating memory for 1 tuples
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: 1987-07-14 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: 3 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: correctly got 1 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: Child 1  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: 16 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_store_result on line 71: allocating memory for 1 tuples
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: correctly got 1 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: Child 2  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: 14 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_store_result on line 71: allocating memory for 1 tuples
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: correctly got 1 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: Child 3  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT: 9 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_store_result on line 71: allocating memory for 1 tuples
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 71: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 71: correctly got 0 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: raising sqlcode 100 on line 71: no data found on line 71
  [NO_PID]: sqlca: code: 100, state: 02000
! [NO_PID]: ecpg_execute on line 88: query: close cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 88: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 88: OK: CLOSE CURSOR
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 91: query: drop table family; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 91: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 91: OK: DROP TABLE
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ECPGtrans on line 94: action "commit"; connection "regress1"
  [NO_PID]: sqlca: code: 0, state: 00000
  [NO_PID]: ecpg_finish: connection regress1 closed
  [NO_PID]: sqlca: code: 0, state: 00000
--- 2,168 ----
  [NO_PID]: sqlca: code: 0, state: 00000
  [NO_PID]: ECPGconnect: opening database regress1 on <DEFAULT> port <DEFAULT>
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 43: query: set datestyle to iso; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 43: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 43: OK: SET
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 46: query: create table family ( name char ( 8 ) , born integer , age smallint ,
marrieddate , children integer ); with 0 parameter(s) on connection regress1 
  [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_execute on line 46: OK: CREATE TABLE
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 49: query: insert into family ( name , married , children ) values ( 'Mum' ,
'19870714', 3 ); with 0 parameter(s) on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 49: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 49: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 50: query: insert into family ( name , born , married , children ) values ( 'Dad' ,
'19610721', '19870714' , 3 ); with 0 parameter(s) on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 50: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 50: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 51: query: insert into family ( name , age ) values ( 'Child 1' , 16 ); with 0
parameter(s)on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 51: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 51: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 52: query: insert into family ( name , age ) values ( 'Child 2' , 14 ); with 0
parameter(s)on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 52: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 52: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 53: query: insert into family ( name , age ) values ( 'Child 3' , 9 ); with 0
parameter(s)on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 53: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 53: OK: INSERT 0 1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ECPGtrans on line 56: action "commit"; connection "regress1"
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 63: query: declare cur cursor for select name , born , age , married , children from
family;with 0 parameter(s) on connection regress1 
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 63: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 63: OK: DECLARE CURSOR
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: correctly got 1 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: Mum      offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_store_result on line 72: allocating memory for 1 tuples
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: 1987-07-14 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: 3 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: correctly got 1 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: Dad      offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: 19610721 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_store_result on line 72: allocating memory for 1 tuples
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: 1987-07-14 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: 3 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: correctly got 1 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: Child 1  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: 16 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_store_result on line 72: allocating memory for 1 tuples
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: correctly got 1 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: Child 2  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: 14 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_store_result on line 72: allocating memory for 1 tuples
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: correctly got 1 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: Child 3  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT: 9 offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_store_result on line 72: allocating memory for 1 tuples
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_get_data on line 72: RESULT:  offset: -1; array: yes
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: query: fetch cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 72: correctly got 0 tuples with 5 fields
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: raising sqlcode 100 on line 72: no data found on line 72
  [NO_PID]: sqlca: code: 100, state: 02000
! [NO_PID]: ecpg_execute on line 89: query: close cur; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 89: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 89: OK: CLOSE CURSOR
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 92: query: drop table family; with 0 parameter(s) on connection regress1
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 92: using PQexec
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ecpg_execute on line 92: OK: DROP TABLE
  [NO_PID]: sqlca: code: 0, state: 00000
! [NO_PID]: ECPGtrans on line 95: action "commit"; connection "regress1"
  [NO_PID]: sqlca: code: 0, state: 00000
  [NO_PID]: ecpg_finish: connection regress1 closed
  [NO_PID]: sqlca: code: 0, state: 00000
diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/sql-binary.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-binary.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/sql-binary.c    2009-08-07 13:06:28.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-binary.c    2009-08-12 18:55:33.000000000 +0200
*************** main (void)
*** 107,113 ****
        exit (sqlca.sqlcode);
      }

!   /* declare C cursor for select name , accs , byte from empl where idnum = $1  */
  #line 58 "binary.pgc"

    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare C cursor for select name , accs , byte from empl where
idnum= $1 ",  
--- 107,113 ----
        exit (sqlca.sqlcode);
      }

!   ECPGreset_sqlca(__LINE__, NULL); /* declare C cursor for select name , accs , byte from empl where idnum = $1  */
  #line 58 "binary.pgc"

    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare C cursor for select name , accs , byte from empl where
idnum= $1 ",  
*************** main (void)
*** 133,139 ****
    printf ("name=%s, accs=%d byte=%s\n", empl.name, empl.accs, empl.byte);

    memset(empl.name, 0, 21L);
!   /* declare B binary cursor for select name , accs , byte from empl where idnum = $1  */
  #line 70 "binary.pgc"

    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare B binary cursor for select name , accs , byte from empl
whereidnum = $1 ",  
--- 133,139 ----
    printf ("name=%s, accs=%d byte=%s\n", empl.name, empl.accs, empl.byte);

    memset(empl.name, 0, 21L);
!   ECPGreset_sqlca(__LINE__, NULL); /* declare B binary cursor for select name , accs , byte from empl where idnum =
$1 */ 
  #line 70 "binary.pgc"

    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare B binary cursor for select name , accs , byte from empl
whereidnum = $1 ",  
*************** main (void)
*** 166,172 ****
      printf("(%o)", (unsigned char)empl.byte[i]);
    printf("\n");

!   /* declare A binary cursor for select byte from empl where idnum = $1  */
  #line 87 "binary.pgc"

    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare A binary cursor for select byte from empl where idnum =
$1",  
--- 166,172 ----
      printf("(%o)", (unsigned char)empl.byte[i]);
    printf("\n");

!   ECPGreset_sqlca(__LINE__, NULL); /* declare A binary cursor for select byte from empl where idnum = $1  */
  #line 87 "binary.pgc"

    { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare A binary cursor for select byte from empl where idnum =
$1",  
diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/sql-desc.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-desc.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/sql-desc.c    2008-12-30 15:28:02.000000000 +0100
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-desc.c    2009-08-12 18:55:34.000000000 +0200
*************** if (sqlca.sqlcode < 0) sqlprint();}
*** 242,248 ****

      printf("output = %s\n", val2output);

!     /* declare c1 cursor for $1 */
  #line 57 "desc.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare c1 cursor for $1",
--- 242,248 ----

      printf("output = %s\n", val2output);

!     ECPGreset_sqlca(__LINE__, NULL); /* declare c1 cursor for $1 */
  #line 57 "desc.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare c1 cursor for $1",
*************** if (sqlca.sqlcode < 0) sqlprint();}
*** 292,298 ****
  #line 67 "desc.pgc"


!     /* declare c2 cursor for $1 */
  #line 69 "desc.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare c2 cursor for $1",
--- 292,298 ----
  #line 67 "desc.pgc"


!     ECPGreset_sqlca(__LINE__, NULL); /* declare c2 cursor for $1 */
  #line 69 "desc.pgc"

      { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "declare c2 cursor for $1",
diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/sql-dyntest.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-dyntest.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/sql-dyntest.c    2009-05-25 12:08:49.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-dyntest.c    2009-08-12 18:55:35.000000000 +0200
*************** if (sqlca.sqlcode < 0) error ( );}
*** 257,263 ****
  if (sqlca.sqlcode < 0) error ( );}
  #line 57 "dyntest.pgc"

!   /* declare MYCURS cursor for $1 */
  #line 58 "dyntest.pgc"


--- 257,263 ----
  if (sqlca.sqlcode < 0) error ( );}
  #line 57 "dyntest.pgc"

!   ECPGreset_sqlca(__LINE__, NULL); /* declare MYCURS cursor for $1 */
  #line 58 "dyntest.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/sql-execute.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-execute.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/sql-execute.c    2008-12-30 15:28:02.000000000 +0100
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-execute.c    2009-08-12 18:55:35.000000000 +0200
*************** if (sqlca.sqlcode < 0) sqlprint();}
*** 136,142 ****
  if (sqlca.sqlcode < 0) sqlprint();}
  #line 49 "execute.pgc"

!     /* declare CUR cursor for $1 */
  #line 50 "execute.pgc"


--- 136,142 ----
  if (sqlca.sqlcode < 0) sqlprint();}
  #line 49 "execute.pgc"

!     ECPGreset_sqlca(__LINE__, NULL); /* declare CUR cursor for $1 */
  #line 50 "execute.pgc"


*************** if (sqlca.sqlcode < 0) sqlprint();}
*** 201,207 ****
  if (sqlca.sqlcode < 0) sqlprint();}
  #line 71 "execute.pgc"

!     /* declare CUR2 cursor for $1 */
  #line 72 "execute.pgc"


--- 201,207 ----
  if (sqlca.sqlcode < 0) sqlprint();}
  #line 71 "execute.pgc"

!     ECPGreset_sqlca(__LINE__, NULL); /* declare CUR2 cursor for $1 */
  #line 72 "execute.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/sql-fetch.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-fetch.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/sql-fetch.c    2009-05-25 12:08:49.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-fetch.c    2009-08-12 18:55:36.000000000 +0200
*************** if (sqlca.sqlcode < 0) sqlprint();}
*** 95,101 ****
  #line 24 "fetch.pgc"


!   /* declare C cursor for select * from My_Table */
  #line 26 "fetch.pgc"


--- 95,101 ----
  #line 24 "fetch.pgc"


!   ECPGreset_sqlca(__LINE__, NULL); /* declare C cursor for select * from My_Table */
  #line 26 "fetch.pgc"


*************** if (sqlca.sqlcode < 0) sqlprint();}
*** 162,168 ****

    printf("%d: %s\n", i, str);

!   /* declare D cursor for select * from My_Table where Item1 = $1 */
  #line 42 "fetch.pgc"


--- 162,168 ----

    printf("%d: %s\n", i, str);

!   ECPGreset_sqlca(__LINE__, NULL); /* declare D cursor for select * from My_Table where Item1 = $1 */
  #line 42 "fetch.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/sql-oldexec.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-oldexec.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/sql-oldexec.c    2008-12-30 15:28:02.000000000 +0100
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-oldexec.c    2009-08-12 18:55:37.000000000 +0200
*************** if (sqlca.sqlcode < 0) sqlprint();}
*** 136,142 ****
  if (sqlca.sqlcode < 0) sqlprint();}
  #line 49 "oldexec.pgc"

!     /* declare CUR cursor for $1 */
  #line 50 "oldexec.pgc"


--- 136,142 ----
  if (sqlca.sqlcode < 0) sqlprint();}
  #line 49 "oldexec.pgc"

!     ECPGreset_sqlca(__LINE__, NULL); /* declare CUR cursor for $1 */
  #line 50 "oldexec.pgc"


*************** if (sqlca.sqlcode < 0) sqlprint();}
*** 195,201 ****
  if (sqlca.sqlcode < 0) sqlprint();}
  #line 70 "oldexec.pgc"

!     /* declare CUR3 cursor for $1 */
  #line 71 "oldexec.pgc"


--- 195,201 ----
  if (sqlca.sqlcode < 0) sqlprint();}
  #line 70 "oldexec.pgc"

!     ECPGreset_sqlca(__LINE__, NULL); /* declare CUR3 cursor for $1 */
  #line 71 "oldexec.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/sql-quote.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-quote.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/sql-quote.c    2009-05-25 12:08:49.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/sql-quote.c    2009-08-12 18:55:37.000000000 +0200
*************** if (sqlca.sqlwarn[0] == 'W') sqlprint();
*** 135,141 ****
  if (sqlca.sqlcode < 0) sqlprint();}
  #line 37 "quote.pgc"

!   /* declare C cursor for select * from \"My_Table\" */
  #line 38 "quote.pgc"


--- 135,141 ----
  if (sqlca.sqlcode < 0) sqlprint();}
  #line 37 "quote.pgc"

!   ECPGreset_sqlca(__LINE__, NULL); /* declare C cursor for select * from \"My_Table\" */
  #line 38 "quote.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/preproc/variable.pgc
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/preproc/variable.pgc
*** pgsql.describe/src/interfaces/ecpg/test/preproc/variable.pgc    2007-05-27 13:16:41.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/preproc/variable.pgc    2009-08-12 18:50:43.000000000 +0200
*************** typedef union { int integer; short small
*** 14,22 ****
  #define BUFFERSIZ 8
  exec sql type str is varchar[BUFFERSIZ];

- exec sql declare cur cursor for
-        select name, born, age, married, children from family;
-
  int
  main (void)
  {
--- 14,19 ----
*************** exec sql end declare section;
*** 58,63 ****
--- 55,64 ----
      strcpy(msg, "commit");
      exec sql commit;

+     strcpy(msg, "declare");
+     exec sql declare cur cursor for
+         select name, born, age, married, children from family;
+
      strcpy(msg, "open");
      exec sql open cur;

Re: DECLARE doesn't set/reset sqlca after DECLARE cursor

From

Michael Meskes

Date:

13 August 2009, 11:33:34

On Wed, Aug 12, 2009 at 07:13:44PM +0200, Boszormenyi Zoltan wrote:
> a customer of us complained a behavioural difference
> ...
> The attached patch implements this. The only downside
> is that now DECLARE CURSOR cannot appear outside
> of a function, a change in test/preproc/variable.pgc reflects

DECLARE by definition is a declarative command and as such should be able to
live outside a function.

Michael

-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

Re: DECLARE doesn't set/reset sqlca after DECLARE cursor

From

Boszormenyi Zoltan

Date:

13 August 2009, 15:56:06

Michael Meskes írta:
> On Wed, Aug 12, 2009 at 07:13:44PM +0200, Boszormenyi Zoltan wrote:
>
>> a customer of us complained a behavioural difference
>> ...
>> The attached patch implements this. The only downside
>> is that now DECLARE CURSOR cannot appear outside
>> of a function, a change in test/preproc/variable.pgc reflects
>>
>
> DECLARE by definition is a declarative command and as such should be able to
> live outside a function.
>

Okay, so it's a declarative command. But if we're in a function,
we should still emit a call to ecpg_init, to be able to follow
the Informix behaviour. We can limit it it compat mode, though.
The attached patch does this, and detects being inside of a function
by braces_open > 0. Short of rewriting ECPG into a flull-fledged
C/C++ preprocessor, we can't do better currently.

In compat mode, you cannot do
    DECLARE mycur CURSOR FOR SELECT ... INTO :var, ...
or
    DECLARE mycur CURSOR FOR SELECT ... WHERE field = ?
in the global scope because adjust_informix() emits function calls
outside of a function. Or is this declaration illegal?
At least it should be documented in PostgreSQL.

Best regards,
Zoltán Böszörményi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

diff -dcrpN pgsql.describe/src/interfaces/ecpg/ecpglib/exports.txt
pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/exports.txt
*** pgsql.describe/src/interfaces/ecpg/ecpglib/exports.txt    2008-03-25 13:58:49.000000000 +0100
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/exports.txt    2009-08-12 18:13:11.000000000 +0200
*************** ECPGstatus                       23
*** 26,28 ****
--- 26,29 ----
  ECPGtrans                        24
  sqlprint                         25
  ECPGget_PGconn             26
+ ECPGreset_sqlca             27
diff -dcrpN pgsql.describe/src/interfaces/ecpg/ecpglib/misc.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/misc.c
*** pgsql.describe/src/interfaces/ecpg/ecpglib/misc.c    2009-08-07 13:06:28.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/misc.c    2009-08-12 18:22:35.000000000 +0200
*************** ecpg_gettext(const char *msgid)
*** 489,491 ****
--- 489,499 ----
  }

  #endif   /* ENABLE_NLS */
+
+ bool
+ ECPGreset_sqlca(int lineno, const char *connection_name)
+ {
+     struct connection *con = ecpg_get_connection(connection_name);
+
+     return ecpg_init(con, connection_name, lineno);
+ }
diff -dcrpN pgsql.describe/src/interfaces/ecpg/include/ecpglib.h
pgsql.declare-reset-sqlca/src/interfaces/ecpg/include/ecpglib.h
*** pgsql.describe/src/interfaces/ecpg/include/ecpglib.h    2009-08-11 14:34:03.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/include/ecpglib.h    2009-08-12 18:21:06.000000000 +0200
*************** bool        ECPGset_desc(int, const char *, in
*** 84,89 ****
--- 84,90 ----
  void        ECPGset_noind_null(enum ECPGttype, void *);
  bool        ECPGis_noind_null(enum ECPGttype, void *);
  bool        ECPGdescribe(int, bool, const char *, const char *, ...);
+ bool        ECPGreset_sqlca(int, const char *);

  /* dynamic result allocation */
  void        ECPGfree_auto_mem(void);
diff -dcrpN pgsql.describe/src/interfaces/ecpg/preproc/ecpg.addons
pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.addons
*** pgsql.describe/src/interfaces/ecpg/preproc/ecpg.addons    2009-08-11 14:34:03.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.addons    2009-08-13 17:40:41.000000000 +0200
*************** ECPG: DeclareCursorStmtDECLAREcursor_nam
*** 318,324 ****
          cur = this;

          if (INFORMIX_MODE)
!             $$ = cat_str(5, adjust_informix(this->argsinsert), adjust_informix(this->argsresult), make_str("/*"),
mm_strdup(this->command),make_str("*/")); 
          else
              $$ = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
      }
--- 318,338 ----
          cur = this;

          if (INFORMIX_MODE)
!         {
!             char *comment;
!             const char *con = connection ? connection : "NULL";
!
!             comment = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
!
!             if (braces_open > 0) /* we're in a function */
!             {
!                 char *command = (char *)mm_alloc(sizeof("ECPGreset_sqlca(__LINE__, );") + strlen(con));
!                 sprintf(command, "ECPGreset_sqlca(__LINE__, %s);", con);
!                 $$ = cat_str(4, adjust_informix(this->argsinsert), adjust_informix(this->argsresult), command,
comment);
!             }
!             else
!                 $$ = cat_str(3, adjust_informix(this->argsinsert), adjust_informix(this->argsresult), comment);
!         }
          else
              $$ = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
      }
diff -dcrpN pgsql.describe/src/interfaces/ecpg/preproc/ecpg.trailer
pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.trailer
*** pgsql.describe/src/interfaces/ecpg/preproc/ecpg.trailer    2009-08-11 17:18:31.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.trailer    2009-08-13 17:48:27.000000000 +0200
*************** ECPGCursorStmt:  DECLARE cursor_name cur
*** 356,362 ****
              add_variable_to_head(&(this->argsinsert), thisquery, &no_indicator);
              cur = this;

!             $$ = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
          }
          ;

--- 356,374 ----
              add_variable_to_head(&(this->argsinsert), thisquery, &no_indicator);
              cur = this;

!             if (INFORMIX_MODE)
!             {
!                 if (braces_open > 0) /* we're in a function */
!                 {
!                     char *command = (char *)mm_alloc(sizeof("ECPGreset_sqlca(__LINE__, );") + strlen(con));
!                     sprintf(command, "ECPGreset_sqlca(__LINE__, %s);", con);
!                     $$ = cat_str(4, command, make_str("/*"), mm_strdup(this->command), make_str("*/"));
!                 }
!                 else
!                     $$ = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
!             }
!             else
!                 $$ = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
          }
          ;

diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-cursor.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-cursor.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-cursor.c    2009-08-10 18:06:56.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-cursor.c    2009-08-13
17:00:25.000000000+0200 
*************** if (sqlca.sqlcode < 0) exit (1);}
*** 156,162 ****

      strcpy(msg, "declare");
      ECPG_informix_set_var( 0, &( curname1 ), __LINE__);\
!  /* declare $0 cursor for select id , t from t1 */
  #line 59 "cursor.pgc"


--- 156,162 ----

      strcpy(msg, "declare");
      ECPG_informix_set_var( 0, &( curname1 ), __LINE__);\
!  ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for select id , t from t1 */
  #line 59 "cursor.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 292,298 ****
      ECPG_informix_set_var( 3, &( curname2 ), __LINE__);\
   ECPG_informix_set_var( 1, ( t ), __LINE__);\
   ECPG_informix_set_var( 2, &( id ), __LINE__);\
!  /* declare $0 cursor for select id , t from t1 */
  #line 100 "cursor.pgc"


--- 292,298 ----
      ECPG_informix_set_var( 3, &( curname2 ), __LINE__);\
   ECPG_informix_set_var( 1, ( t ), __LINE__);\
   ECPG_informix_set_var( 2, &( id ), __LINE__);\
!  ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for select id , t from t1 */
  #line 100 "cursor.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 441,447 ****


      strcpy(msg, "declare");
!     /* declare $0 cursor for $1 */
  #line 143 "cursor.pgc"


--- 441,447 ----


      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for $1 */
  #line 143 "cursor.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 596,602 ****


      strcpy(msg, "declare");
!     /* declare $0 cursor for $1 */
  #line 193 "cursor.pgc"


--- 596,602 ----


      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare $0 cursor for $1 */
  #line 193 "cursor.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-sqlda.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-sqlda.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-sqlda.c    2009-08-11 14:24:20.000000000 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-sqlda.c    2009-08-13
17:00:25.000000000+0200 
*************** if (sqlca.sqlcode < 0) exit (1);}
*** 291,297 ****


      strcpy(msg, "declare");
!     /* declare mycur1 cursor for $1 */
  #line 100 "sqlda.pgc"


--- 291,297 ----


      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare mycur1 cursor for $1 */
  #line 100 "sqlda.pgc"


*************** if (sqlca.sqlcode < 0) exit (1);}
*** 366,372 ****


      strcpy(msg, "declare");
!     /* declare mycur2 cursor for $1 */
  #line 137 "sqlda.pgc"


--- 366,372 ----


      strcpy(msg, "declare");
!     ECPGreset_sqlca(__LINE__, NULL); /* declare mycur2 cursor for $1 */
  #line 137 "sqlda.pgc"


diff -dcrpN pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-test_informix.c
pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-test_informix.c
*** pgsql.describe/src/interfaces/ecpg/test/expected/compat_informix-test_informix.c    2009-08-09 22:00:45.000000000
+0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/test/expected/compat_informix-test_informix.c    2009-08-13
17:00:26.000000000+0200 
*************** if (sqlca.sqlcode < 0) dosqlprint ( );}
*** 147,153 ****


      ECPG_informix_set_var( 0, &( i ), __LINE__);\
!  /* declare c cursor for select * from test where i <= $1  */
  #line 48 "test_informix.pgc"

      openit();
--- 147,153 ----


      ECPG_informix_set_var( 0, &( i ), __LINE__);\
!  ECPGreset_sqlca(__LINE__, NULL); /* declare c cursor for select * from test where i <= $1  */
  #line 48 "test_informix.pgc"

      openit();

Re: DECLARE doesn't set/reset sqlca after DECLARE cursor

From

Michael Meskes

Date:

14 August 2009, 07:59:18

On Thu, Aug 13, 2009 at 05:55:53PM +0200, Boszormenyi Zoltan wrote:
> Okay, so it's a declarative command. But if we're in a function,
> we should still emit a call to ecpg_init, to be able to follow

No, either it is declarative or it is not, but I don't see a reason for
different behaviour depending on where the command is located.

> the Informix behaviour. We can limit it it compat mode, though.

So let's first figure out exactly how Informix handles things. Zoltan, do you
have access to esql the Informix precompiler? If so, please run two simple
tests, one with just a DECLARE statement and one with a DECLARE statement in a
function and post the results please.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

Re: DECLARE doesn't set/reset sqlca after DECLARE cursor

From

Boszormenyi Zoltan

Date:

14 August 2009, 11:02:24

Michael Meskes írta:
> On Thu, Aug 13, 2009 at 05:55:53PM +0200, Boszormenyi Zoltan wrote:
>
>> Okay, so it's a declarative command. But if we're in a function,
>> we should still emit a call to ecpg_init, to be able to follow
>>
>
> No, either it is declarative or it is not, but I don't see a reason for
> different behaviour depending on where the command is located.
>
>
>> the Informix behaviour. We can limit it it compat mode, though.
>>
>
> So let's first figure out exactly how Informix handles things. Zoltan, do you
> have access to esql the Informix precompiler? If so, please run two simple
> tests, one with just a DECLARE statement and one with a DECLARE statement in a
> function and post the results please.
>

Here are the two test files, with their preprocessed C output.
Indeed, Informix emits a function call for DECLARE CURSOR.
And it seems it's not legal to do this outside of a function.

Best regards,
Zoltán Böszörményi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

#include <sqlhdr.h>
#include <sqliapi.h>
static const char *_Cn1 = "mycur";
#line 1 "test1.ec"
#include <stdio.h>

/*
 * exec sql begin declare section;
 */
#line 3 "test1.ec"
#line 4 "test1.ec"
#line 4 "test1.ec"
int id;
/*
 * exec sql end declare section;
 */
#line 5 "test1.ec"


/*
 * exec sql whenever not found goto close_mycur;
 */
#line 7 "test1.ec"

/*
 * exec sql declare mycur cursor for select id from t1;
 */
#line 9 "test1.ec"
  {
#line 9 "test1.ec"
  static const char *sqlcmdtxt[] =
#line 9 "test1.ec"
    {
#line 9 "test1.ec"
    " select id from t1",
    0
    };
#line 9 "test1.ec"
#line 9 "test1.ec"
sqli_curs_decl_stat(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, (char *) _Cn1, 512), (char *) _Cn1,(char **)
sqlcmdtxt,(ifx_sqlda_t *) 0, (ifx_sqlda_t *) 0, 0, (ifx_literal_t *) 0, (ifx_namelist_t *) 0, 2, 0, 0); 
#line 9 "test1.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 9 "test1.ec"
    goto close_mycur;
#line 9 "test1.ec"
  }

int main(int argc, char **argv)
{
/*
 *     exec sql connect to 'test';
 */
#line 13 "test1.ec"
  {
#line 13 "test1.ec"
  sqli_connect_open(ESQLINTVERSION, 0, "test", (char *) 0, (ifx_conn_t *) 0, 0);
#line 13 "test1.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 13 "test1.ec"
    goto close_mycur;
#line 13 "test1.ec"
  }

/*
 *     exec sql open mycur;
 */
#line 15 "test1.ec"
  {
#line 15 "test1.ec"
#line 15 "test1.ec"
  sqli_curs_open(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, (char *) _Cn1, 768), (ifx_sqlda_t *) 0, (char *) 0,
(structvalue *) 0, 0, 0); 
#line 15 "test1.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 15 "test1.ec"
    goto close_mycur;
#line 15 "test1.ec"
  }
    while (1)
    {
/*
 *         exec sql fetch mycur into :id;
 */
#line 18 "test1.ec"
  {
#line 18 "test1.ec"
  static ifx_sqlvar_t _sqobind[] =
    {
      { 102, sizeof(id), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
#line 18 "test1.ec"
    };
  static ifx_sqlda_t _SD0 = { 1, _sqobind, {0}, 1, 0 };
  static _FetchSpec _FS1 = { 0, 1, 0 };
#line 18 "test1.ec"
  _sqobind[0].sqldata = (char *) &id;
#line 18 "test1.ec"
  sqli_curs_fetch(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, (char *) _Cn1, 768), (ifx_sqlda_t *) 0, &_SD0, (char
*)0, &_FS1); 
#line 18 "test1.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 18 "test1.ec"
    goto close_mycur;
#line 18 "test1.ec"
  }
        printf("id=%d\n");
    }
close_mycur:
/*
 *     exec sql close mycur;
 */
#line 22 "test1.ec"
  {
#line 22 "test1.ec"
#line 22 "test1.ec"
  sqli_curs_close(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, (char *) _Cn1, 768));
#line 22 "test1.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 22 "test1.ec"
    goto close_mycur;
#line 22 "test1.ec"
  }

/*
 *     exec sql disconnect current;
 */
#line 24 "test1.ec"
  {
#line 24 "test1.ec"
  sqli_connect_close(3, (char *) 0, 0, 0);
#line 24 "test1.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 24 "test1.ec"
    goto close_mycur;
#line 24 "test1.ec"
  }
}


#line 26 "test1.ec"
#include <stdio.h>

exec sql begin declare section;
    int    id;
exec sql end declare section;

exec sql whenever not found goto close_mycur;

exec sql declare mycur cursor for select id from t1;

int main(int argc, char **argv)
{
    exec sql connect to 'test';

    exec sql open mycur;
    while (1)
    {
        exec sql fetch mycur into :id;
        printf("id=%d\n");
    }
close_mycur:
    exec sql close mycur;

    exec sql disconnect current;
}

#include <sqlhdr.h>
#include <sqliapi.h>
static const char *_Cn1 = "mycur";
#line 1 "test2.ec"
#include <stdio.h>

/*
 * exec sql begin declare section;
 */
#line 3 "test2.ec"
#line 4 "test2.ec"
#line 4 "test2.ec"
int id;
/*
 * exec sql end declare section;
 */
#line 5 "test2.ec"


/*
 * exec sql whenever not found goto close_mycur;
 */
#line 7 "test2.ec"

int main(int argc, char **argv)
{
/*
 *     exec sql connect to 'test';
 */
#line 11 "test2.ec"
  {
#line 11 "test2.ec"
  sqli_connect_open(ESQLINTVERSION, 0, "test", (char *) 0, (ifx_conn_t *) 0, 0);
#line 11 "test2.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 11 "test2.ec"
    goto close_mycur;
#line 11 "test2.ec"
  }

/*
 *     exec sql declare mycur cursor for select id from t1;
 */
#line 13 "test2.ec"
  {
#line 13 "test2.ec"
  static const char *sqlcmdtxt[] =
#line 13 "test2.ec"
    {
#line 13 "test2.ec"
    " select id from t1",
    0
    };
#line 13 "test2.ec"
#line 13 "test2.ec"
sqli_curs_decl_stat(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, (char *) _Cn1, 512), (char *) _Cn1,(char **)
sqlcmdtxt,(ifx_sqlda_t *) 0, (ifx_sqlda_t *) 0, 0, (ifx_literal_t *) 0, (ifx_namelist_t *) 0, 2, 0, 0); 
#line 13 "test2.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 13 "test2.ec"
    goto close_mycur;
#line 13 "test2.ec"
  }

/*
 *     exec sql open mycur;
 */
#line 15 "test2.ec"
  {
#line 15 "test2.ec"
#line 15 "test2.ec"
  sqli_curs_open(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, (char *) _Cn1, 768), (ifx_sqlda_t *) 0, (char *) 0,
(structvalue *) 0, 0, 0); 
#line 15 "test2.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 15 "test2.ec"
    goto close_mycur;
#line 15 "test2.ec"
  }
    while (1)
    {
/*
 *         exec sql fetch mycur into :id;
 */
#line 18 "test2.ec"
  {
#line 18 "test2.ec"
  static ifx_sqlvar_t _sqobind[] =
    {
      { 102, sizeof(id), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 },
#line 18 "test2.ec"
    };
  static ifx_sqlda_t _SD0 = { 1, _sqobind, {0}, 1, 0 };
  static _FetchSpec _FS1 = { 0, 1, 0 };
#line 18 "test2.ec"
  _sqobind[0].sqldata = (char *) &id;
#line 18 "test2.ec"
  sqli_curs_fetch(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, (char *) _Cn1, 768), (ifx_sqlda_t *) 0, &_SD0, (char
*)0, &_FS1); 
#line 18 "test2.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 18 "test2.ec"
    goto close_mycur;
#line 18 "test2.ec"
  }
        printf("id=%d\n");
    }
close_mycur:
/*
 *     exec sql close mycur;
 */
#line 22 "test2.ec"
  {
#line 22 "test2.ec"
#line 22 "test2.ec"
  sqli_curs_close(ESQLINTVERSION, sqli_curs_locate(ESQLINTVERSION, (char *) _Cn1, 768));
#line 22 "test2.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 22 "test2.ec"
    goto close_mycur;
#line 22 "test2.ec"
  }

/*
 *     exec sql disconnect current;
 */
#line 24 "test2.ec"
  {
#line 24 "test2.ec"
  sqli_connect_close(3, (char *) 0, 0, 0);
#line 24 "test2.ec"
  if (SQLCODE == SQLNOTFOUND)
#line 24 "test2.ec"
    goto close_mycur;
#line 24 "test2.ec"
  }
}


#line 26 "test2.ec"
#include <stdio.h>

exec sql begin declare section;
    int    id;
exec sql end declare section;

exec sql whenever not found goto close_mycur;

int main(int argc, char **argv)
{
    exec sql connect to 'test';

    exec sql declare mycur cursor for select id from t1;

    exec sql open mycur;
    while (1)
    {
        exec sql fetch mycur into :id;
        printf("id=%d\n");
    }
close_mycur:
    exec sql close mycur;

    exec sql disconnect current;
}

Re: DECLARE doesn't set/reset sqlca after DECLARE cursor

From

Michael Meskes

Date:

14 August 2009, 13:34:54

On Fri, Aug 14, 2009 at 01:02:07PM +0200, Boszormenyi Zoltan wrote:
> Here are the two test files, with their preprocessed C output.
> Indeed, Informix emits a function call for DECLARE CURSOR.
> And it seems it's not legal to do this outside of a function.

Okay, thanks. I changed several things in your patch mainly due to chosing a
slightly different way to reset the sqlca array and committed that change.
Please test.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

Re: DECLARE doesn't set/reset sqlca after DECLARE cursor

From

Boszormenyi Zoltan

Date:

14 August 2009, 19:09:37

Michael Meskes írta:
> On Fri, Aug 14, 2009 at 01:02:07PM +0200, Boszormenyi Zoltan wrote:
>   
>> Here are the two test files, with their preprocessed C output.
>> Indeed, Informix emits a function call for DECLARE CURSOR.
>> And it seems it's not legal to do this outside of a function.
>>     
>
> Okay, thanks. I changed several things in your patch mainly due to chosing a
> slightly different way to reset the sqlca array and committed that change.
> Please test.
>   

I looked at the committed change, and it works
the same as my version, only the emitted call
is put into the compat library. According to the
regression tests, it works. Thanks. :-)

Best regards,
Zoltán Böszörményi

-- 
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/