Re: DECLARE doesn't set/reset sqlca after DECLARE cursor - Mailing list pgsql-hackers

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;
}