?

二进制数据包的解析一般是借助C#等语言，在通讯程序中解析后形成字段，再统一单笔或者批量（表类型参数）提交至数据库，在通讯程序中，存在BINARY到struct再到table的转换。

现借助CLR提供基本的INT2HEX(小端)等函数，在SQL中直接解析数据包。

?

基本函数

1. [Microsoft.SqlServer.Server.SqlFunction(Name = "Time2UTCBin")]
2. public static SqlBinary Time2UTCBin(DateTime time)
3. {
4. ????return new SqlBinary(BitConverter.GetBytes((uint)(MyTime.ConverDateTimeToJavaMilliSecond(time) / 1000)));
5. }
6. [Microsoft.SqlServer.Server.SqlFunction(Name = "UTCBin2Time")]
7. public static SqlDateTime UTCBin2Time(byte[] data,int offset)
8. {
9. ????return new SqlDateTime(MyTime.ConverDateTimeFromJavaMilliSecond(BitConverter.ToUInt32(data, offset) * 1000L));
10. }
11. ?
12. [Microsoft.SqlServer.Server.SqlFunction(Name = "getSum")]
13. public static SqlByte Sum(byte[] buffer, int startPos, int endPos)
14. {
15. ????byte b = 0;
16. ????for (int i = startPos; i <= endPos; i++)
17. ????{
18. ????????b ^= buffer[i];
19. ????}
20. ????return b;
21. }
22. ?
23. [Microsoft.SqlServer.Server.SqlFunction(Name = "updateSum")]
24. public static SqlBinary updateSum(byte[] buffer, int startPos, int endPos, int sumPos)
25. {
26. ????byte b = 0;
27. ????for (int i = startPos; i <= endPos; i++)
28. ????{
29. ????????b ^= buffer[i];
30. ????}
31. ????buffer[sumPos] = b;
32. ????return new SqlBinary(buffer);
33. }
34. ?
35. ?
36. [Microsoft.SqlServer.Server.SqlFunction(Name = "Int2Bin")]
37. public static SqlBinary Int2Bin(int number)
38. {
39. ????return new SqlBinary(BitConverter.GetBytes(number));
40. }
41. ?
42. [Microsoft.SqlServer.Server.SqlFunction(Name = "Long2Bin")]
43. public static SqlBinary Long2Bin(long number)
44. {
45. ????return new SqlBinary(BitConverter.GetBytes(number));
46. }
47. [Microsoft.SqlServer.Server.SqlFunction(Name = "Bin2Int")]
48. public static SqlInt32 Bin2Int(byte[] data, int offset)
49. {
50. ????return new SqlInt32(BitConverter.ToInt32(data,offset));
51. }
52. [Microsoft.SqlServer.Server.SqlFunction(Name = "Bin2Long")]
53. public static SqlInt64 Bin2Long(byte[] data, int offset)
54. {
55. ????return new SqlInt64(BitConverter.ToInt64(data, offset));
56. }
57. [Microsoft.SqlServer.Server.SqlFunction(Name = "getByte")]
58. public static SqlByte getByte(byte[] data, int offset)
59. {
60. ????return new SqlByte(data[offset]);
61. }
62. [Microsoft.SqlServer.Server.SqlFunction(Name = "getBytes")]
63. public static SqlBytes getBytes(byte[] data, int offset,int count)
64. {
65. ????byte[] temp = new byte[count];
66. ????Array.Copy(data, offset, temp, 0, count);
67. ????return new SqlBytes(temp);
68. }

?

?

数据包的结构体（表类型）

1. --交易扩展记录
2. CREATE TYPE [dbo].[DeviceTranscationEMV] AS TABLE
3. (
4. [RawData] VARBINARY(200) NOT NULL,
5. [DeviceID] UNIQUEIDENTIFIER NOT NULL,
6. [EMVType] TINYINT NOT NULL,
7. [EMVNO] SMALLINT NOT NULL,
8. [HardwareNo] INT NOT NULL,
9. Meter INT,
10. run INT,
11. WORK INT,
12. dead INT,
13. StartTime DATETIME ,
14. EndTime DATETIME
15. )

?

?

SQL中借助CLR实现的转换函数

?

1. CREATE FUNCTION [dbo].[Binary2EMVTaxi]
2. ????(
3. ??????@data VARBINARY(MAX) ,
4. ??????@offset INT = 1 ,
5. ??????@withDeviceID BIT = 0
6. ????)
7. RETURNS @emv TABLE
8. ????(
9. ??????[RawData] BINARY(72) NOT NULL ,
10. ??????[DeviceID] UNIQUEIDENTIFIER ,
11. ??????[EMVType] TINYINT NOT NULL ,
12. ??????[EMVNO] SMALLINT NOT NULL ,
13. ??????[HardwareNo] INT NOT NULL ,
14. ??????Meter INT ,
15. ??????run INT ,
16. ??????WORK INT ,
17. ??????dead INT ,
18. ??????StartTime DATETIME ,
19. ??????EndTime DATETIME
20. ????)
21. AS
22. ????BEGIN
23. ????????DECLARE @c INT
24. ????????DECLARE @size INT
25. ????????DECLARE @sized INT
26. ?
27. ????????IF @withdeviceid = 1
28. ????????????BEGIN
29. ????????????????SET @sized = 16
30. ????????????????SET @size = 72 + @sized
31. ????????????END
32. ????????ELSE
33. ????????????BEGIN
34. ????????????????SET @sized = 0
35. ????????????????SET @size = 72
36. ????????????END
37. ?
38. ????????SET @c = DATALENGTH(@data) / @size;
39. ?
40. ????????WITH sub
41. ??????????????????AS ( SELECT SUBSTRING(@data, 1 + id * @size + @offset,
42. ??????????????????????????????????????????@size) binDATA
43. ???????????????????????FROM sys_id
44. ???????????????????????WHERE id < @c
45. ?????????????????????)
46. ????????????INSERT @emv
47. ????????????????????( rawdata ,
48. ??????????????????????DeviceID ,
49. ??????????????????????hardwareno ,
50. ??????????????????????meter ,
51. ??????????????????????run ,
52. ??????????????????????WORK ,
53. ??????????????????????dead ,
54. ??????????????????????starttime ,
55. ??????????????????????endtime ,
56. ??????????????????????emvtype ,
57. ??????????????????????emvno
58. ????????????????????)
59. ????????????????????SELECT CASE @withDeviceID
60. ??????????????????????????????WHEN 0 THEN bindata
61. ??????????????????????????????ELSE SUBSTRING(bindata, 1 + @sized, @size)
62. ????????????????????????????END ,
63. ????????????????????????????CASE @withDeviceID
64. ??????????????????????????????WHEN 0 THEN NULL
65. ??????????????????????????????ELSE CAST (SUBSTRING(bindata, 1, @sized) AS UNIQUEIDENTIFIER)
66. ????????????????????????????END ,
67. ????????????????????????????dbo.Bin2Int(bindata, 0 + @sized) RecordNo ,
68. ????????????????????????????dbo.Bin2Int(bindata, 4 + @sized) Meter ,
69. ????????????????????????????dbo.Bin2Int(bindata, 8 + @sized) run ,
70. ????????????????????????????dbo.Bin2Int(bindata, 12 + @sized) WORK ,
71. ????????????????????????????dbo.Bin2Int(bindata, 16 + @sized) dead ,
72. ????????????????????????????dbo.utcbin2time(bindata, 20 + @sized) StartTime ,
73. ????????????????????????????dbo.utcbin2time(bindata, 24 + @sized) EndTime ,
74. ????????????????????????????dbo.getByte(bindata, 61 + @sized) RecordType ,
75. ????????????????????????????dbo.getByte(bindata, 62 + @sized) EMVNo
76. ????????????????????FROM sub
77. ????????RETURN
78. ????END

?

1. CREATE FUNCTION [dbo].[EMVTaxi2Binary]
2. ????(
3. ??????@emv DeviceTranscationEMV READONLY ,
4. ??????@withDeviceID BIT = 0
5. ????)
6. RETURNS VARBINARY(MAX)
7. AS
8. ????BEGIN
9. ????????DECLARE @bin VARBINARY(MAX)
10. ????????SET @bin = 0x0
11. ????????IF @withDeviceID = 0
12. ????????????SELECT @bin = @bin + dbo.updatesum(dbo.int2Bin([HardwareNo])
13. ????????????????????????????????????????????????+ dbo.int2Bin(meter)
14. ????????????????????????????????????????????????+ dbo.int2Bin(run)
15. ????????????????????????????????????????????????+ dbo.int2Bin(work)
16. ????????????????????????????????????????????????+ dbo.int2Bin(dead)
17. ????????????????????????????????????????????????+ dbo.time2utcbin(starttime)
18. ????????????????????????????????????????????????+ dbo.time2utcbin(endtime)
19. ????????????????????????????????????????????????+ CAST(0 AS BINARY(33))
20. ????????????????????????????????????????????????+ CAST(emvtype AS BINARY(1))
21. ????????????????????????????????????????????????+ CAST(emvno AS BINARY(1))
22. ????????????????????????????????????????????????+ CAST (0 AS BINARY(1))
23. ????????????????????????????????????????????????+ CAST(0 AS BINARY(7)) + 0x55,
24. ????????????????????????????????????????????????0, 62, 63)
25. ????????????FROM @emv
26. ?
27. ????????ELSE
28. ????????????SELECT @bin = @bin + CAST (deviceid AS BINARY(16))
29. ????????????????????+ dbo.updatesum(dbo.int2Bin([HardwareNo])
30. ????????????????????????????????????+ dbo.int2Bin(meter) + dbo.int2Bin(run)
31. ????????????????????????????????????+ dbo.int2Bin(work) + dbo.int2Bin(dead)
32. ????????????????????????????????????+ dbo.time2utcbin(starttime)
33. ????????????????????????????????????+ dbo.time2utcbin(endtime)
34. ????????????????????????????????????+ CAST(0 AS BINARY(33))
35. ????????????????????????????????????+ CAST(emvtype AS BINARY(1))
36. ????????????????????????????????????+ CAST(emvno AS BINARY(1))
37. ????????????????????????????????????+ CAST (0 AS BINARY(1))
38. ????????????????????????????????????+ CAST(0 AS BINARY(7)) + 0x55, 0, 62, 63)
39. ????????????FROM @emv
40. ?
41. ????????RETURN @bin
42. ????END

?

?

测试代码

?

1. PRINT N‘构造EMV数据，转换为BIN，然后再转换回EMV数据‘
2. go
3. DECLARE @emv DeviceTranscationEMV
4. WITH data
5. ??????????AS ( SELECT id RecordNo ,
6. ????????????????????????15 Meter ,
7. ????????????????????????100 run ,
8. ????????????????????????80 WORK ,
9. ????????????????????????20 dead ,
10. ????????????????????????DATEADD(s, id, ‘2014-9-1 12:50:01‘) StartTime ,
11. ????????????????????????DATEADD(mi, id, ‘2014-9-1 13:23:11‘) EndTime ,
12. ????????????????????????4 RecordType ,
13. ????????????????????????0 EMVNo
14. ???????????????FROM dbo.Sys_ID
15. ???????????????WHERE id < 100
16. ?????????????)
17. ????INSERT INTO @emv
18. ????????????( rawdata ,
19. ??????????????deviceid ,
20. ??????????????emvtype ,
21. ??????????????emvno ,
22. ??????????????hardwareno ,
23. ??????????????meter ,
24. ??????????????run ,
25. ??????????????work ,
26. ??????????????dead ,
27. ??????????????starttime ,
28. ??????????????endtime
29. ????????????)
30. ????????????SELECT 0x00 ,
31. ????????????????????NEWID() ,
32. ????????????????????recordtype ,
33. ????????????????????emvno ,
34. ????????????????????recordno ,
35. ????????????????????meter ,
36. ????????????????????run ,
37. ????????????????????work ,
38. ????????????????????dead ,
39. ????????????????????starttime ,
40. ????????????????????EndTime
41. ????????????FROM data
42. SELECT *
43. FROM @emv
44. DECLARE @data VARBINARY(MAX)
45. ?
46. SELECT @data = dbo.emvtaxi2binary(@emv, 1)
47. PRINT @data
48. ?
49. ?
50. SELECT *
51. FROM dbo.binary2emvtaxi(@data, 1, 1)

?

?

执行结果：

?

?

?

?

资源：

?

?

?

?

在SQL中使用CLR提供基本函数对二进制数据进行解析与构造