TZSQLProcessor to insert Stored Procedures into MySQL-DB

[Igotcha]

Hello,

I use TZSQLProcessor to create tables and fill them with data via SQL-script for an installation process.

What is not working is to create Stored Procedures in the DB with TZSQLProcessor.

This is an example of a SP code, generated with "MySQL Query Browser":

Code: Select all

DELIMITER $$

DROP PROCEDURE IF EXISTS `deletelizenz` $$
CREATE PROCEDURE `deletelizenz`(INID INT(6))
BEGIN
    DELETE FROM pim_pb_liz WHERE id=INID;
    DELETE FROM pim_pb_liz_fl WHERE lizid=INID;
    DELETE FROM pim_pb_liz_zr WHERE lizid=INID;
END $$

DELIMITER ;

I tried with several "Delimiter" and "Delimiter Type" settings but with no success.

I use MySQL 5.x and ZEOS 6.5.x

Thanks,
Igotcha

[Igotcha]

Need to push, sorry.

[mdaems]

Hi Igotcha,

I just fixed this problem in the SVN Testing branch. I hope it does not break the behaviour in other database environments. Changed this :

Code: Select all

procedure TZSQLProcessor.Parse;
begin
  CheckConnected;
  FScriptParser.Tokenizer := Connection.DbcDriver.GetTokenizer;
// mdaems 20060429 : Clear would reset the delimiter of the scriptparser
//  FScriptParser.Clear;
  FScriptParser.ClearUncompleted;
  FScriptParser.ParseText(FScript.Text);
end;

Now set Delimiter to '$$' and Delimiter Type to 'dtSetTerm'. Do not put 'set delimiter' commands in your script. (In fact, these are client commands invented for the mysql console client and the mysql query browser, not understood by the server) The SQLProcessor will split your script into different statements (2) and feed them to the server one by one.

This means you can't use 2 different delimiters in the same script. Your program will have to split the script where the delimiter should be changed and feed the parts to the sqlprocessor separately with the appropriate settings.

Succes,

Mark

[Igotcha]

Works perfect this way, thanks :-)