How to interpret RecordID Data Type via the SQL statement?

igor.chladil

Hello,

I have NAV5 SP1 DB on SQL2005.
I need to do some search via SQL SELECT statement using the RecordID field in the WHERE clause. When I have tried to convert the VARBINARY representation of RecordID on SQL it didn't look like some reasonable text. Could anybody please suggest what the VARBINARY SQL representation really means and what is really stored in there and how it is stored?

Thanks ahead for your replies.

Regards,
Igor Chladil

ara3n

Please read this blog on details of record link

http://blogs.msdn.com/microsoft_dynamic ... erver.aspx

jflynn

Here is some code in C# that I used to get the record link info from the Item table. Hopefully it will help someone.


String itemNumber = "0310100";
String rlKey = "0x1B00000000890" + itemNumber.Length.ToString() +
ConvertToHex(itemNumber) + "000000";
// build the [Record ID] string in hex
// limitations - this only works for links on the item table where Item.No_ length is less
// than 10. I'm sure there is a function to convert length it's binary value and
// if you wanted it to work for other tables you would have to change the first part of the string

String commandText;
commandText = "SELECT [Description], [URL1] from [yourdatabase].[dbo].[Record Link] where sys.fn_varbintohexstr([Record ID]) = '" + rlKey.Trim() + "'";
adp.SelectCommand = new SqlCommand(commandText, cn);
adp.Fill(dt);


// here is the code for the ConverToHex
private string ConvertToHex(string asciiString)
{
string hex = "";
foreach (char c in asciiString)
{
int tmp = c;
hex += String.Format("{0:x2}", (uint)System.Convert.ToUInt32(tmp.ToString()));
}
return hex;
}

santiagonav

TableFilter data type works similar.

Is there some documentation that specifies the mapping TableFilter hexadecimal string?

Thank you