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:
Bit | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
Value | 128 | 64 | 32 | 16 | 8 | 4 | 2 | 1 |
“Normal” Bit Order:
Bit | 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
Value | 128 | 64 | 32 | 16 | 8 | 4 | 2 | 1 |
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:
Operation |
Truth Table for Bit Comparisons (True=on, False=off) |
Description |
And |
False, False = False False, True = False True, False = False True, True = True |
And requires both bits in a comparison to be true in order for the resulting bit to be true. When comparing a single byte or a series of bytes, And will compare each bit and set the corresponding bit in the result to true or false depending on the result of the comparison. |
Or |
False, False = False False, True = True True, False = True True, True = True |
Or requires either bit in a comparison to be true in order for the resulting bit to be true. When comparing a single byte or a series of bytes, Or will compare each bit and set the corresponding bit in the result to true or false depending on the result of the comparison. |
XOR |
False, False = False False, True = True True, False = True True, True = False |
Xor (exclusive Or) requires each set of bits in the comparison to have opposite values in order for the resulting bit to be true. When comparing a single byte or a series of bytes, Xor will compare each bit and set the corresponding bit in the result to true or false depending on the result of the comparison. |
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.
Please participate in our iSeries programming survey at http://www.surveymonkey.com/s.asp?u=74193419903 |
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';