SQL Server
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.Chilkat SQL Server Downloads
-- 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)
DECLARE @success int
SELECT @success = 0
-- 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.
-- --------------------------------------------------------------------------------
-- 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.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 = 0
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
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
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
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
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OAMethod @http, 'SetSslClientCert', @success OUT, @cert
IF @success = 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_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'text/xml'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'SOAPAction', 'urn:be:fgov:ehealth:addressbook:protocol:v1:searchProfessionals'
DECLARE @xmlStr nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetXml', @xmlStr OUT
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', 'https://services.ehealth.fgov.be/AddressBook/v1', @xmlStr, 'utf-8', 'application/xml', @resp
IF @success = 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
EXEC @hr = sp_OADestroy @resp
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 @cert
EXEC @hr = sp_OADestroy @bdSecToken
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
END
GO