ExecuteEx(ClearParamSetsOnError: Boolean): Integer;
ClearParamSetsOnError. Indicates whether the processed parameter value set counter is reset after executing the method.
The ExecuteEx method executes a command at the database server and returns the number of processed records.
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.
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.
See also: