I need execute sql query by python script. I've created two MariaDB stored procedures. First AbonamentStart and MonthlyTransfer. Procedure AbonamentStart should return DATE variable. Second procedure MonthlyTransfer should return SUM of column TotalDay only for records where date is newer than results of AbonamentStart.
AbonamentStart procedure:
DELIMITER $$
CREATE DEFINER=`admin2`@`localhost` PROCEDURE `AbonamentStart`()
BEGIN
SELECT DAYOFMONTH(CURRENT_DATE) INTO @currentday;
SELECT MONTH(CURRENT_DATE) INTO @currentmonth;
SELECT YEAR(CURRENT_DATE) INTO @currentyear;
SELECT IF (@currentday < 22,1,0) INTO @plusminus;
SELECT ADDDATE(CURRENT_DATE, INTERVAL -(@plusminus) MONTH) INTO @plusminustemp;
SELECT DAYOFMONTH(@plusminustemp) INTO @plusminusday;
SELECT ADDDATE(@plusminustemp, INTERVAL (22 - @plusminusday) DAY) INTO @abonamentstart;
SELECT @abonamentstart;
END$$
DELIMITER ;
MonthlyTransfer procedure - works when we provide SearchDate DATE variable by python script:
DELIMITER $$
CREATE DEFINER=`admin2`@`localhost` PROCEDURE `MonthlyTransfer`(IN `SearchDate` DATE)
BEGIN
SELECT SUM(TotalDay)
FROM TRANSFER
WHERE DayDate > SearchDate;
END$$
DELIMITER ;
I've tried modify procedure MonthlyTransfer as below and call procedure MariaDB returned message
#1318 - Incorrect number of arguments for PROCEDURE RUTER.MonthlyTransfer; expected 1, got 0
Thanks in advance for any clue.
I've found sample but not entirely understood. 'Calling a Procedure From Another STORED PROCEDURE' [https://www.softwaretestinghelp.com/mysql-stored-procedure/#MySQL_STORED_PROCEDURES_vs_FUNCTIONS][1]
DELIMITER $$
CREATE DEFINER=`admin2`@`localhost` PROCEDURE `MonthlyTransfer`(IN `@abonamentstart` DATE, OUT TotalTranfer INT)
BEGIN
CALL AbonamentStart();
SELECT SUM(TotalDay)
FROM TRANSFER
WHERE DayDate > @abonamentstart;
END$$
DELIMITER ;
TABLE structure:
CREATE TABLE `TRANSFER` (
`Id` int(11) NOT NULL,
`Download` bigint(15) NOT NULL,
`Upload` bigint(15) NOT NULL,
`DownloadDay` bigint(15) DEFAULT NULL,
`DownloadNight` bigint(15) DEFAULT NULL,
`UploadDay` bigint(15) DEFAULT NULL,
`UploadNight` bigint(15) DEFAULT NULL,
`TotalDay` bigint(15) DEFAULT NULL,
`TotalNight` bigint(15) DEFAULT NULL,
`DayDate` date DEFAULT NULL,
`DayHour` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Server version: 10.5.15-MariaDB-0+deb11u1 Raspbian 11