Thank you. I tried this outside of the procedure and it works. However In
MYSQL 5 I get:
ERROR 1314 (0A000): PREPARE is not allowed in stored procedures
Is there a way without needing to use prepare?
Any idea why CAST(fdata AS UNSIGNED) doesn't work?
David Godsey
> Hello.
>
> You can use this technique:
> drop procedure if exists test20;
> DELIMITER $$
> create procedure test20()
> BEGIN
> DECLARE fdata BLOB;
> DECLARE foffset INT UNSIGNED;
> DECLARE flength INT UNSIGNED;
> DECLARE tmp_int BIGINT UNSIGNED;
>
> SELECT 0xABCDEF0123456789 INTO fdata;
> SELECT 14 INTO foffset;
> SELECT 7 INTO flength;
>
> SELECT SUBSTR(BINARY(fdata),
> FLOOR(foffset/8)+1,
> CEIL((flength + (foffset %8 ))%8))
> INTO fdata;
>
> SELECT HEX(fdata);
> set @a=concat('0x',hex(fdata));
> drop temporary table if exists ta;
> create temporary table ta(a bigint);
> set @s=concat('insert into ta set a=(',@a,'+0)');
> select @s;
> prepare st1 from @s;
> execute st1 ;
> deallocate prepare st1;
> select @b:=a from ta;
> select @b into tmp_int;
> drop temporary table ta;
> -- SELECT CONVERT(fdata,BIGINT) INTO tmp_int;
> SELECT HEX(tmp_int);
> END
> $$
> DELIMITER ;
> call test20();
>
> [EMAIL PROTECTED] mysql-debug-5.0.18-linux-i686-glibc23]$ lmysql <res
> HEX(fdata)
> CDEF012345
> @s
> insert into ta set a=(0xCDEF012345+0)
> @b:=a
> 884478124869
> HEX(tmp_int)
> CDEF012345
>
>
>
>
> David Godsey wrote:
>> I am trying to convert binary data to a bigint so I can do bitwise
>> operations on the data, and I'm having trouble doing it.
>>
>> I noticed that if I have binary data and I:
>> select data>>1; I get 0 (not what I'm expecting).
>>
>> Here is a test procedure I wrote:
>>
>> create procedure test20 ()
>> BEGIN
>> DECLARE fdata BLOB;
>> DECLARE foffset INT UNSIGNED;
>> DECLARE flength INT UNSIGNED;
>> DECLARE tmp_int BIGINT UNSIGNED;
>>
>> SELECT 0xABCDEF0123456789 INTO fdata;
>> SELECT 14 INTO foffset;
>> SELECT 7 INTO flength;
>>
>> SELECT SUBSTR(BINARY(fdata),
>> FLOOR(foffset/8)+1,
>> CEIL((flength + (foffset %8 ))%8))
>> INTO fdata;
>>
>> SELECT HEX(fdata);
>> SELECT CONVERT(fdata,BIGINT) INTO tmp_int;
>> SELECT HEX(tmp_int);
>> END
>> The last two selects are added to show what I would like to do, but have
>> not been able to get it to work.
>>
>> Any help would be great. Thanks in advance.
>>
>> Accomplishing the impossible means only that the boss will add it to
>> your
>> regular duties.
>>
>> David Godsey
>>
>> Accomplishing the impossible means only that the boss will add it to
>> your
>> regular duties.
>>
>> David Godsey
>>
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Gleb Paharenko
> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
> <___/ www.mysql.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
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]
