Some months ago, I posted an article about SQLDictionary ID conflicts. It was a x++ job that scans and solves at once. Last month I have to do this in a use live environment. The customer preferred detailed information about the ID conflicts and solving it was the next step. The keyword in this process is: Don’t lose customer data on the Database!
Let’s look at next example
Tablename | AOT ID | SQL ID |
A | 50003 | 40001 |
B | 40025 | 40013 |
C | 40026 | 40025 |
So I created a script that checks:
- Difference in Table ID
- Difference in Field ID
- Difference in String size
- Changed field properties
Check if documents in document management are attached to tables with an ID conflict. (or amy other table).
From the results I created small jobs that solved the ID conflicts on a structural way.
- Solve any difference in the field Properties In your AOT (string size, Created date, etc)
- Solve the Table ID conflicts Process the tables in an order that the change to a non existing ID in the sqldicttable. So we have to solve first table C next table B. We will not fix the Field ID issues yet.
static void repairSQLDictTableConflicts(Args _args) { SQLDIctionary dictionary; void doTable(int aotTable, int sqlTable) { ttsbegin; while select forupdate dictionary where dictionary.tabId == sqlTable { dictionary.tabId = aotTable; dictionary.update(); } ttscommit; } ; ttsbegin; doTable( tablenum(a), 40001 ) ; doTable( tablenum(c), 40031 ) ; doTable( tablenum(b), 40025 ) ; ttscommit; } |
- Solve the Field ID conflicts. I used the next job for solving the Field ID conflicts
static void repairSQLDictFieldConflicts(Args _args) { SQLDIctionary dictionary; void doField(int aotTtable, int aotField, int sqlField) { ttsbegin; while select forupdate dictionary where dictionary.tabId == aotTtable && dictionary.fieldId == sqlField { dictionary.fieldId = aotField; dictionary.update(); } ttscommit; } ; ttsbegin; dofield(tablenum(A),fieldnum( A,field),4001); dofield(tablenum(B),fieldnum( B,otherfield),4002); ttscommit; } |
For analyzing the ID conflicts I used this script, It can also be used for checking if the ID conflicts are solved.
static void checkSQLDictionary(Args _args) { SQLDictionary dictionary,dictionaryMissing; str tableName; int AOTTableID, AOTFieldID; TreeNode treeNode; boolean doTable; boolean skip; sysdictField sdf; SysDictTable sdt; str propertyName; int sdtFields,sdtFieldsTotal; ; while select dictionary order by tabid,fieldId where dictionary.fieldId <= 61450&& dictionary.tabId < 65505 { if(dictionary.fieldId == 0) { print dictionary.name; skip =dictionary.flags; if (skip) continue; treeNode = TreeNode::findNode('\\data dictionary\\tables\\'+dictionary.name); if (treeNode) { tableName = dictionary.name; AOTTableID = treeNode.applObjectId(); if(AOTTableID != dictionary.tabId) { skip = true; error(strfmt('conflict at Table %1 AOT ID %2 SQL ID %3', dictionary.name,AOTTableID,dictionary.tabId)); } } else { skip = true; sdt = new sysDictTable(dictionary.TabId); if(sdt && sdt.name() != dictionary.name) { error(strfmt('conflict at AOT Table %1ID %2 name SQL Name %3 ID %4', sdt.name(),dictionary.tabId,dictionary.name,dictionary.tabId)); } else { error(strfmt('Table does not exist in AOT %1', dictionary.name)); } } } else { if (skip) continue; if( dictionary.fieldId >= 61440) { treeNode = TreeNode::findNode('\\data dictionary\\tables\\' + tableName); switch (dictionary.fieldId) { case 61440: propertyName = "ModifiedDate"; break; case 61441: propertyName = "ModifiedTime"; break; case 61442: propertyName = "ModifiedBy"; break; case 61443: propertyName = "ModifiedTransactionId"; break; case 61444: propertyName = "CreatedDate"; break; case 61445: propertyName = "CreatedTime"; break; case 61446: propertyName = "CreatedBy"; break; case 61447: propertyName = "CreatedTransactionId"; break; case 61448: propertyName = "SaveDatePerCompany"; break; case 61450: propertyName = "OCC"; break; default: error(strfmt('UnknownField Table %1 Field %2',tableName, dictionary.name)); break; } if(findProperty(treeNode.AOTgetProperties(), propertyName) == 'No') { error(strfmt('conflict Table %1 Propery %2 is turned of',tableName, propertyName)); } } else { treeNode = TreeNode::findNode('\\data dictionary\\tables\\' + tableName + '\\fields\\'+dictionary.name); if (treeNode) { AOTFieldID = treeNode.applObjectId(); if((AOTTableID != dictionary.tabId) || (AOTFieldID != dictionary.fieldId )) { error(strfmt('conflict at Table %1 Field %2 AOT %3, SQL %4',tableName, dictionary.name,AOTFieldID,dictionary.fieldId)); } else { if(dictionary.fieldType == types::String) { sdf = new SysDictField(AOTTableID,AOTFieldID); if(sdf && sdf.stringLen() != dictionary.strSize) { error(strfmt('conflict Fieldsize at Table %1 Field %2',tableName, dictionary.name)); } } } } else { error(strfmt('CRITICAT Table %1 Field %2 does not exist in AOT',tableName,dictionary.name)); } } } } } |