CODEWITHSUNDEEP
sqlstringbit-manipulationbinary-data
Glen Fletcher
Glen Fletcher

Reputation: 654

bitwise XOR, on fixed length binary string (240 Byte) in SQL

I'm storing a 240 Byte (1920 Bit) binary string in a database (currently MySQL however I want to remain database independent).

I need to do the following:

UPDATE `my_table` SET `data` = `data` ^ 'new_data' WHERE `id` = 'field_id'

I believe SQL syntax bit-wise XOR only works on values up to 6b bits.

I'm storing the data using hex encoding in a text field, however I can change this to a blob field holding binary data if necessary.

Currently I'm loading the field with PHP. Then I am doing the XOR and writing it back.

However this field is global to all scripts and this method risks the following:

Script X Loads Data0
Script Y Loads Data0
Script X Generates Data1 = Data0 ^ new_data1
Script Y Generates Data2 = Data0 ^ new_data2
Script X writes Data1
Script Y writes Data2 Error: dosen't contain new_data1

If this was done with SQL statements it would be the following: 

UPDATE `my_table` SET `data` = `data` ^ 'new_data1' WHERE `id` = 'field_id'
UPDATE `my_table` SET `data` = `data` ^ 'new_data2' WHERE `id` = 'field_id'

The database would execute the queries sequentially there by avoiding the error indicated above

If I use 64bit integers to store this data, then I would have to break it up in to 30 separate values due to the size. This option isn't reasonable.

Does anyone know of a database independent manner of achieving this?

Upvotes: 4

Views: 1099

Answers (1)

Tim Child
Tim Child

Reputation: 3012

Consider writing a C language User Defined Function (UDF) to do this operation. C UDF's are supported in many databases MySQL, PostgreSQL, Oracle, ....

They are fast and integrate well into SQL, as they are called like any other stored proc. Unfortunately the creation syntax and binding API all differ for each database.

Here is documentation for MySQL

http://dev.mysql.com/doc/refman/5.0/en/udf-compiling.html

and a Codeproject example for MySQL

http://www.codeproject.com/Articles/15643/MySQL-User-Defined-Functions

and PostgreSQL docs http://www.postgresql.org/docs/9.2/static/xfunc-c.html

there many examples of C UDF's in the PostgreSQL source. http://doxygen.postgresql.org/

PostgreSQL takes UDF's one step further and allows to to create C based User Define Types and Operators.

Upvotes: 1

Related Questions