Idea Details

Allow 'uncompleted' INSERT statements

Last activity 12-27-2018 03:48 AM
julio.ezequiel.decastro's profile image
11-28-2018 04:26 AM

On GEN, when creating a "CREATE" statement, all columns present on the Table's "Data Structure List" will be used for the resulting INSERT SQL. All columns are used even if their corresponding attributes are not used on the CREATE's SET statements.

 

We would like some option to allow an 'uncompleted' CREATE/INSERT: Where only the columns used on CREATE/SET statements will be referenced on the INSERT SQL.

 

This would be useful for avoiding GEN's current timestamp (which is limited to milliseconds precission) and instead using DBMS current_timestamp, which supports microseconds.

 

Also, with this, some other complex functions / special values could be used for the default.

 

Another reason for this request is avoiding the following problem:

 

Consider we want to drop some column on a table. For that reason, we first would remove all references to that column/atribute on the GEN code, and then we would remove the attribute and physical column.

 

Consider an action block that makes a CREATE. It will be modified to remove the SET statement of the column we want to remove. Now, we would regen the AB code and we would think this AB is 'safe'. As it has no more references to the attribute we want to remove.

 

Then we remove the attribute and the physical column. But the previous action block will start to fail as the code generator still references all columns of the table, so the old column is referenced. A new code regen will be necessary.

 

Now, If we wanted to know what packages to regen, we could not know, since all references to the attribute are removed.


Comments

12-27-2018 03:45 AM

Sorry geoff, the specific SQL is important. I suggest you to review the following example. Tested with Oracle, but AFAIK other DBMs should work the same...

 

CREATE TABLE mytable (
  somepk integer not null primary key,
  somecolumn integer default 999
  );  
COMMIT;

 

INSERT INTO mytable (somepk, somecolumn) VALUES (1, null);
COMMIT;

 

INSERT INTO mytable (somepk, somecolumn) VALUES (2, null);
COMMIT;

 

INSERT INTO mytable (somepk, somecolumn) VALUES (3, null);
COMMIT;

 

INSERT INTO mytable (somepk) VALUES (4);
COMMIT;

 

select * from mytable;

 

Results:

 

SOMEPKSOMECOLUMN
2-
3-
1-
4999

 

 

As you can see, only the insert number 4 gets the 999, since the column was not specified during the insert statement.

12-07-2018 01:17 AM

The specific SQL is not important.  The data in the table is.

Try coding and executing your example, by hand not using CA Gen, and look at the results before you say it does not do what you want.  

12-04-2018 03:09 AM

Not the same feature, I believe.

 

At any case, all columns will be referenced on the INSERT statement. The difference is that with "Init unspecified optional fields to NULL selected", all optional non-set columns will be initialized to null.

 

Consider a table like this:

 

CREATE TABLE mytable ( somepk integer not null primary key, somecolumn integer default 999 );

 

With "Init unspecified optional fields to NULL selected", and by no referencing "somecolumn" we would get:

 

INSERT INTO mytable (somepk, somecolum) VALUES ( ? , null );  // still referenced

 

What we would want is:

INSERT INTO mytable (somepk) VALUES ( ? );

 

Also, we would like that wether the column is optional or not.

12-04-2018 02:12 AM

I believe the feature you are after already exists.
01 Open the SAMPLE model
02 Open Technical Defaults
03 Open DB2 z/OS technical design
04 Open Generation Defaults -> Select MVS
05 Open ADD_EMPLOYEE action block
06 Right click the body -> Code
07 Rename ADDEMP.sqb to ADDEMP.CA.sqb
08 Double click Technical Design DB2 z/OS to open Technical Design Properties
09 Select Initialize Unspecified Optional Fields to Null
10 In ADD_EMPLOYEE disable this statement
    22*   !  !  SET service_date TO import employee service_date
11 Right click the body -> Code
12 Rename ADDEMP.sqb to ADDEMP.NULL.sqb
13 Compare the two ADDEMP.*.sqb files to see
Options
      *    Init unspecified optional fields to NULL selected
INSERT
                   :MAIL-STOP-026EN,
                   NULL,
                   :FULL-TIME-ST-030EN,
If the column is implemented as NOT NULL WITH DEFAULT the DBMS allocates the value.