It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Bitwise use of SQL integer Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Bitwise use of SQL integer" Watch "Bitwise use of SQL integer" New topic

Bitwise use of SQL integer

Howard Watson
Ranch Hand

Joined: Jan 07, 2004
Posts: 56
Given JSP/servlet web-app:
Form with twelve checkboxes. I can store the state of each of the checkboxes as a bit set to 1 for box checked, in an SQL integer. I can retrieve the integer and loop through a shifted bit mask to reset the checkboxes in the form for editing.

The problem:
Ideally I'd like to perform a query like (SELECT (COUNT colBox) FROM tblBoxes WHERE colBox AND 3) would return a count of rows with bit 1 set in the integer.
The other choice is to select * colBox, && to a bitmask and count the good ones for 32 bitmasks thousands of times.

I'm sure there's an algorithm or query out there. Any ideas or references would be appreciated.
[ September 28, 2004: Message edited by: Howard Watson ]
Roberto Spier

Joined: Sep 13, 2004
Posts: 7

you haven't indicated what database you are using!

MySQL has a bitwise operator, and you'd do something like

SELECT * FROM table WHERE someBigIntField & 3;

Howard Watson
Ranch Hand

Joined: Jan 07, 2004
Posts: 56
Pervasive SQL 2000i. I think my "Ideally" is supported by some database vendors, and may not be supported by mine. I posted there too.
I agree. Here's the link:
subject: Bitwise use of SQL integer
jQuery in Action, 3rd edition