DML Array with ftLargeInt on Delphi XE3

DML Array with ftLargeInt on Delphi XE3

Postby Shaun » Thu 16 Feb 2017 14:45

We have just added support for ftLargeInt into a project.
We have code that puts together a DML array for insert delete and update operations.

When using and ftLargeInt parameter type and a delphi int64 type I recieve and error from what looks like the variant code. EVariantInvalidArgError - Invalid arguement when using any field in the SQL which is an ftLargeInt field.

I have found that if I always set the parameter length to 1 then the changes to all records will be committed, however the code calculates the length parameter normally and works for all other data types apart from blob. I believe a DML array is more efficient and I can get by with setting the length to 1 if we have a ftLargeInt field for now, but the largeInt has been introduced for ID fields and I do not wish to affect performance if possible.

Initially I migrated from version 8.6.1.2 to the Demo of version 9.7.2.8 for XE3 and it made no difference. The build up of the DML array works for all other types e.g. ftInteger, and ftString etc correctly, to get ftLargeInt to work I have to set the length to 1 however.

Have I missed something, given that the existing code works with all types or is the issue in the component?
Shaun
 
Posts: 3
Joined: Thu 16 Feb 2017 13:49

Re: DML Array with ftLargeInt on Delphi XE3

Postby Shaun » Fri 17 Feb 2017 09:17

It would appear that all the SQL is processed.
The EVariantInvalidArgError - Invalid arguement issue occurs when the final commit is processed.

All other data types work correctly, its only when a ftLargeInt is passed to the DML array for processing that this error occurs.

Any help appreciated as we have to move the system db identifiers to 64 bit a.s.a.p.
Shaun
 
Posts: 3
Joined: Thu 16 Feb 2017 13:49

Re: DML Array with ftLargeInt on Delphi XE3

Postby MaximG » Wed 22 Feb 2017 07:34

To investigate the issue, please provide a small code snippet showing the DML Array work with ftLargeInt in your application.
MaximG
Devart Team
 
Posts: 375
Joined: Mon 06 Jul 2015 11:34

Re: DML Array with ftLargeInt on Delphi XE3

Postby Shaun » Wed 22 Feb 2017 16:54

This array set up has worked with all other types. The introduction of an Int64 fails if I don't set the length to 1. as discussed above.
The type is mapping to ftLargeInt correctly, the only thing that gets it to work is forcing the length to 1. I'm guessing this makes the use of the array less efficient and a bit annoying as all my tables will have largeint id fields in oracle.

Code: Select all
procedure TMyOraArray.Setup(dbDataBaseLayer: IDBLayer; aMyColumnDefs: IColumnDefs; dbTableName: string;  aRows: integer; aSQLList: TStringList);
var
  VLoop: integer;
  vMyColDef: IMyColumnDef;
  VParametereterIndex: integer;
  VParameter: TOraParam;
  bContainsNonArrayFieldType: boolean;
begin
  FDBLayer := dbDataBaseLayer;
  FRows := aRows;

  MyOraSQL.Free;
  MyOraSQL := TOraSQL.Create(nil);
  MyOraSQL.Session := dbDataBaseLayer.OraSession;

  SetSQLStatement(dbTableName, aMyColumnDefs, aSQLList);

  bContainsNonArrayFieldType := False;
 
  for VLoop := 0 to aMyColumnDefs.Count - 1 do

  // SETTING INT64 here to have a max array size of 1 below prevents error in variants after all the SQL has run.
  // It does run but fails on commit
  // Other data types such as Integer work correctly. 
  // dctInt64 is mapped to a ftLargeInt
  if IncludeColumn(aMyColumnDefs[VLoop]) and ((aMyColumnDefs[VLoop].ColumnType = cnstBlob)
      or (aMyColumnDefs[VLoop].ColumnType = cnstInt64)) then
    begin
      bContainsNonArrayFieldType := true;
    end;

  // this Determines the parameter length against rows in array
  if bContainsNonArrayFieldType then
    FMaxArraySize := 1
  else
    FMaxArraySize := 5000;

  VParametereterIndex := -1;
 
  for VLoop := 0 to aMyColumnDefs.Count - 1 do
  begin
    vMyColDef := aMyColumnDefs[VLoop];
    if IncludeColumn(vMyColDef) then
    begin
      Inc(VParametereterIndex);
    
      if FUseNames then
        VParameter := MyOraSQL.ParamByName(vMyColDef.FieldNameGuess)
      else
        VParameter := MyOraSQL.Params[VParametereterIndex];
      
      
      VParameter.ParamType := ptInput;
    
     // this maps cnstint to ftInteger , cnststring to ftString , cnstInt64 to ftLargeInt  etc.
      VParameter.DataType := ColumnTypeToFieldType(vMyColDef.ColumnType);
    
      if (VParameter.DataType = ftString) and (vMyColDef.MaxAbsoluteValue > 0) then
        VParameter.Size := Ceil(vMyColDef.MaxAbsoluteValue)
      else if vMyColDef.ColumnType = cnstBoolean then
        VParameter.Size := 1;
      
      VParameter.Length := Min(FMaxArraySize, aRows);
    end;
  end;

  MyOraSQL.Prepare;
  FArrayIndex := 0;
end;


The addition of
if IncludeColumn(aMyColumnDefs[VLoop]) and ((aMyColumnDefs[VLoop].ColumnType = cnstBlob)
or (aMyColumnDefs[VLoop].ColumnType = cnstInt64)) then
begin
bContainsNonArrayFieldType := true;
end;

Is effectively the difference between a largeint working and it not working.

Thanks for looking.
Shaun
 
Posts: 3
Joined: Thu 16 Feb 2017 13:49


Return to Oracle Data Access Components