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 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 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 data1; 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: 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 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 data1; 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]

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 data1; 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]

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 data1; 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]

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 data1; 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 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 data1; 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