Re: convert help
Hello. > ERROR 1314 (0A000): PREPARE is not allowed in stored procedures PREPARE in the stored procedures should work in the latest release (5.0.18). David Godsey wrote: > 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? > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: convert help
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 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]
Re: convert help
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 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]
Re: [SPAM] - convert help - Bayesian Filter detected spam
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:[EMAIL PROTECTED] >> Sent: Thursday, January 19, 2006 3:33 PM >> To: mysql@lists.mysql.com >> 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); >> S
Re: [SPAM] - convert help - Bayesian Filter detected spam
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:[EMAIL PROTECTED]] Sent: Thursday, January 19, 2006 3:33 PM To: mysql@lists.mysql.com 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/[EMAIL PROTECTED]
RE: [SPAM] - convert help - Bayesian Filter detected spam
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:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 3:33 PM To: mysql@lists.mysql.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
convert help
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]