IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • 4 replies
  • Latest Post - ‏2019-02-27T21:11:29Z by dlstrawn
dlstrawn
dlstrawn
6 Posts

Pinned topic Blank column name in GET DIAGNOSTICS in db2 for i

‏2019-02-26T20:18:43Z |

Here is my sql statement:

exec sql get diagnostics condition 1

   :#COLUMN_NAME = COLUMN_NAME;  (I actually have other variables like table_name and they show the proper value - the name of the table

 

Here is my newly queried table:

create table pubwrksf.Action (
    ActnCode char(2) not null, 
    ActnDesc char(40) not null,
    StatusCd char(10) not null implicitly hidden default 'Active', 
    CreateBy char(64) not null implicitly hidden, 
    CreateOn timestamp not null implicitly hidden default current timestamp, 
    LstChgBy char(64) not null implicitly hidden, 
    LstChgOn for each row on update as row change timestamp 
        not null implicitly hidden,
    constraint pubwrksf.Action_ActnCode_pk 
        primary key (ActnCode),
    constraint pubwrksf.Action_StatusCd_ck
        check (StatusCd in ('Inactive', 'Closed', 'Deleted', 'Active'))
);

/* set label for table */
label on table pubwrksf.Action is 'Actions';

/* set labels for columns */
label on column pubwrksf.Action ( 
    ActnCode text is 'Action Code', 
    ActnDesc text is 'Action Description', 
    StatusCd text is 'Status Code', 
    CreateBy text is 'Created By', 
    CreateOn text is 'Created On', 
    LstChgBy text is 'Last Changed By', 
    LstChgOn text is 'Last Changed On' 
);

 

When I look at #COLUMN_NAME in debug it is blank, any ideas why?

 

Thanks!

 

Updated on 2019-02-26T20:20:46Z at 2019-02-26T20:20:46Z by dlstrawn
  • B.Hauser
    B.Hauser
    320 Posts

    Re: Blank column name in GET DIAGNOSTICS in db2 for i

    ‏2019-02-27T06:04:01Z  

    Not sure what you expect to get returned and where you execute the GET DIAGNOSTIC statement, but in either way here is the description of the COLUMN_NAME information from the Db2 SQL Reference:

    COLUMN_NAME
    If the returned SQLSTATE is class 42 (Syntax Error or Access Rule Violation) and the error was caused by an inaccessible column, the name of the column that caused the error is returned. Otherwise, the empty string is returned.

  • dlstrawn
    dlstrawn
    6 Posts

    Re: Blank column name in GET DIAGNOSTICS in db2 for i

    ‏2019-02-27T15:17:44Z  
    • B.Hauser
    • ‏2019-02-27T06:04:01Z

    Not sure what you expect to get returned and where you execute the GET DIAGNOSTIC statement, but in either way here is the description of the COLUMN_NAME information from the Db2 SQL Reference:

    COLUMN_NAME
    If the returned SQLSTATE is class 42 (Syntax Error or Access Rule Violation) and the error was caused by an inaccessible column, the name of the column that caused the error is returned. Otherwise, the empty string is returned.

    gotcha, so let me give more detail...

    Here is my - embedded into RPG - query:

    exec sql
       insert into Action(
          ActnCode,
          ActnDesc,
          CreateBy,
          LstChgBy
       )
       values(
          :#actions.ActnCode,
          :#actions.ActnDesc,
          :UserID,
          :UserID
       );

    Here is the Index:

    create unique index pubwrksf.Action_Description_ui on pubwrksf.Action(ActnDesc);

    Here is the Create Table:

    create table pubwrksf.Action (
        ActnCode char(2) not null, 
        ActnDesc char(40) not null,
        StatusCd char(10) not null implicitly hidden default 'Active', 
        CreateBy char(64) not null implicitly hidden, 
        CreateOn timestamp not null implicitly hidden default current timestamp, 
        LstChgBy char(64) not null implicitly hidden, 
        LstChgOn for each row on update as row change timestamp 
            not null implicitly hidden,
        constraint pubwrksf.Action_ActnCode_pk 
            primary key (ActnCode),
        constraint pubwrksf.Action_StatusCd_ck
            check (StatusCd in ('Inactive', 'Closed', 'Deleted', 'Active'))
    );

    /* set label for table */
    label on table pubwrksf.Action is 'Actions';

    /* set labels for columns */
    label on column pubwrksf.Action ( 
        ActnCode text is 'Action Code', 
        ActnDesc text is 'Action Description', 
        StatusCd text is 'Status Code', 
        CreateBy text is 'Created By', 
        CreateOn text is 'Created On', 
        LstChgBy text is 'Last Changed By', 
        LstChgOn text is 'Last Changed On' 
    );

    /* set labels for constraints */
    label on constraint pubwrksf.Action_ActnCode_pk is 'Action Code PK';
    label on constraint pubwrksf.Action_StatusCd_ck is 'Status Code Ck';

    Here is the problem:

    If you enter a duplicate Action Code, or duplicate Action Description, sql throws an error.  In order to determine which column is in error (ActnCode or ActnDesc), I am using GET DIAGNOSTICS variables to compose my error message that I then send to the user in the browser.  I can get the error description and table name from the variables I am using above, but can't figure out how to get the column name.  And the SQL Reference is difficult for me (a db2 for i newbie) to make sense of...  How can I get the column name of the errant column (in this case Action Description)?

    Updated on 2019-02-27T15:26:23Z at 2019-02-27T15:26:23Z by dlstrawn
  • ScottMildenberger
    ScottMildenberger
    1 Post

    Re: Blank column name in GET DIAGNOSTICS in db2 for i

    ‏2019-02-27T20:32:01Z  
    • dlstrawn
    • ‏2019-02-27T15:17:44Z

    gotcha, so let me give more detail...

    Here is my - embedded into RPG - query:

    exec sql
       insert into Action(
          ActnCode,
          ActnDesc,
          CreateBy,
          LstChgBy
       )
       values(
          :#actions.ActnCode,
          :#actions.ActnDesc,
          :UserID,
          :UserID
       );

    Here is the Index:

    create unique index pubwrksf.Action_Description_ui on pubwrksf.Action(ActnDesc);

    Here is the Create Table:

    create table pubwrksf.Action (
        ActnCode char(2) not null, 
        ActnDesc char(40) not null,
        StatusCd char(10) not null implicitly hidden default 'Active', 
        CreateBy char(64) not null implicitly hidden, 
        CreateOn timestamp not null implicitly hidden default current timestamp, 
        LstChgBy char(64) not null implicitly hidden, 
        LstChgOn for each row on update as row change timestamp 
            not null implicitly hidden,
        constraint pubwrksf.Action_ActnCode_pk 
            primary key (ActnCode),
        constraint pubwrksf.Action_StatusCd_ck
            check (StatusCd in ('Inactive', 'Closed', 'Deleted', 'Active'))
    );

    /* set label for table */
    label on table pubwrksf.Action is 'Actions';

    /* set labels for columns */
    label on column pubwrksf.Action ( 
        ActnCode text is 'Action Code', 
        ActnDesc text is 'Action Description', 
        StatusCd text is 'Status Code', 
        CreateBy text is 'Created By', 
        CreateOn text is 'Created On', 
        LstChgBy text is 'Last Changed By', 
        LstChgOn text is 'Last Changed On' 
    );

    /* set labels for constraints */
    label on constraint pubwrksf.Action_ActnCode_pk is 'Action Code PK';
    label on constraint pubwrksf.Action_StatusCd_ck is 'Status Code Ck';

    Here is the problem:

    If you enter a duplicate Action Code, or duplicate Action Description, sql throws an error.  In order to determine which column is in error (ActnCode or ActnDesc), I am using GET DIAGNOSTICS variables to compose my error message that I then send to the user in the browser.  I can get the error description and table name from the variables I am using above, but can't figure out how to get the column name.  And the SQL Reference is difficult for me (a db2 for i newbie) to make sense of...  How can I get the column name of the errant column (in this case Action Description)?

    I am guessing you can't get the column name because the unique key constraint could have any number of columns so that type of error is not tied to any particular column.  If the constraint has 3 columns for example, you can't tell the user which one needs changed.  The best you might be able to do is use the Constraint name - you could put the columns in the constraint name to make it more meaningful.

  • dlstrawn
    dlstrawn
    6 Posts

    Re: Blank column name in GET DIAGNOSTICS in db2 for i

    ‏2019-02-27T21:11:29Z  

    I am guessing you can't get the column name because the unique key constraint could have any number of columns so that type of error is not tied to any particular column.  If the constraint has 3 columns for example, you can't tell the user which one needs changed.  The best you might be able to do is use the Constraint name - you could put the columns in the constraint name to make it more meaningful.

    I can get the constraint name, but the problem is that the ActnDesc column is not a constraint technically, its a unique index...  SQL obviously knows which column is in error - certainly there is a way to get the column name back to an RPG program...  Any more ideas?

    Updated on 2019-03-05T16:47:08Z at 2019-03-05T16:47:08Z by dlstrawn