CODEWITHSUNDEEP
mysqlcursor
clarity
clarity

Reputation: 431

mysql cursor not loading variable

I'm having trouble getting the FETCH statement to work correctly in the code below. It's actually not putting any data into the variable registerName. registerName has the same value as it has before the FETCH statement. Thanks!

-- Declare variables/cursors needed for building pivot query
DECLARE qry VARCHAR(8000);
DECLARE registerName VARCHAR(128) DEFAULT '';
DECLARE done BOOLEAN DEFAULT 0;

DECLARE registers CURSOR
FOR
SELECT RegisterName
FROM Register r
INNER JOIN EgaugeDevice ed ON ed.id = r.EgaugeDeviceId
INNER JOIN Site s ON s.id = ed.SiteId
INNER JOIN Facility f ON f.id = s.FacilityId
WHERE ShowInSite = 1 AND FacilityName = FACILITY;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

-- Use temporary table to get results from instantaneous view
CREATE TEMPORARY TABLE IF NOT EXISTS instData (
    id INT,
    RegisterId INT,
    InstantaneousValue BIGINT,
    Date_Time DATETIME,
    Time_Stamp BIGINT
);

TRUNCATE TABLE instData;

INSERT INTO instData(id, RegisterId, InstantaneousValue, Date_Time, Time_Stamp)
SELECT id, RegisterId, InstantaneousValue, Date_Time, Time_Stamp
FROM vRegisterDataInstantaneous
WHERE Date_Time >= now() - INTERVAL 1 DAY
ORDER BY Time_Stamp DESC;

-- build pivot query from registers listed in Register table
OPEN registers;

FETCH registers INTO registerName;

select registerName AS Test;

CLOSE registers;

Upvotes: 1

Views: 45

Answers (1)

Hatem Jaber
Hatem Jaber

Reputation: 2402

Is it possible to not have the same name for the column and the variable RegisterName? This could be causing a conflict, Local Variable Scope and Resolution

Upvotes: 1

Related Questions