File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC 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
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