Skip to Content
avatar image
Former Member

ASE 15.0.3 - MD5 hash for rows

Hi

I am trying below to get a unique MD5 for each row in a table for data comparison purposes and the query fails. Would appreciate any assistance to fix this.

select col1, hash ((select * from t1), 'md5') from t1

If select * is not possible, is there another query example that can be provided which will specify each of the column names?

i.e select col1 + col2 + col3 ... from t1

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

10 Answers

  • Best Answer
    Jun 29, 2017 at 01:50 PM

    I'm guessing your client application may be truncating the output from select @sqlstring.

    Does execute(@sqlstring) fail or succeed? (If the string is being truncated then it should fail with a syntax issue.)

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 28, 2017 at 04:32 PM

    Hi Jay,

    "select *" certainly won't work in this context.

    You can generate a string of sql concatenating column names from syscolumns to plug into hash() with something like this:

    declare @tabname sysname
    select @tabname = "sysusers"
    declare @sqlstring varchar(16000)
    select @sqlstring = "select hash("
    update syscolumns set @sqlstring = @sqlstring + name + ' + ' from syscolumns where id = object_id(@tabname)

    set @sqlstring = @sqlstring + "'' , 'md5') from " + @tabname
    select @sqlstring
    -- exec (@sqlstring) -- in some cases, this output can be executed directly*

    ---------------------------------
    select hash(suid + uid + gid + name + environ + user_status + '' , 'md5') from
    sysusers

    (1 row affected)

    *However, this only works if all the columns are string types. You would have to add explicit
    convert()s to convert the non-string types to a string representation to be concatenated.
    Not impossible, but certainly more work.

    Add comment
    10|10000 characters needed characters exceeded

    • For mixed datatypes I'm thinking hashbytes() might work; with a bit of tweaking to the code that builds @sqlstring you'd get:

      select hashbytes('md5',suid,uid,gid,name,environ,user_status) from sysusers
  • avatar image
    Former Member
    Jun 28, 2017 at 04:48 PM

    Hi Bret

    Thank you for the example. The above sql appears to update syscolums, so I tried by removing the update as below .. but the results are not displaying all the column names as expected?

    Would you have any suggestions on how to fix this or even if there is a better method of doing this? My objective is to get the primary key column + the rest of the columns (MD5) for each record in all the tables. So the result looks like pk_col, MD5_val.

    set nocount on
    declare @tabname sysname
    select @tabname = "sysusers"
    declare @sqlstring varchar(16000)
    select @sqlstring = "select hash("
    select @sqlstring = @sqlstring + name + ' + ' from syscolumns where id = object_id(@tabname)

    set @sqlstring = @sqlstring + "'' , 'md5') from " + @tabname
    select @sqlstring

    Results

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    select hash(environ + '' , 'md5') from sysusers

    Thank you

    Add comment
    10|10000 characters needed characters exceeded

    • Bret's code makes use of a secret (undocumented?) method of appending data from multiple rows into a single value that is stored in a @variable.

      Notice that his UPDATE statement only sets/changes the value of the @sqlstring variable. Nowhere does the code actually set/change a *column* in the syscolumns table.

      By having the set @sqlstring statement append values to @sqlstring, he's able to concatenate strings from several records (rows in syscolumns) into a single string.

  • avatar image
    Former Member
    Jun 28, 2017 at 05:14 PM

    Hi Mark

    I understood that its setting it as a string on syscolumns. However the update requires 'adhoc update on system tables' configured and I do not have SA or SSO on this server unfortunately. Reason for trying it without the update.

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 28, 2017 at 08:12 PM

    Mark

    Many thanks for the response. Varchar(16000) appears to be not large enough as only half the columns are captured and the string is incomplete. Would you please advise workaround for this?

    Thank you

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 28, 2017 at 08:48 PM

    Mark

    Many thanks for the response. Varchar(16000) appears to be not large enough as only half the columns are captured and the string is incomplete. Would you please advise workaround for this?

    Thank you

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 29, 2017 at 02:58 AM

    It returns as below. I am unsure why the string is truncated in this case.


    ----------- -----------
    24 401
    (1 rows)
    Execution time: 0.136 seconds

    Version:

    Adaptive Server Enterprise/15.0.3/EBF 24162 ESD#4.5/P/Sun_svr4/OS 5.8/ase1503/2861/64-bit/FBO/Thu May 7 09:18:29 2015
    (1 rows)

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 28, 2017 at 05:42 PM

    Addressing two issues:

    • - inability to run 'update syscolumns' when 'allow updates to system tables' = 0
    • - applying a md5 hash against non-character columns

    We can do a bit of tweaking to Bret's original code:

    declare @tabname   sysname,
            @sqlstring varchar(16000)
    select @tabname   = "sysusers",
           @sqlstring = NULL
    
    update master..spt_values
    set @sqlstring = @sqlstring + ',' + col_name(object_id(@tabname),number)
    where   type = 'P'
    and     number between 1 and (select max(colid) from syscolumns where id = object_id(@tabname))
    
    set @sqlstring = "select hashbytes('md5'" + @sqlstring + ") from " + @tabname
    select @sqlstring
    -- exec (@sqlstring) -- in some cases, this output can be executed directly*
    go
    
    ----------------------------------------------------------------------------
    select hashbytes('md5',suid,uid,gid,name,environ,user_status) from sysusers
    

    NOTES:

    • - master..spt_values.number (where type = 'P') ranges from 0 to 1024 (inclusive) (at least in my ASE 15.7 dataserver); net result is that this solution won't work if your table has more than 1024 columns
    • - master..spt_values, while technically a 'system' table in the master database, does not require 'allow updates to system tables' = 1
    • - hashbytes() allows for most datatypes (per the ASE 15.0.2 Reference Manual: Blocks - does not accept image, text, unitext or off-row java datatypes)

    ---------------------------

    EDIT: *shoot* Just ran the proposed code (above) using a non-sa account ... it will fail since master..spt_values, by defaut, only allows 'select' access.

    One (obvious) solution would be to create a #temp table to replace master..spt_values (or Bret's syscolumns), eg:

    declare @tabname   sysname,
            @sqlstring varchar(16000)
    
    select @tabname   = "sysusers",
           @sqlstring = "select hash("
    
    select name into #syscolumns from syscolumns where id = object_id(@tabname)
    
    update #syscolumns set @sqlstring = @sqlstring + name + ' + ' 
    
    set @sqlstring = @sqlstring + "'' , 'md5') from " + @tabname
    select @sqlstring
    go
    ---------------------------------------------------------------------------------------
    select hash(suid + uid + gid + name + environ + user_status + '' , 'md5') from sysusers
    

    -- or

    declare @tabname   sysname,
            @sqlstring varchar(16000)
    
    select @tabname   = "sysusers",
           @sqlstring = NULL
    
    select number into #spt_values from master..spt_values where type = 'P' order by number
    
    update #spt_values
    set @sqlstring = @sqlstring + ',' + col_name(object_id(@tabname),number)
    where  number between 1 and (select max(colid) from syscolumns where id = object_id(@tabname))
    
    set @sqlstring = "select hashbytes('md5'" + @sqlstring + ") from " + @tabname
    select @sqlstring
    go
    ---------------------------------------------------------------------------
    select hashbytes('md5',suid,uid,gid,name,environ,user_status) from sysusers
    Add comment
    10|10000 characters needed characters exceeded

  • Jun 28, 2017 at 10:11 PM

    What does the following return:

    select count(*), sum(char_length(name)) from syscolumns where id = object_id('<your_table_name_here>')
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 29, 2017 at 05:17 PM

    Hi Mark

    What you suggested works and the isql client seems to be the issue. Thank you for your assistance. Will test this further.

    Thanks

    Add comment
    10|10000 characters needed characters exceeded