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