Skip to Content
0

ASE 15.0.3 - MD5 hash for rows

Jun 28, 2017 at 03:37 PM

177

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

10 Answers

Best Answer
Mark A Parsons Jun 29, 2017 at 01:50 PM
0

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.)

Share
10 |10000 characters needed characters left characters exceeded
Bret Halford
Jun 28, 2017 at 04:32 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left 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
0
Jay Patel Jun 28, 2017 at 04:48 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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.

0
Jay Patel Jun 28, 2017 at 05:14 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Jay Patel Jun 28, 2017 at 08:12 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Jay Patel Jun 28, 2017 at 08:48 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Jay Patel Jun 29, 2017 at 02:58 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Jun 28, 2017 at 05:42 PM
0

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
Share
10 |10000 characters needed characters left characters exceeded
Mark A Parsons Jun 28, 2017 at 10:11 PM
0

What does the following return:

select count(*), sum(char_length(name)) from syscolumns where id = object_id('<your_table_name_here>')
Share
10 |10000 characters needed characters left characters exceeded
Jay Patel Jun 29, 2017 at 05:17 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded