Home » DbVisualizer Forums » DbVisualizer » DbVisualizer - Support

Thread: Exporting table containing GENERATED ALWAYS columns does not work correctly


Permlink Replies: 3 - Pages: 1 - Last Post: Sep 1, 2011 9:28 PM Last Post By: Hans Bergsten
Chris

Posts: 8
Registered: 07/28/11
Exporting table containing GENERATED ALWAYS columns does not work correctly
Posted: Jul 28, 2011 10:43 AM
   thread.click_reply Reply

Hi guys,

Here is the bug description I found using DBVis 8.0.1 and connected to DB2 schema:

1. Execute following statements in DB2 (create table and insert one new row):

CREATE TABLE TEST_TABLE
(
CUSTOMER_NAME VARCHAR(1000),
UP_CUSTOMER_NAME GENERATED ALWAYS AS (UPPER(CUSTOMER_NAME))
)
;

INSERT INTO TEST_TABLE( CUSTOMER_NAME ) VALUES ( 'Sample_Customer' ) ;

2. Now export the table to the SQL file, please select the options:

  • Generate CREATE statements
  • Generate DROP statements
  • Include table data

3. Open the exported SQL file, it will contain:

DROP TABLE TEST_TABLE;
CREATE TABLE TEST_TABLE ( CUSTOMER_NAME VARCHAR(1000), UP_CUSTOMER_NAME VARCHAR(1000) GENERATED ALWAYS AS (UPPER(CUSTOMER_NAME)) );
insert into TEST_TABLE (CUSTOMER_NAME, UP_CUSTOMER_NAME) values ('Sample_Customer', 'SAMPLE_CUSTOMER');

4. Execute above statements, in the log you will get an ERROR:

WARNING: 10:42:29 [DROP - 0 row(s), 0.203 secs] Command processed. No rows were affected
WARNING: 10:42:30 [CREATE - 0 row(s), 0.500 secs] Command processed. No rows were affected
ERROR: 10:42:30 [INSERT - 0 row(s), 0.000 secs] [Error Code: -798, SQL State: 428C9] A value cannot be specified for column "UP_CUSTOMER_NAME" which is defined as GENERATED ALWAYS.. SQLCODE=-798, SQLSTATE=428C9, DRIVER=3.57.82
SUMMARY: ... 3 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.703/0.000 sec
[0 successful, 2 warnings, 1 errors]

The problem is that when the column is defined as GENERATED ALWAYS we cant insert values to that column, it is automatically set by GENERATED ALWAYS clause.

The insert statement should look like this:
insert into TEST_TABLE (CUSTOMER_NAME) values ('Sample_Customer');

Regards,
Chris
Hans Bergsten


Posts: 1,539
Registered: 10/11/06
Re: Exporting table containing GENERATED ALWAYS columns does not work correctly
Posted: Jul 28, 2011 6:01 PM    global.in_response_to.tooltip in response to: Chris
   thread.click_reply Reply
Hi Chris,

Thanks for reporting this. The INSERT statement generator does not currently know about the DDL details for the table, it just knows about column names and values, which is why it generates the statement like this. I have opened a ticket for looking into a solution to this problem.

Best Regards,
Hans
Chris

Posts: 8
Registered: 07/28/11
Re: Exporting table containing GENERATED ALWAYS columns does not work correctly
Posted: Sep 1, 2011 12:33 PM    global.in_response_to.tooltip in response to: Chris
   thread.click_reply Reply
Hi guys,

Is there any update on this ticket ?

Regards,
Chris
Hans Bergsten


Posts: 1,539
Registered: 10/11/06
Re: Exporting table containing GENERATED ALWAYS columns does not work correctly
Posted: Sep 1, 2011 9:28 PM    global.in_response_to.tooltip in response to: Chris
   thread.click_reply Reply
Hi Chris,

I have this on my "short list" of issues to look at, but I can't give you a date for when it will be fixed.

Best Regards,
Hans

Point your RSS reader here for a feed of the latest messages in all forums