Sparx Systems Forum

Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: Hurra on December 22, 2022, 02:26:07 am

Title: SQLQuery (string SQL) without line break?
Post by: Hurra on December 22, 2022, 02:26:07 am
Hello!

I have a script to export a ModelView to Excel.

We changed our environment to a new remote desktop (or something idk).

Before the move, the exported texts (notes, requirement-texts, etc), came in one cell in Excel even when the text included line breaks. But now I can't parse the returning XML because of line breaks.

I use, from the Repository class, SQLQuery (string SQL)
Code: [Select]
queryResult = Repository.SQLQuery(query);
where the query is picked up from the ViewProperties memo on the ModelView.

The error occurs when I try to parse the XML with the built in function:
Code: [Select]
var DOMDoc = XMLParseXML(queryResult);
because of line breaks in queryResult.

Code: [Select]
EAScriptLib.JScript-XML error: Expected token '&&' found 'NAME'.

//Row/Krav-k-->&<--#228;lla, Line:182


Referring me to:
Quote from: JScript-XML
Code: [Select]
182: var nodeList = xmlDOM.documentElement.selectNodes( nodePath );

As I said, I didn't have this problem before.

Can I somehow clean the queryResult before parsing? I know in Excel line break is CHAR(10).

Or should I just remove all the line breaks in all the texts and have to live with it?

Someone out there with a similar issue?

Thank you for your time!
Title: Re: SQLQuery (string SQL) without line break?
Post by: Geert Bellekens on December 22, 2022, 03:43:19 am
Sure you can clean the queryresult. It's a simple string.

If you post it here we can maybe find what's wrong with it. I'm pretty sure it's not related to newlines at all, but to the xml encoding of certain characters.

In this case the character "ä"
This is encoded as "&#228;" in the notes field.

Might be that you have a query like

Select o.Name as Kravkälla, ... which EA tries to convert to an xml tag <Kravkälla> then gets mangled by the xml encoding stuff.

Geert
Title: Re: SQLQuery (string SQL) without line break?
Post by: Hurra on December 23, 2022, 12:46:58 am
Quote from: Geert Bellekens on December 22, 2022, 03:43:19 am
Sure you can clean the queryresult. It's a simple string.

If you post it here we can maybe find what's wrong with it. I'm pretty sure it's not related to newlines at all, but to the xml encoding of certain characters.

In this case the character "ä"
This is encoded as "&#228;" in the notes field.

Might be that you have a query like

Select o.Name as Kravkälla, ... which EA tries to convert to an xml tag <Kravkälla> then gets mangled by the xml encoding stuff.

Geert

Thank you! I can't believe I didn't realize that.. I'll excuse myself by being a bit under the weather right now 😇

Changing 'Källa' to 'Source' fixed it.


I encountered another 'problem' when I was troubleshooting. It is that Repository.SQLQuery() can't handle #Branch#.

I've been implementing #Branch=<GUID># more and more in my queries and has a lot of use for it. But as you all probably know, #Branch# can't be used with Repository.SQLQuery().

Is there a simple workaround for this? Like, what is the "clean" SQL variant of #Branch#? Is it a lot of inner joins to find child packages to a certain level?

Cheers, and thanks again!
Title: Re: SQLQuery (string SQL) without line break?
Post by: Geert Bellekens on December 23, 2022, 12:52:48 am
The solution is to write the #Branch# logic yourself.
Not super simple, but no rocket science either.

Here's a post that discusses exactly that: https://sparxsystems.com/forums/smf/index.php/topic,38987.msg259113.html#msg259113 (https://sparxsystems.com/forums/smf/index.php/topic,38987.msg259113.html#msg259113)

Geert
Title: Re: SQLQuery (string SQL) without line break?
Post by: qwerty on December 23, 2022, 12:56:36 am
The #branch# only works in queries from the query builder. You can place your query there and use repository.GetElementsByQuery

q.