Recent

Author Topic: MySQL store procedure return values  (Read 1943 times)

eldonfsr

  • Sr. Member
  • ****
  • Posts: 446
MySQL store procedure return values
« on: September 24, 2017, 12:01:39 am »
Hello everybody i start test mysql with store procedures I can call that and runs well but now i wanna see mysql return value for example  if the procedure was executed or some error happend.

i put store procedure to insert a record and validate if that not exist.
Code: Pascal  [Select][+][-]
  1.  
  2. CREATE DEFINER=`root`@`localhost` PROCEDURE `REG_PART`(IN PARTID CHAR(20),IN PARTDESC CHAR(120), IN PARTCAT CHAR(20)
  3. , IN PARTSBC CHAR(20), IN PARTC DECIMAL(18,2), IN PARTPR DECIMAL(18,3), IN PARTSMI DECIMAL(18,3),
  4. IN PARTSTMA DECIMAL(18,3), IN PARTST DECIMAL(18,3), IN PARTLOC CHAR(60), IN PARTPV CHAR(20), OUT ERROR INT)
  5. BEGIN
  6. DECLARE ERROR INT;
  7.    IF EXISTS(
  8.          SELECT PART_NUMBER FROM PARTS WHERE PART_NUMBER = PARTID
  9.                  )
  10.                  THEN
  11.                                  SET @ERROR=1;
  12.          ELSE
  13.  
  14.                    INSERT INTO PARTS(PART_NUMBER,PART_DESCRIPTION,CATEGORY,SUBCATEGORY,PART_COST,PART_REORDER, PART_MINSTOCK,
  15.                          PART_MAXSTOCK,PART_STOCK,PART_LOC, PROVIDER)
  16.                    VALUES( PARTID, PARTDESC,PARTCAT,PARTSBC,PARTC,PARTPR,PARTSMI,PARTSTMA,PARTST,PARTLOC,PARTPV);
  17.       SET ERROR=0;     
  18.                 END IF;
  19. END;
  20.  
i wanna to see if var error get some value when record exist.
and here is my code to call and execute mysql.
Code: Pascal  [Select][+][-]
  1. procedure TFormPI.BtnOkClick(Sender: TObject);
  2. var stm:string;
  3.     error:integer;
  4. begin
  5.    error:=0;
  6.   if( FormPCtrl.lnew = true) then begin
  7.      stm:='call REG_PART('''+EPartNum.Text+''','''+EPartName.Text+''',''';
  8.      stm+=DBCBCat.Text+''','''+DBCBSCat.Text+''','''+ECost.Text+''',''';
  9.      stm+=EReOrder.Text+''','''+ESTMin.Text+''','''+ESTMax.Text+''',''';
  10.      stm+=EPartSt.Text+''','''+ELoca.Text+''','''+DBCBProv.Text+''',@'''+IntToStr(error)+''')';
  11.       Memo1.Text:= stm;
  12.      FormPCtrl.SQLQParts.SQL.Text:=stm;
  13.      FormPCtrl.SQLQParts.ExecSQL;
  14.   end
  15.   else
  16.   begin
  17.  
  18.   end;
  19.   FormMain.SQLT.Commit;
  20.   FormPCtrl.SQLQParts.SQL.Text:='select * from parts;';
  21.   FormPCtrl.DBGrid1.Refresh;
  22.   FormPI.Close;
  23. end;  
  24.  

the question is how i get the value on error var.

some hel pls.

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: MySQL store procedure return values
« Reply #1 on: September 24, 2017, 10:36:50 am »
You can better use parameters. Look at this example
After the execsql, the out parameter can be read into your program.

- create all with params
- fill params
- exec your query
- read your out param.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

eldonfsr

  • Sr. Member
  • ****
  • Posts: 446
Re: MySQL store procedure return values
« Reply #2 on: September 25, 2017, 02:13:50 am »
Ok Thanks, Let me change my code.

 

TinyPortal © 2005-2018