Clarity

Expand all | Collapse all

Authentication problems using webservices with VBA

  • 1.  Authentication problems using webservices with VBA

    Posted 05-23-2014 01:07 PM

    Hello Folks, hope everyone is fine.

    We are developing a solution to migrate transactions, products and some other data into Clarity using a Excel Spreadsheet with VBA code.

    I am trying to do some tests in order to make sure I create all the methods (connecting, desconnecting and requesting). While I was able to login in the environments using my credentials and get the SessionID through SOAP, I had problems when requesting instances of the Vendor object.

    Using this input:

    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">

              <soapenv:Header>
                    <xog:Auth><xog:SessionID>762621__CF669159-B1C6-4C59-A0D0-F1BEBA2D19D</xog:SessionID></xog:Auth>
              </soapenv:Header>
              <soapenv:Body>
                    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_notification.xsd">
                              <Header version="13.0" action="read" objectType="vendor" externalSource="NIKU"/><Query><Filter name="code" criteria="EQUALS">vendor1</Filter></Query>
                     </NikuDataBus>
                </soapenv:Body>
    </soapenv:Envelope>
    I receive this output:
    <soapenv:Body>
        <XOGOutput xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/status.xsd">
          <Status elapsedTime="0.0 seconds" state="FAILURE"/>
          <Statistics failureRecords="0" insertedRecords="0" totalNumberOfRecords="0" updatedRecords="0"/>
          <ErrorInformation>
            <Severity>FATAL</Severity>
            <Description>Not authenticated</Description>
            <Exception/>
          </ErrorInformation>
        </XOGOutput>
      </soapenv:Body>
    Strange is that I receive the session ID without any problem, but when I try to use this sessionID it seems that the environment dont authenticate me.
    Another fact is that when I try to logout using the sessionID that I previously got I receive this message:
      <soapenv:Header/>
      <soapenv:Body/>
    </soapenv:Envelope>
    Would someone have any idea why this is happening?
    Thanks,
    Vitor.


  • 2.  RE: Authentication problems using webservices with VBA

    Former Employee
    Posted 06-06-2014 02:31 PM
    voliveira:

    Hello Folks, hope everyone is fine.

    We are developing a solution to migrate transactions, products and some other data into Clarity using a Excel Spreadsheet with VBA code.

    I am trying to do some tests in order to make sure I create all the methods (connecting, desconnecting and requesting). While I was able to login in the environments using my credentials and get the SessionID through SOAP, I had problems when requesting instances of the Vendor object.

    Using this input:

    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">

              <soapenv:Header>
                    <xog:Auth><xog:SessionID>762621__CF669159-B1C6-4C59-A0D0-F1BEBA2D19D</xog:SessionID></xog:Auth>
              </soapenv:Header>
              <soapenv:Body>
                    <NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_notification.xsd">
                              <Header version="13.0" action="read" objectType="vendor" externalSource="NIKU"/><Query><Filter name="code" criteria="EQUALS">vendor1</Filter></Query>
                     </NikuDataBus>
                </soapenv:Body>
    </soapenv:Envelope>
     
    I receive this output:
    <soapenv:Body>
        <XOGOutput xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/status.xsd">
          <Status elapsedTime="0.0 seconds" state="FAILURE"/>
          <Statistics failureRecords="0" insertedRecords="0" totalNumberOfRecords="0" updatedRecords="0"/>
          <ErrorInformation>
            <Severity>FATAL</Severity>
            <Description>Not authenticated</Description>
            <Exception/>
          </ErrorInformation>
        </XOGOutput>
      </soapenv:Body>
     
    Strange is that I receive the session ID without any problem, but when I try to use this sessionID it seems that the environment dont authenticate me.
     
    Another fact is that when I try to logout using the sessionID that I previously got I receive this message:
      <soapenv:Header/>
      <soapenv:Body/>
    </soapenv:Envelope>
     
    Would someone have any idea why this is happening?
     
    Thanks,
    Vitor.
     

     


    Hi All,

    Any ideas here for Vitor?

    Thanks!

    Chris



  • 3.  Re: Authentication problems using webservices with VBA

    Posted 10-16-2014 02:59 PM

    This question is a bit old now, but I assume the lack of a big green tick means it hasn't yet been answered.  I've been wrestling with XOG over VBA for a while and now have a (very rudimentary version of) it working and thought I'd share.  Vitor, you didn't post your VBA so I don't know where you were hitting speed-bumps, but here's what I have so far (Clarity On Demand, version 13.1):

     

    This procedure logs into XOG and returns a Session ID:

    Function Login(Optional xogUserName As String, Optional xogPassword As String, Optional xogURL As String) As String

            Dim xmlHTTPConnection As MSXML2.XMLHTTP60

        Set xmlHTTPConnection = New MSXML2.XMLHTTP60

     

        If xogURL = "" Then

            xogURL = "https://" & clarityInstance & ".ondemand.ca.com/niku/xog"

        End If

       

        xogMessage = xogMessage & "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:tns=""http://www.niku.com/xog/Query"">"

        xogMessage = xogMessage & "   <soap:Header/>"

        xogMessage = xogMessage & "   <soap:Body>"

        xogMessage = xogMessage & "       <tns:Login>"

        xogMessage = xogMessage & "           <tns:Username>" & xogUserName & "</tns:Username>"

        xogMessage = xogMessage & "           <tns:Password>" & xogPassword & "</tns:Password>"

        xogMessage = xogMessage & "       </tns:Login>"

        xogMessage = xogMessage & "   </soap:Body>"

        xogMessage = xogMessage & "</soap:Envelope>"

       

        xmlHTTPConnection.Open "POST", xogURL, False

        xmlHTTPConnection.setRequestHeader "Content-Type", "text/xml"

     

        xmlHTTPConnection.send xogMessage

       

        Dim xogSessionResponse As MSXML2.DOMDocument60

        Set xogSessionResponse = New MSXML2.DOMDocument60

       

        With xogSessionResponse

            .async = False

            .preserveWhiteSpace = False

            .validateOnParse = False

            .resolveExternals = False

            .setProperty "SelectionLanguage", "XPath"

            .setProperty "SelectionNamespaces", "xmlns:xog=""http://www.niku.com/xog"""

        End With

       

        xogSessionResponse.LoadXML (xmlHTTPConnection.responseText)

     

        Login = xogSessionResponse.Text

       

        Set xmlHTTPConnection = Nothing

        Set xogSessionResponse = Nothing

       

    End Function

     

    This Procedure calls XOG and returns a single Project record (it just writes each Property Name and Value pair to the debug window):

    Function GetQuery(sSID As String, sProjID) As String

     

    Dim xQuery As MSXML2.XMLHTTP60

    Dim xogSessionResponse As MSXML2.DOMDocument60

    Dim sQry As String

    Dim sHeader As String

    Dim xogURL As String

     

    Dim soapenv As MSXML2.IXMLDOMNode

    Dim theProjectNode As MSXML2.IXMLDOMNode

    Dim ProjectProp As MSXML2.IXMLDOMAttribute

     

    Dim oS As Worksheet

     

        xogURL = "https://" & clarityInstance & ".ondemand.ca.com/niku/xog"

     

        Set xQuery = New MSXML2.ServerXMLHTTP60

       

        sHeader = "xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"""

        sHeader = sHeader & " xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"""

        sHeader = sHeader & " xmlns:xsd=""http://www.w3.org/2001/XMLSchema"""

        sHeader = sHeader & " xmlns:tns=""http://www.niku.com/xog/Query"""

       

        sQry = "<soap:Envelope " & sHeader & ">"

        sQry = sQry & " <soap:Header>"

        sQry = sQry & "     <tns:Auth>"

        sQry = sQry & "         <tns:SessionID>" & sSID & "</tns:SessionID>"

        sQry = sQry & "     </tns:Auth>"

        sQry = sQry & " </soap:Header>"

        sQry = sQry & " <soap:Body>"

        sQry = sQry & "     <NikuDataBus xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xsi:noNamespaceSchemaLocation=""../xsd/nikuxog_read.xsd"">"

        sQry = sQry & "         <Header version=""13.1.0.0248"" action=""read"" objectType=""project"" externalSource=""NIKU"">"

        sQry = sQry & "             <args name=""order_by_1"" value=""name""/>"

        sQry = sQry & "             <args name=""order_by_2"" value=""projectID""/>"

        sQry = sQry & "             <args name=""include_tasks"" value=""false""/>"

        sQry = sQry & "             <args name=""include_dependencies"" value=""false""/>"

        sQry = sQry & "             <args name=""include_subprojects"" value=""false""/>"

        sQry = sQry & "             <args name=""include_resources"" value=""false""/>"

        sQry = sQry & "             <args name=""include_baselines"" value=""false""/>"

        sQry = sQry & "             <args name=""include_allocations"" value=""false""/>"

        sQry = sQry & "             <args name=""include_estimates"" value=""false""/>"

        sQry = sQry & "             <args name=""include_actuals"" value=""false""/>"

        sQry = sQry & "             <args name=""include_custom"" value=""false""/>"

        sQry = sQry & "             <args name=""include_burdening"" value=""false""/>"

        sQry = sQry & "         </Header>"

        sQry = sQry & "         <Query>"

        sQry = sQry & "             <Filter name=""projectID"" criteria=""EQUALS"">" & sProjID & "</Filter>"

        sQry = sQry & "         </Query>"

        sQry = sQry & "     </NikuDataBus>"

        sQry = sQry & "   </soap:Body>"

        sQry = sQry & "</soap:Envelope>"

       

        xQuery.Open "POST", xogURL, False

        xQuery.setRequestHeader "soapAction", "QueryCSV"

     

        xQuery.send sQry

       

        Set xogSessionResponse = New MSXML2.DOMDocument60

       

        With xogSessionResponse

            .async = False

            .preserveWhiteSpace = False

            .validateOnParse = False

            .resolveExternals = False

            .setProperty "SelectionLanguage", "XPath"

            .setProperty "SelectionNamespaces", "xmlns:xog=""http://www.niku.com/xog"""

        End With

     

        xogSessionResponse.LoadXML (xQuery.responseText)

       

        Set soapenv = xogSessionResponse.ChildNodes(0)

        Set theProjectNode = soapenv.ChildNodes(1).ChildNodes(0).ChildNodes(1).ChildNodes(0)

        For Each ProjectProp In theProjectNode.Attributes

            Debug.Print ProjectProp.nodeName & ": " & ProjectProp.NodeValue

        Next ProjectProp

       

        GetQuery = xogSessionResponse.Text

       

        Set xQuery = Nothing

        Set xogSessionResponse = Nothing

    End Function

     

    This procedure logs out of XOG:

    Function Logout(sessionID As String) As String

       

        Dim xmlHTTPConnection As MSXML2.XMLHTTP60

        Set xmlHTTPConnection = New MSXML2.XMLHTTP60

     

        If xogURL = "" Then

            xogURL = "https://" & clarityInstance & ".ondemand.ca.com/niku/xog"

        End If

       

        xogMessage = xogMessage & "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:tns=""http://www.niku.com/xog/Query"">"

        xogMessage = xogMessage & "   <soap:Header/>"

        xogMessage = xogMessage & "   <soap:Body>"

        xogMessage = xogMessage & "       <tns:Logout>"

        xogMessage = xogMessage & "           <tns:SessionID>" & xogUserName & "</tns:SessionID>"

        xogMessage = xogMessage & "       </tns:Logout>"

        xogMessage = xogMessage & "   </soap:Body>"

        xogMessage = xogMessage & "</soap:Envelope>"

       

        xmlHTTPConnection.Open "POST", xogURL, False

        xmlHTTPConnection.setRequestHeader "Content-Type", "text/xml"

     

        xmlHTTPConnection.send xogMessage

       

        Dim xogSessionResponse As MSXML2.DOMDocument60

        Set xogSessionResponse = New MSXML2.DOMDocument60

       

        With xogSessionResponse

            .async = False

            .preserveWhiteSpace = False

            .validateOnParse = False

            .resolveExternals = False

            .setProperty "SelectionLanguage", "XPath"

            .setProperty "SelectionNamespaces", "xmlns:xog=""http://www.niku.com/xog"""

        End With

       

        xogSessionResponse.LoadXML (xmlHTTPConnection.responseText)

     

        Logout = xogSessionResponse.Text

       

        Set xmlHTTPConnection = Nothing

        Set xogSessionResponse = Nothing

       

    End Function

     

    Hope this helps.