Performing Numeric Bitwise Comparisons in SQL and RPG

April 14, 2004 Michael Sansoterra

[The code for this article is available for download.]

In the iSeries world, there isn’t a high demand for propeller-heads to write software that does bit manipulation. I’ve used bit manipulation on the iSeries a handful of times–once while writing a BASE64 routine to create binary e-mail attachments, once while communicating with a shop-floor device, and a few of other times for doing things like fixing decimal data errors.

Each attempt to work with bitwise operations in RPG or SQL resulted in a new frustration. That’s because both environments do their bit manipulation and comparisons against strings, instead of numbers, which, in my experience, is the “normal” way of doing things. To further complicate things, RPG’s bit order is reversed from the “normal,” with bit “0” being the highest order bit, as illustrated here.

RPG Bit Order:

“Normal” Bit Order:

While RPG’s bit order makes sense when using string comparisons, generally the bit number’s value is supposed to correspond to a power of 2 (value = 2 ^ bit), and this scheme is ingrained in most programmers’ brains. On the rare occasion that I use string bit operations, I always struggle to relearn the scheme.

For those needing a refresher, here are the common bitwise operations:

Say, for example, you have a two-byte binary field (a.k.a. SMALLINT) called ProductData, and that each bit in the field has a certain meaning (I know this is anathema to good database design, in which every meaningful piece of information should have its own field). Say that bit 7 indicates that the product has been configured for use outside of the United States. Now you have a product on order that has a ProductData value of 129, and you want to find out if bit 7 is on, to see if it’s meant to be exported outside the United States. The And bitwise comparison can be used to determine if bit 7 is on:

               Decimal Value   Binary Value
                               (bits are ordered from 0 to 15, 
                               from right to left)

ProductData    129             00000000 10000001
Bit to test    128             00000000 10000000
AND                            =================
Result         128             00000000 10000000

The And operation compares each bit and sets the resulting bit to “true” only if both bits in the comparison are true.

My latest experience with bit operations resulted from the addition of a new binary field in a database table. The on or off setting of each bit in the field indicated certain information. Engineers were the bitheads behind this field’s content, and they had good reasons for using a binary field. Since each bit in this field had a special meaning, RPG and SQL needed easy access to the bit info. This project moved me to finally develop a few simple C functions to allow numeric bitwise processing.

Service program BitWiseC contains functions that do AND, OR and XOR bitwise comparisons and can be used with both RPG and SQL. As demonstrated in the header, using these routines in SQL is as easy as issuing Create Function statements to register them as user-defined functions.

Using the functions in RPG is as simple as coding the following prototypes and making sure the service program is available for binding at program creation time. (Note that C procedure names are case-sensitive):

 // Bitwise AND - Integers
DBAnd             PR            10I 0 ExtProc('band')
D Parm1                         10I 0 Const
D Parm2                         10I 0 Const
 // Bitwise OR  - Integers
DBOr              PR            10I 0 ExtProc('bor')
D Parm1                         10I 0 Const
D Parm2                         10I 0 Const
 // Bitwise XOR - Integers
DBXor             PR            10I 0 ExtProc('bxor')
D Parm1                         10I 0 Const
D Parm2                         10I 0 Const

 // Bitwise AND - Big Integers
DBigBAnd          PR            20I 0 ExtProc('bigband')
D Parm1                         20I 0 Const
D Parm2                         20I 0 Const
 // Bitwise OR  - Big Integers
DBigBOr           PR            20I 0 ExtProc('bigbor')
D Parm1                         20I 0 Const
D Parm2                         20I 0 Const
 // Bitwise XOR - Big Integers
DBigBXor          PR            20I 0 ExtProc('bigbxor')
D Parm1                         20I 0 Const
D Parm2                         20I 0 Const

When coding the prototypes in RPG, use “10I  0” to represent the C “int” data type, and use “20I  0” to represent the C “long long int” data type.

These operations can then be used as follows, and are similar to the way that bit comparisons are done in C, Java, and many versions of BASIC:

C/Free

 // Test if bit 7 is set in the product data field
 // Remember, bit 7's value is 128: 2^7=128
 If BAnd(ProductData:128)=128;

The equivalent SQL would look something like this:

Select *
  From ProductSales
 Where Band(ProductData,128)=128

If you’ve ever troubled with bit processing in RPG or SQL, hopefully these routines will provide an easier road to travel!

Special thanks to Matt Weiss for improving my C code.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. E-mail: msansoterra@silver-lake.com.

Editor’s Note: Barbara Morris, IBM Toronto Lab, RPG Compiler Development, noted the following in response to this article:

With V5R2, RPG directly supports bitwise comparisons with the %BITAND, %BITOR, %BITXOR, and %BITNOT functions. Further, these functions can receive string and numeric parameters so that the external C code is unnecessary. For example, the tip’s C subprocedure can be replaced with the following RPG code:

If %BITAND(ProductData:128)=128;

Or the RPG code can mix numeric and string data, as follows:

If %BITAND(ProductData:x'80')=x'80';