Lazarus 1.2.2 + ZeosLIB 7.1.3 + MySQL
lazarus / 16-Databases

----------------------------------------------------------------------
robert83a1 · 2014-05-26 06:50

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 · 2014-05-26 08:52

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 · 2014-05-26 17:05

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 · 2014-05-26 20:22

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 · 2014-05-26 21:12

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 · 2014-05-26 21:38

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