List:General Discussion« Previous MessageNext Message »
From:Kevin Baynes Date:May 26 2010 2:07pm
Subject:Impossible Out Param Return Value
View as plain text  
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

Thread
Impossible Out Param Return ValueKevin Baynes26 May
  • RE: Impossible Out Param Return ValueKevin Baynes26 May
    • Re: Impossible Out Param Return ValuePeter Brawley26 May
  • Re: Impossible Out Param Return ValuePeter Brawley26 May