ASE 15.0.3 - MD5 hash for rows

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[former_member188958]

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.