(SQL Server) hotelbeds.com REST API Authentication

Demonstrates how to calculate the X-Signature header and add to requests sent to api.test.hotelbeds.com.

For more information, see https://developer.hotelbeds.com/documentation/getting-started/

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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example duplicates the following CURL statement provided in the hotelbeds.com "Getting Started"
    -- developer documentation:

    -- #!/bin/bash 
    -- apiKey="yourApiKey"
    -- secret="yourSecret"
    -- curl -i \
    --   -X GET \
    --   -H 'Accept:application/json' \
    --   -H 'Api-key:'$apiKey'' \
    --   -H 'X-Signature:'$(echo -n ${apiKey}${secret}$(date +%s)|sha256sum|awk '{ print $1}')'' \
    --   https://api.test.hotelbeds.com/hotel-api/1.0/status

    DECLARE @success int

    -- The "date + %s" command emits a UNIX date/time number such as 1605548842
    -- We need to concatenate the apiKey, secret, and current date/time in UNIX (numeric) time format.
    DECLARE @dt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.CkDateTime', @dt OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
    -- Get the UNIX time string in the GMT timezone.
    DECLARE @bLocalTime int
    SELECT @bLocalTime = 0
    DECLARE @unixTimeStr nvarchar(4000)
    EXEC sp_OAMethod @dt, 'GetAsUnixTimeStr', @unixTimeStr OUT, @bLocalTime

    PRINT @unixTimeStr

    -- Use your own API key and secret.  (These are not valid values)
    DECLARE @apiKey nvarchar(4000)
    SELECT @apiKey = '227b20a2cb705e45f3b0a0944672dc07'
    DECLARE @secret nvarchar(4000)
    SELECT @secret = '54dc7ba9e2'

    DECLARE @sb int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sb OUT

    EXEC sp_OAMethod @sb, 'Append', @success OUT, @apiKey
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @secret
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @unixTimeStr

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hexlower'
    DECLARE @signature nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'HashStringENC', @signature OUT, @sTmp0


    PRINT @signature

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT

    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Accept', 'application/json'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Api-key', @apiKey
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'X-Signature', @signature

    DECLARE @jsonResponse nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @jsonResponse OUT, 'https://api.test.hotelbeds.com/hotel-api/1.0/status'

    PRINT @jsonResponse
    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    PRINT @iTmp0

    -- Sample output:  

    -- 1605549542
    -- 2146aefb36e7331b3b29aafd7638398ca22e689573224f1a71274d60bd201ec2
    -- {"auditData":{"timestamp":"2020-11-16 18:59:02.430"},"status":"OK"}
    -- 200

    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @http


END
GO