---

Hallo,

mit folgendem VB.Net code kann ich direkt die Anzahl aller Items aus dem TCE auslesen.

Code:

---

Imports System.Data.SqlClient

Module Module1

    Sub Main()
        Try
            Dim conStr As String = "Data Source=CAD002\TCE;" & _
                "Initial Catalog=TceProd;" & _
                "Trusted_Connection=yes"
            Dim TCE As SqlConnection = New SqlConnection(conStr)
            AddHandler TCE.StateChange, AddressOf TCE_StateCange
            TCE.Open()
            ' Anweisungen

            ' alle Items
            'Dim cmdStr As String = "SELECT pitem_id FROM PITEM"

            ' Anzahl Items
            Dim cmdStr As String = "SELECT COUNT(*) FROM PITEM"

            Dim cmd As SqlCommand = New SqlCommand(cmdStr, TCE)
            Dim dr As SqlDataReader = cmd.ExecuteReader()
            Do While (dr.Read())
                Console.WriteLine(dr(0))
            Loop
            dr.Close()
            TCE.Close()
        Catch ex As Exception
            Console.WriteLine(ex.ToString)
        End Try
        Console.WriteLine("Taste drücken ...")
        Console.ReadKey()
    End Sub
    Sub TCE_StateCange(ByVal obj As Object, ByVal e As StateChangeEventArgs)
        Console.WriteLine("Zustandsänderung: von {0} nach {1}", _
            e.OriginalState.ToString, e.CurrentState.ToString)
    End Sub
End Module

---

Kann man irgend wie den SQL-code der TCE-Queries auslesen oder wie kann ich z.B. das ergebnis der folgenden TCE-Query per VB.Net erhalten.

Code:

---

SELECT qid FROM ItemRevision WHERE "items_tag.item_id" = "${ID = }" AND "Dataset:IMAN_specification.owning_user.user_name" != "cad_admin" AND "Dataset:IMAN_specification.object_type" = "${Typ = Ideas*}"

---

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hallo,

ich hab es selber gefunden:

Code:

---

Imports System.Data.SqlClient

Module Module1

    Sub Main()
        Dim SqlAbfrage As String = ""
        ' TCE-Suchabfrage: Item_Revisions
        SqlAbfrage = "SELECT PVITEM.pitem_id FROM PVITEMREVISION, PVITEM WHERE PVITEMREVISION.ritems_tagu = PVITEM.puid AND PVITEM.pitem_id LIKE '%'"
        TceAbfrage(SqlAbfrage, "K:\Support.NX\Item_Revision.txt")

        ' TCE-Suchabfrage: EI-NoCadAdmin_Revision
        SqlAbfrage = "SELECT tRevision.pitem_id FROM PVIMANRELATION,(SELECT PVITEMREVISION.puid AS _revision_puid, PVITEM.pitem_id, PVITEMREVISION.pitem_revision_id FROM PVITEMREVISION, PVITEM WHERE PVITEMREVISION.ritems_tagu = PVITEM.puid AND PVITEM.pitem_id LIKE '%') AS tRevision,(SELECT tDatasetType._dataset_puid AS _dataset_puid, tDatasetType.pdatasettype_name, tDatasetUser.puser_id FROM(SELECT PVDATASET.puid AS _dataset_puid, PVDATASETTYPE.pdatasettype_name FROM PVDATASET, PVDATASETTYPE WHERE PVDATASET.rdataset_typeu = PVDATASETTYPE.puid AND (PVDATASETTYPE.pdatasettype_name = 'IdeasPart' OR PVDATASETTYPE.pdatasettype_name = 'IdeasAssembly' OR PVDATASETTYPE.pdatasettype_name = 'IdeasDrawing')) AS tDatasetType,(SELECT PVDATASET.puid AS _dataset_puid, PVUSER.puser_id FROM PVDATASET, PVUSER WHERE PVDATASET.rowning_useru = PVUSER.puid AND PVUSER.puser_id != 'cad_admin') AS tDatasetUser WHERE tDatasetType._dataset_puid = tDatasetUser._dataset_puid) AS tDataset WHERE(rprimary_objectu = tRevision._revision_puid And rsecondary_objectu = tDataset._dataset_puid) ORDER BY tRevision.pitem_id"
        TceAbfrage(SqlAbfrage, "K:\Support.NX\EI-NoCadAdmin_Revision.txt")

        Console.WriteLine("Taste drücken ...")
        Console.ReadKey()
    End Sub
    Sub TCE_StateCange(ByVal obj As Object, ByVal e As StateChangeEventArgs)
        Console.WriteLine("Zustandsänderung: von {0} nach {1}", _
            e.OriginalState.ToString, e.CurrentState.ToString)
    End Sub
    Sub TceAbfrage(ByVal cmdStr As String, ByVal AusgabeDatei As String)
        Try
            Dim conStr As String = "Data Source=CAD002\TCE;" & _
                "Initial Catalog=TceProd;" & _
                "Trusted_Connection=yes"
            Dim TCE As SqlConnection = New SqlConnection(conStr)
            AddHandler TCE.StateChange, AddressOf TCE_StateCange
            TCE.Open()
            Dim cmd As SqlCommand = New SqlCommand(cmdStr, TCE)
            Dim dr As SqlDataReader = cmd.ExecuteReader()
            Dim sw As New IO.StreamWriter(AusgabeDatei)
            Dim Ausgabe As String = ""
            For i As Integer = 0 To dr.FieldCount - 1
                Ausgabe &= dr.GetName(i) & ";"
            Next i
            If Right(Ausgabe, 1) = ";" Then Ausgabe = Ausgabe.Substring(0, Ausgabe.Length - 1)
            sw.WriteLine(Ausgabe)
            Console.WriteLine(Ausgabe)
            Do While (dr.Read())
                Ausgabe = ""
                For i As Integer = 0 To dr.FieldCount - 1
                    Ausgabe &= dr(i) & ";"
                Next i
                If Right(Ausgabe, 1) = ";" Then Ausgabe = Ausgabe.Substring(0, Ausgabe.Length - 1)
                sw.WriteLine(Ausgabe)
                Console.WriteLine(Ausgabe)
            Loop
            sw.Close()
            dr.Close()
            TCE.Close()
        Catch ex As Exception
            Console.WriteLine(ex.ToString)
        End Try
    End Sub
End Module

---

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

In reinem SQL würde des m.E. etwa so aussehen:

select
  i.pitem_id, ir.pitem_revision_id, ds.pobject_type, u.PUSER_ID
from
  pitem i
  ,pitemrevision ir
  ,pimanrelation r
  ,pvdataset ds
  ,pvuser u
where
      i.pitem_id = 'P6191360'
  and ir.ritems_tagu = i.puid
  and r.rprimary_objectu=ir.puid
  and ds.puid = r.rsecondary_objectu
  and u.puid = ds.ROWNING_USERU
  and upper(ds.pobject_type) like 'IDEAS%'
  and u.PUSER_ID != 'cad_admin'

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP