UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Web Service In Vba, Access 2016    
 
   
andywal6
post Oct 1 2019, 09:07 AM
Post#1



Posts: 169
Joined: 11-January 08



Hi All
I know that this is a well covered subject but I have been reading for two days and still can not get my head around this problem.

My client is over the moon with the Access Database solution that I have created for him.
Just one more thing to get sorted.
He needs to link to a Products and Price list hosted on his major suppliers Web Server.
I have contacted them and asked for an API or a URI that I can use to download the Price list when required.
They responded with the SOAP routines below and said it was the only way that we could link to the Service.

Can anyone help me to convert this SOAP to XMLHTTP or an HTTP Request that will retrieve the data tables.
This might just as well be written in sanscrit or hebrew for all the good it does me to study it.
Many thanks
Andy

CODE
POST /PricingSvc/PricingService.asmx HTTP/1.1
Host: ultranet.ultraframe.co.UK
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <GetProducts xmlns="http://tempuri.org/">
      <rwcust>
        <UserId>int</UserId>
        <Password>string</Password>
        <ProxyAccount>string</ProxyAccount>
      </rwcust>
      <tableName>string</tableName>
    </GetProducts>
  </soap12:Body>
</soap12:Envelope>
HTTP/1.1 200 OK
Content-Type: application/soap+xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <GetProductsResponse xmlns="http://tempuri.org/">
      <GetProductsResult>
        <Status>int</Status>
        <Error>string</Error>
        <syncTable>
          <Sproc>string</Sproc>
          <TableName>string</TableName>
          <SortColumn1>string</SortColumn1>
          <SortColumn2>string</SortColumn2>
          <byteArray>base64Binary</byteArray>
        </syncTable>
      </GetProductsResult>
    </GetProductsResponse>
  </soap12:Body>
</soap12:Envelope>
Go to the top of the page
 
jleach
post Oct 2 2019, 05:10 AM
Post#2


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


Hi - you're looking at two things in this code that was posted: the post request (the part that says "hey, I need info based on this data"), and the response from the server.

Look at the first line of that block of markup:

POST /PricingSvc/PricingService.asmx HTTP/1.1

This says it's a POST type of request to the /PricingSvc/ProcingService.asmx endpoint, using HTTP/1.1 version protocol.

The second line simply tells the host:

Host: ultranet.ultraframe.co.UK

So, the actual URL that you'd submit it to is the host + endpoint:

hxxp://ultranet.ultraframe.co.UK/PricingSvc/PricingService.asmx


What you would do (authentication aside) is create a new HTTP Request object (probably via the xmlhttprequest class), point it to that address, then pass the xml (which starts as <?xml... as the body of your post request.

So there's two different things we're looking at: the first "block" is just the raw info on where and how the request is going. The actual body of the request is the xml itself.


But... there's also a response in the code you put. Notice this line after the first of the two xml blocks is closed:

HTTP/1.1 200 OK

That means that the http protocol responded with a 200 status code (200 = ok, google http status codes for more info on various codes and meanings). Everything from here down is the response that you might expect from what you submitted. Again, a piece of xml starts in the response with the same opening tag <?xml... which is likely what you're most interested in.


I have no idea if that helps (it's a big topic), but hopefully it's a start.

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
jleach
post Oct 2 2019, 05:16 AM
Post#3


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


I know you've said you've read for days, but I thought maybe I'd toss this out there anyway. It's a slide deck for a two-part presentation I give sometimes on doing web service work with VBA, and walks through not only the VBA end of things, but also a fairly in-depth with the http protocol and the parts that are important to know so you can understand this type of work.

https://dymeng.com/misc/webservice.zip

(it's too big to upload here - includes two powerpoint presentations, demo app and a bunch of samples)

Maybe it will be helpful...

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
jleach
post Oct 2 2019, 05:21 AM
Post#4


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


This article in the wiki walks through the basic usage of the xmlhttprequest class:

https://www.UtterAccess.com/wiki/HttpRequest_Class


(note, nothing has been mentioned yet about authentication, which is usually the biggest headache of the entire process...)

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
andywal6
post Oct 2 2019, 11:45 AM
Post#5



Posts: 169
Joined: 11-January 08



Hi Jack
Thanks for all the reading matter.
I've spent the afternoon reading all of your presentations and samples.
It has helped me to get my head around a lot of the component parts of Requests.

I still don't feel confident to convert the soap above to HTTPRequest

But I will continue my studies.

Your last point about Authorisation, isn't this covered in the centre of the top section in between the <rwcust> and </rwcust> tags?
I was given a Username, Password and ProxyAccount for the purpose of accessing the data required.

Andy..
Go to the top of the page
 
jleach
post Oct 2 2019, 12:18 PM
Post#6


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


Re: authorization, yes that could be correct (SOAP protocol uses a different type of auth than more modern OAuth based systems do, so maybe that'll be enough on that end).

Regarding "converting to the SOAP to an HTTP Request" - you may be thinking about it wrong. The SOAP XML that you have is simply the content that you send as part of the http request.

An HTTP Request has a few basic parts: headers, and endpoint (the URL you submit to), a request type (usually GET or POST), and a body/content, which you can pretty much stuff whatever in (text, xml, json, encoded file parts, etc).

The SOAP part of this (which is ONLY the XML portion of your original post) is to be sent as the body/content portion of the HTTP request... it's not the HTTP Request itself.

Cheers,

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
andywal6
post Oct 2 2019, 04:37 PM
Post#7



Posts: 169
Joined: 11-January 08



Thanks again Jack.
I'm so tired, I've been reading since 6:30 this morning and I still cant see how to get a flat table price list that I can link in to my database from the soap examples they have sent me.
I've just realized that the code sample I sent in my first post is not the one that calls for all of the products and prices for the price list. That's a much bigger one.
I have also be trudging through the wsdl definitions and I think they have confused me more.
I'll plough on tomorrow as I have to get this done by Monday morning or I'll lose the contract.
Thanks for your help today.
Andy.
Go to the top of the page
 
jleach
post Oct 3 2019, 06:01 AM
Post#8


UtterAccess Administrator
Posts: 10,281
Joined: 7-December 09
From: St. Augustine, FL


Sorry I can't be of more help.

Admittedly, this is a tough topic and requires a lot of knowledge of web development to do well.

Perhaps if you're under a tight timeline and it's worth the investment, you might try to hire someone who knows the area well enough to get you out of a bind. (I know of a few people who may be able to help, though I'm not sure what cost would look like on a short-term emergency basis, but if you want I can direct them to the thread)

--------------------
Jack D. Leach
Founder & CEO
Dymeng Services Inc.
Business Software Solutions
Go to the top of the page
 
andywal6
post Oct 3 2019, 07:12 AM
Post#9



Posts: 169
Joined: 11-January 08



Hello Jack if you are still watching.
I have completed a vba Function with yours and others help, that runs the soap instructions in the request above.
It looks like this below:

CODE
Public Function GetPriceList()

    Dim objHttp As Object
    Dim sURL As String
    Dim sEnv As String
    Dim xmlDoc As New DOMDocument60
    Set objHttp = CreateObject("MSXML2.XMLHTTP")
    sURL = "http://ultranet.ultraframe.co.UK/PricingSvc/PricingService.asmx"
          
    'sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
     sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
     sEnv = sEnv & "  <soap:Body>"
     sEnv = sEnv & "   <GetPrices xmlns=""http://ultraframe.co.UK/"">"
     sEnv = sEnv & "    <rwcust>"
     sEnv = sEnv & "     <UserId>" & lngUser & "</UserId>"
     sEnv = sEnv & "     <Password>" & strPass & "</Password>"
     sEnv = sEnv & "     <ProxyAccount>" & strProxy & "</ProxyAccount>"
     sEnv = sEnv & "    </rwcust>"
     sEnv = sEnv & "   </GetPrices>"
     sEnv = sEnv & "  </soap:Body>"
     sEnv = sEnv & "</soap:Envelope>"
    
     objHttp.Open "GET", sURL, False
     objHttp.setRequestHeader "Host", "ultranet.ultraframe.co.UK"
     objHttp.setRequestHeader "Content-Type", "text/xml"
     objHttp.setRequestHeader "content-Length", "Length"
     objHttp.setRequestHeader "SOAPAction", "http://ultraframe.co.UK/GetPrices"
     objHttp.Send sEnv
     xmlDoc.LoadXML objHttp.responseText
     'Do something with DOMDoc
    Debug.Print objHttp.responseText
    MsgBox objHttp.responseText
    'clean up
    Set objHttp = Nothing
    Set xmlDoc = Nothing
End Function


Surprisingly, the response from running this Function does not return the desired result and certainly not what they show the response to be.

When run the function completes without fault or error.
But what it returns is an html code for the page I started on.
Have I done something wrong, or have they supplied the wrong routine that should return a table of products and prices?
The output is as follows:

CODE
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <link rel="alternate"
type="text/xml" href="/PricingSvc/PricingService.asmx?disco">
  <style type="text/CSS">
BODY { color: #000000; background-color: white; font-family: Verdana; margin-left: 0px; margin-top: 0px; }
#content { margin-left: 30px; font-size: .70em; padding-bottom: 2em; }
A:link { color: #336699; font-weight: bold; text-decoration: underline; }
A:visited { color: #6699cc; font-weight: bold; text-decoration: underline; }
A:active { color: #336699; font-weight: bold; text-decoration: underline; }
A:hover { color: cc3300; font-weight: bold; text-decoration: underline; }
P { color: #000000; margin-top: 0px; margin-bottom: 12px; font-family: Verdana; }
pre { background-color: #e5e5cc; padding: 5px; font-family: Courier New; font-size: x-small; margin-top: -5px; border: 1px #f0f0e0 solid; }
td { color: #000000; font-family: Verdana; font-size: .7em; }
h2 { font-size: 1.5em; font-weight: bold; margin-top: 25px; margin-bottom: 10px; border-top: 1px solid #003366; margin-left: -15px; color: #003366; }
h3 { font-size: 1.1em; color: #000000; margin-left: -15px; margin-top: 10px; margin-bottom: 10px; }
ul { margin-top: 10px; margin-left: 20px; }
ol { margin-top: 10px; margin-left: 20px; }
li { margin-top: 10px; color: #000000; }
font.value { color: darkblue; font: bold; }
font.key { color: darkgreen; font: bold; }
font.error { color: darkred; font: bold; }
.heading1 { color: #ffffff; font-family: Tahoma; font-size: 26px; font-weight: normal; background-color: #003366; margin-top: 0px; margin-bottom: 0px; margin-left: -30px; padding-top: 10px; padding-bottom: 3px; padding-left: 15px; width: 105%; }
.button { background-color: #dcdcdc; font-family: Verdana; font-size: 1em; border-top: #cccccc 1px solid; border-bottom: #666666 1px solid; border-left: #cccccc 1px solid; border-right: #666666 1px solid; }
.frmheader { color: #000000; background: #dcdcdc; font-family: Verdana; font-size: .7em; font-weight: normal; border-bottom: 1px solid #dcdcdc; padding-top: 2px; padding-bottom: 2px; }
.frmtext { font-family: Verdana; font-size: .7em; margin-top: 8px; margin-bottom: 0px; margin-left: 32px; }
.frmInput { font-family: Verdana; font-size: 1em; }
.intro { margin-left: -15px; }
  </style>
  <title>PriceResponder Web
Service
  </title>
</head>
<body>
<div id="content">
<p class="heading1">PriceResponder</p>
<br>
<span>
<p class="intro">The
following operations are supported. For a formal definition, please
review the <a
href="PricingService.asmx?WSDL">Service Description</a>.
</p>
<ul>
  <li>
    <a
href="PricingService.asmx?op=GetPriceEnquiry">GetPriceEnquiry</a>
  </li>
  <p>
  </p>
  <li><a
href="PricingService.asmx?op=GetPrices">GetPrices</a>
  </li>
  <p>
  </p>
  <li><a
href="PricingService.asmx?op=GetPrices2">GetPrices2</a>
  </li>
  <p>
  </p>
  <li><a
href="PricingService.asmx?op=GetPrices3">GetPrices3</a>
  </li>
  <p>
  </p>
  <li><a
href="PricingService.asmx?op=GetProducts">GetProducts</a>
  </li>
  <p>
  </p>
</ul>
</span>
<span></span>
<span>
<hr>
<h3>This web service is using
http://tempuri.org/ as its default namespace.</h3>
<h3>Recommendation: Change the
default namespace before the XML Web service is made public.</h3>
<p class="intro">Each XML
Web service needs a unique namespace in order for client applications
to distinguish it from other services on the Web. http://tempuri.org/
is available for XML Web services that are under development, but
published XML Web services should use a more permanent namespace.</p>
<p class="intro">Your XML
Web service should be identified by a namespace that you control. For
example, you can use your company's Internet domain name as part of the
namespace. Although many XML Web service namespaces look like URLs,
they need not point to actual resources on the Web. (XML Web service
namespaces are URIs.)</p>
<p class="intro">For XML
Web services creating using ASP.NET, the default namespace can be
changed using the WebService attribute's Namespace property. The
WebService attribute is an attribute applied to the class that contains
the XML Web service methods. Below is a code example that sets the
namespace to "http://microsoft.com/webservices/":</p>
<p class="intro">C#</p>
<pre>[WebService(Namespace="http://microsoft.com/webservices/")]<br>public class MyWebService {<br>// implementation<br>}</pre>
<p class="intro">Visual
Basic</p>
<pre>&lt;WebService(Namespace:="http://microsoft.com/webservices/")&gt; Public Class MyWebService<br>' implementation<br>End Class</pre>
<p class="intro">C++</p>
<pre>[WebService(Namespace="http://microsoft.com/webservices/")]<br>public ref class MyWebService {<br>// implementation<br>};</pre>
<p class="intro">For more
details on XML namespaces, see the W3C recommendation on <a
href="http://www.w3.org/TR/REC-xml-names/">Namespaces
in XML</a>.</p>
<p class="intro">For more
details on WSDL, see the <a
href="http://www.w3.org/TR/wsdl">WSDL Specification</a>.</p>
<p class="intro">For more
details on URIs, see <a
href="http://www.ietf.org/rfc/rfc2396.txt">RFC 2396</a>.</p>
</span>
</div>
</body>
</html>


Any ones help would be much appreciated
Andy
Go to the top of the page
 
cheekybuddha
post Oct 3 2019, 03:00 PM
Post#10


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


Hi,

How are you populating the variables lngUser, strPass and strProxy ?

You are sending a GET request, when the spec mentioned in Post#1 seems to ask for a POST request.

Also, why are you commenting out the first line of the SOAP envelope?

These 2 headers look wrong:
CODE
' ...
     objHttp.setRequestHeader "content-Length", "Length"
     objHttp.setRequestHeader "SOAPAction", "http://ultraframe.co.UK/GetPrices"
' ...

The first I would imagine should be:
CODE
' ...
     objHttp.setRequestHeader "content-Length", Len(sEnv)
' ...


I'm not sure about the second. What prompted you to use that header?


d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 3 2019, 03:01 PM
Post#11


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


It seems you are using this link as a reference

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 3 2019, 03:18 PM
Post#12


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


Did you look at the second response in the thread in that link? It has the code 'that worked'

Try something like this:
CODE
Public Function GetPriceList(lngUser As long, strPass As String, strProxy As String)  As Boolean

  Dim sEnv As String, sURL AS String
'  Dim xmlDoc As New Object

  sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
  sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
  sEnv = sEnv & "  <soap:Body>"
  sEnv = sEnv & "   <GetPrices xmlns=""http://ultraframe.co.UK/"">"
  sEnv = sEnv & "    <rwcust>"
  sEnv = sEnv & "     <UserId>" & lngUser & "</UserId>"
  sEnv = sEnv & "     <Password>" & strPass & "</Password>"
  sEnv = sEnv & "     <ProxyAccount>" & strProxy & "</ProxyAccount>"
  sEnv = sEnv & "    </rwcust>"
  sEnv = sEnv & "   </GetPrices>"
  sEnv = sEnv & "  </soap:Body>"
  sEnv = sEnv & "</soap:Envelope>"

  sURL = "http://ultranet.ultraframe.co.UK/PricingSvc/PricingService.asmx"
'  Set xmlDoc = CreateObject("MSXML2.DOMDocument60")

  With CreateObject("MSXML2.XMLHTTP")
    .Open "Post", sURL, False
    .SetRequestHeader "Content-Type", "text/xml"
    .Send sEnv
    Debug.Print .responseText
  End With

  GetPriceList = (Err = 0)

End Function


See if you can get a response first, then worry about extracting the data.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Oct 3 2019, 04:12 PM
Post#13



Posts: 169
Joined: 11-January 08



Hi David
Thank you for offering assistance.

Firstly the lngUser, strPass and strProxy are in Constants at the top of the Module so that I don't have to show them to readers.

I have adjusted the code to the way you have it but on run I get a Fault.

<faultstring>Unable to handle request without a valid action parameter. Please supply a valid soap action.</faultstring>
Which is the Header that you queried I believe.

The Header info I inserted comes from the Soap routine the company provided below.

CODE
POST /PricingSvc/PricingService.asmx HTTP/1.1
Host: ultranet.ultraframe.co.UK
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/GetPrices"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetPrices xmlns="http://tempuri.org/">
      <rwcust>
        <UserId>int</UserId>
        <Password>string</Password>
        <ProxyAccount>string</ProxyAccount>
      </rwcust>
    </GetPrices>
  </soap:Body>
</soap:Envelope>


I don't know if your version is a better match to this.

Andy..
Go to the top of the page
 
cheekybuddha
post Oct 3 2019, 04:17 PM
Post#14


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


OK, so if that header is specified (I didn't see it in the original spec) then add it in.
CODE
' ...
  With CreateObject("MSXML2.XMLHTTP")
    .Open "Post", sURL, False
    .SetRequestHeader "Content-Type", "text/xml"
    .setRequestHeader "SOAPAction", "http://tempuri.org/GetPrices"
    .setRequestHeader "Content-Length", Len(sEnv)
    .Send sEnv
    Debug.Print .responseText
  End With
' ...


NB Edited to use the SOAPAction specified in the previous post, not the one I originally C+P'd from earlier

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Oct 3 2019, 05:02 PM
Post#15



Posts: 169
Joined: 11-January 08



Thank you David
I added the headers back in. Now I get This response:

<?xml version="1.0" encoding="utf-8"?><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"><soap:Body><GetPricesResponse xmlns="http://tempuri.org/"><GetPricesResult><Status>1</Status><Error>Invalid proxy access</Error></GetPricesResult></GetPricesResponse></soap:Body></soap:Envelope>

Possibly have the ProxyAccount name wrong!!
I'll have to check tomorrow.
Andy
Go to the top of the page
 
cheekybuddha
post Oct 3 2019, 05:06 PM
Post#16


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


Andy, it looks like you're making a little headway! One step at a time!!

Keep us posted.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Oct 3 2019, 05:09 PM
Post#17



Posts: 169
Joined: 11-January 08



Do you think that I should put the 'HOST' Header back in?
Go to the top of the page
 
cheekybuddha
post Oct 3 2019, 05:09 PM
Post#18


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


I noticed you were sometimes a little lax with your letter cases. You may find these sorts of web request are less forgiving when it comes to case sensitivity.

Makes sure the proxyname is exactly as they gave it to you.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
andywal6
post Oct 3 2019, 05:23 PM
Post#19



Posts: 169
Joined: 11-January 08



That is totally correct, and as given to me.

Would the wsdl Definition be of any help? (It's Quite Large)

Andy
Go to the top of the page
 
cheekybuddha
post Oct 4 2019, 03:56 AM
Post#20


UtterAccess Moderator
Posts: 11,906
Joined: 6-December 03
From: Telegraph Hill


Yes, probably!

Attach as a text file if it's huge.

--------------------


Regards,

David Marten
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 07:58 PM