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.

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

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

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.

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

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

Bob

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

- ORACLE
- MYSQL
- SQL SERVER
- ACCESS

Thanks.

Thierry

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
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

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.

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