|
Home > Archive > MySQL ODBC Connector > January 2006 > Lost connection to MySQL server during query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Lost connection to MySQL server during query
|
|
| David Godsey 2006-01-24, 8:23 pm |
| 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 MajorFrameDescriptio
n
WHERE name=n
INTO mfid,pid,foffset,fle
ngth,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=r
fid
INTO ptime,fdata;
call toBigInt(fdata,fdata
_bigint);
IF (foffset %8) >0 THEN
SET @mask_off=foffset%8;
call mask_data(fdata,@mas
k_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_para
m)) THEN
call
polyConv(fdata_float
,conv_param,fdata_fl
oat);
END IF;
SET
@fdata_converted=fda
ta_float;
WHEN "Double"
THEN
call
toFloat(fdata_bigint
,fdata_float);
IF(!ISNULL(conv_para
m)) THEN
call
polyConv(fdata_float
,conv_param,fdata_fl
oat);
END IF;
SET
@fdata_converted=fda
ta_float;
WHEN "Signed"
THEN
call
toSigned(fdata_bigin
t,fdata_signed);
SET
@fdata_converted=fda
ta_signed;
WHEN "Unsigned"
THEN
SET
@fdata_converted=fda
ta_bigint;
ELSE
SET @fdata_converted=HEX
(fdata);
END CASE;
call enumConv(fdata_bigin
t,mfid,@fdata_enum);
IF(!ISNULL(@fdata_en
um)) THEN
SET @fdata_converted=@fd
ata_enum;
END IF;
SELECT
mfid AS major_frame_desc_id,
n AS name,
pid AS parent_id,
tpid AS top_level_parent_id,
rfid AS raw_major_frame_id,
foffset AS frame_offset,
flength AS frame_length,
vid AS version_id,
ptime AS payload_time,
HEX(fdata) AS raw_data,
@fdata_converted AS converted_data;
ELSE
SELECT rfid;
END IF;
END
Some procedures it uses are:
CREATE PROCEDURE toBigInt (IN fdata BLOB,OUT fdata_int BIGINT UNSIGNED)
BEGIN
SET @string_data=CONCAT(
'0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toin
t BIGINT
UNSIGNED);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_dat
a,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint from make_conversion into fdata_int;
END
$$
CREATE PROCEDURE toUnsigned (IN fdata BIGINT UNSIGNED,OUT fdata_int INT
UNSIGNED)
BEGIN
SET @string_data=CONCAT(
'0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toin
t INT UNSIGNED);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_dat
a,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint from make_conversion into fdata_int;
END
$$
CREATE PROCEDURE toSigned (IN fdata BIGINT UNSIGNED,OUT fdata_int INT)
BEGIN
SET @string_data=CONCAT(
'0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toin
t INT);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_dat
a,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint from make_conversion into fdata_int;
END
$$
CREATE PROCEDURE toFloat (IN fdata BIGINT UNSIGNED,OUT fdata_float DOUBLE)
BEGIN
SET @string_data=CONCAT(
'0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(tofl
oat DOUBLE);
SET @q=CONCAT('INSERT INTO make_conversion set
tofloat=(',@string_d
ata,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT tofloat from make_conversion into fdata_float;
END
$$
CREATE PROCEDURE shift_right(IN fdata_bigint BIGINT UNSIGNED,IN shift INT
UNSIGNED,OUT fdata_bigint2 BIGINT UNSIGNED)
BEGIN
SELECT fdata_bigint >> shift INTO fdata_bigint2;
END
$$
CREATE PROCEDURE mask_data(IN fdata BLOB,IN mask_off INT UNSIGNED,OUT
fdata_bigint2 BIGINT UNSIGNED)
BEGIN
DECLARE top_byte TINYBLOB;
DECLARE top_int BIGINT UNSIGNED;
SELECT SUBSTR(BINARY(fdata)
,1,1) INTO top_byte;
call toBigInt(top_byte,to
p_int);
SELECT ((top_int << mask_off) & 0xFF) >> mask_off INTO
top_int;
SET
@string_data=CONCAT(
'0x',HEX(top_int),HE
X(SUBSTR(BINARY(fdat
a),2)));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toin
t INT);
SET @q=CONCAT('INSERT INTO make_conversion set
toint=(',@string_dat
a,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint FROM make_conversion INTO fdata_bigint2;
END
$$
CREATE PROCEDURE polyConv(IN fdata DOUBLE,IN conv_param VARCHAR(255),OUT
fdata_converted DOUBLE)
BEGIN
DECLARE beginning VARCHAR(255);
DECLARE end_of VARCHAR(255);
DECLARE query VARCHAR(255);
SELECT SUBSTR(conv_param,1,
POSITION('x' in conv_param)-1)
INTO beginning;
SELECT SUBSTR(conv_param,PO
SITION('x' in conv_param)+1)
INTO end_of;
SET @string_query=CONCAT
("SELECT
" ,beginning,fdata,end
_of,"INTO
@fdata_converted");
PREPARE st1 FROM @string_query;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT @fdata_converted INTO fdata_converted;
END
$$
CREATE PROCEDURE enumConv(IN fdata INT UNSIGNED,mfid INT UNSIGNED,OUT
fdata_converted
VARCHAR(100))
BEGIN
SELECT name FROM EnumLiterals
WHERE major_frame_desc_id=
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/mysql? unsub...sie.nctu.edu.tw
| |
| George Law 2006-01-24, 8:23 pm |
| David,
Are you using persistent connections? Sounds like perhaps a persistent
connection is timing out. =20
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 & #91;mailto:mysql@god
seyfamily.com]=20
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 MajorFrameDescriptio
n
WHERE name=3Dn
INTO mfid,pid,foffset,fle
ngth,vid,tpid;
SELECT attribute_value FROM MajorFrameAttributes
WHERE major_frame_desc_id=
3Dmfid AND
attribute_name=3D"NormalizedType"
INTO data_type;
SELECT attribute_value FROM MajorFrameAttributes
WHERE major_frame_desc_id=
3Dmfid AND
attribute_name=3D"ConvParams"
INTO conv_param;
SELECT attribute_value FROM MajorFrameAttributes
WHERE major_frame_desc_id=
3Dmfid AND
attribute_name=3D"ByteOrder"
INTO byte_order;
SELECT MAX(raw_major_frame_
id)
FROM RawMajorFrames
WHERE major_frame_desc_id=
3Dtpid
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=3
Drfid
INTO ptime,fdata;
call toBigInt(fdata,fdata
_bigint);
IF (foffset %8) >0 THEN
SET @mask_off=3Dfoffset%
8;
call
mask_data(fdata,@mas
k_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_para
m))
THEN
call
polyConv(fdata_float
,conv_param,fdata_fl
oat);
END IF;
SET
@fdata_converted=3Df
data_float;
WHEN "Double"
THEN
call
toFloat(fdata_bigint
,fdata_float);
IF(!ISNULL(conv_para
m))
THEN
call
polyConv(fdata_float
,conv_param,fdata_fl
oat);
END IF;
SET
@fdata_converted=3Df
data_float;
WHEN "Signed"
THEN
call
toSigned(fdata_bigin
t,fdata_signed);
SET
@fdata_converted=3Df
data_signed;
WHEN "Unsigned"
THEN
SET
@fdata_converted=3Df
data_bigint;
ELSE
SET =
@fdata_converted=3DH
EX(fdata);
END CASE;
call enumConv(fdata_bigin
t,mfid,@fdata_enum);
IF(!ISNULL(@fdata_en
um)) THEN
SET @fdata_converted=3D@
fdata_enum;
END IF;
SELECT
mfid AS major_frame_desc_id,
n AS name,
pid AS parent_id,
tpid AS top_level_parent_id,
rfid AS raw_major_frame_id,
foffset AS frame_offset,
flength AS frame_length,
vid AS version_id,
ptime AS payload_time,
HEX(fdata) AS raw_data,
@fdata_converted AS converted_data;
ELSE
SELECT rfid;
END IF;
END
Some procedures it uses are:
CREATE PROCEDURE toBigInt (IN fdata BLOB,OUT fdata_int BIGINT UNSIGNED)
BEGIN
SET @string_data=3DCONCA
T('0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toin
t BIGINT
UNSIGNED);
SET @q=3DCONCAT('INSERT INTO make_conversion set
toint=3D(',@string_d
ata,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint from make_conversion into fdata_int;
END
$$
CREATE PROCEDURE toUnsigned (IN fdata BIGINT UNSIGNED,OUT fdata_int INT
UNSIGNED)
BEGIN
SET @string_data=3DCONCA
T('0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toin
t INT
UNSIGNED);
SET @q=3DCONCAT('INSERT INTO make_conversion set
toint=3D(',@string_d
ata,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint from make_conversion into fdata_int;
END
$$
CREATE PROCEDURE toSigned (IN fdata BIGINT UNSIGNED,OUT fdata_int INT)
BEGIN
SET @string_data=3DCONCA
T('0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toin
t INT);
SET @q=3DCONCAT('INSERT INTO make_conversion set
toint=3D(',@string_d
ata,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint from make_conversion into fdata_int;
END
$$
CREATE PROCEDURE toFloat (IN fdata BIGINT UNSIGNED,OUT fdata_float
DOUBLE)
BEGIN
SET @string_data=3DCONCA
T('0x',HEX(fdata));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(tofl
oat DOUBLE);
SET @q=3DCONCAT('INSERT INTO make_conversion set
tofloat=3D(',@string
_data,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT tofloat from make_conversion into fdata_float;
END
$$
CREATE PROCEDURE shift_right(IN fdata_bigint BIGINT UNSIGNED,IN shift
INT UNSIGNED,OUT fdata_bigint2 BIGINT UNSIGNED)
BEGIN
SELECT fdata_bigint >> shift INTO fdata_bigint2;
END
$$
CREATE PROCEDURE mask_data(IN fdata BLOB,IN mask_off INT UNSIGNED,OUT
fdata_bigint2 BIGINT UNSIGNED)
BEGIN
DECLARE top_byte TINYBLOB;
DECLARE top_int BIGINT UNSIGNED;
SELECT SUBSTR(BINARY(fdata)
,1,1) INTO top_byte;
call toBigInt(top_byte,to
p_int);
SELECT ((top_int << mask_off) & 0xFF) >> mask_off INTO
top_int;
SET
@string_data=3DCONCA
T('0x',HEX(top_int),
HEX(SUBSTR(BINARY(fd
ata),2)));
DROP TEMPORARY TABLE IF EXISTS make_conversion;
CREATE TEMPORARY TABLE make_conversion(toin
t INT);
SET @q=3DCONCAT('INSERT INTO make_conversion set
toint=3D(',@string_d
ata,'+0)');
PREPARE st1 from @q;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT toint FROM make_conversion INTO fdata_bigint2;
END
$$
CREATE PROCEDURE polyConv(IN fdata DOUBLE,IN conv_param VARCHAR(255),OUT
fdata_converted DOUBLE)
BEGIN
DECLARE beginning VARCHAR(255);
DECLARE end_of VARCHAR(255);
DECLARE query VARCHAR(255);
SELECT SUBSTR(conv_param,1,
POSITION('x' in
conv_param)-1) INTO beginning;
SELECT SUBSTR(conv_param,PO
SITION('x' in conv_param)+1)
INTO end_of;
SET @string_query=3DCONC
AT("SELECT
" ,beginning,fdata,end
_of,"INTO
@fdata_converted");
PREPARE st1 FROM @string_query;
EXECUTE st1;
DEALLOCATE PREPARE st1;
SELECT @fdata_converted INTO fdata_converted;
END
$$
CREATE PROCEDURE enumConv(IN fdata INT UNSIGNED,mfid INT UNSIGNED,OUT
fdata_converted
VARCHAR(100))
BEGIN
SELECT name FROM EnumLiterals
WHERE major_frame_desc_id=
3Dmfid AND value=3Dfdata
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/mysql? unsub...h
ere.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| David Godsey 2006-01-24, 8:23 pm |
| 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 & #91;mailto:mysql@god
seyfamily.com]
> 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 MajorFrameDescriptio
n
> WHERE name=n
> INTO mfid,pid,foffset,fle
ngth,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=r
fid
> INTO ptime,fdata;
>
> call toBigInt(fdata,fdata
_bigint);
> IF (foffset %8) >0 THEN
> SET @mask_off=foffset%8;
> call
> mask_data(fdata,@mas
k_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_para
m))
> THEN
> call
> polyConv(fdata_float
,conv_param,fdata_fl
oat);
> END IF;
> SET
> @fdata_converted=fda
ta_float;
>
> WHEN "Double"
> THEN
> call
> toFloat(fdata_bigint
,fdata_float);
> IF(!ISNULL(conv_para
m))
> THEN
> call
> polyConv(fdata_float
,conv_param,fdata_fl
oat);
> END IF;
> SET
> @fdata_converted=fda
ta_float;
>
> WHEN "Signed"
> THEN
> call
> toSigned(fdata_bigin
t,fdata_signed);
> SET
> @fdata_converted=fda
ta_signed;
> WHEN "Unsigned"
> THEN
> SET
> @fdata_converted=fda
ta_bigint;
> ELSE
> SET @fdata_converted=HEX
(fdata);
> END CASE;
> call enumConv(fdata_bigin
t,mfid,@fdata_enum);
> IF(!ISNULL(@fdata_en
um)) THEN
> SET @fdata_converted=@fd
ata_enum;
> END IF;
>
> SELECT
> mfid AS major_frame_desc_id,
> n AS name,
> pid AS parent_id,
> tpid AS top_level_parent_id,
> rfid AS raw_major_frame_id,
> foffset AS frame_offset,
> flength AS frame_length,
> vid AS version_id,
> ptime AS payload_time,
> HEX(fdata) AS raw_data,
> @fdata_converted AS converted_data;
>
> ELSE
> SELECT rfid;
> END IF;
> END
>
>
> Some procedures it uses are:
> CREATE PROCEDURE toBigInt (IN fdata BLOB,OUT fdata_int BIGINT UNSIGNED)
> BEGIN
> SET @string_data=CONCAT(
'0x',HEX(fdata));
> DROP TEMPORARY TABLE IF EXISTS make_conversion;
> CREATE TEMPORARY TABLE make_conversion(toin
t BIGINT
> UNSIGNED);
> SET @q=CONCAT('INSERT INTO make_conversion set
> toint=(',@string_dat
a,'+0)');
> PREPARE st1 from @q;
> EXECUTE st1;
> DEALLOCATE PREPARE st1;
> SELECT toint from make_conversion into fdata_int;
> END
> $$
>
> CREATE PROCEDURE toUnsigned (IN fdata BIGINT UNSIGNED,OUT fdata_int INT
> UNSIGNED)
> BEGIN
> SET @string_data=CONCAT(
'0x',HEX(fdata));
> DROP TEMPORARY TABLE IF EXISTS make_conversion;
> CREATE TEMPORARY TABLE make_conversion(toin
t INT
> UNSIGNED);
> SET @q=CONCAT('INSERT INTO make_conversion set
> toint=(',@string_dat
a,'+0)');
> PREPARE st1 from @q;
> EXECUTE st1;
> DEALLOCATE PREPARE st1;
> SELECT toint from make_conversion into fdata_int;
> END
> $$
>
> CREATE PROCEDURE toSigned (IN fdata BIGINT UNSIGNED,OUT fdata_int INT)
> BEGIN
> SET @string_data=CONCAT(
'0x',HEX(fdata));
> DROP TEMPORARY TABLE IF EXISTS make_conversion;
> CREATE TEMPORARY TABLE make_conversion(toin
t INT);
> SET @q=CONCAT('INSERT INTO make_conversion set
> toint=(',@string_dat
a,'+0)');
> PREPARE st1 from @q;
> EXECUTE st1;
> DEALLOCATE PREPARE st1;
> SELECT toint from make_conversion into fdata_int;
> END
> $$
>
>
> CREATE PROCEDURE toFloat (IN fdata BIGINT UNSIGNED,OUT fdata_float
> DOUBLE)
> BEGIN
> SET @string_data=CONCAT(
'0x',HEX(fdata));
> DROP TEMPORARY TABLE IF EXISTS make_conversion;
> CREATE TEMPORARY TABLE make_conversion(tofl
oat DOUBLE);
> SET @q=CONCAT('INSERT INTO make_conversion set
> tofloat=(',@string_d
ata,'+0)');
> PREPARE st1 from @q;
> EXECUTE st1;
> DEALLOCATE PREPARE st1;
> SELECT tofloat from make_conversion into fdata_float;
> END
> $$
>
> CREATE PROCEDURE shift_right(IN fdata_bigint BIGINT UNSIGNED,IN shift
> INT UNSIGNED,OUT fdata_bigint2 BIGINT UNSIGNED)
> BEGIN
> SELECT fdata_bigint >> shift INTO fdata_bigint2;
> END
> $$
>
> CREATE PROCEDURE mask_data(IN fdata BLOB,IN mask_off INT UNSIGNED,OUT
> fdata_bigint2 BIGINT UNSIGNED)
> BEGIN
> DECLARE top_byte TINYBLOB;
> DECLARE top_int BIGINT UNSIGNED;
> SELECT SUBSTR(BINARY(fdata)
,1,1) INTO top_byte;
> call toBigInt(top_byte,to
p_int);
> SELECT ((top_int << mask_off) & 0xFF) >> mask_off INTO
> top_int;
> SET
> @string_data=CONCAT(
'0x',HEX(top_int),HE
X(SUBSTR(BINARY(fdat
a),2)));
> DROP TEMPORARY TABLE IF EXISTS make_conversion;
> CREATE TEMPORARY TABLE make_conversion(toin
t INT);
> SET @q=CONCAT('INSERT INTO make_conversion set
> toint=(',@string_dat
a,'+0)');
> PREPARE st1 from @q;
> EXECUTE st1;
> DEALLOCATE PREPARE st1;
> SELECT toint FROM make_conversion INTO fdata_bigint2;
> END
> $$
> CREATE PROCEDURE polyConv(IN fdata DOUBLE,IN conv_param VARCHAR(255),OUT
> fdata_converted DOUBLE)
> BEGIN
> DECLARE beginning VARCHAR(255);
> DECLARE end_of VARCHAR(255);
> DECLARE query VARCHAR(255);
> SELECT SUBSTR(conv_param,1,
POSITION('x' in
> conv_param)-1) INTO beginning;
> SELECT SUBSTR(conv_param,PO
SITION('x' in conv_param)+1)
> INTO end_of;
> SET @string_query=CONCAT
("SELECT
> " ,beginning,fdata,end
_of,"INTO
> @fdata_converted");
> PREPARE st1 FROM @string_query;
> EXECUTE st1;
> DEALLOCATE PREPARE st1;
> SELECT @fdata_converted INTO fdata_converted;
> END
> $$
>
> CREATE PROCEDURE enumConv(IN fdata INT UNSIGNED,mfid INT UNSIGNED,OUT
> fdata_converted
> VARCHAR(100))
> BEGIN
> SELECT name FROM EnumLiterals
> WHERE major_frame_desc_id=
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/mysql? unsub...her
e.net
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql? unsub...
mily.com
>
>
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/mysql? unsub...sie.nctu.edu.tw
| |
| Jonathan Mangin 2006-01-24, 8:24 pm |
| I got exactly that error message last night when doing a
numeric comparison on a varchar column. Oops.
Kind of misleading, though.
----- Original Message -----
From: "David Godsey" <mysql@godseyfamily.com>
To: "George Law" <glaw@IONOSPHERE.net>
Cc: <mysql@lists.mysql.com>
Sent: Tuesday, January 24, 2006 4:43 PM
Subject: 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
>
>
> 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/mysql?unsub=...gin@comcast.net
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
|
|
|
|
|