Impossible Out Param Return Value
Impossible Out Param Return Value
am 26.05.2010 16:07:21 von Kevin Baynes
Using MySql 5.1, I have a very simple table with 1 row. I have a Stored Pro=
cedure to select if the row exists. I expect the test of this SP to return =
null, but it returns a value! The value is always returned if the 'path' ma=
tches, regardless of the other values being tested. If the 'path' does not =
match, it returns NULL as expected. Has anyone seen this before?
Thanks,=20
Kevin
--
Full explanation below:
--
Table 'file_detail' with 1 row:
id_file_detail, id_file, id_machine, id_user, path
1 , 1 , 1 , 1 , C:\Program Files\BlueZone
--
Stored Procedure to see if row exists:
--
DROP PROCEDURE IF EXISTS `find_file_detail`$$
CREATE PROCEDURE `find_file_detail`
(
IN id_file int(11),
IN id_machine int(11),
IN id_user int(11),
IN filePath varchar(255),
OUT keyOut int(11)
)
BEGIN
SELECT `id_file_detail`=20
INTO keyOut=20
FROM `file_detail`=20
WHERE (`id_file` =3D id_file=20
AND `id_machine` =3D id_machine=20
AND `id_user` =3D id_user=20
AND `path` =3D filePath)=20
LIMIT 1;
=20
END$$
--
SQL used to test the stored procedure:
--
SET @keyOut =3D NULL;
CALL find_file_detail(99,99,99,'C:\\Program Files\\BlueZone',@keyOut);
SELECT @keyOut;
--
Notice there is 1 row, the values of 99 do not exist, but the path does exi=
st. This test will return @keyOut =3D 1. How is this possible?
If I change the path string to use 'BlueZone1' (so the paths do not match),=
then this test will return @keyOut =3D NULL as expected. It seems as thoug=
h the only thing getting matched is the path and the other 3 values are bei=
ng ignored.
Does this make sense to anyone?
Kevin Baynes
Senior Software Developer
Rocket Software
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
RE: Impossible Out Param Return Value
am 26.05.2010 16:56:23 von Kevin Baynes
The problem occurs because the parameter names are the same as the column n=
ames. I'm not sure why, since the columns are specified in quotes, but it d=
oes.
Thanks,
Kevin
-----Original Message-----
From: Kevin Baynes [mailto:KBaynes@bluezonesoftware.com]=20
Sent: Wednesday, May 26, 2010 10:07 AM
To: mysql@lists.mysql.com
Subject: Impossible Out Param Return Value
Using MySql 5.1, I have a very simple table with 1 row. I have a Stored Pro=
cedure to select if the row exists. I expect the test of this SP to return =
null, but it returns a value! The value is always returned if the 'path' ma=
tches, regardless of the other values being tested. If the 'path' does not =
match, it returns NULL as expected. Has anyone seen this before?
Thanks,=20
Kevin
--
Full explanation below:
--
Table 'file_detail' with 1 row:
id_file_detail, id_file, id_machine, id_user, path
1 , 1 , 1 , 1 , C:\Program Files\BlueZone
--
Stored Procedure to see if row exists:
--
DROP PROCEDURE IF EXISTS `find_file_detail`$$
CREATE PROCEDURE `find_file_detail`
(
IN id_file int(11),
IN id_machine int(11),
IN id_user int(11),
IN filePath varchar(255),
OUT keyOut int(11)
)
BEGIN
SELECT `id_file_detail`=20
INTO keyOut=20
FROM `file_detail`=20
WHERE (`id_file` =3D id_file=20
AND `id_machine` =3D id_machine=20
AND `id_user` =3D id_user=20
AND `path` =3D filePath)=20
LIMIT 1;
=20
END$$
--
SQL used to test the stored procedure:
--
SET @keyOut =3D NULL;
CALL find_file_detail(99,99,99,'C:\\Program Files\\BlueZone',@keyOut);
SELECT @keyOut;
--
Notice there is 1 row, the values of 99 do not exist, but the path does exi=
st. This test will return @keyOut =3D 1. How is this possible?
If I change the path string to use 'BlueZone1' (so the paths do not match),=
then this test will return @keyOut =3D NULL as expected. It seems as thoug=
h the only thing getting matched is the path and the other 3 values are bei=
ng ignored.
Does this make sense to anyone?
Kevin Baynes
Senior Software Developer
Rocket Software
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dkbaynes@seagullsoft=
ware.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
Re: Impossible Out Param Return Value
am 26.05.2010 17:05:03 von Peter Brawley
--------------020407060609020708010909
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Kevin
WHERE (`id_file` = id_file
AND `id_machine` = id_machine
AND `id_user` = id_user
How is MySQL supposed to distinguish `id_file` from id_file &c!? Name
params diffferently from columns.
PB
-----
On 5/26/2010 9:07 AM, Kevin Baynes wrote:
> Using MySql 5.1, I have a very simple table with 1 row. I have a Stored Procedure to select if the row exists. I expect the test of this SP to return null, but it returns a value! The value is always returned if the 'path' matches, regardless of the other values being tested. If the 'path' does not match, it returns NULL as expected. Has anyone seen this before?
>
> Thanks,
> Kevin
>
> --
> Full explanation below:
> --
>
> Table 'file_detail' with 1 row:
>
> id_file_detail, id_file, id_machine, id_user, path
> 1 , 1 , 1 , 1 , C:\Program Files\BlueZone
>
> --
> Stored Procedure to see if row exists:
> --
>
> DROP PROCEDURE IF EXISTS `find_file_detail`$$
> CREATE PROCEDURE `find_file_detail`
> (
> IN id_file int(11),
> IN id_machine int(11),
> IN id_user int(11),
> IN filePath varchar(255),
> OUT keyOut int(11)
> )
> BEGIN
>
> SELECT `id_file_detail`
> INTO keyOut
> FROM `file_detail`
> WHERE (`id_file` = id_file
> AND `id_machine` = id_machine
> AND `id_user` = id_user
> AND `path` = filePath)
> LIMIT 1;
>
> END$$
>
> --
> SQL used to test the stored procedure:
> --
>
> SET @keyOut = NULL;
> CALL find_file_detail(99,99,99,'C:\\Program Files\\BlueZone',@keyOut);
> SELECT @keyOut;
>
> --
>
> Notice there is 1 row, the values of 99 do not exist, but the path does exist. This test will return @keyOut = 1. How is this possible?
>
> If I change the path string to use 'BlueZone1' (so the paths do not match), then this test will return @keyOut = NULL as expected. It seems as though the only thing getting matched is the path and the other 3 values are being ignored.
>
> Does this make sense to anyone?
>
> Kevin Baynes
> Senior Software Developer
> Rocket Software
>
>
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.437 / Virus Database: 271.1.1/2895 - Release Date: 05/25/10 06:26:00
>
>
--------------020407060609020708010909--
Re: Impossible Out Param Return Value
am 26.05.2010 17:35:13 von Peter Brawley
--------------090805010409010202060506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Kevin,
>The problem occurs because the parameter names are the same as the column names.
>I'm not sure why, since the columns are specified in quotes, but it does.
Eh? Backticks merely permit use of reserved words as identifiers.
PB
-----
On 5/26/2010 9:56 AM, Kevin Baynes wrote:
>
> The problem occurs because the parameter names are the same as the column names. I'm not sure why, since the columns are specified in quotes, but it does.
>
> Thanks,
>
> Kevin
>
> -----Original Message-----
> From: Kevin Baynes [mailto:KBaynes@bluezonesoftware.com]
> Sent: Wednesday, May 26, 2010 10:07 AM
> To: mysql@lists.mysql.com
> Subject: Impossible Out Param Return Value
>
>
> Using MySql 5.1, I have a very simple table with 1 row. I have a Stored Procedure to select if the row exists. I expect the test of this SP to return null, but it returns a value! The value is always returned if the 'path' matches, regardless of the other values being tested. If the 'path' does not match, it returns NULL as expected. Has anyone seen this before?
>
> Thanks,
> Kevin
>
> --
> Full explanation below:
> --
>
> Table 'file_detail' with 1 row:
>
> id_file_detail, id_file, id_machine, id_user, path
> 1 , 1 , 1 , 1 , C:\Program Files\BlueZone
>
> --
> Stored Procedure to see if row exists:
> --
>
> DROP PROCEDURE IF EXISTS `find_file_detail`$$
> CREATE PROCEDURE `find_file_detail`
> (
> IN id_file int(11),
> IN id_machine int(11),
> IN id_user int(11),
> IN filePath varchar(255),
> OUT keyOut int(11)
> )
> BEGIN
>
> SELECT `id_file_detail`
> INTO keyOut
> FROM `file_detail`
> WHERE (`id_file` = id_file
> AND `id_machine` = id_machine
> AND `id_user` = id_user
> AND `path` = filePath)
> LIMIT 1;
>
> END$$
>
> --
> SQL used to test the stored procedure:
> --
>
> SET @keyOut = NULL;
> CALL find_file_detail(99,99,99,'C:\\Program Files\\BlueZone',@keyOut);
> SELECT @keyOut;
>
> --
>
> Notice there is 1 row, the values of 99 do not exist, but the path does exist. This test will return @keyOut = 1. How is this possible?
>
> If I change the path string to use 'BlueZone1' (so the paths do not match), then this test will return @keyOut = NULL as expected. It seems as though the only thing getting matched is the path and the other 3 values are being ignored.
>
> Does this make sense to anyone?
>
> Kevin Baynes
> Senior Software Developer
> Rocket Software
>
>
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.437 / Virus Database: 271.1.1/2897 - Release Date: 05/26/10 06:25:00
>
>
--------------090805010409010202060506--