Logical operations on binary columns

[Former Member]

We need to store approx 200 bitfields so we thought we'd use a binary datatype.

We can set the bit fields easily using

    set bitfield = bitfield  | 1

    set bitfield = bitfield  | 2

    set bitfield = bitfield  | 4

However we don't want to hard-code these values we like to use a table like this

    update tableA

    set bitfield = bitfield | table.value 

    where ..... table.mask = "MASK1"

However, it seems there is a limitation where you can only OR/AND a binary datatype with an integer.

So the column table.value can only go up to 64 bits.

Why can't we use AND/OR betweem two binary columns ?

Is there some sort of work around ?

(Any chance of any changes in ASE ?)

Incidently MS-SQL suffers the same problem.

[former_member188958]

>Why can't we use AND/OR betweem two binary columns ?

Logical bitwise operations on the binary datatype simply hasn't been implemented.

There hasn't been much demand for it.

>Is there some sort of work around ?

You could use substring to process larger binary values 4 bytes as a time.

>(Any chance of any changes in ASE ?)

Changes are being made all the time.  If there was a strong business case for supporting bitwise operators on BINARY(), I don't see any reason the feature couldn't be implemented.

I should note that I don't recommend using bitwise operators that compare binary with int values.

The bitwise values are intended for use with integer datatypes.   Using bitwise operators with a mix of binary and int values works because the binary is implicitly converted to an int.  Such code may seem to work fine, but it isn't portable when migrating between big- and little-endian platforms.

Endianness - Wikipedia, the free encyclopedia

Ex: on Spar Solaris (big-endian),   0x00000001 & 1 will return 1 because integer 1 is represented as 0x00000001, but migrate to Windows and the bitwise comparison will return 0 rather than 1 because integer 1 is represented as 0x01000000 on little-endian systems.

-bret

[Former Member]

> Logical bitwise operations on the binary datatype simply hasn't been implemented.

> There hasn't been much demand for it.

Agreed - I wouldn't expect much demand - but a useful feature.

> >Is there some sort of work around ?

> You could use substring to process larger binary values 4 bytes as a time.

Thanks - That's what we've done.

> Changes are being made all the time.

I'd really like to see what's on the roadmap. Sybase 16 didn't have any features for us developers but I appreciate 16 was about internal changes. Hope we'll see some developer features in 16.1

> If there was a strong business case for supporting bitwise

> operators on BINARY(), I don't see any reason the feature couldn't be implemented.

I can't confess there's *strong* business case but there's is a case.

Thanks for the endianism - I suspected the endianism might an issue with portability. But we've moved off Sparc a couple of years ago and don't expect we'll be changing hardware.

[jayrijnbergen]

I think you mixed up bit and binary datatype.

Binary datatype is normally used for images, text, storing a pdf in your db, etc

If you want 1 column to store multiple values using bitmasks, than you can use an integer to do that.

e.g. see examples in system tables like sysdatabases..status (small int) and sysdatabases..status4 (int)

Sybooks: The bitwise operators are a Transact-SQL extension for use with integer type data.

So it looks like you've got to use multiple columns to achieve wat you want.

Why would you want to store 200 bitfields in 1 column?

[Former Member]

> I think you mixed up bit and binary datatype.

> Binary datatype is normally used for images, text, storing a pdf in your db, etc

I'm aware that's what they're usually used for (although I have thought varbinary would have been better)

but I need to store lots of bit fields and  was hoping I could use a binary datatype, rather than have status1, status2, status3, etc

> If you want 1 column to store multiple values using bitmasks, than you can use an integer to do that.

> e.g. see examples in system tables like sysdatabases..status (small int) and sysdatabases..status4 (int)

That's what we're using at the moment - if fact we're now using unisgned bigint - that gives 64 bits.

The fact that sys* tables have multiple status fields shows it would be useful here.

> Sybooks: The bitwise operators are a Transact-SQL extension for use with integer type data.

> So it looks like you've got to use multiple columns to achieve wat you want.

Yep - that's exactly what we've found.

It seems unfortunate to not allow bitwise operations on binary datatypes. Its not a common use of them I know

but I've worked at couple of places which would have benefited from this.

We've spent some time writing our own functions that implement bitwise operations on binary fields, but would have been better if these were build in.

> Why would you want to store 200 bitfields in 1 column?

For a similar reason as the sys* tables have multiple status columns - If binary columns allowed bitwise operations - they'd only need 1 status field.