Check if Array Exists...

rdrunner

Here is a small function that caused me another call from a client (No I didnt write it... I just had to fix it.. Would i be posting it otherwise?!?!)... For those who do not remember the odd language used there. It was called VB6 in those days...

Enjoy :)

Public Function CheckArrayExists(Parts() As String) As Boolean
'SNIP DB Access to retrieve an Recordset which contains several Rows with Data
'Format:
' Name              | Parts
' DOG / CAT / Mouse |   3
' Fish / Bird       |   2
'SNAP - Also snipped declarations and stuff
    If oRecordSet.EOF Then
        CheckArrayExists = False
    Else
        Do While Not oRecordSet.EOF
            hits = 0
            Exists = False
                For i = LBound(Parts) To UBound(Parts)
                    'if the part exists in an existing name then increment a counter
                    If InStr(oRecordSet!Name, Parts(i)) > 0 Then
                      hits = hits + 1
                    Else
                      hits = IIf(hits = 0, 0, hits - 1)
                    End If
                Next i
                'if all the parts exist in an existing array then the loop must be exited and true returned
                If hits = oRecordSet!Parts Then
                    Exists = True
                    Exit Do
                End If
            oRecordSet.MoveNext
        Loop
    End If
    oRecordSet.Close
    Set oRecordSet = Nothing
    CheckArrayExists = Exists
End Function

'Example Input vs Exampel Data:
' Lion , Fish, Bird
' Fish, Bird, Lion

emurphy

Ouch. WTF is this supposed to be doing, IOW what would a non-WTF rewrite look like? Also, how much of the WTF (if any) is due to poor database design?

rdrunner

It SHOULD try to figure out if all "parts of an array" are present in a string that represents an array of strings that is joined by a "/"

A rewrite should split the string from the DB and compare each element vs an array element.

Also it should not use substring since OF COURSE there is also a "Catfish" in the DB (No its not tracking Animals...)

Futhermore it should only limit the SQL to retrieve only elements where at LEAST the "Parts" count matches the number of array elements

Next it would have to remove the STUPID line with the IIF... If I fail to find an array element... how about bailing out of the for loop since we already KNOW that the "arrays" dont match

Edit: The DB WOULD Support a better querry... But i am not allowed to rewrite 4 layers of code to pass IDs instead of strings... Even changing ONE Layer of this App causes WEEKS of Acceptance tests...

SpoonMeiser

[quote user="rdrunner"]

Edit: The DB WOULD Support a better querry... But i am not allowed to rewrite 4 layers of code to pass IDs instead of strings... Even changing ONE Layer of this App causes WEEKS of Acceptance tests...

[/quote]

How did this thing get past any sort of testing in the first place?

rdrunner

An "internal" rule that causes the input array t be build in a special way (by "Humans") and this is the 1st time that caused the array to be build in "another" way since the order of the array elements also has a speciffic meaning... (Cant say more or my customer might find me :) )

But if you know other parts of the code, then this is only a minor WTF ;) I already have another post in this section that explains the DB performance of this application (Condensed Version: 400.000+ SQL Statements to insert 10 rows of data into a small table)