Help me fix my EA 16 query it crashes when run in EA (new problem!!)

[Richard Freggi]

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)

Code: [Select]

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:

Code: [Select]

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%')


[Geert Bellekens]

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

[Richard Freggi]

Thanks Geert it worked!  I would have never thought of that.  Cheers and happy new year.

[Richard Freggi]

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:

Code: [Select]

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!

[Geert Bellekens]

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

[Richard Freggi]

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?

[Geert Bellekens]

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

[Richard Freggi]

I guess no choice but looking into it - thanks again Geert!