Re: Is there anyway to return an array?
So, in theory this should be pretty straight forward to do right? Well
I'm new to UDF's, so how mysql is passing the data to the UDF is a bit of
a mystery. I'm hoping someone can help me understand this.
I'm selecting data from a BLOB field like this:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
You can see that I'm only taking a portion of the string, but it is still
in raw form.
Now I would like to pass it to my UDF function called toDoubleArray, to
convert each 8 byte section to a double.
I call the function like this: (you can ignore conv_param)
SELECT toDoubleArray(fdata,%1.3E,conv_param) INTO fdata_string;
Well, I get a Lost Connection when the function is called.
Here is the UDF:
my_bool toDoubleArray_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
if (args-arg_count != 3)
{
strcpy(message,Wrong arguments to toDouble: should be
toDoubleArray(blob));
return 1;
}
initid-max_length = strlen(args-args[0])/8 * 128;
return 0;
}
char * toDoubleArray(UDF_INIT *initid, UDF_ARGS *args, char *result,
unsigned long *length,char *is_null,char *error)
{
int curr_buf_ptr =0; /* current length of FloatString */
char * data = args-args[0]; /* just to make it easier to reference the
string */
char * f = args-args[1]; /* get the format string */
char format[15];
sprintf(format,%s,,f); /* put a comma at the end of format for CSV
format */
for(int i=0;istrlen(data);i +=8){
sprintf(result + curr_buf_ptr,format,*((double *)(data +i)));
curr_buf_ptr = strlen(result);
}
result[curr_buf_ptr -1] = '\0';
*length = strlen(result);
return result;
}
This should return a comma delimited list of double values in a string
format (ascii representation).
Like I said, I'm new to UDF's so it is likely I'm not aware of conventions
to follow that are well known to others.
Any help would be great.
David Godsey
David Godsey wrote:
I know, I know, sounds like something that should be done in the
presentation layer, howerver if possible, I would like to provide
common
data presentation to multiple presentation layers (written in different
languages).
So is there anyway to return an array in mysql?
Your aware your doing something stupid and want to do it anyway :-(
Why not return the values from your user defined mysql function as a
(properly quoted) ,comma seperated list. Since almost every application
language now has a standard csv file handling library it should be easy
to use across diverse display technologies.
Urrgh
Nigel
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: Is there anyway to return an array?
Never mind, I was being dumb. I wasn't using the args-lengths array for
binary data, so it was dying on a strlen(args-args[0]) which makes sense.
Thanks anyway.
David Godsey
So, in theory this should be pretty straight forward to do right? Well
I'm new to UDF's, so how mysql is passing the data to the UDF is a bit of
a mystery. I'm hoping someone can help me understand this.
I'm selecting data from a BLOB field like this:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
You can see that I'm only taking a portion of the string, but it is still
in raw form.
Now I would like to pass it to my UDF function called toDoubleArray, to
convert each 8 byte section to a double.
I call the function like this: (you can ignore conv_param)
SELECT toDoubleArray(fdata,%1.3E,conv_param) INTO fdata_string;
Well, I get a Lost Connection when the function is called.
Here is the UDF:
my_bool toDoubleArray_init(UDF_INIT *initid, UDF_ARGS *args, char
*message)
{
if (args-arg_count != 3)
{
strcpy(message,Wrong arguments to toDouble: should be
toDoubleArray(blob));
return 1;
}
initid-max_length = strlen(args-args[0])/8 * 128;
return 0;
}
char * toDoubleArray(UDF_INIT *initid, UDF_ARGS *args, char *result,
unsigned long *length,char *is_null,char *error)
{
int curr_buf_ptr =0; /* current length of FloatString */
char * data = args-args[0]; /* just to make it easier to reference the
string */
char * f = args-args[1]; /* get the format string */
char format[15];
sprintf(format,%s,,f); /* put a comma at the end of format for CSV
format */
for(int i=0;istrlen(data);i +=8){
sprintf(result + curr_buf_ptr,format,*((double *)(data +i)));
curr_buf_ptr = strlen(result);
}
result[curr_buf_ptr -1] = '\0';
*length = strlen(result);
return result;
}
This should return a comma delimited list of double values in a string
format (ascii representation).
Like I said, I'm new to UDF's so it is likely I'm not aware of conventions
to follow that are well known to others.
Any help would be great.
David Godsey
David Godsey wrote:
I know, I know, sounds like something that should be done in the
presentation layer, howerver if possible, I would like to provide
common
data presentation to multiple presentation layers (written in
different
languages).
So is there anyway to return an array in mysql?
Your aware your doing something stupid and want to do it anyway :-(
Why not return the values from your user defined mysql function as a
(properly quoted) ,comma seperated list. Since almost every application
language now has a standard csv file handling library it should be easy
to use across diverse display technologies.
Urrgh
Nigel
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: Is there anyway to return an array?
Well, I'm doing something stupid because that is what the bosses want. I appreciate the suggestions, I think the CSV string format is the way to go. David Godsey David Godsey wrote: I know, I know, sounds like something that should be done in the presentation layer, howerver if possible, I would like to provide common data presentation to multiple presentation layers (written in different languages). So is there anyway to return an array in mysql? Your aware your doing something stupid and want to do it anyway :-( Why not return the values from your user defined mysql function as a (properly quoted) ,comma seperated list. Since almost every application language now has a standard csv file handling library it should be easy to use across diverse display technologies. Urrgh Nigel 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]
Is there anyway to return an array?
This is not a typically thing that anyone would expect Mysql to do, however with the application I am writing it would be helpful. Here goes: The application stores raw data from a satellite, along with a configuration of how to read the data. So of the data types are Int Arrays and Float arrays. Each can have conversion parameters to apply to the data. So I have to loop through this raw data, converting to the correct type, and applying some convertion parameter (say multiply by .26 for a float). So I will pass a function (a UDF function) a binary string, perform some conversion, then I would like to return it as an array. I know, I know, sounds like something that should be done in the presentation layer, howerver if possible, I would like to provide common data presentation to multiple presentation layers (written in different languages). So is there anyway to return an array in mysql? 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]
UDF help, convert BLOB to BIGINT
I'm in the process of writing my first UDF and would appreciate some help.
I am pulling data from a table like:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
frame_data is type BLOB. It is raw data collected. The substr will get
the specific bytes I'm interested in. What I need to do, is if the data
is = 8bytes, convert it to a BIGINT, so I can do some masking on the
data.
So I am writing a UDF to do the job, but I am apparently unfamiliar with
the Mysql data types and how I can convert them.
In a procedure.
DECLARE fdata_bigint BIGINT UNSIGNED;
SELECT BlobToInt(binary(fdata)) INTO fdata_bigint;
my_bool BlobToInt_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
if (args-arg_count != 1)
{
strcpy(message,Wrong arguments to BlobToInt; should be
BlobToInt(blob));
return 1;
}
return 0;
}
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char
*error )
{
longlong tmplong = *((longlong*)args-args[0]);
return tmplong;
}
I guess I was just assuming I could just cast the data as the type I want,
but that doesn't seem to work. The function returns a 0.
Any help would be appreciated.
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: UDF help, convert BLOB to BIGINT
Just figured it out without a UDF(not documented anywhere that I found).
SELECT conv(hex(fdata),16,10) INTO fdata_bigint;
So a double conversion seems to work for me.
You solution looks like it will work, but since I was able to get it to
work without a UDF, I'm not going to test it out. Thanks.
David Godsey
David Godsey [EMAIL PROTECTED] wrote on 03/22/2006 01:21:07 PM:
I'm in the process of writing my first UDF and would appreciate some
help.
I am pulling data from a table like:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
frame_data is type BLOB. It is raw data collected. The substr will get
the specific bytes I'm interested in. What I need to do, is if the data
is = 8bytes, convert it to a BIGINT, so I can do some masking on the
data.
So I am writing a UDF to do the job, but I am apparently unfamiliar with
the Mysql data types and how I can convert them.
In a procedure.
DECLARE fdata_bigint BIGINT UNSIGNED;
SELECT BlobToInt(binary(fdata)) INTO fdata_bigint;
my_bool BlobToInt_init( UDF_INIT* initid, UDF_ARGS* args, char* message
)
{
if (args-arg_count != 1)
{
strcpy(message,Wrong arguments to BlobToInt; should be
BlobToInt(blob));
return 1;
}
return 0;
}
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null,
char
*error )
{
longlong tmplong = *((longlong*)args-args[0]);
return tmplong;
}
I guess I was just assuming I could just cast the data as the type I
want,
but that doesn't seem to work. The function returns a 0.
Any help would be appreciated.
Accomplishing the impossible means only that the boss will add it to
your
regular duties.
David Godsey
C is not my strongest language but aren't you getting a null-terminated
string as args[0] ? What if you allocated a longlong and byte-copied the
bytes from args[0] into your longlong? Maybe something like...
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char
*error )
{
char idx, *cArg
longlong tmplong, *plonglong ;
plonglong = tmplong;
cArg = (args-args[0]);
for(idx=0;idx8;idx++) {
plonglong[idx]=cArg[idx];
}
return tmplong;
}
Again, I strongly stress that C/C++ is not my best language (I don't use
it nearly enough) but I think you can see what I was trying to do. Other
options: memcpy(), strcpy(), strncpy() etc
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
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]
Byte Swaping (Re Post)
Well, just thought I'd try one more time because I didn't get an answer to my question last time. So what I have is a random data stream that is sent in raw form, and based on some data definition, I can assemble with the correct data types and such. One of my requirements is that I have to store the data in raw form, and when I pull the data out, it displays based on the configuration (with the correct data types and such). So floats and doubles are IEEE standards so I don't have to worry about those, however with integer types, I may need to do some byte swapping (because this data can come from variouse systems that could be either big or little endian). So I am singling out the data I need, but now I need to add the ability to byte swap the data. Keep in mind that it would be best if I can do this in SQL so that it is portable. I realize that it can easily be done in C, but that makes my code less portable (which is also a requirement, to have it portable that is). So does anybody know of a MySQL function that is already implemented to do byte swapping? or know of a way to implement this in SQL? If not, is my only other option to write a UDF? Thanks for any help. 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]
Byte Swapping
This might be an unusual question because mysql always stores in little endian for all platforms (if I'm not mistaken). However, I am receiving data from several different platforms (custom boards with various processors) in raw form. I will have stored as a configuration option what endianess it is. So what I need to do is write a procdure, or use a mysql function (if there is one available) to byte swap the data (for integer types). I'm really just hoping someone has done this, or it is implemented as a mysql function and I just wasn't able to find it. Any help will be appreciated. Thanks. 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: Byte Swapping
If I do it in C I need to do a user-defined function correct?
Anyway, I am infact storing the data raw. Unfortunately byte swapping
before is not an option because I am getting a stream of raw data and
storing it in a blob field. Then based on some configuration, I pull
parts of the data out of the BLOB, and do conversions on it. So it a
specific element that I am looking for is defined as and Integer, I query
the BLOB data, do a substring (based on some offset and length) then
perform some conversion (Floats, Doubles, Signed, Unsigned).
You would think, hey why don't you break the data out and store it in the
correct form to make your life easier? Well, I have to preserve the data
in Raw form, and also, the definitions of the data change (base on some
other engineers decisions). So it isn't practical nor desirable to
convert the data before I put it in the database.
What I am really looking for (I know byte swapping in C is easy) is a
procedure or a Mysql function that does this. If I do a UDF then I have
to compile it for several platforms, because this is also supposed to be
very portable. If it was completely up to me, it wouldn't be this way,
but alas it is not.
David Godsey
Hi David,
This might be an unusual question because mysql always stores in little
endian for all platforms (if I'm not mistaken). However, I am receiving
data from several different platforms (custom boards with various
processors) in raw form. I will have stored as a configuration option
what endianess it is.
So what I need to do is write a procdure, or use a mysql function (if
there is one available) to byte swap the data (for integer types). I'm
really just hoping someone has done this, or it is implemented as a
mysql
function and I just wasn't able to find it.
This seems very strange that you're storing it in raw form. Are you
sure that you actually are?
Anyway, swapping between big/little endian is not difficult in C.
The code is all here:
http://jcole.us/svn/libmygis/mygis.h
e.g.:
#define SWAP(x, y) { (x) ^= (y) ^= (x) ^= (y); }
#define MYGIS_SWAP4(pos) { \
SWAP(((byte *)(pos))[0], ((byte *)(pos))[3]); \
SWAP(((byte *)(pos))[1], ((byte *)(pos))[2]); \
}
That's enough to swap 32-bits (4-bytes) of whatever is stored at any
pointer address.
I would encourage that you:
a. make sure you really are storing the data raw -- it seems very odd
b. stop storing the data raw... do the swap before storing
Can you give more details about what you're doing?
Regards,
Jeremy
--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104
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: Error 2016 and 2013
George Law gave me the answer: http://bugs.mysql.com/bug.php?id=14993 I had read the link you suggested, however it didn't really explain why I was getting the error. Thanks. David Godsey Hello. See: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html David Godsey wrote: nativecode=2013 ** Lost connection to MySQL server during query I think 2016 is Lost database connection What I am doing is I have a PHP class object that calls a stored procedure. I don't think I'm hitting any timeouts because it happens in less than a second. So here is my stored procedure: create procedure getElement (IN n VARCHAR(255),IN ptime DOUBLE) BEGIN DECLARE mfid INT UNSIGNED; DECLARE pid INT UNSIGNED; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE vid INT UNSIGNED; DECLARE rfid INT UNSIGNED; DECLARE tpid INT UNSIGNED; DECLARE fdata BLOB; DECLARE fdata_tmp BLOB; DECLARE fdata_bigint BIGINT UNSIGNED; DECLARE fdata_signed INT; DECLARE fdata_unsigned INT UNSIGNED; DECLARE fdata_float DOUBLE; DECLARE data_type VARCHAR(20); DECLARE byte_order VARCHAR(20); DECLARE conv_param VARCHAR(255); SELECT major_frame_desc_id, parent_id, frame_offset, frame_length, version_id, top_level_parent_id FROM MajorFrameDescription WHERE name=n INTO mfid,pid,foffset,flength,vid,tpid; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=NormalizedType INTO data_type; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ConvParams INTO conv_param; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ByteOrder INTO byte_order; SELECT MAX(raw_major_frame_id) FROM RawMajorFrames WHERE major_frame_desc_id=tpid INTO rfid; IF rfid 0 THEN SELECT payload_time, SUBSTR(BINARY(frame_data), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))/8)) FROM RawMajorFrames WHERE raw_major_frame_id=rfid INTO ptime,fdata; call toBigInt(fdata,fdata_bigint); IF (foffset %8) 0 THEN SET @mask_off=foffset%8; call mask_data(fdata,@mask_off,fdata_bigint); END IF; IF (8-((flength+(foffset%8)) %8)) 0 THEN SELECT (fdata_bigint (8-((flength+(foffset%8)) %8))) INTO fdata_bigint; END IF; CASE data_type WHEN Float THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN call polyConv(fdata_float,conv_param,fdata_float); END IF; SET @fdata_converted=fdata_float; WHEN Double THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN call polyConv(fdata_float,conv_param,fdata_float); END IF; SET @fdata_converted=fdata_float; WHEN Signed THEN call toSigned(fdata_bigint,fdata_signed); SET @fdata_converted=fdata_signed; WHEN Unsigned THEN SET @fdata_converted=fdata_bigint; ELSE SET @fdata_converted=HEX(fdata); END CASE; call enumConv(fdata_bigint,mfid,@fdata_enum); IF(!ISNULL(@fdata_enum)) THEN SET @[EMAIL PROTECTED]; END IF; SELECT mfid AS major_frame_desc_id, nAS name
Error 2016 and 2013
database connection loss? BTW: these are the first procedures I've writting so I'm a novice with stored procedures. 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]
Lost connection to MySQL server during query
=mfid AND value=fdata INTO fdata_converted; END 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: Lost connection to MySQL server during query
Thanks for the reply. I'm not using persistend connections though. It appears that it looses the connection in the middle of the query or in other words, before the procedure returns. So that means I not getting the data I need. So for debug purposes, are you saying to do a check status from PHP or in the procedure? From the procedure it wouldn't do any good right? From PHP it would be after I didn't get the data, so I would have to reconnect and rerun the query. That won't really work for me either. David Godsey David, Are you using persistent connections? Sounds like perhaps a persistent connection is timing out. Maybe a quick work around would be to call a check status routine (ie - do a show status), just to see if the connection is still there. If this fails, just do a mysql_connect... Before continuing. -- George -Original Message- From: David Godsey [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 4:09 PM To: mysql@lists.mysql.com Subject: Lost connection to MySQL server during query I am getting this error when connecting to mysql with PHP: Lost connection to MySQL server during query This happens only when I use this procedure, but it doesn't necessarily fail when this procedure is called. The error will happen frequently, however it is not consistent. This is my first procedure I've written, so I'm sure I've done something wrong here. I assume the error message means I'm hitting some kind of timeout? Any ideas would be welcome. Thanks. create procedure getElement (IN n VARCHAR(255),IN ptime DOUBLE) BEGIN DECLARE mfid INT UNSIGNED; DECLARE pid INT UNSIGNED; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE vid INT UNSIGNED; DECLARE rfid INT UNSIGNED; DECLARE tpid INT UNSIGNED; DECLARE fdata BLOB; DECLARE fdata_tmp BLOB; DECLARE fdata_bigint BIGINT UNSIGNED; DECLARE fdata_signed INT; DECLARE fdata_unsigned INT UNSIGNED; DECLARE fdata_float DOUBLE; DECLARE data_type VARCHAR(20); DECLARE byte_order VARCHAR(20); DECLARE conv_param VARCHAR(255); SELECT major_frame_desc_id, parent_id, frame_offset, frame_length, version_id, top_level_parent_id FROM MajorFrameDescription WHERE name=n INTO mfid,pid,foffset,flength,vid,tpid; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=NormalizedType INTO data_type; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ConvParams INTO conv_param; SELECT attribute_value FROM MajorFrameAttributes WHERE major_frame_desc_id=mfid AND attribute_name=ByteOrder INTO byte_order; SELECT MAX(raw_major_frame_id) FROM RawMajorFrames WHERE major_frame_desc_id=tpid INTO rfid; IF rfid 0 THEN SELECT payload_time, SUBSTR(BINARY(frame_data), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))/8)) FROM RawMajorFrames WHERE raw_major_frame_id=rfid INTO ptime,fdata; call toBigInt(fdata,fdata_bigint); IF (foffset %8) 0 THEN SET @mask_off=foffset%8; call mask_data(fdata,@mask_off,fdata_bigint); END IF; IF (8-((flength+(foffset%8)) %8)) 0 THEN SELECT (fdata_bigint (8-((flength+(foffset%8)) %8))) INTO fdata_bigint; END IF; CASE data_type WHEN Float THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param)) THEN call polyConv(fdata_float,conv_param,fdata_float); END IF; SET @fdata_converted=fdata_float; WHEN Double THEN call toFloat(fdata_bigint,fdata_float); IF(!ISNULL(conv_param
BLOB to UNSIGNED, can you do it?
I posted this earlier, however it has a nasty SPAM tag attached to it, so I thought I would resend it. My end goal is to select arbitrarily large data from a BLOB column use SUBSTR to pull out certain pieces of data and do some bit shifting and masking on that data. I believe you can only do bit shifting and masking with integer types so I am trying to cast BLOB as UNSIGNED, but without luck (the data I SUBSTR out will fit into a BIGINT UNSIGNED). Here is a precedure I wrote to show the problem: create procedure test3 () BEGIN DECLARE fdata BLOB; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; SELECT 0xABCDEF0123456789ABCDEF123456789ABCDEF123456789 INTO fdata; SELECT 14 INTO foffset; SELECT 7 INTO flength; SELECT HEX(fdata); SELECT SUBSTR(BINARY(fdata), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))/8)) INTO fdata; SELECT HEX(fdata); SELECT CAST(fdata AS UNSIGNED); END Here are the results: mysql call test3()// ++ | HEX(fdata) | ++ | ABCDEF0123456789ABCDEF123456789ABCDEF123456789 | ++ 1 row in set (0.00 sec) ++ | HEX(fdata) | ++ | CDEF | ++ 1 row in set (0.00 sec) +-+ | CAST(fdata AS UNSIGNED) | +-+ | 0 | +-+ 1 row in set (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) mysql In another procedure I declaring a variable of type BIGINT UNSIGNED and SELECTING the substr data into that, but same result. Any help would be appreciated. I don't even know if what I am doing is possible, but I hope it is. The docs on the mysql website don't exclude BLOB data from CASTING to UNSIGNED, so I assume what I am doing should be possible. 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
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
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
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
