KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q194206: FIX: Jet Drivers Do Not Support Bit-wise Operators

Article: Q194206
Product(s): Open Database Connectivity (ODBC)
Version(s): WINDOWS:1.0,2.0,2.1 SP2,2.5,3.0,3.5,3.51,3.6
Operating System(s): 
Keyword(s): kbole kbODBC kbDSupport kbMDAC250
Last Modified: 19-JUL-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft ODBC Driver for Access, versions 1.0, 2.0, 3.0, 3.5, 3.6 
- Microsoft OLE DB Provider for Jet, version 3.51 
- Microsoft Data Access Components versions 2.1 SP2, 2.5 
-------------------------------------------------------------------------------

SYMPTOMS
========

The Microsoft ODBC Driver for Access and the Microsoft OLE DB Provider for Jet
do not provide support for bit-wise operations in SQL statements. Attempts to
use AND, OR, and XOR with numeric fields in a SQL statement return the result of
a logical operation (true or false).

CAUSE
=====

The Microsoft Jet database engine does not support bit-wise operations in SQL.

RESOLUTION
==========

Bit-wise operations must be replaced with the equivalent mathematical
expressions or performed on the data outside of a SQL statement (performed in
Visual Basic for Applications code for example).

STATUS
======

This behavior is by design.

MORE INFORMATION
================

You can test for a single bit set in a long integer or integer field using the
following algorithm:

  ( <value> \ (2^<bit>) ) mod 2

This expression will return 1 if the <bit> is set and 0 if <bit> is
not set. <bit> is numbered from 0 to 30 inclusive where <bit>=0 is
the first bit. <bit> values greater than 30 will not work with this
algorithm because Jet uses unsigned long integer values and 2^31 is one larger
than the largest unsigned long integer value and thus will cause numeric
overflow when the division is evaluated.

Note that the \ operator and not the / operator is used. The \ operator is used
for integer division. The / operator is used for floating point division and
will cause unexpected results when used with this algorithm.

You can check <bit> 31 for a long integer using the following algorithm:

  iif( <value> < 0, 1, 0 )

This works because an unsigned long integer that is less than zero means the
highest order bit (bit 31 for a long) is set.

Suppose you have a table named Test and a long integer field named TestFlags. You
can use the following SQL statements to test to see if the bit 11 is set in the
TestFlags field

  SELECT * FROM Test WHERE ([TestFlags]\2^11) mod 2 = 1

or replace 2^11 with 2048 to save some query calculation time:

  SELECT * FROM Test WHERE ([TestFlags]\2048) mod 2 = 1

You can use the following SQL to test for bit 31

  SELECT * FROM Test WHERE iif( [TestFlags] < 0, 1 ,0 ) = 1

but this SQL statement would be a much more efficient test for bit 31:

  SELECT * FROM Test WHERE [TestFlags] < 0

Note that you can also create calculated columns in SQL to display the results of
one or more bit checks:

  SELECT ([TestFlags]\2^11) mod 2 AS Bit11Set FROM Test

You can run the following ADO code to verify that this algorithm works correctly
over various ranges and with various bit flags. Note the test requires a blank
Microsoft Access database named C:\Db1.mdb and a reference to Microsoft ActiveX
Data Objects.

     ' START SAMPLE CODE
     Sub VerifyBitTest()
     Dim i As Long, min As Long, max As Long, bit As Long
     Dim conn As New ADODB.Connection
     Dim rs As New ADODB.recordset

        conn.Open "DRIVER=Microsoft Access Driver (*.mdb);" & _
        "MAXBUFFERSIZE=128;DBQ=c:\db1.mdb"
        On Error Resume Next
           conn.Execute "DROP TABLE Test"
        On Error GoTo 0
        conn.Execute "CREATE TABLE Test (TestFlags LONG)"
        conn.Execute "INSERT INTO Test (TestFlags) VALUES (0)"
        min = 2 ^ 0: max = 2 ^ 30: bit = 11
        For i = min To max ' This could take a while.
           rs.Open "SELECT (" & i & "\2^" & bit & _
                   ") mod 2 AS BitSet FROM Test", conn
           If rs!BitSet <> IIf((i And (2 ^ bit)) > 0, 1, 0) Then
              MsgBox "Bit Test Failure!"
              Exit Sub
           End If
           rs.Close
           DoEvents
           If i Mod 100 = 0 Then Debug.Print "Verified " & i & " of " & max
        Next i

     End Sub
     ' END SAMPLE CODE

Additional query words:

======================================================================
Keywords          : kbole kbODBC kbDSupport kbMDAC250 
Technology        : kbAudDeveloper kbAccessSearch kbODBCSearch kbOLEDBSearch kbMDACSearch kbMDAC210SP2 kbMDAC250 kbODBCAccess100 kbODBCAccess200 kbODBCAccess300 kbODBCAccess350 kbODBCAccess360 kbOLEDBProvJet351 kbOLEDBProvSearch
Version           : WINDOWS:1.0,2.0,2.1 SP2,2.5,3.0,3.5,3.51,3.6
Issue type        : kbbug
Solution Type     : kbfix

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.