bit array of boolean values stored in a long/double field to be used to query table via "Select *..." in database based on specific value.


I hope someone out there can help me out with the following:

I want to store various option for my application in a database. These options are all of boolean type. So instead of storing every option into a separate field, I want to store them in a variable of type double (64bit) in a binary format i.e. 100010001 where the marked bit is on and the unmarked bit is off. This would allow me to store up to 64 options per variable.

I have implemented this via VB code for many years now, and it always has been very useful to use a bit array to store many options i.e. over 360 options in our application so we simply store the lot in 12 variable of type long.

Anyway, back to my issue, I want to store a bit array into the a field in a table, but I want to be able to query the database and based my condition based on a value held in the database.

Is there a way to do this, but I need a way that will work for at least the following databases: MS Access, SQL Server, Oracle and MySQL. In other words, it can't be specific only to the one database.

I hope I was clear enough in terms of what I'm trying to achieve! If you know of any other methods on how to achieve this, let me know.

Please note that this needs to be very efficient as these options will be associated on a record based, and our database often ends-up with millions of records. So when a user query a record, I would either do a join query on a separate table or I may stick the field in the same table as where all the main records are stored. I have not decided that part!

Any feedback is greatly appreciated.

Many thanks.

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Bob LearnedConnect With a Mentor Commented:
I would suggest that you use a Long Integer value for the AccessRight bitflag field.

In Access you can create a module with the following function:

If the bit position for RecordVisible is 8 (zero-based index).
SELECT * FROM Table Where IsBitSet([AccessRight], 8)

Public Function IsBitSet(ByVal lNumber As Long, ByVal lBitPosition As Long)

Dim bResult As Boolean
Dim dBitValue As Double

    dBitValue = (2 ^ lBitPosition)
    bResult = (lNumber And dBitValue) = dBitValue
    IsBitSet = bResult
End Function

In SQL Server, you can use the bitwise operator, but takes an Integer as the data type (it won't work with a double).  With a Long Integer, you can have 32 bit  positions.

SELECT * FROM Table WHERE (AccessRight & 256) = 256

(256 = 2 ^ 8) for the 9th bit position.
Dear taf,

In MS Access, implementation is NOT impossible but very insufficient.
Access limits text to be 255 chars. You can use a memo field for this but from my experience access does not handle this well.

(first, in order to access the field value, you must have a set table of metadata)
Variable <==> position pair table.
simply assume you have 300 boolean values, each of which represent wether to display the Nth column represented by Nth character.
having 101011 : columns to display: 1st, 3rd, 5th and 6th;

Table Name: DBOptions
Fields: OptionID, OptionValues

In MS Access:
  add a module.
 in this module add a function:
Function getValue(boolStr As String, position As Integer) As Boolean
    Select Case CInt(Mid(boolStr, position, 1))
        Case 0:
            getValue = False
        Case Else:
            getValue = True
    End Select
End Function

and then you can run a query like: SELECT DBOptions.OptionValues, getValue([optionvalues],1) AS Value1, getValue([optionvalues],2) AS Value2 FROM DBOptions;
note: VBA is 1 based .. not 0 based.

similarly in SQL Server, you can create a user defined function that takes 2 parameters and returns the value. let me know if you want me to write the UDF in SQL.

But I'd still recommend using 300+ fields to store the info. Better yet, in few of my programs where I had similar issues, I normalized it all.

Table: OptionTypes
Fields: OptionTypeID (numeric -- identitiy), OptionType (varchar)

Table: OptionValues
Fields: OptionValueID (numeric -- identitiy), OptionTypeID (numeric), OptionValue (varchar)

However, in your case since all values are bool, you can just use "bit" instead of varchar.

The problems with bitstring is that it's too cryptic and even the programmer himself can screw up accidently.

hope this helps
Bob LearnedCommented:
You could do what Micro$oft does with enumerations for MsgBox options, and store the bitflags as numbers that correspond to each bit position:

1, 2, 4, 8, 16, 32, 64, 128, 256...

Example:  00011 would be 24.

You could use boolean logic to create these values and determine the state of each bit position:

8 or 16 = 24
24 and 8 = 8

With the double variable, you could get a fairly large number of bit positions.

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Yes .. i'd've recommended that solution but having 300 bits would require storing a number of atleast 2.0370359763344860862684456884094e+90 . Furthermore this also complicated since at run time the user'd have to parse the bit string using bitwise OR operator. but for small set of numbers, i'd totally go with learnedone's solution.
tafAuthor Commented:

Thanks a lot for coming back with some information.

Just for the record, when I mentioned I was storing 300+ options, this was used for our application's options. Not a big deal as I used 13 x 32-bit (comma separate) variable stored in memo/nvarchar field and as the user logged in, I requested all the group the user was part of and merge these together. This was only done one so it did not require a huge amount of processing nor thinking!

As you mentioned, it can be messy, but we have provided a very easy to use UI which works quite well, so it is not to messy after all.

Anyway, back to what I want to achieve!

I want to assing access right on a record level which basically requires each record to have it's own set of options. i.e. view record, view files associated with record, allow change access right, allow delete, allow delete files associated with record, etc... There should not be more than 32 (certainly not more than 64).

So what I'd like to do is assign various access rights to various groups and assign a record to be visible (for example!) only to group 1 & group 2, but not to group 3.

Is there an easy way for me to have one table containing my record info i.e

RecordID - AccountNo - AccountName - InvoiceNo - InvoiceValue
1                 - 123          - Company Ltd  - 12345      - €124.55      

Then have another table containing the access right (assume that the second bit would be used for record visiblity)

RecordID  - GroupID - AccessRight
1                 - 1           - 0101000101 (325)
1                 - 2           - 0100001001 (265)
1                 - 3           - 0010000011 (131)

I guess looking at my table definitions, there reallly isn't anyway to provide a query that would allow me to exclude these at run time? or is there?

The only option I need to access straight away via an sql query is the "record visiblity" as the rest of the options could be used at a later stage once the records have been decided on.

Would something like this do:

RecordID - GroupID - RecordVisible - AccessRight
1                 - 1           - 1                   - 0101000101 (325)
1                 - 2           - 1                   - 0100001001 (265)
1                 - 3           - 0                   - 0010000011 (131)

The fact that the currently logged in user could be part of x (3 for example) groups means that there really isn't a way to exclude this via an sql statement! Please tell me I'm wrong and/or my design is incorrect!

If you have any better suggestions please let me know as I'd really like to provide record level security to my application.

My current solution would be to do a regular sql query including all records and checking the access right when loaded up into my grid. Not the best, but at least I know this would work.

Many thanks once again to all of you for coming back with suggestions!

Bob LearnedCommented:
What are you using for DBMS (Access, SQL, Oracle)?

tafAuthor Commented:
I need to have solution for at least the following DBMS:



Bob LearnedCommented:
With Access you could generate an SQL, given the fact that read rights were in bit 4:

SELECT * FROM Table WHERE Mid(Field, 4, 1) = '1'.

With SQL Server, it would be:

SELECT * FROM Table WHERE Substring(Field, 4, 1) = '1'.
one note though .. in my previous comment i suggested the same.. but one advantage of having these functions in a module or a UDF is that they're precompiled -- so the SQL compiler/parser does not have to recompile it.

I posted how you can achieve this in Access using module.

In sql server, you should use UDF -- user defined functions.
Here's an implementation in SQL server:

select dbo.getParamValue( 2, BitStingField) as Value from Test where TestID=15

create function getParamValue (@position integer, @string varchar(300))
returns bit
            declare @ret bit
            set @ret=0

            declare @temp varchar(1)
            set @temp = substring(@string, @position, 1)
            if (@temp is null)
                  set @ret=0
            else if (@temp = '1')
                  set @ret=1
                  set @ret=0
            return (@ret)

hope this helps
tafAuthor Commented:
Hi 'TheLearnedOne',

This is not exactly what I want as the value will not be stored as a string in the format '010001011', but as decimal value representing this binary value.

Also who could I handle this via having multiple rows each with containing its own decimal value representing a binary list?

Again, I'm open to suggestion if you have done something similar in the past i.e. record security level?

What about my previous suggestion?


RecordID - GroupID - RecordVisible - AccessRight
1                 - 1           - 1                   - 0101000101 (325)
1                 - 2           - 1                   - 0100001001 (265)
1                 - 3           - 0                   - 0010000011 (131)

Would it be possible to do a join query and use the RecordVisible but based only on the groups the user is part of?


tafAuthor Commented:
Thanks for feeback! Will give it a shot soon! Will assign points for now as I'm just starting to info on various issues I want to tackle! May come back to it at a later stage.

I'll post back info to let you know how I get on!

Many thanks once again!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.