I posted this earlier, however it has a nasty SPAM tag attached to it, so
I thought I would resend it.
My end goal is to select arbitrarily large data from a BLOB column use
SUBSTR to pull out certain pieces of data and do some bit shifting and
masking on that data. I believe you can only do bit shifting and masking
with integer types so I am trying to cast BLOB as UNSIGNED, but without
luck (the data I SUBSTR out will fit into a BIGINT UNSIGNED).
Here is a precedure I wrote to show the problem:
create procedure test3 ()
BEGIN
DECLARE fdata BLOB;
DECLARE foffset INT UNSIGNED;
DECLARE flength INT UNSIGNED;
SELECT 0xABCDEF0123456789ABCDEF123456789ABCDEF123456789 INTO fdata;
SELECT 14 INTO foffset;
SELECT 7 INTO flength;
SELECT HEX(fdata);
SELECT SUBSTR(BINARY(fdata),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
INTO fdata;
SELECT HEX(fdata);
SELECT CAST(fdata AS UNSIGNED);
END
Here are the results:
mysql> call test3()//
+------------------------------------------------+
| HEX(fdata) |
+------------------------------------------------+
| ABCDEF0123456789ABCDEF123456789ABCDEF123456789 |
+------------------------------------------------+
1 row in set (0.00 sec)
+------------+
| HEX(fdata) |
+------------+
| CDEF |
+------------+
1 row in set (0.00 sec)
+-------------------------+
| CAST(fdata AS UNSIGNED) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
In another procedure I declaring a variable of type BIGINT UNSIGNED and
SELECTING the substr data into that, but same result.
Any help would be appreciated. I don't even know if what I am doing is
possible, but I hope it is. The docs on the mysql website don't exclude
BLOB data from CASTING to UNSIGNED, so I assume what I am doing should be
possible.
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
