[SOLVED] MySQL SELECT INTO statement: Beware of 0, 2 or more result rows!

rm_-rf_windows · 03-21-2012, 07:25 AM

Hi all,

I've been struggling for an hour to try and figure out how to retrieve a bit type value from one table and insert it into another table in a stored procedure. I've tried casting, changing variable types, etc. Nothing works.

Here's a simplified example of what I'd like to do:

Code:

(stored procedure introductory code omitted, no problem there)
DECLARE _pos_info BIT(4);
-- I also tried DECLARE _pos_info INTEGER;
DECLARE _name VARCHAR(256);
DECLARE _id INTEGER;
(etc.)

-- table1 and table2 have pos_info types BIT(4) and I don't want to change that because of readability (too lengthy to explain why in detail).

        SELECT pos_info INTO _pos_info FROM table1 WHERE attribute_name_t1 = _name;        
        UPDATE table2 SET pos_info_t2 = _pos_info WHERE id = _id;
-- there is a CURSOR to go through all the id's
-- _name works in statement 1 (I won't go into detail on this either)

Now, I've tried, at the same point in the script, to change other attributes, and it always works, except for the BIT type, so the problem is indeed the bit type.

What's strange is that this works (in the script)!:

Code:

UPDATE table2 SET pos_info_t2 = 9 WHERE id = _id;

That gives me '1001' if I ask for BIT(pos_info+0)!

Also, if I go directly into the mysql terminal and type the same two lines and declaring a variable @a to use for the transfer, it works! But the equivalent never works in the script, in the procedure!

I've tried pos_info+0 (as INT and as BIT(4).
I've tried BIN(pos_info+0) (as BIT(4)).
I've tried many other possibilities, it never works!

Does anybody have the solution to the problem?

Thanks in advance!

rm

rm_-rf_windows · 03-22-2012, 06:58 AM

Okay, okay... I don't think the problem is the bit data type, I think it has something to do with the varchar variable.

Here's the entire script:

Code:

DELIMITER |
DROP PROCEDURE IF EXISTS complete_node_table; |
CREATE PROCEDURE complete_node_table()
BEGIN
    DECLARE _eid, _type, _weight, _pos_info, _count, _total_count, _abu_occurrences, _ml_id INTEGER;
    DECLARE _name, _ml_word VARCHAR(256);
    DECLARE _no_more_rows_n, _ml_lemma BOOLEAN;
    DECLARE _ml_pos_info BIT(4);
    DECLARE _curs_n CURSOR FOR SELECT eid, name, type, weight, pos_info FROM node;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _no_more_rows_n := TRUE;
    OPEN _curs_n;
    loop1: LOOP
        FETCH _curs_n INTO _eid, _name, _type, _weight, _pos_info;

        -- IF _name = "erreur" THEN        
        SELECT pos_info INTO _ml_pos_info FROM ml_all WHERE word=_name;            
        UPDATE node SET ml_pos_info = _ml_pos_info WHERE eid=_eid;
        -- END IF;

        IF _no_more_rows_n THEN
            CLOSE _curs_n;
            LEAVE loop1;
        END IF;
    END LOOP loop1;
END |
DELIMITER ;

CALL complete_node_table();

There is no error message, but the update just doesn't happen at all!

What's strange is if I uncomment the IF statement, it works for the word in question:

Code:

        IF _name = "erreur" THEN        
        SELECT pos_info INTO _ml_pos_info FROM ml_all WHERE word=_name;            
        UPDATE node SET ml_pos_info = _ml_pos_info WHERE eid=_eid;
        END IF;

In both tables, "word" and "name" are indeed VARCHAR(256) data types (respectively).

I just can't figure it out! Is "erreur" assigned to _name in the IF statement? Is that why it works with the IF statement and not otherwise?

Obviously, without the IF statement, the idea is to take values from one table and insert them in another.

Now, not all of the queries in the SELECT statement will yield results (I'm talking about the inner SELECT statement, not the CURSOR one). Some will have no rows as a result. Might this have something to do with it? If so, how do I change my code so that it works?

Many thanks,

rm

rm_-rf_windows · 03-22-2012, 07:41 AM

Okay, I've finally figured it out!

It was neither a bit data type problem nor a strange "IF assignment", the problem is that some of the queries in the inner SELECT statement yielded no results, in which case (I'm not sure about this, exactly), the script stopped executing completely, exiting the cursor.

This made it work:

Code:

        SET _count = 0;
        SELECT COUNT(*) INTO _count FROM ml_all WHERE word=_name;
        IF _count = 1 THEN
            SET _ml_pos_info = NULL;
            SELECT pos_info INTO _ml_pos_info FROM ml_all WHERE word=_name;       
                IF _ml_pos_info IS NOT NULL THEN     
                    UPDATE node SET ml_pos_info = _ml_pos_info WHERE eid=_eid;
                END IF;
        END IF;

Of course, the opposite is also true, the SELECT statement could have more than one result row, however I don't think this is the case in my particular case.

The MySQL Reference web pages says this about SELECT INTO... (variables):

Quote:

The INTO clause can name a list of one or more variables, which can be user-defined variables, stored procedure or function parameters, or stored program local variables (see Section 12.6.4, “Variables in Stored Programs”). The selected values are assigned to the variables. The number of variables must match the number of columns. The query should return a single row. If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged. If the query returns multiple rows, error 1172 occurs (Result consisted of more than one row). If it is possible that the statement may retrieve multiple rows, you can use LIMIT 1 to limit the result set to a single row.

However, in my case, I got no error message whatsoever, it's simply that my tables remained unchanged.

Perhaps someone out there knows more about this condition and how to (better) deal with it? If so, you're comments would certainly be appreciated.

rm_-rf_windows · 03-22-2012, 05:52 PM

Well, database questions aren't all that sexy... No response... I'm therefore going to mark this thread as SOLVED!!

rm