MS SQL VS. PostgreSQL repository automation

[m2i7c9k]

Hi Guys!

I have vb.NET 2019 Microsoft Word Add-in that can connect to EA repository, and gets
currently opened diagram element(s).

The problem is that if I have MS SQL repository, my code works fine, but on the PostgreSQL repository
my code returns nothing (without any error).

I'm parsing XLM string:


            Dim eaXML As String = GetDiagramElements(EARep.GetCurrentDiagram.DiagramID, EARep)

            Dim xmlData As String = eaXML
            Dim doc As XElement = XElement.Parse(xmlData)

            'Parse XML Data.
            Dim query = From d In doc.<Dataset_0>.<Data>.<Row>
                        Select New With
                            {
                             .ElementName = d.<ElementName>.Value,
                             .ElementNote = d.<ElementNote>.Value,
                             .ElementGUID = d.<ElementGUID>.Value
                            }

            'Import current diagram elements.
            For Each d In query
                With .ActiveDocument.Tables(1)
                    .Cell(rIdx, 1).Range.Font.Bold = True
                    .Cell(rIdx, 1).Range.Text = d.ElementName

                    If d.ElementName <> "" And d.ElementNote = "" Then
                        .Cell(rIdx, 2).Range.Text = "N/A" & vbCr & d.ElementGUID
                    Else
                        .Cell(rIdx, 2).Range.Text = d.ElementNote & vbCr & d.ElementGUID '& vbCr & d.ElementStereo
                    End If
                End With
                rIdx += 1
            Next

Is there some another way to parse XML on the PostgreSQL repository?

Thankx a lot

Mick

[Geert Bellekens]

The problem with PostGreSQL is dat all tables and columns are lowercase

This also means that the results from things like Repository.SQLQuery are also in lowercase.
In my code I use xpath to get details from an xml result.

This is my code to format the xpath depending on the repository type:

Code: [Select]

        public string formatXPath(string xpath)
        {
            switch (this.repositoryType)
            {

                case RepositoryType.ORACLE:
                case RepositoryType.FIREBIRD:
                    return xpath.ToUpper();
                case RepositoryType.POSTGRES:
                    return xpath.ToLower();
                default:
                    return xpath;
            }
        }
Geert

[m2i7c9k]

Hi Greet!

Thank you very much from your code. I transformed it like this:

    Public Enum RepositoryType
        ORACLE
        FIREBIRD
        POSTGRES
    End Enum

    Public Function formatXPath(ByVal xpath As String) As String
        Select Case EARep.RepositoryType
            Case RepositoryType.ORACLE, RepositoryType.FIREBIRD
                Return xpath.ToUpper()
            Case RepositoryType.POSTGRES
                Return xpath.ToLower()
            Case Else
                Return xpath
        End Select
    End Function

But it still returns empty strings

Mick

[Geert Bellekens]

Mick,

Have you looked into the xml returned from the query?
If you do you'll probably easily see what is going wrong.

Geert

[m2i7c9k]

Hi Greet,

it looks same?
SQL
<?xml version="1.0"?>
<EADATA version="1.0" exporter="Enterprise Architect">
   <Dataset_0><Data><Row><ElementName>REQ</ElementName><ElementNote>N/A</ElementNote><ElementGUID>{247B1DDE-8B58-44f4-9139-215AFE5B6EF4}</ElementGUID></Row><Row><ElementName>REQ2</ElementName><ElementNote>N/A</ElementNote><ElementGUID>{A835E90D-8181-485e-8E2E-50EDDCB0EDA1}</ElementGUID></Row><Row><ElementName>REQ3</ElementName><ElementNote>Test. Pokus.</ElementNote><ElementGUID>{670EED37-B43E-4caf-A45A-707A35D421D0}</ElementGUID></Row></Data></Dataset_0></EADATA>


POSTGRES
<?xml version="1.0"?>
<EADATA version="1.0" exporter="Enterprise Architect">
   <Dataset_0><Data><Row><elementname>Requirement1</elementname><elementnote>&lt;b&gt;Notes&lt;/b&gt;</elementnote><elementguid>{47709442-0FCB-4d61-A70A-CDB6106DD9BC}</elementguid></Row><Row><elementname>Requirement2</elementname><elementnote>&lt;i&gt;Notes&lt;/i&gt;</elementnote><elementguid>{22E29562-1A7D-4baf-9503-13BC831CB07C}</elementguid></Row></Data></Dataset_0></EADATA>

AND it is wrong?
If I understand it well, POSTGRES must be lowercase, correct?
That means a whole string?

Mick

[Geert Bellekens]

It's not the same.
SQL Server has
"<ElementName>"
whereas Postgres has
"<elementname>"

Remember, XML libraries are case sensitive.

Geert

[m2i7c9k]

At this moment I'm frozen.

Your code works, it converts XML to lowercase, but the output is still empty.

This is my SQL:

Dim sqlCmd As String = "SELECT " & vbCr &
                                    "t_object.name As ElementName, " & vbCr &
                                    "t_object.note As ElementNote, " & vbCr &
                                    "t_object.ea_guid As ElementGUID " & vbCr &
                                "FROM   " & vbCr &
                                    "t_diagramobjects LEFT JOIN t_object ON " & vbCr &
                                    "t_diagramobjects.Object_ID = t_object.Object_ID " & vbCr &
                                "WHERE  " & vbCr &
                                    "t_diagramobjects.Diagram_ID = " & DiagramID & " " & vbCr &
                                "ORDER BY  " & vbCr &
                                    "t_diagramobjects.RectBottom DESC; "

        Dim queryResult As String = eaRep.SQLQuery(sqlCmd)
        ' Debug.Print(queryResult)
        Return CStr(queryResult)

at this moment I'll give up, thanks for your time

[Geert Bellekens]

I'm pretty sure you'll have to change this part of the code:

Code: [Select]

Dim query = From d In doc.<Dataset_0>.<Data>.<Row>
                        Select New With
                            {
                             .ElementName = d.<ElementName>.Value,
                             .ElementNote = d.<ElementNote>.Value,
                             .ElementGUID = d.<ElementGUID>.Value
                            }
Because it is here that you reference the node names such as ElementName
And it's not as simple as all lowercase or all uppercase. As you can see in the results the "Dataset_0" still uses capitals. It's only the nodes that are generated from the query that are all in lowercase.


Geert

[m2i7c9k]

Yes, somewhere must be something wrong; you were right.
It's between keyboard and user, I will work on in it, and maybe one day
I'll figure it out Until I make it for MS SQL only.

Thank you a lot.

[m2i7c9k]

Hi Geert,

as you said (as always:) the problem was here:
 
Dim query = From d In doc.<dataset_0>.<data>.<row>
                   Select New With {.ElementnName = d.<elementname>.Value,
                                              .ElementNote = d.<elementnote>.Value,
                                              .ElementGUID = d.<elementguid>.Value}

I used your code "formatXPath," but I realized, that code (of course) do.ToLower, and it also converts the ElementName, Note and that is not good.

The question is: is there a way to convert only the XML TAGS?


Thank You

Mick

[m2i7c9k]

Finally, I found this function:

    Public Function SentenceCase(ByVal Input As String) As String
        Dim ThingsThatDontTriggerNewSentence As String = System.Text.RegularExpressions.Regex.Escape("();:'""-")
        Dim Pattern As String = "(?<=(?<![A-Za-z0-9 " & ThingsThatDontTriggerNewSentence & "])
        (([ " & ThingsThatDontTriggerNewSentence & "]){0,})?)[a-z]"
        Return System.Text.RegularExpressions.Regex.Replace(Input, Pattern, Function(m) m.Value(0).ToString().ToUpper() & m.Value.Substring(1))
    End Function

and its works