MySQL Forums
Forum List  »  Stored Procedures

Re: Mysql 8 stored procedure
Posted by: Zoltan Szmutku
Date: June 15, 2020 06:13AM

Hello,

I made a simplified teszt script.
You need to make an empty database named zozo0615 and add this function .
If you run this under 5.6 server there is not error. Under 8.0 you get a syntax error. It is stable error situation in this short test script but in our big program environment this error sometimes go fix without any interaction under 8.0.
Unfortunately I did not work with mysql in last years so I am not an expert.

CREATE PROCEDURE `teszt`()

BEGIN

DECLARE done INT default False;
DECLARE mField CHAR(20) default '';
DECLARE mFields TEXT;

/* This next tempcur declaration did work under 5.6 and sometimes work on 8.0, but sometimes give error
because fields name list arrive in alphabetical order.
But sometimes it improves without any program change under 8.0 .. and it work further all the time.
*/
DECLARE tempcur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'zozo0615' AND TABLE_NAME ='bizonylat_torzs_2031';

/* This new tempcur declaration always work under 5.6 and under 8.0 also because of it contains ORDER BY ORDINAL_POSITION keywords */

/*
DECLARE tempcur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'zozo0615' AND TABLE_NAME ='bizonylat_torzs_2031'
ORDER BY ORDINAL_POSITION ;
*/

/* variables create test table...etc*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET mFields='';
SET mField='';
SET @lcCommand ='REPLACE INTO zozo0615.bizonylat_torzs_2031 (SELECT ';

DROP TABLE IF EXISTS zozo0615.bizonylat_torzs_2031;

CREATE TABLE zozo0615.bizonylat_torzs_2031 (
PAZON bigint NOT NULL AUTO_INCREMENT,
ADOSZAZON int NOT NULL DEFAULT '0',
BANKAZON int NOT NULL DEFAULT '0',
BOLTAZON int NOT NULL DEFAULT '0',
TOMBPAZON int NOT NULL DEFAULT '0',
USERDAZON int NOT NULL DEFAULT '0',
USERDPAZON int NOT NULL DEFAULT '0',
IRANY decimal(1,0) NOT NULL DEFAULT '0',
BIZTIPUS int NOT NULL DEFAULT '0',
PLATFORM decimal(2,0) NOT NULL DEFAULT '0',
LOGOAZON int NOT NULL DEFAULT '0',
EVSZAM decimal(4,0) NOT NULL DEFAULT '0',
BIZSZAM decimal(12,0) NOT NULL DEFAULT '0',
BIZTIPUSP char(4) NOT NULL DEFAULT '',
TOMBJEL char(7) NOT NULL DEFAULT '',
BIZFEJNEV varchar(50) NOT NULL DEFAULT '',
REPORTFAJL varchar(30) NOT NULL DEFAULT '',
INFORMACIO varchar(30) NOT NULL DEFAULT '',
KIBDT date NOT NULL DEFAULT '1900-01-01',
TELJDT date NOT NULL DEFAULT '1900-01-01',
FIZHATDT date NOT NULL DEFAULT '1900-01-01',
IGAZOLDT date NOT NULL DEFAULT '1900-01-01',
ERVENYDT date NOT NULL DEFAULT '1900-01-01',
FIZDT date NOT NULL DEFAULT '1900-01-01',
EGYSZERU decimal(1,0) NOT NULL DEFAULT '0',
ELOSZAMLA decimal(1,0) NOT NULL DEFAULT '0',
VEGSZAMLA decimal(1,0) NOT NULL DEFAULT '0',
AFAS decimal(1,0) NOT NULL DEFAULT '0',
KATA decimal(1,0) NOT NULL DEFAULT '0',
PFEL decimal(1,0) NOT NULL DEFAULT '0',
MAGYAREURO decimal(1,0) NOT NULL DEFAULT '0',
EXPORT decimal(1,0) NOT NULL DEFAULT '0',
EUNBELULI decimal(1,0) NOT NULL DEFAULT '0',
FOA decimal(1,0) NOT NULL DEFAULT '0',
PLDOFF decimal(1,0) NOT NULL DEFAULT '0',
PELDANY int NOT NULL DEFAULT '0',
STORNO decimal(1,0) NOT NULL DEFAULT '0',
STORNOZOTT decimal(1,0) NOT NULL DEFAULT '0',
JOVAIRO decimal(1,0) NOT NULL DEFAULT '0',
JOVAIRT decimal(1,0) NOT NULL DEFAULT '0',
NYELV decimal(2,0) NOT NULL DEFAULT '0',
PENZNEM char(3) NOT NULL DEFAULT '',
ARFOLYAM decimal(15,4) NOT NULL DEFAULT '0.0000',
FIZMOD char(15) NOT NULL DEFAULT '',
FIZMODAZ int NOT NULL DEFAULT '0',
ARSOR char(15) NOT NULL DEFAULT '',
ARSORAZ int NOT NULL DEFAULT '0',
FIZETVE decimal(1,0) NOT NULL DEFAULT '0',
FIZOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
SZOVEGAZON int NOT NULL DEFAULT '0',
KESZAZON int NOT NULL DEFAULT '0',
KASSZAUSER int NOT NULL DEFAULT '0',
AFAOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
FAFAOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
NETTOOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
FNETTOOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
BRUTTOOSSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
FBRUTTOOSZ decimal(15,4) NOT NULL DEFAULT '0.0000',
ELOBIZTIP int NOT NULL DEFAULT '0',
ELOBIZAZON bigint NOT NULL DEFAULT '0',
ELOBIZSZAM char(25) NOT NULL DEFAULT '',
ELOBIZEVSZ decimal(4,0) NOT NULL DEFAULT '0',
KOVBIZTIP int NOT NULL DEFAULT '0',
KOVBIZAZON bigint NOT NULL DEFAULT '0',
KOVBIZSZAM char(25) NOT NULL DEFAULT '',
KOVBIZEVSZ decimal(4,0) NOT NULL DEFAULT '0',
SMEGJEGYZ longtext ,
HIVATKOZAS longtext ,
ARTIZEDES decimal(1,0) NOT NULL DEFAULT '0',
METIZEDES decimal(1,0) NOT NULL DEFAULT '0',
ELOLEGN decimal(15,4) NOT NULL DEFAULT '0.0000',
ELOLEGB decimal(15,4) NOT NULL DEFAULT '0.0000',
PARTPAZON int NOT NULL DEFAULT '0',
PNEV varchar(100) NOT NULL DEFAULT '',
PADOSZAM varchar(17) NOT NULL DEFAULT '',
PADOCSOP varchar(13) NOT NULL DEFAULT '',
PEMAIL varchar(50) NOT NULL DEFAULT '',
PNAVFORMAT decimal(1,0) NOT NULL DEFAULT '0',
TAGSAGAZON int NOT NULL DEFAULT '0',
TORSZAG char(3) NOT NULL DEFAULT '',
TKORZET varchar(20) NOT NULL DEFAULT '',
TIRSZ varchar(8) NOT NULL DEFAULT '',
THELYSEG varchar(20) NOT NULL DEFAULT '',
TCIM varchar(80) NOT NULL DEFAULT '',
TKOZNEVE varchar(25) NOT NULL DEFAULT '',
TKOZJELLEG varchar(10) NOT NULL DEFAULT '',
TKOZSZAM varchar(10) NOT NULL DEFAULT '',
TEPULET varchar(5) NOT NULL DEFAULT '',
TLEPCSOHAZ varchar(5) NOT NULL DEFAULT '',
TEMELET char(3) NOT NULL DEFAULT '',
TAJTO char(3) NOT NULL DEFAULT '',
LNEV varchar(100) NOT NULL DEFAULT '',
LORSZAG char(3) NOT NULL DEFAULT '',
LKORZET varchar(20) NOT NULL DEFAULT '',
LIRSZ varchar(8) NOT NULL DEFAULT '',
LHELYSEG varchar(20) NOT NULL DEFAULT '',
LCIM varchar(80) NOT NULL DEFAULT '',
OBIZTIP int NOT NULL DEFAULT '0',
OBIZFAJTA int NOT NULL DEFAULT '0',
OSZABSZOV int NOT NULL DEFAULT '0',
OAFATIP int NOT NULL DEFAULT '0',
ONETTO int NOT NULL DEFAULT '0',
ESZAMEMAIL datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
ESZAMPDF datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
ESZAMCOPY datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
_SERVDATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
LINEEND int NOT NULL DEFAULT '0',
MODBIZSZAM char(25) NOT NULL DEFAULT '',
MODBIZELOZ char(25) NOT NULL DEFAULT '',
MODOSITO decimal(1,0) NOT NULL DEFAULT '0',
NAVJELENT decimal(1,0) NOT NULL DEFAULT '0',
NAVAFA decimal(15,4) NOT NULL DEFAULT '0.0000',
NAVID varchar(30) NOT NULL DEFAULT '',
NAVMODSZ int NOT NULL DEFAULT '0',
PRIMARY KEY (PAZON),
KEY BIZTIPUS (BIZTIPUS),
KEY TOMBPAZON (TOMBPAZON),
KEY BIZSZAM (BIZSZAM),
KEY PNEV (PNEV),
KEY FIZDT (FIZDT),
KEY ERVENYDT (ERVENYDT),
KEY IGAZOLDT (IGAZOLDT),
KEY FIZHATDT (FIZHATDT),
KEY TELJDT (TELJDT),
KEY KIBDT (KIBDT),
KEY BOLTAZON (BOLTAZON)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8 ;

/*insert first row for test*/
REPLACE INTO zozo0615.bizonylat_torzs_2031 (SMEGJEGYZ,HIVATKOZAS) VALUES ('','');


/* Start REPLACE into command generation */
SET done = False;
OPEN tempcur;

read_loop: LOOP
FETCH tempcur INTO mField;
IF done THEN
LEAVE read_loop;
END IF;

IF (mFields!='') THEN
SET mFields = CONCAT(mFields,',') ;
END IF;

/* SET mFields = CONCAT(mFields,mField); */
SET mFields = CONCAT(mFields,IF((mField <> "PAZON"),mField,"0 as pazon"));
END LOOP;

CLOSE tempcur;

SET @lcCommand = CONCAT(@lcCommand,mFields,' FROM zozo0615.bizonylat_torzs_2031) ');
/* END REPLACE into command generation */
/*SELECT @lcCommand; what is in it? */

/*EXECUTE the command*/
PREPARE stmt FROM @lcCommand;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

**********************************
and a next strange thing.

Put this command into an execute window!
CREATE TABLE zozo0615.proba (
onefield bigint NOT NULL AUTO_INCREMENT,
twofield int NOT NULL DEFAULT '0',
PRIMARY KEY (onefield)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8 SELECT "" AS threefield SELECT "" AS three

You will get a table with 3 fields because of a semicolon is missing between two command .

Is it regular ?
Because if you put the command as
CREATE TABLE zozo0615.proba (
onefield bigint NOT NULL AUTO_INCREMENT,
twofield int NOT NULL DEFAULT '0',
PRIMARY KEY (onefield)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8 threefield ;
then you get a syntax error.

Options: ReplyQuote


Subject
Views
Written By
Posted
469
June 12, 2020 12:47AM
194
June 12, 2020 12:52AM
106
June 12, 2020 03:01AM
129
June 12, 2020 11:17AM
105
June 14, 2020 01:39AM
85
June 14, 2020 10:53AM
Re: Mysql 8 stored procedure
88
June 15, 2020 06:13AM
108
June 15, 2020 12:29PM
130
June 15, 2020 12:46PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.