In SQL Server, I need to pack 2 characters into 1 character, similar to HEX. How?

Question

I have a SQL Server table that has a column in it that is defined as Binary(7). It is updated with data from a Cobol program that has Comp-3 data (packed decimal). I wrote a C# program to take a number and create the Comp-3 value. I have it available to SQL Server via CLR Integration. I'm able to access it like a stored procedure.

My problem is, I need to take the value from this program and save it in the binary column. When I select a row of data that is already in there, I am seeing a value like the following:

0x00012F0000000F

The value shown is COBOL comp-3 (packed decimal) data, stored in the SQL table. Remember, this field is defined as Binary(7). There are two values concatenated and stored here. Unsigned value 12, and unsigned value 0.

I need to concatenate 0x00012F (length of 3 characters) and 0x0000000F (length of 4 characters) together and write it to the column.

My question is two part.

1) I am able to return a string representation of the Comp-3 value from my program. But, I'm not sure if this is the format I need to return to make this work. What format should I return to SQL, so it can be used correctly?

2) What do I need to do to convert this to make it work?

I hope I was clear enough. It's a lot to digest...Thanks!

Answer 1

I figured it out! I needed to change the output to byte[], and reference it coming out of the program in SQL as varbinary.

This is the code, if anyone else in the future needs it. I hope this helps others that need to create Comp-3 (packed decimal) in SQL. I'll outline the steps to use it below.

Below is the source for the C# program. Compile it as a dll.

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

namespace Numeric2Comp3
{
//PackedDecimal conversions

public class PackedDecimal
{

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ToComp3(string numberin, out byte[] hexarray, out string hexvalue)
    {

        long value;
        bool result = Int64.TryParse(numberin, out value);

        if (!result)
        {
            hexarray = null;
            hexvalue = null;
            return;
        }

        Stack<byte> comp3 = new Stack<byte>(10);

        byte currentByte;
        if (value < 0)
        {
            currentByte = 0x0d;     //signed -
            value = -value;
        }
        else if (numberin.Trim().StartsWith("+"))
        {
            currentByte = 0x0c;     //signed +
        }
        else
        {
            currentByte = 0x0f;     //unsigned 
        }

        bool byteComplete = false;
        while (value != 0)
        {
            if (byteComplete)
                currentByte = (byte)(value % 10);
            else
                currentByte |= (byte)((value % 10) << 4);
            value /= 10;
            byteComplete = !byteComplete;
            if (byteComplete)
                comp3.Push(currentByte);
        }
        if (!byteComplete)
            comp3.Push(currentByte);
        hexarray = comp3.ToArray();
        hexvalue = bytesToHex(comp3.ToArray());
    }

    private static string bytesToHex(byte[] buf)
    {
        string HexChars = "0123456789ABCDEF";
        System.Text.StringBuilder sb = new System.Text.StringBuilder((buf.Length / 2) * 5 + 3);
        for (int i = 0; i < buf.Length; i++)
        {
            sbyte b = Convert.ToSByte(buf[i]);
            b = (sbyte)(b >> 4);     // Hit to bottom
            b = (sbyte)(b & 0x0F);   // get HI byte
            sb.Append(HexChars[b]);
            b = Convert.ToSByte(buf[i]);             // refresh
            b = (sbyte)(b & 0x0F);   // get LOW byte
            sb.Append(HexChars[b]);
        }
        return sb.ToString();
    } 

} 
}

Save the dll somewhere in a folder on the SQL Server machine. I used 'C:\NTA\Libraries\Numeric2Comp3.dll'.

Next, you'll need to enable CLR Integration on SQL Server. Read about it on Microsoft's website here: Introduction to SQL Server CLR Integration. Open SQL Server Management Studio and execute the following to enable CLR Integration:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Once that is done, execute the following in Management Studio:

CREATE ASSEMBLY Numeric2Comp3 from 'C:\NTA\Libraries\Numeric2Comp3.dll' WITH PERMISSION_SET = SAFE

You can execute the following to remove the assembly, if you need to for any reason:

drop assembly Numeric2Comp3

Next, in Management studio, execute the following to create the stored procedure to reference the dll:

CREATE PROCEDURE Numeric2Comp3
@numberin nchar(27), @hexarray varbinary(27) OUTPUT, @hexstring nchar(27) OUTPUT
AS
EXTERNAL NAME Numeric2Comp3.[Numeric2Comp3.PackedDecimal].ToComp3

If everything above runs successfully, you're done!

Here is some SQL to test it out:

DECLARE @in nchar(27), @hexstring nchar(27), @hexarray varbinary(27)
set @in = '20120123'
EXEC Numeric2Comp3 @in, @hexarray out, @hexstring out

select len(@hexarray), @hexarray

select len(@hexstring), @hexstring

This will return the following values:

(No column name)    (No column name)
5                   0x020120123F

(No column name)    (No column name)
10                  020120123F                 

In my case, what I need is the value coming out of @hexarray. This will be written to the Binary column in my table.

I hope this helps others that may need it!

Answer 2

If you have Comp-3 stored in a binary filed as a hex string, well I wonder if the process that created this is working as it should.

Be that as it may, the best solution would be to cast them in the select; the cast sytax is simple, but I don't know if a comp-3 cast is available.

Here are examples on MSDN.

So let's work with the string: To transform the string you use this:

string in2 = "020120123C";
long iOut = Convert.ToInt64(in2.Substring(0, in2.Length - 1)) 
          * (in2.Substring(in2.Length - 1, 1)=="D"? -1 : 1 ) ;

It treats the last character as th sign, with 'D' being the one negative sign. Both 'F' and 'C' would be positive.

Will you also need to write the data back?

I am curious: What string representaion comes out for fractional numbers like 123.45 ?

( I'll leave the original answer for reference..:)

---

Here are a few lines of code to show how you can work with bit and bytes.

The operations to use are:

• shift the data n bits right or left: << n or >> n
• masking/clearing unwanted high bits: e.g. set all to 0 except the last 4 bits: & 0xF
• adding bitwise: |

If you have a string representation like the one you have shown the out3 and out4 byte would be the result. The other conversions are just examples how to process bit; you can't possibly have decimals as binarys or binarys that look like decimals. Maybe you get integers - then out7 and out8 would be the results.

To combine two bytes into one integer look at the last calculation!

// 3 possible inputs:
long input = 0x00012F0000071F;
long input2 = 3143;
string inputS = "0x00012F0000071F";

// take binary input as such
byte out1 = (byte)((input >> 4) & 0xFFFFFF );
byte out2 = (byte)(input >> 36);

// take string as decimals
byte out3 = Convert.ToByte(inputS.Substring(5, 2));
byte out4 = Convert.ToByte(inputS.Substring(13, 2));

// take binary as decimal
byte out5 = (byte)(10 * ((input >> 40) & 0xF) + (byte)((input >> 36) & 0xF));
byte out6 = (byte)(10 * ((input >> 8) & 0xF) + (byte)((input >> 4) & 0xF));

// take integer and pick out 3rd and last byte 
byte out7 = (byte)(input2 >> 8);
byte out8 = (byte)(input2 & 0xFF);

// combine two bytes to one integer
int byte1and2 = (byte)(12) << 8 | (byte)(71) ;

Console.WriteLine(out1.ToString());
Console.WriteLine(out2.ToString());
Console.WriteLine(out3.ToString());
Console.WriteLine(out4.ToString());
Console.WriteLine(out5.ToString());
Console.WriteLine(out6.ToString());
Console.WriteLine(out7.ToString());
Console.WriteLine(out8.ToString());    
Console.WriteLine(byte2.ToString());