✔ mySQL Variable will nicht im Query arbeiten...

[NetBull]

Hi,

hab da folgendes Problem:
Wäre klasse wenn mir einer mal auf die Sprünge helfen könnte.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`dbCopy`@`%` PROCEDURE `fillTestTable`()
BEGIN
    
	DECLARE rowCount INT;
	DECLARE currentRow INT;	
	DECLARE currentName VARCHAR(255);
	DECLARE currentId INT;

	SET rowCount = (select count(*) from testSuite);
	SET currentRow = 1;
	SET currentId = 0;
	
		testSuites:WHILE(currentRow < rowCount+1) DO
			SELECT id FROM testSuite LIMIT currentRow, 1 into currentId;
			
			SET currentRow = currentRow + 1;
			
			#SELECT left(name, instr(name, ".xml")-1) FROM testSuite LIMIT currentRow, 1 into currentName;						
			#select concat("1. try on ", currentName, " with ID#", currentId, "on row #", currentRow);
			#UPDATE test SET testSuiteId = currentId WHERE testName = currentName;

		end while testSuites;
	
    END

Die zeile "SELECT id FROM testSuite LIMIT currentRow, 1 into currentId;" mag das currentRow nicht. Was mache ich hier falsch?

LG deAndro!

 

[Yaslaw]

Ich seh den Fehler gerade nicht. Aber warum arbeitest du nicht mit einem Cursor?

DECLARE currentId INT;
DECLARE cur CURSOR FOR SELECT id FROM testSuite;
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

OPEN cur;
select FOUND_ROWS() into num_rows;
the_loop: LOOP
    FETCH  cur  INTO 
    IF no_more_rows THEN
        CLOSE cur;
        LEAVE the_loop;
    END IF;
    -- TODO: Was es mit der ID auch immer zu tun gibt
END LOOP the_loop;

Anleitung zum Cursor-Loop:
http://www.kbedell.com/2009/03/02/a-simple-example-of-a-mysql-stored-procedure-that-uses-a-cursor/

Achja, das ganze ist ungetestet...

 

[NetBull]

merci, habs nun anhand Deines Beispiels lösen können.