Lazarus 1.2.2 + ZeosLIB 7.1.3 + MySQL

[robert83a1]

Hi all,
 I'm trying to convert my app to using MySQL, I've converted the tables without any problems, right now I did one procedure by hand... I don't know 100% if it's okay...under SQL I was able to add it , only thing is I don't know how to execute it even from the query :

Code: [Select]

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spUser`(IN OP varchar(10),
IN ID int,
IN Name varchar(50),
IN Password varchar(50),
IN Rights int,
IN ModifierID int,
IN ModifyDate datetime,
IN LockedUserID int,
OUT BackID int ,
OUT ERROR int
)
BEGIN
DECLARE locked int;
DECLARE CheckInsert int; 
DECLARE CheckModify int;
SET CheckInsert = 0;
SET CheckModify = 0;

SELECT count(*) INTO CheckInsert from User where Name=@Name;
SELECT count(*) INTO CheckModify from User where Name=@Name and id<>@id;

select locked = ifnull(Locked, 0) from User where ID = @ID;

  IF @OP='LOCK' THEN
      IF @lock=0 THEN
          UPDATE User set Locked=1,LockedUserID=@LockedUserID
          where ID=@ID;
        END IF;
      IF @lock=1 THEN
          UPDATE User set Locked=0,LockedUserID=''
          where ID=@ID;
        END IF;
  END IF;

  IF @OP='INS' THEN
      IF (CheckInsert = 0) THEN
        INSERT INTO User(Name,Password,Rights,ModifierID,ModifyDate)
      VALUES(ltrim(rtrim(@Name)), ltrim(rtrim(@Password)), @Rights, @ModifierID, @ModifyDate);
      SELECT @ERROR = '0';
SELECT @BackID = IDENT_CURRENT('[User]');       
   ELSE
       SELECT @ERROR='1';
   END IF;
  END IF;

  IF @OP='UPD' THEN
       IF (CheckModify = 0) THEN
        UPDATE User SET Name=ltrim(rtrim(@Name)),
              Password=ltrim(rtrim(@Password)),
              Rights=@Rights,
              ModifierID=@ModifierID,
              ModifyDate=@ModifyDate
        where ID=@ID;
        SELECT @ERROR='0';
   ELSE
       SELECT @ERROR='1';
       END IF;
   END IF;

  IF @OP='DEL' THEN
       DELETE FROM User where ID=@ID;
  END IF;

END

I've tried : CALL spUser('INS','11','Test','test','1','0','2014-05-26 00:13:00','0','0','0') , but it complains about last 2 variables, which our OUT , I don't know why I need to specify it here... : Error Code: 1414. OUT or INOUT argument 9 for routine _planb.spUser is not a variable or NEW pseudo-variable in BEFORE trigger

Anyway from Lazarus, I was able to connect to this db by using MySQL protocol, I was able to select a table and display data... but for some reason when I insert a ZStoredProcedure , it does not display available Stored Procedures (under MSSQL 2008 r2 it does get them...) so I manually entered them by name spUser , and manually added the 10 parameters...but then it complains about Syntax error.


Can someone point out the mistake here ?

Greetings
Robert

[robert83a1]

So I did a bit of further playing and this is working , INS , UPD, DEL :

Code: [Select]

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `spUser`(IN pOP varchar(10),
IN pID int,
IN pName varchar(50),
IN pPassword varchar(50),
IN pRights int,
IN pModifierID int,
IN pModifyDate datetime,
IN pLockedUserID int
/*OUT pBackID int ,
--OUT pERROR int */
)
BEGIN
DECLARE plock int;
DECLARE CheckInsert int; 
DECLARE CheckModify int;
SET CheckInsert = 0;
SET CheckModify = 0;

SELECT count(*) INTO CheckInsert from User where Name=pName;
SELECT count(*) INTO CheckModify from User where Name=pName and id<>@id;

select plock = ifnull(Locked, 0) from User where ID = pID;

  IF pOP='LOCK' THEN
      IF plock=0 THEN
          UPDATE User set Locked=1,LockedUserID=pLockedUserID
          where ID=pID;
        END IF;
      IF plock=1 THEN
          UPDATE User set Locked=0,LockedUserID=''
          where ID=pID;
        END IF;
  END IF;

  IF pOP='INS' THEN
      IF (CheckInsert = 0) THEN
        INSERT INTO User(Name,Password,Rights,ModifierID,ModifyDate)
      VALUES(ltrim(rtrim(pName)), ltrim(rtrim(pPassword)), pRights, pModifierID, pModifyDate);
      /* SELECT pERROR = '0';
SELECT pBackID = IDENT_CURRENT('[User]');        */
   ELSE
       SELECT pERROR='1';
   END IF;
  END IF;

  IF pOP='UPD' THEN
       IF (CheckModify = 0) THEN
        UPDATE User SET Name=ltrim(rtrim(pName)),
              Password=ltrim(rtrim(pPassword)),
              Rights=pRights,
              ModifierID=pModifierID,
              ModifyDate=pModifyDate
        where ID=pID;
        /*SELECT pERROR='0';*/
   /*ELSE
       SELECT pERROR='1'; */
       END IF;
   END IF;

  IF pOP='DEL' THEN
       DELETE FROM User where ID=pID;
  END IF;

END

Unfortuantely I don't know how to handle OUT variables...
for example I would like to return if something succeded see
SELECT pERROR='0' for example 0 means OK, 1 means problem... I handle this in my app....

Greetings
Robert

[miab3]

Code: [Select]

ZStoredProc1.Close;
ZStoredProc1.StoredProcName:='proc1';
ZStoredProc1.Params[0].Value:=50;
ZStoredProc1.ParamByName('inParam1').Value:='abc';
...
ZStoredProc1.Open;
And look for OUT in dataset:

Code: [Select]

o1:=ZStoredProc1.ParamByName('outParam1').AsInteger;
o2:=ZStoredProc1.Params[5].AsString;
Michal

[robert83a1]

Thank you very much Michal ,

 My problem is that if I leave the OUT parameters inside the query and from MySQL I try to execute it...like this

 CALL `_planb`.`spUser`('LOCK', '10011','0','0','0','0','2014-05-26 10:49:00','0','OUT_PARAMETER, I just type 0 here');

 I get this error from MySQL

 Error Code: 1414. OUT or INOUT argument 9 for routine _planb.spUser is not a variable or NEW pseudo-variable in BEFORE trigger

 Any ideas why?

 UPDATE thank's to the nice people at MySQL this is how it works :
CALL `_planb`.`spUser`('LOCK', '10011','0','0','0','0','2014-05-26 10:49:00','0',@RESULT);
Greetings
Robert

[robert83a1]

What I find very strange is that in Zeoslib MySQL stored procedures don't show up...only if I enter them manually

but then the parameters are empty as well...

whereas in MSSQL it does show up...and it does populate parameters...

is this normal behavior with MySQL....

I can get the spUser from MySQL query working...but not from Lazarus, if I add manually the parameters.... it does an access violation

Greetings
Robert

[robert83a1]

SOLVED :

 How stupid : in ZConnection you need to set CATALOG to _planB (my database ) as well...

I only had Database set to _planB ...

 now I can see available list of stored procedures...unfortunately...I still have to manually put in parameters...but after that it works like this :
 
 

Code: [Select]

   spUser.Params.ParamByName('pOP').AsString:='INS';
  spUser.Params.ParamByName('pName').AsString:=Edit1.Text;
  spUser.Params.ParamByName('pPassword').AsString:=Edit2.Text;
  spUser.Params.ParamByName('pRights').AsInteger:=strtoint(Edit3.Text);
  spUser.Params.ParamByName('pModifierID').AsInteger:=1;
  spUser.Params.ParamByName('pModifyDate').AsDateTime:=date+time;
  spUser.Params.ParamByName('pLockedUserID').AsInteger:=0;

  spUser.ExecProc;
 
Greetings
Robert