MySQL delimiter problem[ SOLVED]
-
Hello! I'm trying to create a trigger from my appliction, using QSqlQuery class. I'm doing it like this
@queryTxt = "DELIMITER /";
query.exec(queryTxt);
err=query.lastError().text();
query.finish();
queryTxt= "CREATE TRIGGERtransitparams_triggerBEFORE INSERT ONtransitparamsFOR EACH ROW BEGIN"
"declare numberact, numberreport, numberresolution, numberfillinform int;"
"if (new.category <> '1') then"
"SELECT transitparams.actnumber, transitparams.reportnumber, transitparams.resolutionnumber, transitparams.fillinformnumber into numberact,numberreport,numberresolution, numberfillinform"
"FROM transitparams"
"WHERE transitparams.paramid=(select max(transitparams.paramid) from transitparams where transitparams.category <> '1');"
"if (numberact is null) then"
"set new.actnumber=1;"
"set new.reportnumber=1;"
"set new.resolutionnumber=1;"
"set new.fillinformnumber=1;"
"else"
"if(new.actnumber is null) then"
"set new.actnumber=numberact+1;"
"end if;"
"if (new.reportnumber is null) then"
"set new.reportnumber=numberreport+1;"
"end if;"
"if (new.resolutionnumber is null) then"
"set new.resolutionnumber=numberresolution+1;"
"end if;"
"if (new.fillinformnumber is null) then"
"set new.fillinformnumber=numberfillinform+1;"
"end if;"
"end if;"
"end if;"
"END;";
bool done = query.exec(queryTxt);@
And when I execute line №2, I get MySql syntax error:"check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER /'". What is wrong? When I run it on the server , it works fine. But from Qt it refuses to work. Thanx a lot! -
DELIMITER is not an SQL statement. It is the command of mysql command line client. Normally, mysql command line client uses semicolon to recognize an end of SQL statement entered by user. If user wants to enter a compound statement (like CREATE TRIGGER), which in itself contains semicolons, the user has to use DELIMITER command to temporarily change what mysql command line client recognizes as an end of SQL statement.
In your case, however, you are using QSqlQuery. The whole string passed to its constructor is always treated as one SQL statement, even if it contains semicolons. You don't need DELIMITER command.