Sparx Systems Forum
Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: Richard Freggi on December 29, 2022, 02:05:29 am
-
For the life of me, I cannot understand why this code crashes EA 16.1 on my Win 10 machine (32GB RAM).
It's a custom query (My searches) to generate a DDL template for HANA views. My repository is standard *.qea file on my local hard disk.
EA shuts down every time I run it. If I run from DBeaver (accessing the *.qea file and using the package GUID) it runs fine.
Bug? My EA instance is possessed? Any ideas (I need to use the #Branch# statement)
SELECT
'CREATE VIEW "_SYS_BIC"."' || t_package.Name || '/' || t_object.Name || '" AS' || CHAR(10) || COALESCE(vdef.notes, '')
FROM t_object
LEFT OUTER JOIN t_package ON t_object.Package_ID = t_package.Package_ID
LEFT OUTER JOIN t_objectproperties vdef ON t_object.Object_ID = vdef.Object_ID AND vdef.Property = 'viewdef'
WHERE t_object.Package_ID IN (#Branch#) AND LOWER(t_object.Stereotype) LIKE('%view%')
This runs OK in EA:
SELECT t_package.Name || t_object.Name || COALESCE(vdef.notes, '')
FROM t_object
LEFT OUTER JOIN t_package ON t_object.Package_ID = t_package.Package_ID
LEFT OUTER JOIN t_objectproperties vdef ON t_object.Object_ID = vdef.Object_ID AND vdef.Property = 'viewdef'
WHERE t_object.Package_ID IN (#Branch#) AND LOWER(t_object.Stereotype) LIKE('%view%')
-
might be because you didn't name the columns you returned.
Try adding "as myCol" or something.
EA tries to convert queries into XML strings, using the column names as entity names.
Geert
-
Thanks Geert it worked! I would have never thought of that. Cheers and happy new year.
-
So now the query runs and produces the right result. But EA is adding double quotes in the wrong places when I export the result to text or CSV.
I tried escaping the double quotes in the query strings, using CHAR() etc. but it seems that the double quotes " are added by the results window, regardless if I copy (CTRL C) or I use CSV Import/export to export the results.
If I paste into Excel it displays OK but if I paste into any test editor I get extra ". Any ideas on how to get rid of the unwanted " (besides of course a global REPLACE in the text editor)
The DML is:
SELECT
'CREATE VIEW "_SYS_BIC"."' || t_package.Name || '/' || t_object.Name || '" AS' || CHAR(10) || COALESCE(vdef.notes, '') || ';' || CHAR(10) ||
COALESCE ('COMMENT ON VIEW "_SYS_BIC"."' || t_package.Name || '/' || t_object.Name || '" IS ' || CHAR(39) || t_object.Note || CHAR(39) || ';', '')
The result pasted to text editor or exported to CSV is:
"CREATE VIEW ""_SYS_BIC"".""Utility/AB"" AS ...
The desired result is:
CREATE VIEW "_SYS_BIC"."Utility/AB" AS ...
I've never seen this random addition of " not just at beginning and end but also inside the result string. Any ideas? Thanks!
-
That's not random addition of double quotes, that's simply the proper escaping for the RFC 4180 CSV format.
As far as I can see, EA does exactly what it needs to do here.
Geert
-
I understand now thanks Geert. So now I'm reduced to global replace "" with " on the text export from EA - not ideal. Anyone knows a way to disactivate EA's escape of " when copying or exporting from the results window?
-
Looks like you are using the wrong tool for the job.
If you don't need .CSV, you shouldn't be exporting to CSV
Have you thought about writing a little script that executes the SQL query and writes to the format you need?
Geert
-
I guess no choice but looking into it - thanks again Geert!