2

Preambule

I have MSSQL 2016 instance with CDC enabled. And I stream changes by Debezium kafka connector. Connector in logs and topics refer Log Sequence Number (LSN) in form of 000000aa:af9c3f2d:0f5e1. In MSSQL I have tables like cdc.dbo_Entity_cdc_CT where present columns __$start_lsn, __$start_lsn, __$end_lsn but it has type varbinary(10).

I would match values.

Based mostly on information in Day 11 of 31 Days of Disaster: Converting LSN Formats article which give hint it should be converted in chain convert to binary style 1 -> bigint, I wrote helper functions for that: https://github.com/Hubbitus/sql/commit/4ccf96492f2aba9796ccaecb1b74e9ba53a45895#diff-e495aa591f98a29c28153735379ad6fd

It mostly works. Contains descriptions and even testing conversion doing right.

But there is one problem I can't solve now.

8 byte part exceeded bigint

When I convert input varbinary(16) value, I split it into two parts: 2 and 8 bytes length, each convert into bigint, and then into varchar, f.e.:

DECLARE @lsn VARBINARY(10) ...
-- Fill by \0 at left
SET @lsn = CONVERT(varbinary, REPLICATE(char(0), 10 - LEN(@lsn))) + @lsn
DECLARE @lsn_s varchar(max) = CONVERT(varchar, CONVERT(bigint, SUBSTRING(@lsn, 1, 2), 1)) + CONVERT(varchar, CONVERT(bigint, SUBSTRING(@lsn, 3, 8), 1))

It works in most cases (f.e. for test table I see only 4 records with error), f.e. for value like:

DECLARE @lsn varbinary(10) = CONVERT(varbinary(10), '0x025D020F35B80001', 1)

You may check it like:

SELECT dbo.__tmp_td_cdc_decode_check(CONVERT(varbinary(10), '0x025D020F35B80001', 1))

But fails, said on:

DECLARE @lsn varbinary(10) = CONVERT(varbinary(10), '0x0043275D010200440020', 1)
SELECT dbo.__tmp_td_cdc_decode_check(@sql)

Indeed, MSSQL have not unsigned datatypes.

The main question

I do not understand how to work around this problem in line:

DECLARE @lsn_s varchar(max) = CONVERT(varchar, CONVERT(bigint, SUBSTRING(@lsn, 1, 2), 1)) + CONVERT(varchar, CONVERT(bigint, SUBSTRING(@lsn, 3, 8), 1))

How I should correctly transform varbinary(10) into varchar in form of decimal digits for any cases?

What I also do not understand, sometimes I got error "data truncation" on conversions, but sometimes it happened silently. Please look:

DECLARE @lsn varbinary(10) = CONVERT(varbinary(10), '0x0043275D010200440020', 1) -- Bigint overflow
SELECT
    dbo.__tmp_td_cdc_decode_check(@lsn) as ToCheck
    ,CONVERT(varchar, @lsn, 1) as orig_lsn_hex
    ,CONVERT(bigint, @lsn, 1) as orig_lsn_bigint -- In many cases it will lead overflow!
    ,dbo.__tmp_td_cdc_binary_decode_to_string(@lsn) as string
    ,dbo.__tmp_td_cdc_string_decode_to_binary_DECSTRING(dbo.__tmp_td_cdc_binary_decode_to_string(@lsn)) as decimal_string_reverse

Output:

┌─────────┬────────────────────────┬─────────────────────┬─────────────────────────┬────────────────────────┐
│ ToCheck │      orig_lsn_hex      │   orig_lsn_bigint   │         string          │ decimal_string_reverse │
├─────────┼────────────────────────┼─────────────────────┼─────────────────────────┼────────────────────────┤
│ Error   │ 0x0043275D010200440020 │ 2836424448419299360 │ 000012e6:2f707ba4:02580 │ 4838079590083609600    │
└─────────┴────────────────────────┴─────────────────────┴─────────────────────────┴────────────────────────┘

Hexadecimal value 0x0043275D010200440020 should be 1_238_768_277_386_959_257_632 and not 2_836_424_448_419_299_360 what I get from CONVERT(bigint, @lsn, 1)

P.S. Also interesting and helpful materials:

Improve this question
6
  • DECLARE @lsn varbinary(10) = CONVERT(varbinary(10), '0x0043275D010200440020', 1) can't generate an Overflow error for a bigint; there are no bigints in that statement. Also, in some of your code you're declaring a varchar without a length; which is a really bad idea. Commented Dec 25, 2019 at 23:21
  • That hex value, however, as a bigint would be 1,238,768,277,386,959,257,632. The largest number you can store in a bigint is 9,223,372,036,854,775,807. So yes, your hex values is over 100 times too large. Commented Dec 25, 2019 at 23:30
  • The largest value you could store in a varbinary(10) would be 0xFFFFFFFFFFFFFFFFFFFF, which as an integer would be 1,208,925,819,614,629,174,706,175. If you do need to store values as large as that, yours going to need a decimal(25,0). Commented Dec 25, 2019 at 23:36
  • Actually, if summarize, question is: how to properly convert number, said represent by decimal string 4838079590083609600 into binary(10)? My variant works almost all times, but in rare cases there bigint overflow happened (and conversion silently incorrect). Commented Dec 26, 2019 at 8:50
  • Like I said, 0x0043275D010200440020 is too big for a bigint, you'll need to use a different datatype like decimal. However, binary values of a decimal are not the same as a int. The decimal 1238768277386959257632 as a binary(10), for example, is 0x19000001200044000201. Commented Dec 26, 2019 at 11:25

1 Answer 1

Reset to default
0

If you must convert the value like you have using "normal" hexidecimal values, you'll need to be a bit more "clever". This is one idea, using a tally:

USE Sandbox;
GO

SELECT V.YourHex,
       SUM(CONVERT(decimal(25,0),CASE SS.Char WHEN 'A' THEN 10
                                              WHEN 'B' THEN 11
                                              WHEN 'C' THEN 12
                                              WHEN 'D' THEN 13
                                              WHEN 'R' THEN 14
                                              WHEN 'F' THEN 15
                                              ELSE CONVERT(int,SS.Char)
                                 END) * POWER(CONVERT(decimal(25,0),16),20-pos)) AS YourInt
FROM (VALUES(0x0043275D010200440020))V(YourHex)
     CROSS APPLY (VALUES(CONVERT(varchar(20),V.YourHex,2)))C(YourString)
     CROSS APPLY (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20))I(I)
     CROSS APPLY (VALUES(SUBSTRING(C.YourString,I.I, 1),I.I))SS(Char, Pos)
GROUP BY V.YourHex;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I want not int as result. I have that value and provide examples. I need from my int (1238768277386959275408 in you example) reconstruct varbinary(10) value which will match to the source
You don't explain what your after well at all here @Hubbitus. I've told you the why, I've told you the how. If neither of those answer your question, the. Your question isn't the real question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.