Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

access binary comparison

Posted on 2010-01-06
14
Medium Priority
?
438 Views
Last Modified: 2013-11-29
Hi,

This is a continuation of my previous post.  LINK: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Architecture-Design/Q_25022091.html
I needed to compare two tables, one table containing the ID in binary format, and one table containing the same ID in varchar.  

I closed my earlier post, because eval(id) seemed to take care of this issue, but I realized after that it only worked on a small set that I was testing.

The issue is:
say the id in question is:  0x00001900004F6E38
when i do say: tablea.id = eval(tableb.id)
all table A ids that had 0x00001900004F and any 4 other chars showed up as being the same.
meaning 0x00001900004F1111 showed as being equal to  0x00001900004F6E38, as well as 0x00001900004F9999, etc...

Does anybody know any way around this?? Thanks.
0
Comment
Question by:iamnamja
  • 8
  • 6
14 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 26192712
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

Open in new window

0
 

Author Comment

by:iamnamja
ID: 26193490
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?
0
 
LVL 58

Expert Comment

by:harfang
ID: 26193828
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°)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:iamnamja
ID: 26193966
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.  
0
 

Author Comment

by:iamnamja
ID: 26194017
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
0
 
LVL 58

Expert Comment

by:harfang
ID: 26194143
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°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 26194159
Yes, when treated as characters (as the function does when using AscW()), the byte order is reversed.

(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 26194172
From the immediate pane:

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

(°v°)
0
 

Author Comment

by:iamnamja
ID: 26194307
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.
0
 

Author Comment

by:iamnamja
ID: 26194412
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.
 
0
 
LVL 58

Expert Comment

by:harfang
ID: 26194495
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°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 26194560
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

Open in new window

0
 

Author Comment

by:iamnamja
ID: 26195148
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.
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 26196293
Well, all comparison are a little like "Like". For example "A" = "a" and "é" < "f". This doesn't make sense for binary data, and cannot be turned off in Access (it can in other engines).

Once the binary data has been converted to a hex representation, it doesn't matter any more, and you can perform exact comparisons and binary sort. I just wrote an article about the reverse: performing accent insensitive searches (http:/A_2183.html). I was planning on another one about case sensitivity.

(°v°)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

576 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question