IDalCommandEx.ExecuteEx

Syntax

ExecuteEx(ClearParamSetsOnError: Boolean): Integer;

Parameters

ClearParamSetsOnError. Indicates whether the processed parameter value set counter is reset after executing the method.

Description

The ExecuteEx method executes a command at the database server and returns the number of processed records.

Comments

If the True value is passed as the ClearParamSetsOnError parameter, this method works similarly with the IDalCommand.Execute method.

If the ClearParamSetsOnError property is set to False, after executing the method, the counter of processed sets of parameter values available in the IDalCommand.CurrentParamsRow property is not cleared. If some errors occurred during command execution, get the IDalCommand.CurrentParamsRow value to find out how many sets were processed. Use the SetCurrentParamsRow method to go through the parameter sets and execute a separate command with the selected values. Thus, a set of values can be determined that causes the SQL query runtime error.

Example

Executing the example requires that the repository contains a database with the DB identifier. Create a table with the Table1 name in the DBMS, to which the database is set up. This table has the Comments character field that has a limit for the data length of no more that 60 characters.

Sub UserProc;
Var
    //...
    MB: IMetabase;
    DBInst: IDatabaseInstance;
    Connect: ISecurityConnection;
    Transaction1, Transaction2: IConnectionTransaction;
    Command, CheckCommand: IDalCommand;
    CommandEx: IDalCommandEx;
    Param, CheckParam: IDalCommandParam;
    i, Count: Integer;
    SQL: String;
Begin
    MB := MetabaseClass.Active;
    //Query that is executed
    SQL := "insert into Table1(Comments) values(:CommentValue)";
    DBInst := MB.ItemById("DB").Open(Null) As IDatabaseInstance;
    Connect := DBInst.Connection;
    Command := Connect.CreateCommand(SQL);
    //SQL query analysis
    Command.Parse;
    //Specify collection of parameter values
    Param := Command.Params.Item(0);
    Param.DataType := DbDataType.String;
    Command.MaxParamsRows := 4;
    //First value
    Param.Value := "Value 1";
    Command.NextParamsRow;
    //Second value
    Param.Value := "Value 2";
    Command.NextParamsRow;
    //Third value
    Param.Value := " A value too long for the     table field".
    Command.NextParamsRow;
    //Forth value
    Param.Value := "Value 4";
    //Get command
    CommandEx := Command As IDalCommandEx;
    //Execute
    Try
        //Initialize transaction and execute command
        Transaction1 := Connect.StartTransaction(False);
        CommandEx.ExecuteEx(False);
    Except
        //When error occurs, rollback all changes
        Transaction1.Rollback;
        Debug.WriteLine("An error occurred on executing SQL query  ");
        //Check command execution in separate transaction
        //with separate parameter values.
        //To do this, create a separate command
        Transaction2 := Connect.StartTransaction(False);
        CheckCommand := Transaction2.CreateCommand(SQL);
        CheckCommand.Parse;
        CheckParam := CheckCommand.Params.Item(0);
        //Initialize a separate transaction
        Count := Command.CurrentParamsRow;
        For i := 0 To Count Do
            CommandEx.SetCurrentParamsRow(i);
            //Set a value used for checking
            CheckParam.Value := Param.Value;
            Try
                CheckCommand.Execute;
            Except
                Debug.WriteLine("Error on executing query with parameter value:" + Command.Params.Item(0).Value);
            End Try;
        End For;
        //Rollback all changes made during checking
        Transaction2.Rollback;
    Finally
        If Connect.InTransaction Then
            Transaction1.Commit;
        End If;
    End Try;
    //Clear counter of parameter sets
    CommandEx.ClearParamSets;
    //Change parameter sets for further use
    //The CommandEx commands
    //...
End Sub UserProc;

On executing the example the SQL command is executed to insert a value set in the table. After executing the command the counter of parameter value sets is not cleared. If an error occurs at execution, the SQL command execution is checked with separate parameter values.