Highlighted
Honored Contributor.
Honored Contributor.
410 views

How to create field in dbdict with SQL Type= IMAGE like svc.options fields

Jump to solution

Hi Expert,

Good day! Our Incidents(probsummary table) don't have svc.options field unlike other tables. We may need to add this field so that we can copy the value from Interaction(svc.options) to related Incident(svc.options).

Creating new field from dbdict doesn't option to select SQL type=IMAGE like the one existing in Interaction table. Please help what is the best way to add this new field with SQL type=IMAGE. Thank you

0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

Well, this is actually the situation, when the new field is already mapped to the database. Them a conversion from old to now column type on the database must exist (and SM RTE must be prepared to use it).

If you want to set a column type before the column is created on SQL, you need to do it directly when creating the field, and before clicking OK in the dbdict utility - which means to SM apply all changes to database.

I think svc.options is an OOB field - and therefore mapped already.

 

Changing the mapping for existing columns, when the conversion does not exist, is a painful process:

Actually you will need to map the field to null table, and then you can change is as you like. But nulltable mapping means all data in the column gets lost - so you need to think about backing it up beforehand and restore it afterwards..

As you change the column type the database may not be able to do this - so you need SM RTE.

The rough procedure therefore is:

1. Use dbdict utility to copy the dbdict with data

2. Change in original dbdict the field mapping to null table: See Remove a field from an existing database dictionary record 

3. Change in orginal dbdict the field mapping from null table to the desired column type in the desired table

4. Use mass update to copy the data from backup dbdict to orginal dbdict

5. Verify data in original dbdict

6. Delete backup dbdict

 

I recommend strongly to do this in test system first to understand it in all details.

 

Best regards,

 

Armin

View solution in original post

4 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Hello,

When you create a new field, typically you only tell SM the field name and the SM data type. SM then will use the default mapping as defined in dbdict sqldbinfo/sqloptions (for OOB fields there are special default mappings in dbdict sqlhints, so when you install SM and load the OOB data initially, some fields may vary from default mapping).

Now, back to creating a new field in dbdict utility: When you put the cursor on your newly added field and click the "Edit field/key" button, you'll get to a popup window allowing you to change/set the SQL field name, SQL column type, etc.  So if you set the SQL values here, SM will not use the defaults.

Regarding modifying SQL column types: This is only possible, if a translation of the types on the database exists. Otherwise SM will show an error message that this is not possible.

Best regards,

Armin Franke

Highlighted
Honored Contributor.
Honored Contributor.

Hi Armin,

Thank you for the response. Exactly I have error after trying to modify the newly created field using dbdict utility.  Is it best to have it modify from sql db directly?

Its prompting: Changing SQL data type from 'VARCHAR(60)' to 'IMAGE' for column 'SVC.OPTIONS' in table 'PROBSUMMARYM2' is not supported. (record.update,start)

 

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Well, this is actually the situation, when the new field is already mapped to the database. Them a conversion from old to now column type on the database must exist (and SM RTE must be prepared to use it).

If you want to set a column type before the column is created on SQL, you need to do it directly when creating the field, and before clicking OK in the dbdict utility - which means to SM apply all changes to database.

I think svc.options is an OOB field - and therefore mapped already.

 

Changing the mapping for existing columns, when the conversion does not exist, is a painful process:

Actually you will need to map the field to null table, and then you can change is as you like. But nulltable mapping means all data in the column gets lost - so you need to think about backing it up beforehand and restore it afterwards..

As you change the column type the database may not be able to do this - so you need SM RTE.

The rough procedure therefore is:

1. Use dbdict utility to copy the dbdict with data

2. Change in original dbdict the field mapping to null table: See Remove a field from an existing database dictionary record 

3. Change in orginal dbdict the field mapping from null table to the desired column type in the desired table

4. Use mass update to copy the data from backup dbdict to orginal dbdict

5. Verify data in original dbdict

6. Delete backup dbdict

 

I recommend strongly to do this in test system first to understand it in all details.

 

Best regards,

 

Armin

View solution in original post

Highlighted
Honored Contributor.
Honored Contributor.

Greate inputs Armin.

after defining the new field in dbdict utility, I have make sure to edit the the sql type = IMAGE before clicking OK and the SM Alters. This saves the new field in SQL exactly what I need and the same as svc.options from other modules.

Was just surprise to see that svc.options field is not present in our probsummary table.

Thank you again.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.