Skip to Content
0

Problem with single space default value and altering the table

Oct 24, 2017 at 10:27 AM

18

avatar image
Former Member

I'm using PD16.6.2.0

I’m creating the table in the database with below statement

create table TABLE_TEST

(

DESCRIPTION VARCHAR2(255) default ' ',

NAME VARCHAR2(50) default ' ',

ID INTEGER not null,

constraint PK_TABLE_TEST primary key (ID)

);

and reverse engineering it into physical model. The default value single space character is recognized properly. In the table generated in PD in properties of the table in the preview tab, i see:

create table TABLE_TEST

(

DESCRIPTION VARCHAR2(255) default ' ',

NAME VARCHAR2(50) default ' ',

ID NUMBER not null,

constraint PK_TABLE_TEST primary key (ID)

)

Though in the properties of the column in the Standard Checks tab the single space character seems not visible in the Default control, but if clicked on the edit box i see that the space character is there.

When setting the default value in the database for the column that is already existing in both database and the model using:

alter table TABLE_TEST

modify DESCRIPTION default ' '

…and then using “Update model from the database”, PowerDesigner does not recognize changes and does not update the column in the model with the default value.

The reverse situation when editing the model by setting “ “ single space character as default value in the column that exists in both model and the database, and then “Apply model changes to database” causes to generate empty script.

Though when in the model “’ ‘” value is set as default, “Apply model changes to database” generates proper statement:

alter table TABLE_TEST

modify DESCRIPTION default ' '

If the entire table is created in the model (nevertheless if the “ “ or “’ ‘” format is used), the create statement that will be generated by the “Apply model changes to database” will be ok:

create table TABLE_TEST

(

DESCRIPTION VARCHAR2(255) default ' ',

NAME VARCHAR2(50) default ' ',

ID NUMBER not null,

constraint PK_TABLE_TEST primary key (ID)

)

So it seems that:

  • single space default value works ok when creating the entire table
  • if table is altered in the database to have single space default value, the change will not be recognized and transferred to the model when using “Update model from the database” functionality.
  • if table is altered in the model to have single space default value in a “ “ format, the change will not be recognized and transferred to the delta script when using “Apply model changes to database” functionality, nevertheless if I compare with the database or previous version of the model from the repository.
  • if table is altered in the model to have single space default value in a “’ ‘“ format, the change will be recognized and generated to the delta script when using “Apply model changes to database” functionality, nevertheless if I compare with the database or previous version of the model from the repository.

Is it the expected behavior of the PowerDesigner? How to resolve the problem of not recognizing the changes made to the column default value when updating the model from the database?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

avatar image
Former Member Oct 24, 2017 at 11:26 AM
0

It's a bug, as the SQL retrieving the ALL_TAB_COLS.DATA_DEFAULT works correct. Changing the default from 'xxx' to ' ' will be recognized, but not NULL -> ' '... so... bug...

The select used for that is SqlListQuery in the Column Metaclass...

Share
10 |10000 characters needed characters left characters exceeded