Solved

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.

Posted on 2004-03-30
11
408 Views
Last Modified: 2006-11-17
Hi,

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.

Thierry
0
Comment
Question by:taf
  • 4
  • 4
  • 3
11 Comments
 
LVL 9

Expert Comment

by:malharone
Comment Utility
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
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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.

Bob
0
 
LVL 9

Expert Comment

by:malharone
Comment Utility
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.
0
 

Author Comment

by:taf
Comment Utility
Hi,

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!

Thierry
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
What are you using for DBMS (Access, SQL, Oracle)?

Bob
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:taf
Comment Utility
I need to have solution for at least the following DBMS:

- ORACLE
- MYSQL
- SQL SERVER
- ACCESS

Thanks.

Thierry
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
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'.
0
 
LVL 9

Expert Comment

by:malharone
Comment Utility
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
as
      begin
            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
            else
                  set @ret=0
            return (@ret)
      end
go



hope this helps
0
 

Author Comment

by:taf
Comment Utility
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?

i.e

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?

Thanks.

T.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:taf
Comment Utility
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!

Thierry
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now