(Solution to) SQL Bit Testing without UDFs

Hi all --

I developed this little routine to test an individual bit's status in an
integer column. I don't think you can test for a bit not being set, but
that would be easily added with a small modification to the code.

Some examples:

// Selects all PEOPLE with TYPE bit 0 set to '1'
MyQuery.SQL.Add('select * from PEOPLE where ' + SQLBitTest(0, 'TYPE'));

// Selects all PEOPLE with TYPE bit 0 set to '1' and bit 7 set to '1'
MyQuery.SQL.Add('select * from PEOPLE where ' + SQLBitTest(0, 'TYPE'));
MyQuery.SQL.Add(' and ' + SQLBitTest(7, 'TYPE'));

Please send any modifications or suggestions to me. Enjoy.

Kevin Herrmann
Director, Software Development
Enigma Medical Solutions, LLC

---snip for code
function SQLBitTest(const pBit : integer; const iField : string) : string;
{ Returns SQL string which is boolean test for bit 'pBit' being set in
  integer 'iField' field. Returns equivalent of 'False' for out-of-range
  'pBit's

  * May only work with Interbase

Quote
}

var
  lValue : longint;
begin
  lValue := iPower(2, pBit);
  result := '';

  if (pBit >= 0) and (pBit <= 31) then
    result := '(((' + iField + ' - ' + intToStr(lValue) + ') >= 0) and ' +
        '(cast ((' + iField + '/' + intToStr(lValue) + ') - .5 as integer)
<> ' +
        '2 * cast (' + iField + '/' + intToStr(lValue*2) + ' as integer)))'
  else
    result := '(0 = 1)';
end;
---end

And here is iPower:

---snip for code
function iPower(pBase : integer; pExp : integer) : longint;
{ !! This is an "integer" Power function -- negative numbers
  do not behave correctly (fractions); instead we return zero.

Quote
}

var
  i : integer;
begin
  result := 1;

  If (pExp = 0) then
    result := 1
  else if (pExp > 0) then
    for i := 1 to pExp do result := result * pBase
  else
    result := 0;
end;
---end