Error calling a stored procedure in MariadDB
Hi
I have MariaDB running on Linux. I have created a stored procedure:
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `UpdatePeriodLocaltime`(IN `startUtcTime` datetime, IN `tableName` VARCHAR(50))
BEGIN
DECLARE period, localperiod, startrange, endrange datetime;
declare _offset time;
declare endtime datetime;
declare _dstStart, _dstEnd date;
declare statement varchar(256);
SET period = startUtcTime;
set endtime = addtime(startUtcTime,'1 00:15:00');
select offset, dstStart, dstEnd from timezone into _offset, _dstStart, _dstEnd;
if (startUtcTime >= _dstStart and startUtcTime < _dstEnd) then
set _offset = addtime(_offset, '01:00:00');
end if;
periods_loop : LOOP
IF period >= endtime
THEN leave periods_loop;
END IF;
set localperiod = addtime(period, _offset);
set startrange = addtime(period, '-00:07:30');
set endrange = addtime(period,'00:07:30');
UPDATE updperiod SET timest = period;
set @sql = concat('update ', tableName, ' set periodlocaltime = "', localperiod, '" where starttime > "', startrange, '" and starttime <= "', endrange, '"');
#UPDATE pre_mib2 SET periodlocaltime = addtime(period, _offset) WHERE starttime > (addtime(period, '-00:07:30')) AND starttime <= (addtime(period,'00:07:30'));
prepare stmt1 from @sql;
execute stmt1;
deallocate prepare stmt1;
set period = addtime(period, '00:15:00');
END loop;
END//
DELIMITER ;
But when I try to call the procedure I get the following error:
mysql> call UpdatePeriodLocaltime("2015-04-02","table_name");
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1
I have created another procedure with only one IN parameter and that works just fine...
What am I missing here?
P
I have MariaDB running on Linux. I have created a stored procedure:
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `UpdatePeriodLocaltime`(IN `startUtcTime` datetime, IN `tableName` VARCHAR(50))
BEGIN
DECLARE period, localperiod, startrange, endrange datetime;
declare _offset time;
declare endtime datetime;
declare _dstStart, _dstEnd date;
declare statement varchar(256);
SET period = startUtcTime;
set endtime = addtime(startUtcTime,'1 00:15:00');
select offset, dstStart, dstEnd from timezone into _offset, _dstStart, _dstEnd;
if (startUtcTime >= _dstStart and startUtcTime < _dstEnd) then
set _offset = addtime(_offset, '01:00:00');
end if;
periods_loop : LOOP
IF period >= endtime
THEN leave periods_loop;
END IF;
set localperiod = addtime(period, _offset);
set startrange = addtime(period, '-00:07:30');
set endrange = addtime(period,'00:07:30');
UPDATE updperiod SET timest = period;
set @sql = concat('update ', tableName, ' set periodlocaltime = "', localperiod, '" where starttime > "', startrange, '" and starttime <= "', endrange, '"');
#UPDATE pre_mib2 SET periodlocaltime = addtime(period, _offset) WHERE starttime > (addtime(period, '-00:07:30')) AND starttime <= (addtime(period,'00:07:30'));
prepare stmt1 from @sql;
execute stmt1;
deallocate prepare stmt1;
set period = addtime(period, '00:15:00');
END loop;
END//
DELIMITER ;
But when I try to call the procedure I get the following error:
mysql> call UpdatePeriodLocaltime("2015-04-02","table_name");
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1
I have created another procedure with only one IN parameter and that works just fine...
What am I missing here?
P