bit comparing in sql

[simplyJ]

Is it possable to use bit comparing in SQL statements? If I use bit numbers(ie.. &H000001) for security permissions, how would I do a search for 2 different levels? I have about 5 different security settings that users can have. I need to do a search and retrieve anyone with any of these 5 settings. Any ideas for the sql?


I can find information about the basic comparisons and operations used in sql, but I don't see anything about bit comparison in sql.

here is an sql example

GROUP_MEMBER = &H000001

&quot;SELECT MAX(username) as username, MAX(fname) as fname, MAX(lname) as lname FROM (groupmembers gm left JOIN users us ON gm.gmUserID = us.dbid) WHERE gmGroupID = &quot; & groupID & &quot; AND ((gmStatus and &quot; & GROUP_MEMBER & &quot <> 0)&quot; & &quot; GROUP by gmStatus, gmStartDate&quot;

This syntax will run, but it returns the wrong results.

Does this work in a sql statement?
((gmStatus and &quot; & GROUP_MEMBER & &quot <> 0)

 

[MichaelRed]

&quot;&&quot; <> &quot;AND&quot;

&quot;&&quot; is concatenation, &quot;AND&quot; is comparison.



The current practice is to use the Integer (or Long) values, generally in a ENUM. It improves readability and save some younger folks from having to learn hex.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

Jet SQL offers no bitwise comparison that I know about. You can use AND to do a bitwise comparison in a VB function. Then call the function from the query.

Function CheckSecurity(val1, val2) As Boolean
CheckSecurity = (val1 And val2) <> 0
End Function

&quot;CheckSecurity(&quot; & GROUP_MEMBER & &quot;, gmStatus) = True)&quot;

You could use the following in a query without creating a VBA function if gmStatus is always a single bit - 1, 2, 4, etc. I found this code in an Access newsgroup. It was posted by George Shears.

([GROUP_MEMBER]-([GROUP_MEMBER] Mod [gmStatus])) Mod ([gmStatus]*2)=[gmStatus]

I also question the need for using this type security scheme in a relational database. Conceptually, it would be easier to understand a table driven security scheme for example. Just an opinion. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[simplyJ]

Here is an example of why I need this. (maybe you all can give suggestions)

When a user signs up to be a member he get a status of GROUP_MEMBER or &H00000010 or (16). When they become an admin there status increases to (GROUP_ADMIN OR GROUP_MEMBER)or (&H00000010 + &H00000020) or 48. Now if the admin is given the ability to approve messages their status goes up to (GROUP_ADMIN OR GROUP_MEMBER OR GROUP_ADMIN_APPMESSAGE), equal to 256 + 32 + 16 = 304.

The problem is now how can I search the DB for all the admins. The admins aren't all the same status. The admin in this example is a status of 304, but if I add another privelege to him, the status changes again.

example constants for priveleges:
CONST GROUP_MEMBER = &H00000020
CONST GROUP_ADMIN = &H00000040
CONST GROUP_OWNER = &H00000080
CONST GROUP_ADMIN_APPMESSAGE = &H00000100
CONST GROUP_ADMIN_APPMEMBER = &H00000200



How do I search and find all admins? Searching for all > x would work, but I am looking for a way that would let me add in other group constants later with no problem. If I had to add one in lower than the admins, I would be up a creet.

 

[tlbroadbent]

I understand the scheme. It is commonly used. I merely suggested an alternative database design. I'm not suggesting there is anything wrong with the scheme except it is harder to understand and use in this environment as you can see from the query difficulty.

You should be able to use the function I suggested in your queries. Add the function to a module and test it in a query to see if you can produce the results you desire. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[simplyJ]

I didn't understand your previous post. This makes more sense now. What I am having problems with is what part of your example actually goes into the SQL statement? I don't mean to be so slow, but I have never gotten into this advanced of SQL. Normally, it is just small select statements.

Would I put this into the SQL syntax?

&quot;CheckSecurity(&quot; & GROUP_MEMBER & &quot;, gmStatus) = True)&quot;

***OR***

([GROUP_MEMBER]-([GROUP_MEMBER] Mod [gmStatus])) Mod ([gmStatus]*2)=[gmStatus]

 

[tlbroadbent]

You can modify the original query as follows to utilize the function.
[tt]
SQL=&quot;SELECT MAX(username) as UsrName,&quot; & _
&quot; MAX(fname) as firstname,&quot; & _
&quot; MAX(lname) as lastname&quot; & _
&quot; FROM groupmembers gm&quot; & _
&quot; LEFT JOIN users us ON gm.gmUserID = us.dbid&quot; & _
&quot; WHERE gmGroupID = &quot; & groupID&quot; & _
&quot; AND CheckSecurity(&quot; & GROUP_MEMBER&quot; & _
&quot; , gmStatus) = True&quot; & _
&quot; GROUP by gmStatus, gmStartDate&quot;
[/tt] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[simplyJ]

I cannot get these to work. I have tried both the function and the other code and neither will work. When I use the function it gives me an error that says:

Undefined function 'CheckSecurity' in expression.
/groups/groupAMembers.asp, line 193

The function is written directly over the query, so I can't figure it out.


The other code gives me the following error:

Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
/groups/groupAMembers.asp, line 199

The query works without this addition, so I haven't misspelled a field, and I don't know what else it could be.
Here is an example of the code and the text from a response.write after the sql is made.

SQL = &quot;SELECT MAX(gmID) as gmID, MAX(gmUserID) as gmUserID,&quot; & _
&quot; MAX(gmGroupID) as gmGroupID, MAX(gmStartDate) as gmStartDate,&quot; & _
&quot; MAX(gmEmailPrime) as gmEmailPrime, MAX(gmEmailAlt) as gmEmailAlt,&quot; & _
&quot; MAX(gmPreferences) as gmPreferences, MAX(gmStatus) as gmStatus,&quot; & _
&quot; MAX(username) as username, MAX(fname) as fname, MAX(lname) as lname&quot; & _
&quot; FROM&quot; & _
&quot; (groupmembers gm left JOIN users us ON gm.gmUserID = us.dbid)&quot; & _
&quot; WHERE gmGroupID = &quot; & groupID & _
&quot; AND ([&quot; & GROUP_MEMBER & &quot;]-([&quot; & GROUP_MEMBER & &quot;] Mod [gmStatus])) Mod&quot; & _
&quot; ([gmStatus]*2)=[gmStatus]&quot;

SELECT MAX(gmID) as gmID, MAX(gmUserID) as gmUserID, MAX(gmGroupID) as gmGroupID, MAX(gmStartDate) as gmStartDate, MAX(gmEmailPrime) as gmEmailPrime, MAX(gmEmailAlt) as gmEmailAlt, MAX(gmPreferences) as gmPreferences, MAX(gmStatus) as gmStatus, MAX(username) as username, MAX(fname) as fname, MAX(lname) as lname FROM (groupmembers gm left JOIN users us ON gm.gmUserID = us.dbid) WHERE gmGroupID = 18 AND ([32]-([32] Mod [gmStatus])) Mod ([gmStatus]*2)=[gmStatus]

 

[tlbroadbent]

In the query that you posted, it appears that you need to remove the brackets around the GROUP_MEMBER variable.

You won't be able to use an Access function call in your ASP page. It will only work if you are executing the query in Access. Why not just return the value of gmStatus and do the bitwise compare in the ASP page. It should be very simple to code in VB script which has the same Bitwise function AND. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[simplyJ]

Here is a little about this project, if you are wandering about my methods...(if you have any suggestions feel free to suggest, this is my first system I have designed)

I am trying to copy the egroups.com system. This is bascially a system that allows groups(ie..Bible studies, churches, etc..) to create their own private/public message board for communication. Later I am going to incorporate this system with a listserv program.

When a user click on a page I have to search to find out information about the group, then I have to see what permission the user has for the group, then I have to find out if the user is an admin of the group. To reduce the number of searches in the DB I opted to denormalize the db structure and incorporate an admin field into the groupmember table, instead of creating a whole admin table. The group leader can created as many admin as they want and give them a wide range of permissions, this is where I user bit comparing.

This is an example of why I need to search with bits. Members can go in and look at all of the admins for the group. I can search for all members with a status over X amount, but the problem appears if I ever have to add permissions. I then have to recalucate every permission, if the added permission needs to go before the admin status.

ie..
1-banned
2-quit
4-member
8-admin
16-permission1
32-permission2

in this list i forgot kicked out, which is different than banned. To fix this I can't put it after permission2 because when I search for admin(numbers greater than 8) a kicked out member will appear. If I search by bits I can change the permissions at any time with out hurting anything.

I might be to worried about the future, but I am trying to make this very expandable.

please, give me your opinion. You can bash it all you want...that is how i will learn.