List:General Discussion« Previous MessageNext Message »
From:David Godsey Date:January 20 2006 4:03am
Subject:Re: [SPAM] - convert help - Bayesian Filter detected spam
View as plain text  
I forgot to do reply all, sorry peter for the duplicate:

Thank you for the responses to my question, however with a little poking
around after the suggestions, I still am unable to do the conversion.

mysql> select 0xABCDEF0123456789 into @fdata;
Query OK, 1 row affected (0.00 sec)

mysql> select hex(@fdata);
+------------------+
| hex(@fdata)      |
+------------------+
| ABCDEF0123456789 |
+------------------+
1 row in set (0.00 sec)

mysql> select cast(@fdata AS UNSIGNED);
+--------------------------+
| cast(@fdata AS UNSIGNED) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

Essentially what I am trying to accomplish is I have a BLOB column that
can have arbitrarily large data, and I am using SUBSTR to pull out
sections of it, and some of it needs to be masked and bit shifted.  I can
pull out the data I'm interested in, however to do the masking and bit
shifting it appears I need it to be an integer of some kind.  So I am
taking blob data and trying to convert it to an integer type (unsigned) so
I can do those bitwise operations.  So I am putting the data into a
variable like:
SELECT SUBSTR(BINARY(blob_col),offset,length)) into fdata; //example

This works fine.  I can view this data.  The problem I am having is I
can't convert it to UNSIGNED to do some further bitwise operations on the
data.

Thanks for any further help.

> Gordon,
>
>>...
>>SELECT CONVERT(fdata,BIGINT) INTO tmp_int;
>>SELECT HEX(tmp_int);
> ...
>
> Are you looking for...
>
> SELECT CAST(0xABCDEF0123456789 AS UNSIGNED);
> +--------------------------------------+
> | CAST(0xABCDEF0123456789 AS UNSIGNED) |
> +--------------------------------------+
> |                 12379813738877118345 |
> +--------------------------------------+
> SELECT HEX(12379813738877118345);
> +---------------------------+
> | HEX(12379813738877118345) |
> +---------------------------+
> | ABCDEF0123456789          |
> +---------------------------+
>
> PB
>
> -----
>
> Gordon Bruce wrote:
>> I just added a user variable @fdata to get visabilility outside of the
>> procedure and this is what I get.
>>
>> mysql> delimiter //
>> mysql> 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;
>>     ->   set @fdata:=fdata;
>>     -> END//
>> Query OK, 0 rows affected (0.00 sec)
>>
>> mysql>
>> mysql> delimiter ;
>> mysql>
>> mysql> call test20();
>> Query OK, 0 rows affected (0.00 sec)
>>
>> mysql>
>> mysql> select @fdata, hex(@fdata)
>>     ->
>>     -> ;
>> +--------+-------------+
>> | @fdata | hex(@fdata) |
>> +--------+-------------+
>> | ═∩☺#E  | CDEF012345  |
>> +--------+-------------+
>> 1 row in set (0.00 sec)
>>
>> -----Original Message-----
>> From: David Godsey [mailto:mysql@stripped]
>> Sent: Thursday, January 19, 2006 3:33 PM
>> To: mysql@stripped
>> Subject: [SPAM] - convert help - Bayesian Filter detected spam
>>
>>
>> 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
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> No virus found in this incoming message.
>> Checked by AVG Free Edition.
>> Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date:
>> 1/19/2006
>>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 1/19/2006
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1


Accomplishing the impossible means only that the boss will add it to your
regular duties.

David Godsey

Thread
RE: [SPAM] - convert help - Bayesian Filter detected spamGordon Bruce19 Jan
  • Re: [SPAM] - convert help - Bayesian Filter detected spamPeter Brawley20 Jan
    • Re: [SPAM] - convert help - Bayesian Filter detected spamDavid Godsey20 Jan