RecnumToRowID

Recently I had a problem at a customer who migrated all their data to MSSQL.

So far no problem.

But they want to employ embedded SQL and then use that in their app. That part of the app depends on RowID's so it would be highly preferred not having to rewrite that back to using recnum's.

They are not using standard tables, so a recnum column is available, but not a rowID. (Not that standard tables have rowID columns, but I digress)

So it would be nice if we can somehow convert that recnum value we can retrieve via ESQL to a rowID value.

DAW has told us we never need to know the secret formula, because their CK takes care of this.

Except when it doesn't, like in this case.

So I remembered a post at the forum that explained how the conversion from recnum to rowid worked, but couldn't find the post (or code) that did that. But luckily did figure it out.

So here is the code to transform a recnum into a rowid.

Obviously this comes without any warranty or guarantee.

      
  // from cHexLib -> https://www.dataaccess.com/kbasepublic/KBPrint.asp?ArticleID=2196
  Function DecToHex Integer iDec Returns String
    String sHex

    Move "" To sHex
    Repeat
        Move (Mid ("0123456789ABCDEF", 1, ((iDec iAnd |CI$0F) + 1)) + sHex) To sHex
        Move (iDec / |CI$10) To iDec
    Until (iDec = 0)
    Function_Return sHex
  End_Function // DecToHex
  
  // This is a hack, but we have recnum in our SQL database, not rowid.. 
  // DAW in all their wisdom does not provide this function.
  Function RecnumToRowID Integer iRecnum Returns String
    String sRowID
    String sHex
    
    Get DecToHex iRecnum to sHex
    // rowid is always 8 characters long
    While (Length(sHex)<8)
      Move ("0"+sHex) To sHex
    Loop
    // reorder bytes
    Move (Mid(sHex,2,7)+Mid(sHex,2,5)+Mid(sHex,2,3)+Mid(sHex,2,1)) To sRowID
    Function_Return (lowercase(sRowID))
  End_Function