Stored Procedure Query returns strange result

[Fabian Eschrich]

Hi,

currently I am testing my stored procedures on 4.0b2 and there were strange results, so I added a lot of debugging messages.

Here is one entry of my debug log:

Print : Fetched Manager 1622375229700882386 for Address 200692117291889670020068261110336231300200682113519997630020030206102706 25.10.2006 23:59:59:000
Print : select manager_id from managers where address_id = 200692117291889670020068261110336231300200682113519997630020030206102706 AND startdate < 25.10.2006 23:59:59:000 AND (enddate > 25.10.2006 23:59:59:000 OR enddate IS NULL) ORDER BY startdate DESC;


The problem is: A managerid '1622375229700882386' does not exist. It cannot exist because all ids start with a timestamp.

This is the procedure code:

CREATE OR REPLACE PROCEDURE [SaveStatisticForManager]( IN VADDRESSID VARCHAR, IN VDATETIME DATETIME, IN VNAME VARCHAR, IN VVAL DOUBLE, IN VVALV VARCHAR )
BEGIN
	declare vManagerID INTEGER;
	DECLARE vcurManager CURSOR FOR select manager_id from managers where address_id = VADDRESSID AND startdate < VDATETIME AND (enddate > VDATETIME OR enddate IS NULL) ORDER BY startdate DESC;
	OPEN vcurManager;

	BEGIN
		FETCH FIRST vcurManager INTO vManagerID;  
		PRINT CONCAT('Fetched Manager ',vManagerID,' for Address ',VADDRESSID,' ',VDATETIME);
		PRINT CONCAT('select manager_id from managers where address_id = ',VADDRESSID,' AND startdate < ',VDATETIME,' AND (enddate > ',VDATETIME,' OR enddate IS NULL) ORDER BY startdate DESC;');
		close vcurManager;
		CALL SaveStatisticForAddress(vManagerID,getdate(VDATETIME),VNAME,VVAL,VVALV);
	
		-- Recursive Call to myselv
		CALL SaveStatisticForManager(vManagerID,VDATETIME,VNAME,VVAL,VVALV);
	
	EXCEPTION WHEN ERR_CURSOR_WRONG_POSITION THEN
		PRINT CONCAT('NO MANAGER FOR ',VADDRESSID,' ' ,VDATETIME);
		PRINT 		PRINT CONCAT('select manager_id from managers where address_id = ',VADDRESSID,' AND startdate < ',VDATETIME,' AND (enddate > ',VDATETIME,' OR enddate IS NULL) ORDER BY startdate DESC;');
		PRINT 
		close vcurManager;
	end
END;

Have a nice day,

Fabian