Missing text from <memo> field in table in report

vascobnunes 0
Viewed 36

I have multiple models in Sparx Enterprise Architect in file-based, i.e. using MS access.

I'm using a custom template to populate a table with data from object's properties, including some with <memo> fields.

This is the query i'm using in the template fragment:

SELECT obj.object_id,
obj.Stereotype,
objp.Property as Prop,
switch(objp.Value = '<memo>', objp.Notes, objp.Value LIKE '{*}', 
NULL, 1=1, objp.Value) AS Val,
(SELECT tobj2.ea_guid & tobj2.Name FROM t_object tobj2 WHERE 
tobj2.ea_guid = objp.Value) AS [Obj-Hyperlink]
FROM t_object obj 
INNER JOIN t_objectproperties objp
ON (obj.object_id = objp.object_id)
WHERE obj.object_id = #OBJECTID# AND obj.Stereotype='Data- 
Stream' AND objp.Property NOT IN ('isEncapsulated')
ORDER BY objp.Property ASC;

enter image description here

I found that the when these fields are longer than 249 chars I get an error message when generating the reports and the cell in the generated table is simply empty. This is also noticeable with a query:

enter image description here

The error I'm getting states: "Error Processing xml document: an invalid character was found in text context"

Is there any workarround to enable including the <memo> fields' data with more than 249 chars in the reports?

Any help is much appreciated.

enterprise-architect ms-access
1 Answers

I've found a workaround for this by joining two queries with a "Union all". The first query will handle the non-memo fields with the switch function and the second one the memo fields without the switch function.

    select 
     obj.object_id, 
     obj.Stereotype, 
     objp.Property as Prop, 
     objp.Notes AS Val, 
     (
      SELECT 
       tobj2.ea_guid & tobj2.Name 
      FROM 
       t_object tobj2 
      WHERE 
      tobj2.ea_guid = objp.Value
     ) AS [Obj-Hyperlink] 
  from 
     t_objectproperties objp 
     left join t_object obj on (obj.object_id = objp.object_ID) 
  where 
     obj.object_id = #OBJECTID# 
     AND obj.Stereotype = 'Data-Stream' 
     AND objp.Property NOT IN ('isEncapsulated') 
     AND objp.Value = "<memo>" 
  UNION ALL 
  SELECT 
     obj2.object_id, 
     obj2.Stereotype, 
     objp2.Property as Prop, 
     switch(
      objp2.Value LIKE '{*}', NULL, 1 = 1, objp2.Value
     ) AS Val, 
     (
      SELECT 
        tobj2.ea_guid & tobj2.Name 
      FROM 
        t_object tobj2 
      WHERE 
        tobj2.ea_guid = objp2.Value
     ) AS [Obj-Hyperlink] 
  FROM 
     t_object obj2 
     INNER JOIN t_objectproperties objp2 ON (obj2.object_id = objp2.object_id) 
  WHERE 
     obj2.object_id = #OBJECTID# 
     AND obj2.Stereotype = 'Data-Stream' 
     AND objp2.Property NOT IN ('isEncapsulated') 
     and objp2.Value <> "<memo>" 
  order by 
     3 asc;

Thanks a lot @geertbellekens for your comment which was crucial to find this solution.

vascobnunes0
Related