sql4002n : precompiler strange error

[Yan302]

Hi everyone,

I'm working with DB2 and I got a strange precomplier error ; hope someone will have any idea how to resolve this because I'm completely disappointed about that problem

I'll try to explain my problem clearer as possible but it's not trivial.

First of all, I’ve got two similar functions declared like this:

CREATE FUNCTION "DB2ADMIN"."FUNC_BK_BIG_2_624001995" (
    "COMPOUND_2_1"	CHARACTER(8),
    "COMPOUND_2_2"	CHARACTER(8) )
  RETURNS VARCHAR(16)
  SPECIFIC "SQL090520165705700"
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  CONTAINS SQL
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
RETURN VARCHAR(COMPOUND_2_1, 8) || VARCHAR(COMPOUND_2_2, 8);

CREATE FUNCTION "DB2ADMIN"."FUNC_BK_BIG_3_79140483" (
    "COMPOUND_4_1"	CHARACTER(4),
    "COMPOUND_4_2"	CHARACTER(4),
    "COMPOUND_4_3"	CHARACTER(4),
    "COMPOUND_4_4"	CHARACTER(4) )
  RETURNS VARCHAR(16)
  SPECIFIC "SQL090520165706200"
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  CONTAINS SQL
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
RETURN VARCHAR(COMPOUND_4_1, 4) || VARCHAR(COMPOUND_4_2, 4) || VARCHAR(COMPOUND_4_3, 4) || VARCHAR(COMPOUND_4_4, 4);

When I try to precompile this C code:

EXEC SQL BEGIN DECLARE SECTION;

static struct _index_buff_102_OT {
    struct {short len; char buff[8];} COMPOUND_2_1;
    struct {short len; char buff[8];} COMPOUND_2_2;
} index_buff_A_102;

  EXEC SQL DECLARE SEQ_ON_INDEX_102 CURSOR FOR
    SELECT 
        RID,
        VERSION,
        "COL1",
        "COL2",
        "COL3",
        "COL4",
        "COL5",
        "COL6",
        "COL7",
        "COL8",
        "COMPOUND",
        "COMPOUND_2_1",
        "COMPOUND_2_2",
        "COMPOUND_4_1",
        "COMPOUND_4_2",
        "COMPOUND_4_3",
        "COMPOUND_4_4",
        ("AMOUNT" * 100)
      FROM
        BIG
      WHERE
        BK_BIG_2_624001995 >= func_BK_BIG_2_624001995(:index_buff_A_102.COMPOUND_2_1.buff, :index_buff_A_102.COMPOUND_2_2.buff)
      ORDER BY
        "BK_BIG_2_624001995";

EXEC SQL END DECLARE SECTION;

everything goes well:

        SQL0060W  The "C" precompiler is in progress.
        SQL0091W  Precompilation or binding was ended with "0"
                  errors and "0" warnings.

but if I try to precompile :

EXEC SQL BEGIN DECLARE SECTION;

static struct _index_buff_103_OT {
    struct {short len; char buff[4];} COMPOUND_4_1;
    struct {short len; char buff[4];} COMPOUND_4_2;
    struct {short len; char buff[4];} COMPOUND_4_3;
    struct {short len; char buff[4];} COMPOUND_4_4;
} index_buff_A_103;

  EXEC SQL DECLARE SEQ_ON_INDEX_103 CURSOR FOR
    SELECT 
        RID,
        VERSION,
        "COL1",
        "COL2",
        "COL3",
        "COL4",
        "COL5",
        "COL6",
        "COL7",
        "COL8",
        "COMPOUND",
        "COMPOUND_2_1",
        "COMPOUND_2_2",
        "COMPOUND_4_1",
        "COMPOUND_4_2",
        "COMPOUND_4_3",
        "COMPOUND_4_4",
        ("AMOUNT" * 100)
      FROM
        BIG
      WHERE
        BK_BIG_3_79140483 >= func_BK_BIG_3_79140483(:index_buff_A_103.COMPOUND_4_1.buff, :index_buff_A_103.COMPOUND_4_2.buff, :index_buff_A_103.COMPOUND_4_3.buff, :index_buff_A_103.COMPOUND_4_4.buff)
      ORDER BY
        "BK_BIG_3_79140483";

EXEC SQL END DECLARE SECTION;

I got this error :

        SQL0060W  The "C" precompiler is in progress.
   10   SQL4002N  "index_buff_A_103.COMPOUND_4_3.buff" and
                  "index_buff_A_103.COMPOUND_4_2.buff" are undeclared host
                  variables that cannot both be used as descriptor names in a
                  single SQL statement.
   10   SQL4002N  "EXEC" and "" are undeclared host variables that
                  cannot both be used as descriptor names in a single SQL
                  statement.
        SQL0095N  No bind file was created because of previous
                  errors.
        SQL0091W  Precompilation or binding was ended with "3"
                  errors and "0" warnings.

Someone got any ideas what the problem is and how to resolved it ?

Any help would really be appreciated.

Thanks in advance,

Yan302

[Yan302]

Hi all,

I've found a workaround. I use VARCHAR instead of CHAR.
No more problem.