Document generation with fragment

[[email protected]]

I have created a document template with an embedded fragment. Within the fragment (template) I am using a custom query to select a specific requirements. From these requirements I want to display some attributes and of of them is the description which is stored in the field t_object.note.
The syntax of the query is like Select o.note as RequirementDescription from t_object o .....where ....
and the alias  RequirementDescription is mapped to the field in the fragment.     
Now I have recognized, that in case of a long description I got not the whole text but only 254 characters.
Is there a way to read the whole content and bring it to the document?

Thanks for your help
Andy

[Geert Bellekens]

Check out this page of the manuel:
http://sparxsystems.com/enterprise_architect_user_guide/13.5/model_publishing/custom_sql_fragments.html

Somewhere at the bottom of no 5 you'll find:

It is possible to force a field to be processed as a hyperlink or as formatted notes, by providing a column in this format:
    custom >
     {Name}
     <fieldname>.Formatted      (or <fieldname>.Hyperlink)       (for all repositories, excluding .eap)
     < custom
In your custom SQL Query statement, you must use an alias matching the template field name; for example (in MySQL):
     SELECT ea_guid AS CLASSGUID, Object_Type AS CLASSTYPE, Name, Note as "Note.Formatted" FROM t_object
     Where Object_ID=#OBJECTID#

Geert

[[email protected]]

Hi Geert,
thanks for your help. I tried it out only now, but it didn't works.

1. The query did work only in the format ....Note as [Note-Formatted], a dot "." always returned an error
2. In the custom field on the template I tried different variants, but I only get the 255 characters.

Did you tried it out?
How was your query and how was the exact content of the custom field?

Andy   

[Geert Bellekens]

Andy,

Depending on the database you'll have to use a dot "." or a hyphen "-", so that error you got is normal.
The custom field in the template is always uses a dot.
Yes, I have several templates that use this and it works.

Geert

[[email protected]]

Hi Geert,
I am back again.

I tried following query using a hyphen:
select distinct (o.object_ID), o.ea_guid AS CLASSGUID, o.Object_Type AS CLASSTYPE, o.name as ObjectName, p1.value as ObjectID, p2.value as MESRelease,
o.note as "Note-Formatted", c.start_object_id
from t_object o, t_connector c, t_objectproperties p, t_objectproperties p1, t_objectproperties p2 where ........

and I got following error:
Error: Code = 0x0 Source = Line: 0; Char : 0 Error Description = (null)

I didn't configure a custom field for the "Note-Formatted", therefore the error seems to be generated, while executing the query.
The other fields (ObjectName, ObjectID and MESRelease) are reported.

Any ideas?

By the way: I am using EA version 12.0.1214 and Database version 4.01.

Thanks a lot.
Andy
 

[Geert Bellekens]

Andreas,

The best way to make sure that your query is correct is to test in in the SQL scratch pad (open model search, edit the search and select SQL scratch pad.)
Just make sure you replace the #OBJECTID#/#PACKAGEID#/#DIAGRAMID# macro's with actual id's.

Things that are suspicious in your query:
- distinct (o.object_ID) why the parentheses? You don't normally use parentheses for distinct
- as "Note-Formatted" double quotes are almost never used in any SQL dialect. Use square brackets instead e.g. [Note-Formatted]

Other than the error you are getting is often seen when there is an issue with some kind of xml processing internally, but it is a very general non-descript error messages that doesn't help you much in resolving the problem.

I do believe that I've seen some improvements in that area in the latest versions.

Also, you didn't mention what type of database you are using. That determines the SQL dialect you should use (and whether to use [Note.Formatted] or [Note-Formatted])

Geert

[[email protected]]

Hi Geert,

Now I have found the reason of my problem. When I am using the "distinct" in my query, it returns only 255 characters of the Note file of the Object. This looks strange to me.
But now I have to modify my query in order it returns a single row for one object.

Thanks a lot for your support
Andy

[Paolo F Cantoni]

Hi Geert,

Now I have found the reason of my problem. When I am using the "distinct" in my query, it returns only 255 characters of the Note file of the Object. This looks strange to me.
But now I have to modify my query in order it returns a single row for one object.

Thanks a lot for your support
Andy

Hi Andreas,

Not so strange...  I believe what is going on is that when you use "DISTINCT" it needs a key mechanism in order to determine uniqueness (distinctiveness).  You can't have a normal key on a Notes field (Memo etc) so it creates a transient key - which can't be longer than 255 characters (since it's a string field, not memo).  It returns the key value, NOT the Notes value.

Paolo