Re: convert help

2006-01-21 Thread Gleb Paharenko
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

2006-01-20 Thread David Godsey
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

2006-01-20 Thread Gleb Paharenko
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

2006-01-19 Thread David Godsey
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

2006-01-19 Thread Peter Brawley




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

2006-01-19 Thread Gordon Bruce
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

2006-01-19 Thread David Godsey

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]