CODEWITHSUNDEEP
mysqlnullcursorprimary-key
Dave Knise
Dave Knise

Reputation: 106

MySql Cursor FETCH not returning Primary Key fields

I have a simple cursor that fetches the primary key of a table. Looking through the results, every value of the primary key column that is fetched is null. If I run the cursor's query as a standalone query, it properly returns the results. This table references my Account table with it's primary key value. If I change "SELECT PrimaryKeyId" to "SELECT AccountId", it properly fetches the field's value.

What am I missing here?

DECLARE testtableid INT UNSIGNED;
DECLARE accountid INT UNSIGNED DEFAULT getAccountId(inUserLoginId);
DECLARE cur CURSOR FOR SELECT TestTableId
                                 FROM testtable
                                WHERE AccountId = accountId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;    

named_loop: LOOP

    FETCH cur INTO testtableid ;

    IF done
    THEN
        LEAVE named_loop;
    END IF;
END named_loop;

CLOSE cur;

Upvotes: 0

Views: 802

Answers (1)

Dave Knise
Dave Knise

Reputation: 106

Boom!

http://bugs.mysql.com/bug.php?id=28227

I keep forgetting that I'm developing the MySql database on a Windows environment, paying attention to case sensitivity even though that's only applied in a Linux environment.

The query:

DECLARE testtableid INT;
SELECT TestTableId FROM testable

When not executed in a case sensitive environment, selects the local variable of the same, case insensitive name. This value was NULL as it was undeclared.

I'm leaving this up in hopes that I save someone an hour of frustration someday. :)

Upvotes: 1

Related Questions