Send integer as varbinary to SQL Trigger

Question

I use this code to set context_info within a SQL-Trigger

string strUser = _app.Settings.User;
string strInt = "";

// strInt += '\x0100'; => becomes 1 in trigger
strInt += '\x0000';
strInt += '\x0200'; //=> becomes two in the trigger
strInt += '\u0000';
strInt += '\u0300';//=> becomes 3 in the trigger

// strInt += '\x0000'; 
// strInt += '\x0210'; //=> becomes 4098 in the trigger( 4096=1 + 2=2)
// strInt += '\x0000'; 
// strInt += '\u1300';//=> becomes 19 in the trigger


// strInt += '\x0200'; // = 131072
// strInt += '\x0000'; 
// strInt += '\u0100';// = 65536
// strInt += '\u0000';

//strInt += '\x0000'; 
//strInt += '\x0003'; //768
//strInt += '\u0000';
//strInt += '\u0002'; //512

string strUser = "myself";
string strContextValue = strInt + strUser + "$";
string strContext = "declare @context_info varbinary(120) set @context_info =  CONVERT(varbinary(120), N'" + strContextValue + "') set context_info @context_info";

 SqlCommand scContext = new SqlCommand(strContext, conn, tran);
 scContext.ExecuteNonQuery();

In the Trigger the first 8 chars are interpreted as two numbers:

declare @ctxt varbinary(128)
select @ctxt=context_info from master.dbo.sysprocesses where spid = @@spid
set @session=substring(@ctxt,1,4)
set @contextid=substring(@ctxt,5,4)

I'm not the author of the trigger so I can't change it but it would be great if you could explain me what kind of magic is going on here? How can be ensured that the first 8 chars are numbers? How do I have to encode two Intergers so that they become 4 chars long at the SQL-Trigger?

I already did a lot of encoding stuff but can't look thru it.

When I load the corretly written (from another application) data back to a c# DataTable I can get the correct value of the numbers by doing:

 byte[] binaryString = (byte[])dataTable.Rows[0][2];
 string x = Encoding.ASCII.GetString(binaryString);

 string strSubX2 = x.Substring(3, 4);
 int iResult = BitConverter.ToInt32(Encoding.Unicode.GetBytes(strSubX2), 0);

There is no guarantee of that, this code just converts a string to binary, if you look at that it will always numeric as your looking at the numeric value of each byte. Still not sure what your asking. — Alex K., Jun 11 '18 at 13:54
How to encode two integer in c# so that CONVERT creates 8 chars as shown in SQL Code...Pls look at: set @session=substring(@ctxt,1,4). How do I have to format my SQL-Command so that CONVERT encodes an intager in 1-4 ? — stefan, Jun 11 '18 at 14:02
Can't you just use fill with leading zeros? Like string.Format("{0:D4}", session) + string.Format("{0:D4}", contextId) — PrfctByDsgn, Jun 12 '18 at 07:05
Nope. Since it just makes a text representation of the string. So if the first Integer is 8 digits long this would use all space (8)... — stefan, Jun 12 '18 at 08:42
But the Trigger Code you pasted above extracts for example @session from the first 4 characters of the context_info ... the the max value you can pass there should be 9999 ... 8 digits integer values cannot be handled like this ... you could increase the range a bit by encoding the values as hexadecimal string ... — PrfctByDsgn, Jun 15 '18 at 07:54
Thats the point!! As described above! Also samples are included! The question is simple and also written above: How to encode? How to encode int so that SQL-CONVERT does it right? — stefan, Jun 15 '18 at 09:22

Vladimir Baranov · Accepted Answer · 2018-06-16T04:57:57.903

It looks like your confusion comes from misunderstanding what varbinary is. varbinary is a string of bytes, not chars. It is not even really a string, it is an array of bytes.

varbinary can be represented as a string of hexadecimal values and varbinary constants in T-SQL code are represented as hexadecimal strings. Two hexadecimal characters per byte.

The SUBSTRING function in T-SQL can work with strings (varchar) and with bytes (varbinary).

Syntax

SUBSTRING ( expression ,start , length )

The values for start and length must be specified in number of characters for ntext, char, or varchar data types and bytes for text, image, binary, or varbinary data types.

So, the following T-SQL trigger code

declare @ctxt varbinary(128)
select @ctxt=context_info from master.dbo.sysprocesses where spid = @@spid
set @session=substring(@ctxt,1,4)

takes first 4 bytes (not chars) from the @ctxt array of bytes and assigns them to the @session variable. What type is @session? Most likely it is int.

Run this sample code in SSMS and you'll see the hexadecimal representation of some integers:

DECLARE @T TABLE (I int);
INSERT INTO @T VALUES
(0),
(1),
(3),
(19),
(255),
(256),
(512),
(768),
(4098),
(65535),
(65536),
(131072),
(1234567890),
(-1),
(-65535);

SELECT
    I, CONVERT(varbinary(120), I) AS BinI
FROM @T;

Result

+------------+------------+
|     I      |    BinI    |
+------------+------------+
|          0 | 0x00000000 |
|          1 | 0x00000001 |
|          3 | 0x00000003 |
|         19 | 0x00000013 |
|        255 | 0x000000FF |
|        256 | 0x00000100 |
|        512 | 0x00000200 |
|        768 | 0x00000300 |
|       4098 | 0x00001002 |
|      65535 | 0x0000FFFF |
|      65536 | 0x00010000 |
|     131072 | 0x00020000 |
| 1234567890 | 0x499602D2 |
|         -1 | 0xFFFFFFFF |
|     -65535 | 0xFFFF0001 |
+------------+------------+

Your C# code in the question doesn't make much sense. Wrapping both strInt + strUser into CONVERT(varbinary(120), N'" + strContextValue + "') doesn't make sense.

The C# code should construct the binary array. The T-SQL trigger code expects that first 4 bytes of this array is @session, next 4 bytes is @contextid. bytes, not characters.

So, if you want to pass, say, 131072 decimal as @session, 1234567890 decimal as @contextid, plus myself$ string as extra info you should run the following T-SQL code:

declare @context_info varbinary(128); -- note 128 length here
set @context_info = 0x00020000;
-- note, there are no quotes around the constants, they are numbers, not strings
set @context_info = @context_info + 0x499602D2;
set @context_info = @context_info + CONVERT(varbinary(120), N'myself$');
-- note 120 length here, first 8 bytes are used by two integers
set context_info @context_info;

Run this code in SSMS, but replace of the last line with SELECT @context_info; to see what it does. You'll get the following result:

+------------------------------------------------+
|                (No column name)                |
+------------------------------------------------+
| 0x00020000499602D26D007900730065006C0066002400 |
+------------------------------------------------+

You can see the binary representation of two integers plus binary representation of the unicode text string.

In C# you construct this code by concatenating hexadecimal representation of integers. Note, that you should not enclose 0x00020000 and 0x499602D2 into quotes or CONVERT(varbinary, ...). You should enclose only the myself$ string into the CONVERT(varbinary, ...).

The C# code should look something like this:

string strUser = "myself$";
int iSession = 131072;
int iContextID = 1234567890;

StringBuilder sb = new StringBuilder();
sb.Append("declare @context_info varbinary(120);");
sb.Append("set @context_info = 0x");
sb.Append(iSession.ToString("X8"));
sb.Append(";");
sb.Append("set @context_info = @context_info + 0x");
sb.Append(iContextID.ToString("X8"));
sb.Append(";");
sb.Append("set @context_info = @context_info + CONVERT(varbinary(120), N'");
sb.Append(strUser);
sb.Append("');");
sb.Append("set context_info @context_info;");

string strContext = sb.ToSTring();

SqlCommand scContext = new SqlCommand(strContext, conn, tran);
scContext.ExecuteNonQuery();

See also C# convert integer to hex and back again

Thanks a lot man! I really did a lot of investigation and also found the link you have posted. However, I wasn't able to make it work. — stefan, Jun 16 '18 at 12:57

Send integer as varbinary to SQL Trigger

1 Answers1