Issue
I'm working on MS-TDS protocol, what I'm trying todo is to read/write TDS stream, and get/set a System.Data.DataTable
from/to it.
I got all C# types working already, except decimal
varchar(max)
varbinary(max)
columns
in C#, I got following results for 12345678912345678912.3456789m
and 1234567891234567891.23456789m
var bits = decimal.GetBits(12345678912345678912.3456789m)
using (var ms = new System.IO.MemoryStream())
{
foreach (var b in bits)
ms.Write(BitConverter.GetBytes(b), 0, 4);
var decimalBytes = ms.ToArray();
}
10 15 5F 04 7C 9F B1 E3 F2 FD 1E 66 00 00 00 07 00
10 15 5F 04 7C 9F B1 E3 F2 FD 1E 66 00 00 00 08 00
these totally make sense to me, the last byte is decimal size(position), which is 7 and 8, the value part is the same. but in sql, they look completely different:
SELECT CAST(CAST(12345678912345678912.3456789 AS decimal(38,15)) AS varbinary)
SELECT CAST(CAST(1234567891234567891.23456789 AS decimal(38,15)) AS varbinary)
results:
0x260F 0001 0075 AA3F 0AF2 2A3A DB18 0560 B060 0200
0x260F 0001 80D8 5D06 01E5 9D52 7C82 0070 DE3C 0000
and in TDS stream DataType=[0x6A,0x11,0x26,0x0F] decimal(38,15)
, sql server returns this:
0x81, // token "COLMETADATA"
0x03, 0x00, 0x00, 0x00, // 3 columns
0x00, 0x00, 0x00, 0x00, 0x09, 0x00, // usertype = 0, falgs = 9
0xa7, 0xff, 0xff, 0x09, 0x04, 0xd0, 0x00, 0x34, // 0xA7=BIGVARCHRTYPE 0xffff=MAX,
0x0e, 0x56, 0x00, 0x41, 0x00, 0x52, 0x00, 0x43, 0x00, 0x48, 0x00, 0x41, 0x00, 0x52, 0x00, 0x4d, 0x00, 0x41, 0x00, 0x58, 0x00, 0x5f, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6c, 0x00, // unicode "VARCHARMAX_Col"
0x00, 0x00, 0x00, 0x00, 0x09, 0x00, // usertype = 0, falgs = 9
0x6a, 0x11, 0x26, 0x0f, // 0x6A=DECIMAL 0x11=Size, precision = (38,15)
0x0b, 0x44, 0x00, 0x45, 0x00, 0x43, 0x00, 0x49, 0x00, 0x4d, 0x00, 0x41, 0x00, 0x4c, 0x00, 0x5f, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6c, 0x00, // unicode "DECIMAL_Col"
0x00, 0x00, 0x00, 0x00, 0x09, 0x00, // usertype = 0, falgs = 9
0xa5, 0xff, 0xff, // 0xA5=BIGVARBINTYPE 0xffff=MAX
0x0d, 0x56, 0x00, 0x41, 0x00, 0x52, 0x00, 0x42, 0x00, 0x49, 0x00, 0x4e, 0x00, 0x4d, 0x00, 0x41, 0x00, 0x58, 0x00, 0x5f, 0x00, 0x43, 0x00, 0x6f, 0x00, 0x6c, 0x00, // unicode "VARBINMAX_Col"
0xd1, // token "ROW"
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x41, 0x41, 0x41, 0x41, 0x42, 0x00, 0x00, 0x00, 0x00, // varchar(max) value, how to read this ?
0x11, 0x01, 0x00, 0x92, 0xa8, 0x7c, 0x7e, 0xe4, 0x25, 0x1a, 0x0e, 0xab, 0x6b, 0x4d, 0x82, 0x84, 0x04, 0x00, // decimal value, how to read this ?
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x61, 0x61, 0x61, 0x61, 0x62, 0x00, 0x00, 0x00, 0x00, // varbinary(max) value, how to read this ?
0xd1, // token "ROW"
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x41, 0x41, 0x41, 0x41, 0x43, 0x00, 0x00, 0x00, 0x00,
0x11, 0x01, 0x00, 0x75, 0xaa, 0x3f, 0xa6, 0x63, 0x9d, 0x02, 0x1b, 0x91, 0x57, 0xa1, 0xa6, 0x73, 0x00, 0x00,
0x05, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x05, 0x00, 0x00, 0x00, 0x61, 0x61, 0x61, 0x61, 0x63, 0x00, 0x00, 0x00, 0x00,
0xd2, // token "NBCROW"
0x05, // NullBitmap 00000101: 1st and 3rd columns are null
0x11, 0x01, 0x50, 0x24, 0xc2, 0x2a, 0xf2, 0x77, 0xd5, 0x97, 0x0f, 0x03, 0xee, 0xf5, 0x02, 0x00, 0x00, 0x00,
decimal value in the 1st row is
0x11, 0x01, 0x00, 0x92, 0xa8, 0x7c, 0x7e, 0xe4, 0x25, 0x1a, 0x0e, 0xab, 0x6b, 0x4d, 0x82, 0x84, 0x04, 0x00,
0x11
should be length, 0x01
means positive value (from this post How does SQL Server store decimal type values internally?)
Question: how can I resolving these bytes to a decimal value? and how should I write varchar(max)
varbinary(max)
value to TDS ROW TokenStream
? Any help is appreciated
Solution
I figured it out. the difference is TDS stream
use the first byte as sign and use fixed Decimal point, decimal.GetBits
use few bits in last byte as sign and Decimal point
// write decimal to TDS stream
static byte[] DecimalBytes(decimal dec, int precision = 15)
{
var round = decimal.Round(dec, precision);
var valueToWrite = round;
var sign = round < 0 ? (byte)0x00 : (byte)0x01;
// get the string
var str = round.ToString();
// string without Decimal point
var numbers = str.Replace(".", string.Empty);
var dotIdx = str.IndexOf('.');
if (dotIdx > 0)
{
// there must be {precision} digits on the right side of "."
var padding = precision - (numbers.Length - dotIdx);
// padding numbers with '0' to precision length
numbers = numbers.PadRight(numbers.Length + padding, '0');
}
else
{
numbers = numbers.PadRight(numbers.Length + precision, '0');
}
if (!decimal.TryParse(numbers, out valueToWrite))
{
throw new ArgumentOutOfRangeException($"Invalid decimal value for Database Type decimal(38,{precision})");
}
using (var ms = new System.IO.MemoryStream())
{
var bits = decimal.GetBits(valueToWrite);
ms.WriteByte(0x0D); // length=13
ms.WriteByte(sign);
ms.Write(BitConverter.GetBytes(bits[0]), 0, 4);
ms.Write(BitConverter.GetBytes(bits[1]), 0, 4);
ms.Write(BitConverter.GetBytes(bits[2]), 0, 4);
return ms.ToArray();
}
}
Answered By - Leowan Answer Checked By - Marilyn (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.