Get return result from TMyStoredProc

[dwh]

Delphi 7, MySql 5...

I have a stored procedure that returns the value of an auto-incremented field.

Code: Select all

CREATE FUNCTION `spAddSignalData`(
  vMeasured Double Precision,
  vResolution Double Precision,
  vSignalFollowed Double Precision,
  vVariance Double Precision,
  vRate Double Precision,
  vRateVariance Double Precision) RETURNS int(11)
BEGIN

DECLARE RecId Integer;

INSERT INTO SignalData
 (Measured, Resolution, SignalFollowed, Variance, Rate, RateVariance)
VALUES
 (vMeasured, vResolution, vSignalFollowed, vVariance, vRate, vRateVariance);

SELECT LAST_INSERT_ID() INTO RecId;

RETURN RecId;
END

The sp works fine.

I have some Delphi code to run the sp. It executes Ok, but I can't figure out how to get the returned result from the sp. Asking for a parameter called RETURN_VALUE or @Return_value does not work.

Code: Select all

procedure TSignalData.SaveToMySqlDatabase_SP(SP : TMyStoredProc);
begin
  SP.StoredProcName := 'spAddSignalData';
  SP.ParamByName('vMeasured').AsFloat := Measured;
  SP.ParamByName('vResolution').AsFloat := Resolution;
  SP.ParamByName('vSignalFollowed').AsFloat := SignalFollowed;
  SP.ParamByName('vVariance').AsFloat := Variance;
  SP.ParamByName('vRate').AsFloat := Rate;
  SP.ParamByName('vRateVariance').AsFloat := RateVariance;
  SP.Execute;
  SignalId := SP.ParamByName('RETURN_VALUE').AsInteger;
end;

I get the error that parameter RETURN_VALUE is unknown.

What is the correct process to get the return result from a stored procedure call?

Thanks
DaveH

[Antaeus]

MySQL Server data transferring protocol does not support output parameters. Please refer to this topic: http://crlab.com/forums/viewtopic.php?t=2790.

[LarryEvilsizer]

dwh, can't help you about getting the result of stored procedure, but you can get the last auto-increment value for an insert from the insertid attribute in a TCustomMyDataSet object. See pg. 102 of the Mydac reference manual.

Larry

[digitron3000]

Este es un ejemplo:

Code: Select all

if DBConeccion.Connected then
               begin

               MiProcedimiento.Connection:=DBConeccion;
               MiProcedimiento.StoredProcName:='ExisteEmpleado';
               MiProcedimiento.ParamByName('n_dni').AsString:=NumeroID;
               MiProcedimiento.Execute;
                  try
                  if (MiProcedimiento.ParamByName('result').AsBoolean) then
                        DNIExiste:=2
                  else
                        DNIExiste:=0;
                  except
                  // generar execpcion
                  end;

               end;