二进制数据包的解析一般是借助C#等语言,在通讯程序中解析后形成字段,再统一单笔或者批量(表类型参数)提交至数据库,在通讯程序中,存在BINARY到struct再到table的转换。
现借助CLR提供基本的INT2HEX(小端)等函数,在SQL中直接解析数据包。
基本函数
- [Microsoft.SqlServer.Server.SqlFunction(Name = "Time2UTCBin")]
- public static SqlBinary Time2UTCBin(DateTime time)
- {
- return new SqlBinary(BitConverter.GetBytes((uint)(MyTime.ConverDateTimeToJavaMilliSecond(time) / 1000)));
- }
- [Microsoft.SqlServer.Server.SqlFunction(Name = "UTCBin2Time")]
- public static SqlDateTime UTCBin2Time(byte[] data,int offset)
- {
- return new SqlDateTime(MyTime.ConverDateTimeFromJavaMilliSecond(BitConverter.ToUInt32(data, offset) * 1000L));
- }
- [Microsoft.SqlServer.Server.SqlFunction(Name = "getSum")]
- public static SqlByte Sum(byte[] buffer, int startPos, int endPos)
- {
- byte b = 0;
- for (int i = startPos; i <= endPos; i++)
- {
- b ^= buffer[i];
- }
- return b;
- }
- [Microsoft.SqlServer.Server.SqlFunction(Name = "updateSum")]
- public static SqlBinary updateSum(byte[] buffer, int startPos, int endPos, int sumPos)
- {
- byte b = 0;
- for (int i = startPos; i <= endPos; i++)
- {
- b ^= buffer[i];
- }
- buffer[sumPos] = b;
- return new SqlBinary(buffer);
- }
- [Microsoft.SqlServer.Server.SqlFunction(Name = "Int2Bin")]
- public static SqlBinary Int2Bin(int number)
- {
- return new SqlBinary(BitConverter.GetBytes(number));
- }
- [Microsoft.SqlServer.Server.SqlFunction(Name = "Long2Bin")]
- public static SqlBinary Long2Bin(long number)
- {
- return new SqlBinary(BitConverter.GetBytes(number));
- }
- [Microsoft.SqlServer.Server.SqlFunction(Name = "Bin2Int")]
- public static SqlInt32 Bin2Int(byte[] data, int offset)
- {
- return new SqlInt32(BitConverter.ToInt32(data,offset));
- }
- [Microsoft.SqlServer.Server.SqlFunction(Name = "Bin2Long")]
- public static SqlInt64 Bin2Long(byte[] data, int offset)
- {
- return new SqlInt64(BitConverter.ToInt64(data, offset));
- }
- [Microsoft.SqlServer.Server.SqlFunction(Name = "getByte")]
- public static SqlByte getByte(byte[] data, int offset)
- {
- return new SqlByte(data[offset]);
- }
- [Microsoft.SqlServer.Server.SqlFunction(Name = "getBytes")]
- public static SqlBytes getBytes(byte[] data, int offset,int count)
- {
- byte[] temp = new byte[count];
- Array.Copy(data, offset, temp, 0, count);
- return new SqlBytes(temp);
- }
数据包的结构体(表类型)
- --交易扩展记录
- (
- [DeviceID] UNIQUEIDENTIFIER NOT NULL,
- Meter INT,
- run INT,
- dead INT,
- StartTime DATETIME ,
- EndTime DATETIME
- )
SQL中借助CLR实现的转换函数
- (
- @offset INT = 1 ,
- @withDeviceID BIT = 0
- )
- (
- [DeviceID] UNIQUEIDENTIFIER ,
- Meter INT ,
- run INT ,
- dead INT ,
- StartTime DATETIME ,
- EndTime DATETIME
- )
- IF @withdeviceid = 1
- SET @sized = 16
- SET @sized = 0
- WITH sub
- @size) binDATA
- FROM sys_id
- WHERE id < @c
- )
- INSERT @emv
- ( rawdata ,
- DeviceID ,
- hardwareno ,
- meter ,
- run ,
- WORK ,
- dead ,
- starttime ,
- endtime ,
- emvtype ,
- emvno
- )
- END ,
- CASE @withDeviceID
- ELSE CAST (SUBSTRING(bindata, 1, @sized) AS UNIQUEIDENTIFIER)
- END ,
- dbo.Bin2Int(bindata, 0 + @sized) RecordNo ,
- dbo.Bin2Int(bindata, 4 + @sized) Meter ,
- dbo.Bin2Int(bindata, 8 + @sized) run ,
- dbo.Bin2Int(bindata, 16 + @sized) dead ,
- dbo.utcbin2time(bindata, 20 + @sized) StartTime ,
- dbo.utcbin2time(bindata, 24 + @sized) EndTime ,
- dbo.getByte(bindata, 61 + @sized) RecordType ,
- dbo.getByte(bindata, 62 + @sized) EMVNo
- FROM sub
- (
- @emv DeviceTranscationEMV READONLY ,
- @withDeviceID BIT = 0
- )
- SET @bin = 0x0
- IF @withDeviceID = 0
- + dbo.int2Bin(meter)
- + dbo.int2Bin(run)
- + dbo.int2Bin(dead)
- + dbo.time2utcbin(starttime)
- + dbo.time2utcbin(endtime)
- 0, 62, 63)
- FROM @emv
- + dbo.updatesum(dbo.int2Bin([HardwareNo])
- + dbo.time2utcbin(starttime)
- + dbo.time2utcbin(endtime)
- FROM @emv
- RETURN @bin
测试代码
- PRINT N'构造EMV数据,转换为BIN,然后再转换回EMV数据'
- go
- DECLARE @emv DeviceTranscationEMV
- WITH data
- 15 Meter ,
- 100 run ,
- 80 WORK ,
- 20 dead ,
- DATEADD(s, id, '2014-9-1 12:50:01') StartTime ,
- DATEADD(mi, id, '2014-9-1 13:23:11') EndTime ,
- 4 RecordType ,
- 0 EMVNo
- FROM dbo.Sys_ID
- WHERE id < 100
- )
- ( rawdata ,
- deviceid ,
- emvtype ,
- emvno ,
- hardwareno ,
- meter ,
- run ,
- work ,
- dead ,
- starttime ,
- endtime
- )
- SELECT 0x00 ,
- NEWID() ,
- recordtype ,
- emvno ,
- recordno ,
- meter ,
- run ,
- work ,
- dead ,
- starttime ,
- EndTime
- FROM data
- SELECT *
- FROM @emv
- PRINT @data
- SELECT *
执行结果:
资源: