access binary comparison

I suspect that Access performs a string comparison, i.e. not binary. You have zero-characters in your data, for example, you shouldn't see anything in the id field when viewed from the interface, do you? In fact, I have problems recreating your data.

You can create a binary field in your second table, I do believe that an inner join on binary fields is performed with binary matching. Are you using this data type just like a very long number?

Perhaps you can try the reverse: convert the binary field to text, and use that for comparison. The function below should get you started.

(°v°)

Function BinaryHex(ID)
    Dim i As Integer
    Dim ret As String
    For i = 1 To Len(ID)
        ret = ret & Right(String(4, "0") & Hex(AscW(Mid(ID, i, 1))), 4)
    Next i
    BinaryHex = ret
End Function

Select allOpen in new window

Hi harfang,

You're correct, i don't see anything in the interface for the ID column.

I ran your function, and it converts the binary to a string.  e.g. 0x000000194F003C6A shows as 000000194F003C6A.
Table B stores these as a varchar assuming because of the "0x".
Although I can see how converting all binary IDs to a text by using your function will work, it would significantly slow down since it would need to run each id through the function.

Any ideas on how I can fix this?

That or the reverse: converting all string IDs from table B to binary. Once the data types are compatible, you should be able to link the tables in the normal fashion. You are absolutely right, using a function call in a link, filter or sort expression is terrible in terms of performance.

What is the idea behind this structure? Why do you need to handle binary ID fields?

(°v°)

harfang,

The binary ID fields are done by a non-local database, so I can't say why they chose this data type.  

But i did find something interesting.  it seems as though the eval function trunckates the last few chars.

e.g. i tested what the result would be by looking at what binaryhex(eval("0x00001900004F6E38")) would give: the result was: 000000194F00386E

Any idea why its cutting off the "38"?  I think this is the reason why i'm getting more matches per ID.  

Actually,
What's interesting is, its not JUST cutting off the last two digits, if you look closely at the result, they're different in how its represented.  
The digits are not arranged in the same mannor
00001900004F6E38 vs
000000194F00386E

It doesn't truncate, but apparently swaps high and low bytes. Compare:

    0000 1900 004F 6E38
    0000 0019 4F00 386E

Apparently, the binary data is built one way, and decoded the other when viewed as a string. Same problem again.

By the way, how can you read the values starting with 0x in your database, in the binary field, I mean?

(°v°)

Yes, when treated as characters (as the function does when using AscW()), the byte order is reversed.

(°v°)

From the immediate pane:

? Hex(AscW(Eval("0x1234")))
3412

(°v°)

I can't see it from my access database which i'm currently using.

But I can see the values when i query it using sybase.  the results show without the "0x" but to query the result "0x" is added

in this case, what can i do to have the values re-reversed?  The main issue is, Table A contains a much smaller set, so running it through a function won't cause much of an issue (set of roughly 1000 items) vs. table B which contains several hundred thousand items.

So converting all text representation of the hex into a hex shouldn't take that long.  Unless you have any other ideas.

Actually,

This still doesn't explain then why comparing the two ids after converting the text to binary through eval is producing the correct results.  If the reversal is due to the way vba is handeling the converstion from binary to string, what i am trying to do shouldn't matter.

as mentioned before any id with ignoring the last four characters turn up as a match.
 

No, I haven't any better ideas. You need to make your ID fields compatible, one way or another. If you let Jet change the IDs to strings, the comparison is done in "database mode" (using Unicode representations), which you don't want.

When you apply the function directly to the binary data, are the high and low bytes reversed? Swapping high and low bytes is a pain in VB, because there are no unsigned numeric types.

(°v°)

A.id is binary, and B.id is a varchar containing the corresponding hex representation.

  WHERE A.id = Eval('0x'+B.id)

In this case, both sides are converted to string. Comparison is performed in string mode (ignoring case for example) and it might also stop after certain special control characters (e.g. character zero)

  WHERE Bin2hex(A.id) = B.id

This time, the binary data is converted to its string representation (perhaps with high and low bytes swapped, I don't know). In any case, the comparison will be exact.

Ideally, you need:

  WHERE A.id = B.id

With both sides having the same data type. If you need this query often, consider converting one the fields to the other's type. It's worth it.

Below is one way to swap bytes in an integer (e.g. in a code resulting from AscW())

(°v°)

Private Declare Sub RtlMoveMemory Lib "kernel32" ( _
    ByRef lpvDest As Any, _
    ByRef lpvSrc As Any, _
    ByVal cbLength As Long)
 
Function SwapHiLo(pint As Integer) As Integer
    Dim ret As Integer
    RtlMoveMemory ByVal VarPtr(ret) + 1, ByVal VarPtr(pint), 1
    RtlMoveMemory ByVal VarPtr(ret), ByVal VarPtr(pint) + 1, 1
    SwapHiLo = ret
End Function

Select allOpen in new window

harfang,

The fuction that you provided does organize the bytes as it originally was.

I guess I would have to look through a bit further, but I just find it so odd, that comparison by converting the string to binary through eval, acts as if i'm doing a "like" comparsion.