Error calling a stored procedure in MariadDB

  • Answered

I have MariaDB running on Linux. I have created a stored procedure:

CREATE DEFINER=`root`@`%` PROCEDURE `UpdatePeriodLocaltime`(IN `startUtcTime` datetime, IN `tableName` VARCHAR(50))
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;


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;


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?

Hello _nappi, Sorry that you're having problems with your code dealing with MariaDB. Unfortunately, our scope of service does not provide programming support of this nature. You may want look into support forums that provide MariaDB support. Check the MariaDB Foundation for more information and also links to developer support services. If you have any further questions or comments, please let us know. Regards, Arnel C.