we are in a process of migration V8i hybrid XFM + DB project to OpenCities Map CE environment. It seems that in Update 6 XFM schema migration is simpler and less buggy that it was before (good work!), but we found this (critical) issue:
There is a feature in DGN with linked data in database (both in directly linked table and others in lookup tables). The feature has annotation, that is split into several rows, using '\010' sequence (as described in BM documentation). It looks like this one:
PBA definition of the annotation is (one from several variants):
SELECT 'Š(' + MatZkratka + ')[DnX]\010[SachtaID]-[UzelID]\010[VyskaPoklop]\010([VyskaDno])' FROM CisMaterial WHERE MatID=[MatID]
which converted into SQL command is (captured using SESSION DEBUG ON):
SELECT 'Š(' + MatZkratka + ')1000\0102-5\010433.06\010(430.73)' FROM CisMaterial WHERE MatID=12
It's the same both in V8i and CE, so there is no problem.
The result of this query is:
When the feature is edited (or even not edited, only OK is pressed in Feature edit dialog), the annotation become corrupted:
Analysis / ideas:
Because query result is Š(BT)1000\0102-5\010433.06\010(430.73) and the corrupted text is Š(BT)1000\0102, I guess SQL query is analyzed as Unicode, so it's not about \010 (new line), but it's converted to U+0066, which is 102 in DEC, so insted of "<new line>2" it's interpreted as "f".
Summary / questions:
This issue is critical and the migration blocker, because nearly every feature in the project is annotated somehow and many from them use multiline annotations.
It seems I found a workaround: When I changed PBA (SQL) from
SELECT 'Š(' + MatZkratka + ')[DnX]','[SachtaID]-[UzelID]','[VyskaPoklop]','([VyskaDno])' FROM CisMaterial WHERE MatID=[MatID]
so I removed all \010 and structured the query to return a column per annotation line, it seems it works fine.
But I do not like this solution, because it requires to manually edit tens (probably over a hundred) of annotation definitions, which is risky (a threat syntax will corrupted), and also time consuming and hard to test. Just because in CE does not work what has worked for many years :-(
Labyrinth Technology | dev.notes() | cad.point
We are looking into this Jan. It appears to be an issue in the Platform code, when compressing character escape sequences in text strings. We will get a Defect on-file for the Development staff to investigate.
Answer Verified By: Jan Šlegr
thanks a lot for the issue analysis.
I will have to discuss the information with the customer and to decide next steps, because if the problem is inside platform code, I assume it cannot be fixed quickly. PowerPlatform CE U15 is probably too close to include the fix into it, so let's hope for Update 16 (May 2021?) plus a delay when OCM based on such update will follow.
We will analyze how many multiline annotations exist in project and what it requires to use the workaround I mentioned in my previous post (to change SQL in PBAs to return individual column per line and to test all modified features).
an update: I found out that the problem is not with PBA (SQL) only, but also with standard expressions :-(
It's bigger problem, because I am able to change all PBA (SQL) rules to do not use \010 (because individual columns are converted to separate lines automatically without problems), I do not see any workaround in the case of normal PBA expressions.