MySQL store procedure return values
lazarus / 16-Databases

----------------------------------------------------------------------
eldonfsr · 2017-09-23 22:01

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  CREATE
DEFINER=`root`@`localhost` PROCEDURE `REG_PART`(IN PARTID CHAR(20),IN
PARTDESC CHAR(120), IN PARTCAT CHAR(20), IN PARTSBC CHAR(20), IN PARTC
DECIMAL(18,2), IN PARTPR DECIMAL(18,3), IN PARTSMI DECIMAL(18,3),IN
PARTSTMA DECIMAL(18,3), IN PARTST DECIMAL(18,3), IN PARTLOC CHAR(60),
IN PARTPV CHAR(20), OUT ERROR INT)BEGINDECLARE ERROR INT;   IF
EXISTS(         SELECT PART_NUMBER FROM PARTS WHERE PART_NUMBER =
PARTID                 )                 THEN                        
       SET @ERROR=1;         ELSE                    INSERT INTO PARTS
(PART_NUMBER,PART_DESCRIPTION,CATEGORY,SUBCATEGORY,PART_COST,PART_REOR
DER, PART_MINSTOCK,                       
 PART_MAXSTOCK,PART_STOCK,PART_LOC, PROVIDER)                 
 VALUES( PARTID, PARTDESC,PARTCAT,PARTSBC,PARTC,PARTPR,PARTSMI,PARTSTM
A,PARTST,PARTLOC,PARTPV);      SET ERROR=0;                      END
IF;END; 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TFormPI.BtnOkClick(Sender: TObject);var stm:string;   
error:integer;begin   error:=0;  if( FormPCtrl.lnew = true) then
begin     stm:='call
REG_PART('''+EPartNum.Text+''','''+EPartName.Text+''',''';   
 stm+=DBCBCat.Text+''','''+DBCBSCat.Text+''','''+ECost.Text+''','''; 
  stm+=EReOrder.Text+''','''+ESTMin.Text+''','''+ESTMax.Text+''','''; 
   stm+=EPartSt.Text+''','''+ELoca.Text+''','''+DBCBProv.Text+''',@'''
+IntToStr(error)+''')';      Memo1.Text:= stm;   
 FormPCtrl.SQLQParts.SQL.Text:=stm;     FormPCtrl.SQLQParts.ExecSQL; 
end  else  begin   end;  FormMain.SQLT.Commit; 
FormPCtrl.SQLQParts.SQL.Text:='select * from parts;'; 
FormPCtrl.DBGrid1.Refresh;  FormPI.Close;end;   
the question is how i get the value on error var.

some hel pls.

----------------------------------------------------------------------
mangakissa · 2017-09-24 08:36

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.

----------------------------------------------------------------------
eldonfsr · 2017-09-25 00:13

Ok Thanks, Let me change my code.