MyScript error with creating triggers and stored procedures

Zsolt Csillag · Post by **Zsolt Csillag** » Thu 25 Jun 2009 07:54

Hello,

I use a console program written in Delphi to read a database file (a plain text file) and execute the contents (this is a whole creation of database, its tables, stored procedures).

I debug it with DbMonitor and I noticed that no matter how long the text is it always stops BEFORE THE FIRST TRIGGER (OR STORED PROCEDURE). (it is interesting, that the DbMonitor doesn't give any error, it shows the last statement that was before the Trigger with "Completed successfully".)

It should be something with the syntax because if I execute ONLY a trigger alone, it works without an error like this:

CREATE TRIGGER `termek_after_ins_tr` AFTER INSERT ON `termek`
FOR EACH ROW
BEGIN

If GetEgyBeallitas("AutomataVonalkodGeneralas")="1" Then
call GeneralAutomataVonalkod(new.Termek_Azonosito);
End IF;

END;

But if ANY OTHER statement is before it, it would stop after the statement BEFORE the trigger:

CREATE TABLE `termek` (
`Termek_Azonosito` int(11) unsigned NOT NULL auto_increment,
`Termek_SajatCikkSzam` varchar(30) default NULL,
`Termek_Letrehozas_Idobelyeg` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`Termek_Megnevezes` varchar(80) NOT NULL,
`Termek_Megnevezes2` varchar(80) default NULL,
`Termek_BeszerzesiAr` int(11) default NULL COMMENT 'netto',
`Termek_Osztalyozas` char(1) default 'T' COMMENT 'T(ermek)\r\nS(zolgaltatas)',
`Termek_Tipus` char(1) default 'K' COMMENT '(S)eged\r\n(F)elkesz\r\n(K)esz',
`Termek_Csomagolasi_egyseg` varchar(20) default NULL,
`Termek_Meret` varchar(20) default NULL,
`Termek_MinimumKeszlet` int(11) default '1',
`Termek_Megjegyzes` text,
`Termek_AktualisKeszlet` decimal(11,2) default '0.00',
`Termek_TermekCsop_Azon` int(11) unsigned default NULL,
`Termek_Afa` tinyint(2) unsigned default '20',
`Termek_UtolsoLeltarDatuma` date default NULL,
`Termek_Vamtarifa` varchar(30) default NULL,
`Termek_EladasiAr1` int(11) default NULL,
`Termek_EladasiAr2` int(11) default NULL,
`Termek_EladasiAr3` int(11) default NULL,
`Termek_EladasiAr1N` int(11) default NULL,
`Termek_EladasiAr2N` int(11) default NULL,
`Termek_EladasiAr3N` int(11) default NULL,
`Termek_UtolsoBeszallitoKod` int(11) unsigned default NULL,
`Termek_UtolsoBeszallitoNev` varchar(80) default NULL,
`Termek_EgyediSorszamuTermek` int(11) default '0',
`Termek_HosszbanTaroltTermek` int(1) default '0' COMMENT '0 vagy 1',
`Termek_Hosszusag` int(5) unsigned default NULL,
`Termek_Raktarkod` int(11) unsigned default '0',
`Termek_GyartasiAr` int(11) default '0' COMMENT 'netto',
`Termek_Lock_termek` char(20) default '0',
`Termek_AlapertelmezettPolc` varchar(50) default NULL COMMENT 'ez tartalmazza az alapertelmezett kod egyedi nevet',
`Termek_K_BerletiDij` decimal(14,2) default '0.00',
`Termek_K_ToresiAr` decimal(14,2) default '0.00',
`Termek_K_LetetiDij` decimal(14,2) default '0.00',
`Termek_K_MosasiAr` decimal(14,2) default '0.00',
`Termek_K_Foglalo_Osszege` decimal(14,2) default '0.00',
`Termek_K_IsKotelezoMosogatas` smallint(2) default '0',
`Termek_K_IsUjraHasznalhato` smallint(2) default '0',
`Termek_K_KintlevoKeszlet` double(15,2) default '0.00',
`Termek_K_MegNemHasznalhatoKeszlet` double(15,2) default '0.00' COMMENT 'Bent van raktaron, de meg nem hasznalhato pl.piszkos',
`Termek_K_IsKolcsonozheto` smallint(2) default '0' COMMENT 'Ha 1re van allitva, akkor kolcsonozheto a termek. Ha nem kolcsonozheto, akkor csak eladni lehet',
`Termek_K_IgnoreAllowance` tinyint(2) default '0' COMMENT 'Ha be van kapcsolva,nem veszi figyelembe a cegek kedvezmenyet',
`Termek_Fifo_BeszerzesiArSzazalekNovel` decimal(11,2) default '20.00' COMMENT 'Ezzel a szazalekkel megnoveli Fifo eseten az eladasi arat',
`Termek_leiras` varchar(500) default NULL,
`Termek_IsEladhato` smallint(2) default '1' COMMENT 'Ha 1re van allitva, akkor eladhato a termek. ',
`Termek_Munkadij` decimal(14,2) default '0.00' COMMENT 'A termek beszerelesi,telepitesi ara',
PRIMARY KEY (`Termek_Azonosito`),
UNIQUE KEY `Termek_Azonosito` (`Termek_Azonosito`),
UNIQUE KEY `Termek_Megnevezes` (`Termek_Megnevezes`),
UNIQUE KEY `Termek_SajatCikkSzam` (`Termek_SajatCikkSzam`),
KEY `Termek_TermekCsop_Azon` (`Termek_TermekCsop_Azon`),
CONSTRAINT `termek_ibfk_1` FOREIGN KEY (`Termek_TermekCsop_Azon`) REFERENCES `termekcsoport` (`termekcsop_azon`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10337 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=203;

CREATE TRIGGER `termek_after_ins_tr` AFTER INSERT ON `termek`
FOR EACH ROW
BEGIN

If GetEgyBeallitas("AutomataVonalkodGeneralas")="1" Then
call GeneralAutomataVonalkod(new.Termek_Azonosito);
End IF;

END;

At first I tried Execute, but after I tried this:

with MyScript do
begin
for j := 0 to Statements.Count - 1 do
Begin

writeln( IntToStr(j) +' '+Statements[j].SQL); //feedback

Statements[j].Execute;
end;
end;

And this is very interesting too: the help states that this code above would execute the statements one by one but the "writeln( IntToStr(j) +' '+Statements[j].SQL); " shows me that the MyScript thinks that it is only one statement

Is something to do with delimiter? If so, how can I set it? I tried putting in the Sql but it didn't work.

Thank you in advance

Dimon · Post by **Dimon** » Fri 26 Jun 2009 08:08

TMyScript has the Delimiter property to set the delimiter string that separates script statements. Also, to separate single statements you can use semicolon (;), slash (/) ,and the DELIMITER keyword.

It seems that TMyScipt separates the CREATE TRIGGER query on a few statements by semicolon delimiter.
To solve the problem you can use the following code:

Code: Select all

DELIMITER $$

CREATE TRIGGER `termek_after_ins_tr` AFTER INSERT ON `termek` 
FOR EACH ROW 
BEGIN 
If GetEgyBeallitas("AutomataVonalkodGeneralas")="1" Then 
call GeneralAutomataVonalkod(new.Termek_Azonosito); 
End IF; 
END $$

DELIMITER ;

Zsolt Csillag · Post by **Zsolt Csillag** » Fri 26 Jun 2009 21:27

Hello,

I tried EXACTLY YOUR CODE:
DELIMITER $$

CREATE TRIGGER `termek_after_ins_tr` AFTER INSERT ON `termek`
FOR EACH ROW
BEGIN
If GetEgyBeallitas("AutomataVonalkodGeneralas")="1" Then
call GeneralAutomataVonalkod(new.Termek_Azonosito);
End IF;
END $$

However this also gave me an error. It is interesting that the code above in the DbMonitor changed into code with only one "$":

CREATE TRIGGER
`termek_after_ins_tr` AFTER INSERT ON `termek`
FOR EACH ROW
BEGIN

If GetEgyBeallitas("AutomataVonalkodGeneralas")="1" Then
call GeneralAutomataVonalkod(new.Termek_Azonosito);
End IF;

END $

And the error in the DbMonitor is:

#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$' at line 10

What can be the problem? Thank you very much.[/code]

Dimon · Post by **Dimon** » Tue 30 Jun 2009 06:59

I could not reproduce the problem.
Please try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

Also supply me the following information:
- the exact version of MyDAC. You can see it in the About sheet of TMyConnection Editor;
- the exact version of your IDE.

Zsolt Csillag · Post by **Zsolt Csillag** » Tue 30 Jun 2009 08:10

Hello,

I found out that if the DbMonitor was open, I got the error. If the DbMonitor was not open, everything was fine.

I think it should be a bug in DbMonitor.

Thank you very much for your help.

Dimon · Post by **Dimon** » Tue 30 Jun 2009 14:07

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next MyDAC build.
As a temporary solution you can use the TMyScript.Execute method instead of calling TMyScript.Statements.Execute in loop.