Generating a unique fixed-length hash in VB6

[G_Hosa_Phat]

Now, I can come up with some decent logic that would make that work. In the case of a collision, start with the first (or last) character of the random string, start incrementing that specific character through the available characters. If no suitable value can be found, move to the next character and do the same thing. That sounds like a reasonable way to maintain the general randomness. Sure, the result in THIS situation is less than random, but it should certainly be random enough for the implementation.

[Schmidt]

@Schmidt Thank you for your suggestion. I believe I've found a workable solution by creating a custom ID field that generates a random alphanumeric code in the RDBMS so that, when a new record is inserted, the database will automatically take care of this part of it for me (rather than having the application logic generate something and hope that I don't run into a concurrency issue). The function that's written in the database to generate the unique ID will actually loop until it finds one that isn't in use before committing the INSERT.

Writing your own Stored-Procedure to re-invent (in a much less performant way), what any decent
DB-Engine has already built-in: "handing out a truly Unique-Value for a new inserted Record"
is just duplicated efforts for a problem that was already solved (by the inherent AutoID-mechanism
any decent DB-Engine already ships with).

That mechanism is used in countless concurrency-scenarios safely, worldwide by:
BeginTransaction
... add new record
... get the new AutoID that was generated for it
EndTransaction

Post #35 contains already code for that (using ADOs Cnn.Begin/Commit/Rollback-Trans mechanism).

There's no extra-checking needed in your Application-Code, whether the ID you got out
from a successful (not rolled-back) transaction, is colliding with something - it is not -
that's what the AutoID- as well as the transaction-mechanisms of the DB-Engine made sure of.

The only thing that remains now for the Application-Code, is to express this sequential
(increasing) ID in a "safe-nonsequential-manner" - though still supporting a true 1:1 mapping
(no randomness involved, no collisions possible).

And the code for that was given in #35 as well, resulting in 5-Char-Sequences which are even
below your targeted 10 Chars (using a decent enough encryption-algo).

If your worries are, that the current implementation covers only 24Bit of that ever-increasing
sequential number (which should be more than enough for your planned Notification-volume),
one can easily enhance the mechanism to cover the full 32Bit (4Bytes instead of 3) of an Int32-
AutoID (which then would require not 5 Chars, but 7 - still less than your targeted 10 Chars).

IMO there's only to choose between 2 approaches - either commit fully to:
- a true Hash-Based solution with *long-enough* Hashes (of at least 100Bit = 20 5Bit-Chars)
- or an absolute collision-free (the Chars then scrambled by encryption) Auto-ID approach

The third option (using a "half-assed" Hash with a shorter BitLength than 100) - and then
checking (and avoiding) collisions through performance-affecting extra-loop-code, is just
combining the "worst of both worlds" (IMO).

As for the safety of the encrypted Auto-ID-approach, a potential attacker will
(due to the quality of the used encryption-algo) find it nearly impossible
(without applying huge amounts of brute-force CPU-power), to deduce the
underlying AutoID (which has the original Value of 2) from e.g. the 5 Chars:
- A3TWB
even when he should have come-by the information somehow, that the String:
- DU9RA
represents the AutoID with the Value of 1

(the above two outputs were BTW generated with a different Key and Salt as shown below:
?IdToFiveChars(1, "Some longer Key", "Some longer Salt"), IdToFiveChars(2, "Some longer Key", "Some longer Salt")

To be able to derive the value of 2 from A3TWB, he would need:
- a rough clue of what was used for the encryption-algo
- and even should he find the exact code somewhere (as e.g. in posting #35)
- he would need to find out what was used on your end, for the Key and the Salt input-params

And that's only possible through (huge amounts of) brute force.

And even *if* a hacker should be able to find out the algo and Key/Salt-pair
you were using to create your 5Char-identifiers, what use would it be for
him, to know that "on a DB-System he has no access to", a certain AutoID
represents "something else, he has no way to access or find out either".

So, even with known (or deducable) AutoIDs, the knowledge what concrete
informations these IDs "link to" in your DB, are still safe - and your very own
property - *especially* when using AutoIDs, since their generation does *not*
depend and is not *based* on the concrete field-information they are linking to
in your Notifications-Table.

Olaf

[dilettante]

AUTONUMBER/IDENTITY type fields are not meant for external use.

For many DBMSs compaction maintenance can renumber the values as part of garbage collecting data pages and rebuilding indexes.

[Schmidt]

AUTONUMBER/IDENTITY type fields are not meant for external use.

For many DBMSs compaction maintenance can renumber the values as part of garbage collecting data pages and rebuilding indexes.

This requires User-Interaction by a DB-Admin who would explicitely have to
define that behaviour in the "Compaction-Runs".

No DB-Engine I know of, would do such an Identity-Column-renumbering "by default".

The OP can easily ask, if something like that is taking place with the DB they are using.

Or in his special case (since he said, that there will never be anything deleted from that table),
he could instead of an AutoID-Column simply use the current Count of that table as the
(next) AutoID:

Changes to be made in my larger DB-example in #35 would be:

1. Change the Table-Definition line to the following:

  Cnn.Execute "Create Table Notifications(ID nVarChar(5) Primary Key, CustNr nVarChar(5), AccNr nVarChar(25), NotifyDate DateTime)"

2. Change the Transaction-Routine to the following:

Function AddNotification(CustNr$, AccNr$, NotifyDate As Date, Optional ID As Long) As String
  On Error GoTo RollBack
  Cnn.BeginTrans 'wrap the whole Operation in a Transaction

  ID = Cnn.Execute("Select Count(*)+1 From Notifications")(0) 'get the current RecordCount + 1
  
  Dim FiveChars As String
      FiveChars = IdToFiveChars(ID, Key, Salt)
  
  With GetRs("Select * From Notifications Where 1=0")
    .AddNew 'apply the 3 Values into a new Record
        !ID = FiveChars
        !CustNr = CustNr
        !AccNr = AccNr
        !NotifyDate = NotifyDate
    .Update 'and update it
  End With
  
  Cnn.CommitTrans 'no failures so far, so we commit the Insert
  
  AddNotification = FiveChars 'return the 5-Chars as a result only in case of success
  Exit Function
  
RollBack: 'Error-Logging omitted in this simplified example
  Cnn.RollbackTrans
End Function

Edit (Note for SQLServer-Users):
Whilst for the JET-Engine the above Transaction-Wrapping might be "sufficiently exclusive",
for other (bigger) DB-Engines the above Count-Retrieval has to be elevated to work in
a truly exclusive Manner (since "read-uncommited" is the performance-relevant default usually).
For MS-SQLServer the needed change would be in the Count-retrieving line:

  ID = Cnn.Execute("Select Count(*)+1 From Notifications With(TabLock,HoldLock)")(0)

The above exclusive Table-Lock is then hold for the duration of the entire transaction.

3. Change the List-Click-Event to the following:

  With GetRs("Select * From Notifications Where ID='" & List1.Text & "'")
    MsgBox !ID & vbCrLf & !CustNr & vbCrLf & !AccNr & vbCrLf & !NotifyDate
  End With

Olaf