(SQL Server) Belgium eHealth Platform - AddressBook - Search for Professionals

See more Belgian eHealth Platform Examples

Demonstrates how to search for professionals using the AddressBook API.

For more information, see https://www.ehealth.fgov.be/ehealthplatform/nl/data/file/view/6c419e5685327eed7200d1c543c38a55d5387d30?name=AddressBook%20Consultation%20WS%20v.1%20-%20Cookbook%20v.1.11%20dd%2031082023.pdf

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- --------------------------------------------------------------------------------
    -- Also see Chilkat's Online WSDL Code Generator
    -- to generate code and SOAP Request and Response XML for each operation in a WSDL.
    -- --------------------------------------------------------------------------------

    DECLARE @success int
    SELECT @success = 1

    -- Provide a certificate + private key.
    -- Note: If your certificate + private key is located on a hardware token or smartcard, you can call a different function to load from smartcard..
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'SSIN=12345678.acc.p12', 'p12_password'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Let's get the SAML Security Token obtained from https://services-acpt.ehealth.fgov.be/IAM/SecurityTokenService/v1/RequestSecurityToken
    -- a very short time ago..

    -- See this example for how we got the SAML security token: eHealth Belgium Request Security Token
    DECLARE @bdSecToken int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdSecToken OUT

    EXEC sp_OAMethod @bdSecToken, 'LoadFile', @success OUT, 'qa_data/tokens/ehealth-fgov-be-sectoken.xml'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load SAML security token'
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdSecToken
        RETURN
      END

    -- The SAML security token is just the part from <Assertion ..> ... </Assertion>
    -- So let's get just that part..
    DECLARE @startIdx int
    EXEC sp_OAMethod @bdSecToken, 'FindString', @startIdx OUT, '<Assertion', 0, 'utf-8'
    IF @startIdx < 0
      BEGIN

        PRINT 'Did not find the start of the SAML assertion.'
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdSecToken
        RETURN
      END
    DECLARE @endIdx int
    EXEC sp_OAMethod @bdSecToken, 'FindString', @endIdx OUT, '</Assertion>', 0, 'utf-8'
    IF @endIdx < 0
      BEGIN

        PRINT 'Did not find the end of the SAML assertion.'
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdSecToken
        RETURN
      END

    -- Adjust the index to the 1st byte after "</Assertion>"
    SELECT @endIdx = @endIdx + 12
    DECLARE @base64_saml_token nvarchar(4000)
    EXEC sp_OAMethod @bdSecToken, 'GetEncodedChunk', @base64_saml_token OUT, @startIdx, @endIdx - @startIdx, 'base64'


    PRINT @base64_saml_token

    -- Our SOAP request will look like this:

    -- The SOAP request to search for professionals looks like this:

    -- <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:add="urn:be:fgov:ehealth:addressbook:protocol:v1">
    --     <soapenv:Header>
    -- 	      <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
    -- 	         <wsse:BinarySecurityToken
    -- 	            ValueType="http://docs.oasis-open.org/wss/oasis-wss-saml-token-profile-1.0#SAMLAssertionID"
    -- 	            EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">BASE64_SAML_TOKEN</wsse:BinarySecurityToken>
    -- 	      </wsse:Security>
    --     </soapenv:Header>
    --     <soapenv:Body>
    --         <add:SearchProfessionalsRequest Id="bdc38ae62-3e7f-4f80-80f7-c3e745500fa3" IssueInstant="2016-03-23T18:49:26.968+01:00" Offset="0" MaxElements="100">
    --             <add:SSIN>74062423769</add:SSIN>
    --         </add:SearchProfessionalsRequest>
    --     </soapenv:Body>
    -- </soapenv:Envelope>

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OASetProperty @xml, 'Tag', 'soapenv:Envelope'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security', 1, 'xmlns:wsse', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsse:BinarySecurityToken', 1, 'ValueType', 'http://docs.oasis-open.org/wss/oasis-wss-saml-token-profile-1.0#SAMLAssertionID'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsse:BinarySecurityToken', 1, 'EncodingType', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary'

    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsse:BinarySecurityToken', @base64_saml_token

    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|add:SearchProfessionalsRequest', 1, 'Id', 'bdc38ae62-3e7f-4f80-80f7-c3e745500fa3'

    DECLARE @dt int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT

    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
    EXEC sp_OAMethod @dt, 'GetAsTimestamp', @sTmp0 OUT, 1
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|add:SearchProfessionalsRequest', 1, 'IssueInstant', @sTmp0

    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|add:SearchProfessionalsRequest', 1, 'Offset', '0'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'soapenv:Body|add:SearchProfessionalsRequest', 1, 'MaxElements', '100'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soapenv:Body|add:SearchProfessionalsRequest|urn:SSIN', '74062423769'

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    EXEC sp_OAMethod @http, 'SetSslClientCert', @success OUT, @cert
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdSecToken
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'text/xml'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'SOAPAction', 'urn:be:fgov:ehealth:addressbook:protocol:v1:searchProfessionals'

    DECLARE @resp int
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostXml', @resp OUT, 'https://services.ehealth.fgov.be/AddressBook/v1', @sTmp0, 'utf-8'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @bdSecToken
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'response status code = ' + @iTmp0

    EXEC @hr = sp_OADestroy @resp


    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @bdSecToken
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @http


END
GO