Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am in process of writing a simple MySQL function that looks up a value in a table and returns it. In case there is not value found it returns null. However, even with a continue handler defined I still end up with a warning "No data - zero rows fetched, selected, or processed". My code is below, what am I doing wrong? I really want to get rid of this warning :)

DROP FUNCTION IF EXISTS `getCompanySetting`;
DELIMITER |

CREATE FUNCTION `getCompanySetting`(setting_company_id INT, setting_name VARCHAR(255))
    RETURNS TEXT
BEGIN
    DECLARE setting_value TEXT DEFAULT NULL;
    DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET setting_value = NULL;

    SELECT
        value
    FROM company_settings
    WHERE
        company_id = `setting_company_id`
        AND
        name       = `setting_name`
    INTO setting_value;

    RETURN setting_value;
END|

DELIMITER ;

mysql> SELECT getCompanySetting(24, 'observers_active');
+-------------------------------------------+
| getCompanySetting(24, 'observers_active') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Update: MySQL Version 5.5.25

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Before MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked.

I have explained this kind of behavior and the solution in this post:

Event Scheduler: No data - zero rows fetched, selected, or processed

share|improve this answer

If you ask me there is no better way to fix this kind of issue with handlers or exceptional handling declarative statements.

But you may try to give a constant value that will return even if your answer should be NULL. Let that constant value be a wierdest one that you can expect from the fucntion that could return. Add that constant value to your return function.

Disclaimer: I haven't tested it, but you may give it a try. Let me know on your results.

In this case I'm mentioning 'clops' as the constant.

Try below changes to your function only under select clause and see.

 SELECT concat('clops',value) FROM company_settings    WHERE
 company_id = `setting_company_id`
 AND
 name       = `setting_name`
 INTO setting_value;
mysql> SELECT getCompanySetting(24, 'observers_active');

=====>clops

As only you are aware of the word you may keep that word as a seperator. And at your application end split this word, if you get any other data after seperating 'clops' from the return type then treat it as a real return data. By this way you can ignore/suppress the warning that you get.

share|improve this answer
    
Unfortunately the warning is still there :( –  clops Jun 6 '13 at 15:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.