(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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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

    -- Create the XML to be signed...

    DECLARE @xmlToSign int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xmlToSign OUT

    EXEC sp_OASetProperty @xmlToSign, 'Tag', 'soapenv:Envelope'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:soapenv', 'http://schemas.xmlsoap.org/soap/envelope/'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:urn', 'urn:be:fgov:ehealth:platformintegrationconsumertest:v1'
    EXEC sp_OAMethod @xmlToSign, 'AddAttribute', @success OUT, 'xmlns:urn1', 'urn:be:fgov:ehealth:platformintegrationconsumertest:types:v1'
    EXEC sp_OAMethod @xmlToSign, '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 @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security', 1, 'xmlns:wsu', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd'
    EXEC sp_OAMethod @xmlToSign, '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 @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsse:BinarySecurityToken', 1, 'ValueType', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509v3'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsse:BinarySecurityToken', 1, 'wsu:Id', 'X509-FC77E2C72083DA8E0F16711753508182856'

    -- ---------------------------------------------------------------------------------------------------------------
    -- A note about the Id's, such as X509-FC77E2C72083DA8E0F16711753508182856, TS-FC77E2C72083DA8E0F16711753508042855, etc.
    -- These Id's simply need to be unique within the XML document.  You don't need to generate new Id's every time.
    -- You can use the same Id's in each XML document that is submitted. The purpose of each Id is to
    -- match the XMLDsig Reference to the element in XML being referenced. 
    -- In other words, you could use the Id's "mickey_mouse", "donald_duck", and "goofy", and it would work perfectly OK,
    -- as long as no other XML elements also use the Id's "mickey_mouse", "donald_duck", or "goofy"
    -- ---------------------------------------------------------------------------------------------------------------

    DECLARE @bdCert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdCert OUT

    EXEC sp_OAMethod @cert, 'ExportCertDerBd', @success OUT, @bdCert

    EXEC sp_OAMethod @bdCert, 'GetEncoded', @sTmp0 OUT, 'base64'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsse:BinarySecurityToken', @sTmp0
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Header|wsse:Security|wsu:Timestamp', 1, 'wsu:Id', 'TS-FC77E2C72083DA8E0F16711753508042855'

    DECLARE @dt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.CkDateTime', @dt OUT

    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
    EXEC sp_OAMethod @dt, 'GetAsTimestamp', @sTmp0 OUT, 0
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsu:Timestamp|wsu:Created', @sTmp0
    EXEC sp_OAMethod @dt, 'AddSeconds', @success OUT, 3600
    EXEC sp_OAMethod @dt, 'GetAsTimestamp', @sTmp0 OUT, 0
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Header|wsse:Security|wsu:Timestamp|wsu:Expires', @sTmp0
    EXEC sp_OAMethod @dt, 'AddSeconds', @success OUT, -3600

    -- The body part of the SOAP request to search for professionals looks like this:
    -- <soapenv:Body>
    --    <urn:SearchProfessionalsRequest Id="id-FC77E2C72083DA8E0F16711753508182859" IssueInstant="2016-03-23T18:49:26.968+01:00" Offset="0" MaxElements="100">
    --        <urn:SSIN>74062423769</urn:SSIN>
    --    </urn:SearchProfessionalsRequest>
    -- </soapenv:Body>

    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|urn:SearchProfessionalsRequest', 1, 'Id', 'id-FC77E2C72083DA8E0F16711753508182859'
    EXEC sp_OAMethod @dt, 'GetAsTimestamp', @sTmp0 OUT, 1
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|urn:SearchProfessionalsRequest', 1, 'IssueInstant', @sTmp0
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|urn:SearchProfessionalsRequest', 1, 'Offset', '0'
    EXEC sp_OAMethod @xmlToSign, 'UpdateAttrAt', @success OUT, 'soapenv:Body|urn:SearchProfessionalsRequest', 1, 'MaxElements', '100'
    EXEC sp_OAMethod @xmlToSign, 'UpdateChildContent', NULL, 'soapenv:Body|urn:SearchProfessionalsRequest|urn:SSIN', '12345678910'

    DECLARE @gen int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.XmlDSigGen', @gen OUT

    EXEC sp_OASetProperty @gen, 'SigLocation', 'soapenv:Envelope|soapenv:Header|wsse:Security|wsse:BinarySecurityToken'
    EXEC sp_OASetProperty @gen, 'SigLocationMod', 1
    EXEC sp_OASetProperty @gen, 'SigId', 'SIG-FC77E2C72083DA8E0F16711753508252860'
    EXEC sp_OASetProperty @gen, 'SigNamespacePrefix', 'ds'
    EXEC sp_OASetProperty @gen, 'SigNamespaceUri', 'http://www.w3.org/2000/09/xmldsig#'
    EXEC sp_OASetProperty @gen, 'SignedInfoPrefixList', 'soapenv urn urn1'
    EXEC sp_OASetProperty @gen, 'IncNamespacePrefix', 'ec'
    EXEC sp_OASetProperty @gen, 'IncNamespaceUri', 'http://www.w3.org/2001/10/xml-exc-c14n#'
    EXEC sp_OASetProperty @gen, 'SignedInfoCanonAlg', 'EXCL_C14N'
    EXEC sp_OASetProperty @gen, 'SignedInfoDigestMethod', 'sha256'

    -- Set the KeyInfoId before adding references..
    EXEC sp_OASetProperty @gen, 'KeyInfoId', 'KI-FC77E2C72083DA8E0F16711753508182857'

    -- -------- Reference 1 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'TS-FC77E2C72083DA8E0F16711753508042855', 'sha256', 'EXCL_C14N', 'wsse soapenv urn urn1', ''

    -- -------- Reference 2 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'id-FC77E2C72083DA8E0F16711753508182859', 'sha256', 'EXCL_C14N', 'urn urn1', ''

    -- -------- Reference 3 --------
    EXEC sp_OAMethod @gen, 'AddSameDocRef', @success OUT, 'X509-FC77E2C72083DA8E0F16711753508182856', 'sha256', 'EXCL_C14N', '_EMPTY_', ''

    EXEC sp_OAMethod @gen, 'SetX509Cert', @success OUT, @cert, 1

    EXEC sp_OASetProperty @gen, 'KeyInfoType', 'Custom'

    -- Create the custom KeyInfo XML..
    DECLARE @xmlCustomKeyInfo int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xmlCustomKeyInfo OUT

    EXEC sp_OASetProperty @xmlCustomKeyInfo, 'Tag', 'wsse:SecurityTokenReference'
    EXEC sp_OAMethod @xmlCustomKeyInfo, 'AddAttribute', @success OUT, 'wsu:Id', 'STR-FC77E2C72083DA8E0F16711753508182858'
    EXEC sp_OAMethod @xmlCustomKeyInfo, 'UpdateAttrAt', @success OUT, 'wsse:Reference', 1, 'URI', '#X509-FC77E2C72083DA8E0F16711753508182856'
    EXEC sp_OAMethod @xmlCustomKeyInfo, 'UpdateAttrAt', @success OUT, 'wsse:Reference', 1, 'ValueType', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509v3'

    EXEC sp_OASetProperty @xmlCustomKeyInfo, 'EmitXmlDecl', 0
    EXEC sp_OAMethod @xmlCustomKeyInfo, 'GetXml', @sTmp0 OUT
    EXEC sp_OASetProperty @gen, 'CustomKeyInfoXml', @sTmp0

    -- Load XML to be signed...
    DECLARE @sbXml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbXml OUT

    EXEC sp_OAMethod @xmlToSign, 'GetXmlSb', @success OUT, @sbXml

    EXEC sp_OASetProperty @gen, 'Behaviors', 'IndentedSignature'

    -- Sign the XML...
    EXEC sp_OAMethod @gen, 'CreateXmlDSigSb', @success OUT, @sbXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @gen, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @xmlToSign
        EXEC @hr = sp_OADestroy @bdCert
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @xmlCustomKeyInfo
        EXEC @hr = sp_OADestroy @sbXml
        RETURN
      END
    -- -----------------------------------------------
    -- Send the signed XML...
    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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 @xmlToSign
        EXEC @hr = sp_OADestroy @bdCert
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @xmlCustomKeyInfo
        EXEC @hr = sp_OADestroy @sbXml
        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 @sbXml, 'GetAsString', @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 @xmlToSign
        EXEC @hr = sp_OADestroy @bdCert
        EXEC @hr = sp_OADestroy @dt
        EXEC @hr = sp_OADestroy @gen
        EXEC @hr = sp_OADestroy @xmlCustomKeyInfo
        EXEC @hr = sp_OADestroy @sbXml
        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 @xmlToSign
    EXEC @hr = sp_OADestroy @bdCert
    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @gen
    EXEC @hr = sp_OADestroy @xmlCustomKeyInfo
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @http


END
GO