We have a solution with the previous DAC since 2012 and we are trying to make a seamless migration to UniDAC.
While I was doing my testings on batch operations, I found an error executing a batch using manual parameters:
Our UniQuery objects have the property ParamCheck always set to false, this is because we always create the query parameters manually. So executing the next code (based on the help topic Bacth Operations) returns an access violation:
Code: Select all
procedure TFTest_DataAccessComponents.Button1Click(Sender: TObject);
var
i, smallint: Integer;
UniQueryTest: TUniQuery;
begin
StartTest(UniConnection1);
UniConnection1.StartTransaction;
UniQueryTest:= nil;
try
// creates a UniQuery object and defines it's properties
UniQueryTest := TUniQuery.Create(Self);
with UniQueryTest do begin
Name := 'UniQueryTest';
Connection := UniConnection1;
CachedUpdates := True; // Important setting in our project. This values can't be changed
ParamCheck:= False; // Important setting in our project. This values can't be changed. We always define the params manually. Note: When this values is true, the batch insertion works
end;
// describe the SQL query
UniQueryTest.Close;
UniQueryTest.Params.Clear;
UniQueryTest.SQL.Clear;
// The SQL text is generated the same way by a function that can't be changed
UniQueryTest.SQL.Add('INSERT INTO _UniDacDemo ("ID", "F_INTEGER", "F_FLOAT", "F_STRING", "F_DATETIME", "F_SMALLINT", "F_MEMO", "F_CURRENCY", "F_DATE", "F_BOOLEAN")');
UniQueryTest.SQL.Add('VALUES (:F1, :F2, :F3, :F4, :F5, :F6, :F7, :F8, :F9, :F10)');
// definition of the parameter types passed to the query :
{UniQuery1.Params.CreateParam(ftInteger, 'ID', ptInput);}
UniQueryTest.Params.Add;
UniQueryTest.Params.Add;
UniQueryTest.Params.Add;
UniQueryTest.Params.Add;
UniQueryTest.Params.Add;
UniQueryTest.Params.Add;
UniQueryTest.Params.Add;
UniQueryTest.Params.Add;
UniQueryTest.Params.Add;
UniQueryTest.Params.Add;
UniQueryTest.Params[0].DataType := ftInteger;
UniQueryTest.Params[1].DataType := ftInteger;
UniQueryTest.Params[2].DataType := ftFloat;
UniQueryTest.Params[3].DataType := ftString;
UniQueryTest.Params[4].DataType := ftDateTime;
UniQueryTest.Params[5].DataType := ftSmallint;
UniQueryTest.Params[6].DataType := ftMemo;
UniQueryTest.Params[7].DataType := ftCurrency;
UniQueryTest.Params[8].DataType := ftDate;
UniQueryTest.Params[9].DataType := ftBoolean;
// specifies the array dimension:
UniQueryTest.Params.ValueCount := 1000;
smallint := 1000;
// populates the array with parameter values:
for i:= 0 to UniQueryTest.Params.ValueCount - 1 do begin
smallint := smallint + 1000 + 10;
if smallint > 32767 then
smallint:= 1;
UniQueryTest.Params[0][i].AsInteger := i + 1;
UniQueryTest.Params[1][i].AsInteger := i + 2000 + 1;
UniQueryTest.Params[2][i].AsFloat := (i + 1) / 12;
UniQueryTest.Params[3][i].AsString := 'Values ' + IntToStr(i + 1);
UniQueryTest.Params[4][i].AsDateTime := Now;
UniQueryTest.Params[5][i].AsSmallInt := smallint;
UniQueryTest.Params[6][i].AsMemo := 'Memo ' + IntToStr(i + 1);
UniQueryTest.Params[7][i].AsCurrency := (i + 100) / 11;
UniQueryTest.Params[8][i].AsDate := Date;
UniQueryTest.Params[9][i].AsBoolean := (i mod 2 = 0);
end;//for
// insert 1000 rows into the BATCH_TEST table
UniQueryTest.Execute(1000);
UniConnection1.Commit;
except on E: Exception do begin
if UniConnection1.InTransaction then
UniConnection1.Rollback;
ShowMessage(e.Message);
end;//Exception
end;//try-except
if Assigned(UniQueryTest) then
FreeAndNil(UniQueryTest);
end;
Also this may be not concern of my expertise but I found that skipping the line 8 of this portion of code contained in the uni.pas unit allows to run the batch without errors with ParamCheck set to false:
Code: Select all
procedure TUniSQL.CheckUniMacros;
var
UsedCon: TUniConnection;
begin
if EnableUniSQL then begin
UsedCon := TUniConnection(UsedConnection);
// values here are UsedCon not null, FMacrosVersion = 0, UsedCon.FMacrosVersion = 0
if (UsedCon <> nil) and (FMacrosVersion < UsedCon.FMacrosVersion) then begin
// jumping the previous if statement while debugging executes the batch operation correctly
if FICommand <> nil then begin
SetICommandSQL;
WriteParams;
end;
FMacrosVersion := UsedCon.FMacrosVersion;
end;
end;
end;
Cheers.