views:

95

answers:

1

I am working with Devart's MyDac and MySQL Server 5.0.41. Here is a section from the documentation on executing stored procedures with TMyConnection.ExecProc:

Note: Stored functions unlike stored procedures return result values that are obtained internally through the RESULT parameter. You will no longer have to provide anonymous value in the Params array to describe the result of the function. The stored function result is obtained from the Params[0] indexed property or with the ParamByName('RESULT') method call.

They also give an example on how to execute a stored function:

aStringVariable1 := TMyConnection.ExecProc('StoredFunctionName',['Param1','Param2']); aStringVariable2 := TMyConnection.ParamByName('Result').AsString;

By Following these examples, my execution of the stored functions are returning Param1 in the variable aStringVariable2.The execution of the functions in the Query Browser returns the right results. Any pointers on the right way to execute stored functions in MyDAC with TMyConnection or TMyStoredProc will be appreciated.

Thanks in advance.

A: 

Here is the code we use to call stored procedures - hope it helps

function TDbControl.DatabaseStoredProc(FConnectionsAddr: integer; SpName: string;var Params: TDAParams): boolean;
var
  MyStoredProc: TMyStoredProc;
  PramsTxt: String;
  Idx, Idx2: Integer;
begin
  result := False;
  MyStoredProc := nil;
  try
    try
      MyStoredProc := TMyStoredProc.Create(nil);
      MyStoredProc.Connection := TMyConnection(FConnectionsAddr);
      MyStoredProc.StoredProcName := SpName;
      MyStoredProc.ParamCheck := False;
      if assigned(Params) then
      begin
        for Idx := 0 to Params.Count - 1 do
        begin
            MyStoredProc.ParamByName(Params[Idx].Name).DataType := Params[Idx].DataType;
            MyStoredProc.ParamByName(Params[Idx].Name).Value := Params[Idx].Value;
        end;
      end;
      MyStoredProc.Execute;
      if assigned(Params) then
      begin
        for Idx := 0 to Params.Count - 1 do
        begin
         if (Params[Idx].ParamType =  ptOutput ) then
            Params[Idx].Value := MyStoredProc.ParamByName(Params[Idx].Name).Value;
        end;
      end;
      result := True;
    except
      on E: Exception do
      begin
        PramsTxt := '';
        if assigned(Params) then
        begin
          for Idx2 := 0 to Params.Count - 1 do
          begin
            PramsTxt := PramsTxt + Params.Items[Idx2].Name + '=' + Params[Idx2].AsString + ',';
          end;
        end;
        LogText(FConnectionsAddr, 'DatabaseStoredProc Err:' + E.Message + '  SpName:' + SpName + '  Prams:' + PramsTxt);
        raise ;
      end;
    end;
  finally
    FreeAndNil(MyStoredProc);
  end;
end;
Charles Faiga
I would expect *no need* to write such code with a commercial data access library. Is not MyDAC has something buildin for that ?
oodesigner
@oodesigner, MyDAC builting functionality is what I was asking about and it seems it does not have a simple way to work with stored functions. I have been able to call stored functions with no `OUT` parameters with one function call and I asked the question wondering whether there was a similar way to do the same for stored function or procedures with out parameters. @Charles, this works. Thanks
NicM
stored procedures with no `OUT` parameters and not stored functions
NicM