Executing Stored Procedures and Functions: the Dal Assembly

Executing Stored Procedures and Functions

The created command for executing SQL queries also enables the user to execute a procedure or function stored in the database. To do this, specify the procedure or function name instead of the query in the IDalCommand.SQL property and specify the DalCommandType.StoredProcedure type in the IDalCommand.Type property. To get function execution results in the IDalCommand.Params collection, create a parameter. Specify the DalParamDirection.ReturnValue or DalParamDirection.RefCursor value for this parameter in the IDalCommandParam.Direction property. Call the IDalCommand.Execute method to start execution:

Var
    //...
    ORCLConnect: IDalConnection;
    Command: IDalCommand;
    Param: IDalCommandParam;
    //...
Begin
    //...
    Command := ORCLConnect.CreateCommand;
    //The F_LOAD executed function
    Command.SQL := "F_LOAD";
    Command.Type := DalCommandType.StoredProcedure;
    //Create a parameter, in which function execution result is available
    Param := Command.Params.Add("Result");
    Param.Direction := DalParamDirection.ReturnValue;
    Command.Execute;
    Command.Close;
    Debug.WriteLine("Output value: " + Param.Value);