Hash values of columns

Question

André Rothe · Jun 22, 2017

#Caché #SQL

I try to find a function, which generates hash values of columns. In MS SQL Server I can use

select hashbytes('sha2_256', my_column) ...

to create hash values of my_column. Is it possible to use such things in Caché?

Thank you
André

Discussion (1)0

Timothy Leavitt · Jun 22, 2017

I can't think of a built-in stored procedure for this, but it would be relatively simple to create one to wrap $System.Encryption.SHAHash(bitlength, text).

For example, approximating HASHBYTES with the features built in to $System.Encryption:

Class DC.Demo.SQL
{

ClassMethod HashBytes(pAlgorithm As %String, pText As %String) As %Binary [ SqlProc ]
{
    // Note: will result in <ILLEGAL VALUE> if pAlgorithm is not supported
    Quit $Case($ZConvert(pAlgorithm,"L"),
        "md5":$System.Encryption.MD5Hash(pText),
        "sha":$System.Encryption.SHA1Hash(pText),
        "sha1":$System.Encryption.SHA1Hash(pText),
        "sha2_256":$System.Encryption.SHAHash(256,pText),
        "sha2_512":$System.Encryption.SHAHash(512,pText))
}

}

Use (in SAMPLES):

select top 5 Name,DC_Demo.SQL_HashBytes('sha2_512',DOB) from Sample.Person

1 0