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
> Using Soap - In Over My Head, Access 2016    
 
   
aoh
post Dec 12 2017, 09:36 AM
Post#1



Posts: 1,417
Joined: 20-February 04
From: Dublin, Ireland


Hi guys,

A hospital where some of my clients work are trying to automate their booking forms. They have sent me the specs and details and I can put together the XML but I'm stumped as to how to proceed. I need to be able to send them all the info they need - patient, insurance, next of kin, procedure, etc. and then pull apart the response to see if it was accepted or rejected - and if rejected, why.

They also sent me sample code but it's in JavaScript and I've been trying to translate into VBA but failing miserably.

So far, I have

CODE
Public Function HospitalXML()
    Dim objXML As Object
    Dim strURL As String
    Dim strData As String

    strData = BuildData

    Set objXML = CreateObject("MSXML2.XMLHTTP")
    strURL = "http://hospital.com/b2b/externalwebservices/hospitaltransferwebservice.asmx/RequestBooking"
    Debug.Print strURL

    objXML.Open "POST", strURL, False
    objXML.send (strData)
    MsgBox objXML.responseText
    
    Set objXML = Nothing
  
End Function

Public Function BuildData() As String

    Dim strData As String

    strData = "POST /b2b/externalwebservices/hospitaltransferwebservice.asmx HTTP/1.1" & vbCrLf & _
        "Host: hospital.smartsoftware360.com" & vbCrLf & _
        "Content-Type: text/xml; charset=utf-8" & vbCrLf & _
        "Content -length: length" & vbCrLf & _
        "SOAPAction: " & Chr(34) & "http://hospital.com/b2b/externalwebservices/hospitaltransferwebservice.asmx/RequestBooking" & _
        Chr(34) & vbCrLf & _
        "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "utf-8" & Chr(34) & "?>" & vbCrLf & _
        "<soap:Envelope xmlns:xsi=" & Chr(34) & "http://www.w3.org/2001/XMLSchema-instance" & Chr(34) & _
            " xmlns:xsd=" & Chr(34) & "http://www.w3.org/2001/XMLSchema" & Chr(34) & _
            " xmlns:soap=" & Chr(34) & "http://schemas.xmlsoap.org/soap/envelope/" & Chr(34) & ">" & vbCrLf & _
        "<soap:Body>" & vbCrLf & _
        "<RequestBooking xmlns=" & Chr(34) & "http://HospitalTransfer.org/" & Chr(34) & ">" & vbCrLf

    
    strData = strData & "<User>annec</User>" & vbCrLf
    strData = strData & "<Password>?Rockki800</Password>" & vbCrLf
    etc.
    strData = strData & "</BookingData>" & vbCrLf

    BuildData = strData
  
End Function


Can anyone help?
Go to the top of the page
 
theDBguy
post Dec 12 2017, 10:43 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,501
Joined: 19-June 07
From: SunnySandyEggo


Hi Anne,

Is there a specific area you need help with? Are you trying to pull from or push data to the web service?
Go to the top of the page
 
jleach
post Dec 12 2017, 01:30 PM
Post#3


UtterAccess Editor
Posts: 9,934
Joined: 7-December 09
From: Staten Island, NY, USA


There's a lot of details that need to be just right, but I'm noticing that your body seems to have header information in it, which need to be set as headers of the request rather than submitted as part of the body.

In this part:

CODE
strData = "POST /b2b/externalwebservices/hospitaltransferwebservice.asmx HTTP/1.1" & vbCrLf & _
        "Host: hospital.smartsoftware360.com" & vbCrLf & _
        "Content-Type: text/xml; charset=utf-8" & vbCrLf & _
        "Content -length: length" & vbCrLf & _
        "SOAPAction: " & Chr(34) & "http://hospital.com/b2b/externalwebservices/hospitaltransferwebservice.asmx/RequestBooking" & _
        Chr(34) & vbCrLf & _
        "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "utf-8" & Chr(34) & "?>" & vbCrLf & _
        "<soap:Envelope xmlns:xsi=" & Chr(34) & "http://www.w3.org/2001/XMLSchema-instance" & Chr(34) & _
            " xmlns:xsd=" & Chr(34) & "http://www.w3.org/2001/XMLSchema" & Chr(34) & _
            " xmlns:soap=" & Chr(34) & "http://schemas.xmlsoap.org/soap/envelope/" & Chr(34) & ">" & vbCrLf & _
        "<soap:Body>" & vbCrLf & _
        "<RequestBooking xmlns=" & Chr(34) & "http://HospitalTransfer.org/" & Chr(34) & ">" & vbCrLf


... there's a few things going on. Your specifying the method (POST), the host, then three headers, then the body itself (starting with the xml declaration). While this is technically correct in terms of the raw request format, the XMLHTTP client you're using will take care of much of this for you. You supply just the body, and it will generate the raw details according to how you have it configured.

For example, it will add POST based on the fact that you have it specified in the Open method. And it will add the host automatically. The headers it will do as well, but you need to supply those:

CODE
objXML.Open "POST", strURL, False

objXML.SetRequestHeader "Content-Type", "text/xml"
objXML.SetRequestHeader "OtherKey", "OtherValue"
'etc

objXML.send (strData)


Thus, your BuildData should only return the string starting at the <?xml... line.

There may be others: this work is very specific, but that should give you a good push in the right direction.

Cheers
Go to the top of the page
 
jleach
post Dec 12 2017, 01:32 PM
Post#4


UtterAccess Editor
Posts: 9,934
Joined: 7-December 09
From: Staten Island, NY, USA


(also, if you can link to the JavaScript example or documentation, that'd be helpful so we can verify the requirements. You might also be interested to read through here: https://dymeng.com/web-work-with-VBA/ )
Go to the top of the page
 
aoh
post Dec 13 2017, 08:01 AM
Post#5



Posts: 1,417
Joined: 20-February 04
From: Dublin, Ireland


Hi guys,

Thanks for the pointers.

Have attached a cut down version of the Java Script - am guessing you don't need to know all 125 fields to be passed over!

Basically, I need to send a booking request which will contain details for one patient and then details of one or more procedures for that patient. That's the RequestBooking. There is also the option to then attach a file to the booking (e.g. test results) and that's the ReceiveSingleFile.

Each one will respond with a success or failure massage. If RequestBooking is successful, I need to store the Internal Booking Reference and if either of them is a failure, I need to tell the user why.

Now I'm away to read your link.

Attached File  Soap_Java.txt ( 5.33K )Number of downloads: 13
Go to the top of the page
 
jleach
post Dec 13 2017, 11:07 AM
Post#6


UtterAccess Editor
Posts: 9,934
Joined: 7-December 09
From: Staten Island, NY, USA


Hi Anne,

Are you sure this is a SOAP request? It doesn't appear to be in the JS example you gave.

In any case, do you have a link to their API documentation? We can try to infer things from the JS example, but a direct link to the documentation would be ever so much easier.

Thanks,
Go to the top of the page
 
cheekybuddha
post Dec 13 2017, 11:18 AM
Post#7


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


I agree with Jack.

This looks like a more straightforward request to a webservice (via POST).

You will need to create the JSON string from your db data to pass as the POST data.

There maybe a SOAP api as well that you can use more in the way you were attempting in your original post.

Jack can probably fig out some good examples quicker than I will be able to.

thumbup.gif

d
Go to the top of the page
 
cheekybuddha
post Dec 13 2017, 03:55 PM
Post#8


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


fig = dig

(Doh! Phone autocorrect / fat fingers)

d
Go to the top of the page
 
aoh
post Dec 14 2017, 04:17 AM
Post#9



Posts: 1,417
Joined: 20-February 04
From: Dublin, Ireland


Hi Jack,

I have documentation but it's a bit sensitive so I'd rather not post it online. Could I email it to you?

Thanks for the help.
Go to the top of the page
 
aoh
post Dec 15 2017, 05:36 AM
Post#10



Posts: 1,417
Joined: 20-February 04
From: Dublin, Ireland


OK, I think I've taken all the confidential stuff out of the attached - and again deleted most of the fields to be passed over. Would appreciate any help.

Attached File  Hospital_Webservice.zip ( 26.47K )Number of downloads: 16
Go to the top of the page
 
jleach
post Dec 16 2017, 03:37 AM
Post#11


UtterAccess Editor
Posts: 9,934
Joined: 7-December 09
From: Staten Island, NY, USA


I'd say go back to my previous post about setting request headers and including the body starting at the xml declaration.

When you send it, you should get a response object back that includes a statusText and status. Those would be helpful to debug any issues. (see here: http://www.UtterAccess.com/wiki/HttpRequest_Class)

Also, note that there's three version of the API described in the document: SOAP1.1, SOAP1.2 and a non-soap, regular request via JavaScript. Try using the SOAP1.2 format. (as a side note - I'm skeptical that this is "real" documentation: if so, it's the worst documentation I've never seen)

And finally: are you trying to upload files? If so, those would need to be converted accordingly, but in any case let's figure out the basic request stuff first.
Go to the top of the page
 
aoh
post Dec 17 2017, 07:12 AM
Post#12



Posts: 1,417
Joined: 20-February 04
From: Dublin, Ireland


Hi Jack,

I'm making progress, even if it's slow. The XML being produced looks good - I even had someone else check it out.

I can get a response of:

CODE
Redirecting...
function redirectHttpToHttps()
{
    var httpURL= window.location.hostname + window.location.pathname + window.location.search;
    var httpsURL= "https://" + httpURL;
    window.location = httpsURL;
}
redirectHttpToHttps();


and I have no idea what that means.

My latest code is:

CODE
Option Compare Database
Option Explicit

Public Function HospitalXML()
    Dim objXML As Object
    Dim strURL As String
    Dim strData As String
    Dim docDoc As DOMDocument60

    strData = BuildData

    strURL = "http://Hospital.whatever.com/b2b/externalwebservices/Hospitaltransferwebservice.asmx/RequestBooking"

    Set docDoc = New DOMDocument60
    Set objXML = CreateObject("MSXML2.XMLHTTP")

    With objXML
        .Open "POST", strURL, False
        .setRequestHeader "Content-Type", "text/xml"
        .send strData
        docDoc.loadXML (.responseText)
    End With
    MsgBox docDoc.Text
    
    Set objXML = Nothing
    Set docDoc = Nothing
  
End Function

Public Function BuildData() As String

    Dim strData As String

    strData = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "utf-8" & Chr(34) & "?>" & vbCrLf & _
        "<soap:Envelope xmlns:xsi=" & Chr(34) & "http://www.w3.org/2001/XMLSchema-instance" & Chr(34) & _
        " xmlns:xsd=" & Chr(34) & "http://www.w3.org/2001/XMLSchema" & Chr(34) & _
        " xmlns:soap=" & Chr(34) & "http://schemas.xmlsoap.org/soap/envelope/" & Chr(34) & ">" & vbCrLf & _
        "<soap:Body>" & vbCrLf & _
        "<RequestBooking xmlns=" & Chr(34) & "http://HospitalTransfer.org/" & Chr(34) & ">" & vbCrLf
    
    strData = strData & "<User>annec</User>" & vbCrLf
    strData = strData & "<Password>?Rockki800</Password>" & vbCrLf
    strData = strData & "<BookingData>" & vbCrLf
    strData = strData & "<InterfaceSystem>Cluan Medical</InterfaceSystem>" & vbCrLf
    strData = strData & "<ExtBookReference>1</ExtBookReference>" & vbCrLf

    strData = strData & "<ProcedureList>" & vbCrLf
    strData = strData & "<Procedure>" & vbCrLf
    
    strData = strData & "<ExtBookingRef>1</ExtBookingRef>" & vbCrLf
    strData = strData & "<ProcedureSeq>1</ProcedureSeq>" & vbCrLf
    strData = strData & "<ProcedureCode>1234</ProcedureCode>" & vbCrLf
    strData = strData & "<ProcedureComments></ProcedureComments>" & vbCrLf
    strData = strData & "<DateOfSurgery>14-12-2017</DateOfSurgery>" & vbCrLf
    
    strData = strData & "</Procedure>" & vbCrLf
    strData = strData & "</ProcedureList>" & vbCrLf

    strData = strData & "<ErrorMsg></ErrorMsg>" & vbCrLf
    strData = strData & "<BookingStatus></BookingStatus>" & vbCrLf
    strData = strData & "<InsuranceStatus></InsuranceStatus>" & vbCrLf
    strData = strData & "<SPApprovalNo></SPApprovalNo>" & vbCrLf
    strData = strData & "</BookingData>" & vbCrLf

    strData = strData & "</soap:Body>" & vbCrLf
    strData = strData & "</soap:Envelope>" & vbCrLf

    BuildData = strData
  
End Function
Go to the top of the page
 
jleach
post Dec 17 2017, 10:08 AM
Post#13


UtterAccess Editor
Posts: 9,934
Joined: 7-December 09
From: Staten Island, NY, USA


That means the server is redirecting to the secure channel (https) because you're submitting to the non-SSL channel (http).

In your submission code, when you enter the URL, use https instead:

CODE
strURL = "https://Hospital.whatever.com/b2b/externalwebservices/Hospitaltransferwebservice.asmx/RequestBooking"


That should clear this hurdle and see what comes next.

Cheers,
Go to the top of the page
 
aoh
post Dec 18 2017, 08:50 AM
Post#14



Posts: 1,417
Joined: 20-February 04
From: Dublin, Ireland


Hi Jack,

When I submit to https, the response text is

System.InvalidOperationException: RequestBooking Web Service method name is not valid.
at System.Web.Services.Protocols.HttpServerProtocol.Initialize()
at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)

and the status text is Internal Server Error!

I'm trying the hospital IT department for help but they didn't develop it, have no idea how to code for it and won't put me in touch with the actual developers. This is turning into a nightmare so I appreciate your help.
Go to the top of the page
 
jleach
post Dec 18 2017, 04:27 PM
Post#15


UtterAccess Editor
Posts: 9,934
Joined: 7-December 09
From: Staten Island, NY, USA


Web services are a trial, that's for sure.

Unfortunately, other than double checking the spelling (case sensitive, for good measure) against their documentation, there's not much else you can do. 500 internal service error indicates an issue on their end, so - providing your request is correct - it's something they have to solve (this is not unusual: many web service implementations are... less than perfect we can say... and there's other indications that this one isn't the ripest apple on the tree).

Even in the event that it's your error and not theirs, they should be returning a different status code and message. 500 level messages are reserved for "this is a problem on our end" responses from the server, so it's technically out of your hands. With that said, sometimes some further trial an error can resolve the issue from your end and work around their mistake, but that's even less fun with no guarantees.
Go to the top of the page
 
cheekybuddha
post Dec 18 2017, 07:45 PM
Post#16


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


I still think using the pure json method will be easier for you rather than SOAP and XML.

I'll try and work up an example for you from your documentation but may not get a chance for a day or two.

d
Go to the top of the page
 
jleach
post Dec 18 2017, 07:57 PM
Post#17


UtterAccess Editor
Posts: 9,934
Joined: 7-December 09
From: Staten Island, NY, USA


I agree that the JS API does seem a bit more straightforward - odd though that they've apparently got two very different APIs, but that may be a means around the 500 error you're getting now.

Problem is, if you don't have at least some light web development experience, trying the JS route could very well be digging into yet another rabbit hole.

I'm pretty well buried here myself, but I'll help out how I can...
Go to the top of the page
 
aoh
post Dec 31 2017, 07:03 AM
Post#18



Posts: 1,417
Joined: 20-February 04
From: Dublin, Ireland


Hi guys,

Just thought I'd update you on what happened in the week before Christmas. Someone from the hospital IT dept came out to me and made a few tweaks but we were still getting nowhere. He emailed the web service guys, who did not respond, so he got me to email them. They replied with a couple of suggestions which made no difference, so they tried the code themselves. Turns out:

1. The Web service is not yet set up to accept SOAP, so that will have to be done later this week. dazed.gif
2. My XML was all over the place - field names spelled incorrectly, dates in the wrong format, fields missing and fields that need a default value didn't have one. However, when I compare what they sent me to the original documentation, the documentation was wrong thumbdn.gif

No wonder I was getting nowhere - but at least hopefully this week, I will.

Thanks to both of you for the advice and encouragement. uarulez2.gif
Go to the top of the page
 
jleach
post Dec 31 2017, 08:24 AM
Post#19


UtterAccess Editor
Posts: 9,934
Joined: 7-December 09
From: Staten Island, NY, USA


This doesn't surprise me in the least. Unfortunately, this is very typical of web service work. It's usually either bad or ok (rarely great), and this is almost always what bad looks like.

Have fun!

Cheers,
Go to the top of the page
 
cheekybuddha
post Dec 31 2017, 08:56 AM
Post#20


UtterAccess VIP
Posts: 10,462
Joined: 6-December 03
From: Telegraph Hill


Hi Anne,

Sorry I haven''t had a chance to have a play as I had hoped.

I still intend to when I get a spare moment, but as always time is scarce, especially over Christmas.

Hope you had a good one, and glad to see you are making progress.

Will try and get back in the next few days.

Hi Jack! wavehi.gif

d
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 10:33 AM