SQL Server
SQL Server
Hungary NAV Invoicing Signature Calculation
See more Hungary NAV Invoicing Examples
Demonstrates the requestSignature calculation for the Hungarian NAV Online Invoicing System REST API v2.0.Note: This example requires Chilkat v9.5.0.83 or greater.
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
-- 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.
-- This example performs the following calculation:
-- 1.5.1 Calculations for manageInvoice and manageAnnulment operations
--
-- For the manageInvoice and manageAnnulment operations comprising the core of requestSignature,
-- requestSignature is calculated using partial authentication, concatenating the hash values for
-- indices 1-100 as well as additional SHA3-512 hash operations. The partial authentication can be
-- derived by concatenating the following values:
--
-- - requestId value
-- - UTC timestamp tag value using a YYYYMMDDhhmmss mask
-- - string literal of the technical user�s signature key
--
-- When concatenating, the date and time separators as well as the time zone must be removed for timestamp masking.
--
-- The index hash value can be derived from the uppercase SHA3-512 hash values following the concatenation of the
-- operation and base64 content for the individual indices:
--
-- - literal value of invoiceOperation or annulmentOperation
-- - base64 content in the invoiceData or invoiceAnnulment tag
--
-- The calculated hash values should be appended after the partial hash, in the order corresponding to the indices.
-- The SHA3-512 hash result (capitals) of the string thus concatenated yields the requestSignature value.
--
-- A fictitious example for request data:
--
-- - requestId = TSTKFT1222564
-- - timestamp = 2017-12-30T18:25:45.000Z
-- - technical user�s signature key = ce-8f5e-215119fa7dd621DLMRHRLH2S
-- - the index#1 invoice data items
-- - invoiceOperation = CREATE
-- - invoiceData = QWJjZDEyMzQ=
-- - the index#2 invoice data items
-- - invoiceOperation = MODIFY
-- - invoiceData = RGNiYTQzMjE=
-- - partial authentication value = TSTKFT122256420171230182545ce-8f5e-215119fa7dd621DLMRHRLH2S
-- - the first index hash =
-- - hash base = CREATEQWJjZDEyMzQ=
-- - lowercase hash = 4317798460962869bc67f07c48ea7e4a3afa301513ceb87b8eb94ecf92bc220a89c480f87f0860e85e29a3b6c0463d4f29712c5ad48104a6486ce839dc2f24cb
-- - uppercase hash = 4317798460962869BC67F07C48EA7E4A3AFA301513CEB87B8EB94ECF92BC220A89C480F87F0860E85E29A3B6C0463D4F29712C5AD48104A6486CE839DC2F24CB
-- - the second index hash =
-- - hash base = MODIFYRGNiYTQzMjE=
-- - lowercase hash = a881218238933f6ffb9e167445cb4daa9749bcf484fde48ab7649fd25e8b634a4736a65a7c4a8e2831119f739837e006566f97370415aad55e268605206f2a6c
-- - uppercase hash = A881218238933F6FFB9E167445CB4DAA9749BCF484FDE48AB7649FD25E8B634A4736A65A7C4A8E2831119F739837E006566F97370415AAD55E268605206F2A6C
--
-- Thus, the base for the entire requestSignature is:
-- TSTKFT122256420171230182545ce-8f5e-215119fa7dd621DLMRHRLH2S4317798460962869BC67F07C48EA7E4A3AFA301513CEB87B8EB94ECF92BC220A89C480F87F0860E85E29A3B6C0463D4F29712C5AD48104A6486CE839DC2F24CBA881218238933F6FFB9E167445CB4DAA9749BCF484FDE48AB7649FD25E8B634A4736A65A7C4A8E2831119F739837E006566F97370415AAD55E268605206F2A6C
--
-- Value of requestSignature after SHA3-512 hashing and converting to uppercase:
-- 60BC80609EE3B8F42FE904200A49A1921A1DADA08D55319ACD40C59F626514B74EEA49011D372600A10DBCF8199D590DA9C2841D987308F2D83DAE17C2470C42
--
-- -------------------
DECLARE @crypt int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater. The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
-- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater. The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
-- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater. The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
-- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater. The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
-- IMPORTANT: This example requires Chilkat v9.5.0.83 or greater. The SHA3 hash algorithm is implemented in Chilkat v9.5.0.83, but not in earlier versions.
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha3-512'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'
DECLARE @requestId nvarchar(4000)
SELECT @requestId = 'TSTKFT1222564'
DECLARE @timestamp nvarchar(4000)
SELECT @timestamp = '2017-12-30T18:25:45.000Z'
DECLARE @signatureKey nvarchar(4000)
SELECT @signatureKey = 'ce-8f5e-215119fa7dd621DLMRHRLH2S'
-- Step 1: Concatenate requestId, UTC timestamp, and user's signature key.
DECLARE @sbFinalHashBase int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbFinalHashBase OUT
-- First append the timestamp because we are going to remove certain chars/parts.
EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @timestamp
DECLARE @numReplaced int
EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, '.000Z', ''
EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, '-', ''
EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, ':', ''
EXEC sp_OAMethod @sbFinalHashBase, 'Replace', @numReplaced OUT, 'T', ''
-- Prepend the requestId and append the signatureKey
EXEC sp_OAMethod @sbFinalHashBase, 'Prepend', @success OUT, @requestId
EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @signatureKey
-- Step 2: Calculate first index hash.
DECLARE @invoiceOperation nvarchar(4000)
SELECT @invoiceOperation = 'CREATE'
DECLARE @invoiceData nvarchar(4000)
SELECT @invoiceData = 'QWJjZDEyMzQ='
DECLARE @sbHashBase int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbHashBase OUT
EXEC sp_OAMethod @sbHashBase, 'Append', @success OUT, @invoiceOperation
EXEC sp_OAMethod @sbHashBase, 'Append', @success OUT, @invoiceData
EXEC sp_OAMethod @sbHashBase, 'GetAsString', @sTmp0 OUT
PRINT 'first hash base = ' + @sTmp0
DECLARE @firstHash nvarchar(4000)
EXEC sp_OAMethod @sbHashBase, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @crypt, 'HashStringENC', @firstHash OUT, @sTmp0
PRINT 'first hash = ' + @firstHash
-- Output should be:
-- first hash base = CREATEQWJjZDEyMzQ=
-- first hash = 4317798460962869BC67F07C48EA7E4A3AFA301513CEB87B8EB94ECF92BC220A89C480F87F0860E85E29A3B6C0463D4F29712C5AD48104A6486CE839DC2F24CB
-- Step 3: Calculate second index hash.
SELECT @invoiceOperation = 'MODIFY'
SELECT @invoiceData = 'RGNiYTQzMjE='
EXEC sp_OAMethod @sbHashBase, 'Clear', NULL
EXEC sp_OAMethod @sbHashBase, 'Append', @success OUT, @invoiceOperation
EXEC sp_OAMethod @sbHashBase, 'Append', @success OUT, @invoiceData
EXEC sp_OAMethod @sbHashBase, 'GetAsString', @sTmp0 OUT
PRINT 'second hash base = ' + @sTmp0
DECLARE @secondHash nvarchar(4000)
EXEC sp_OAMethod @sbHashBase, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @crypt, 'HashStringENC', @secondHash OUT, @sTmp0
PRINT 'second hash = ' + @secondHash
-- Output should be:
-- second hash base = MODIFYRGNiYTQzMjE=
-- second hash = A881218238933F6FFB9E167445CB4DAA9749BCF484FDE48AB7649FD25E8B634A4736A65A7C4A8E2831119F739837E006566F97370415AAD55E268605206F2A6C
-- Step 4: Append the uppercase hex hashes to the sbFinalHashBase
EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @firstHash
EXEC sp_OAMethod @sbFinalHashBase, 'Append', @success OUT, @secondHash
EXEC sp_OAMethod @sbFinalHashBase, 'GetAsString', @sTmp0 OUT
PRINT 'sbFinalHashBase = ' + @sTmp0
-- Step 5: SHA3-512 hash to get the requestSignature
DECLARE @requestSignature nvarchar(4000)
EXEC sp_OAMethod @sbFinalHashBase, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @crypt, 'HashStringENC', @requestSignature OUT, @sTmp0
PRINT 'requestSignature = ' + @requestSignature
-- Output should be:
-- requestSignature = 60BC80609EE3B8F42FE904200A49A1921A1DADA08D55319ACD40C59F626514B74EEA49011D372600A10DBCF8199D590DA9C2841D987308F2D83DAE17C2470C42
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @sbFinalHashBase
EXEC @hr = sp_OADestroy @sbHashBase
END
GO