ExecuteEx(ClearParamSetsOnError: Boolean): Integer;
ClearParamSetsOnError. Indicates whether the processed parameter value set counter is reset after method execution.
The ExecuteEx method executes a command on the database server and returns the number of processed records.
If the True value is passed as the ClearParamSetsOnError parameter, this method works the same as the IDalCommand.Execute method.
If the ClearParamSetsOnError property is set to False, after executing this method, the counter of processed sets of parameter values available in the IDalCommand.CurrentParamsRow property is not cleared. If the command execution returns errors, get the IDalCommand.CurrentParamsRow value to find out how many sets were processed. Use the SetCurrentParamsRow method to go through the parameter sets and run a separate command with the selected values. Thus, a set of values can be determined that causes the SQL query runtime error.
Executing the example requires a database with the DB identifier in the repository. 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;
//The query that is to be executed
SQL := "insert into Table1(Comments) values(:CommentValue)";
DBInst := MB.ItemById("DB").Open(Null) As IDatabaseInstance;
Connect := DBInst.Connection;
Command := Connect.CreateCommand(SQL);
// The SQL query analysis
Command.Parse;
//Specify a 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;
//Run
Try
//Initialize transaction and run command
Transaction1 := Connect.StartTransaction(False);
CommandEx.ExecuteEx(False);
Except
//When an error occurs, rollback all changes
Transaction1.Rollback;
Debug.WriteLine("An error occurred on executing SQL query ");
//Check the command running in a 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 foe checking
CheckParam.Value := Param.Value;
Try
CheckCommand.Execute;
Except
Debug.WriteLine("Error on running the query with the parameter value:" + Command.Params.Item(0).Value);
End Try;
End For;
//Rollback all the changes made during checking
Transaction2.Rollback;
Finally
If Connect.InTransaction Then
Transaction1.Commit;
End If;
End Try;
//Clear the counter of parameter sets
CommandEx.ClearParamSets;
//Change parameter sets for further use
//The CommandEx commands
//...
End Sub UserProc;
During executing this example, the SQL command is run to insert a set of values in the table. After executing the command the counter of parameter value sets is not cleared. If an error occurs at runtime, the SQL command execution is checked with separate parameter values.
See also: