DML Array with ftLargeInt on Delphi XE3

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Shaun
Posts: 5
Joined: Thu 16 Feb 2017 13:49

DML Array with ftLargeInt on Delphi XE3

Post by 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: 5
Joined: Thu 16 Feb 2017 13:49

Re: DML Array with ftLargeInt on Delphi XE3

Post by 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.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: DML Array with ftLargeInt on Delphi XE3

Post by 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.

Shaun
Posts: 5
Joined: Thu 16 Feb 2017 13:49

Re: DML Array with ftLargeInt on Delphi XE3

Post by 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.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: DML Array with ftLargeInt on Delphi XE3

Post by MaximG » Mon 27 Feb 2017 14:55

Unfortunately we could not reproduce the problem using the sent code snippet. For further problem investigatoion, please compose and send us a small full project where the problem occurs. In addition, we will need the script to create database objects used in this project. You can send this sample via the e-support form ( https://www.devart.com menu "Support"\"Request Support" )

Shaun
Posts: 5
Joined: Thu 16 Feb 2017 13:49

Re: DML Array with ftLargeInt on Delphi XE3

Post by Shaun » Wed 01 Mar 2017 11:17

Hi I have finally seen what is causing the issue, unfortunately I dont have your source code so cannot step into it, it shows its behaviour in a different way depending on the field type.

We have some code which sets the length of the parameters to an maximum number, in our case 1000, - this is the maximum number we allow before a forced execution. The issue is not with the forced execution of the the array.

Once we have accumulated the records needed we then set the length to the correct value depending on the number of records e.g. 2 Upon execution we get the error. I have noted that if you set the length value to a high number for parameters, if you subsequently set it to a lower number after accumulating the data it sets the value to one more than requested. If you array contains float types the issue is not shown on execution, if you use large int the issue is found. I include an example which tries to set 2 values into a simple table. If you set the field types to float - no problem - when set to largeint you get the access violation. In both cases the length parameter is set to a value greater than requested.

Comments in the code to aid recreation.

Code: Select all

procedure TfrmShaun64.Button1Click(Sender: TObject);
var
  OraSQL: TOraSQL;
  VRecordCount, vParamIndex: Integer;
begin
  GlobalDBlayer.StartTransaction;
  // our ora session and query.
  OraQuery1.Session := GlobalDBLayer.OraSession;
  OraSQL := TOraSQL.Create(nil);
  OraSQL.Prepare;

  // The table shaun64 contains two fields of Number(38)
  // I have also set the following in the session elsewhere in the code
  // FORASession.Options.EnableLargeInt := True;
  //  LargeIntPrecision := 38;

  OraSQL.SQL.Text := 'INSERT INTO Shaun64 VALUES(:shaun_64ID, :old_age)';


  // We set the length of all parameters up to a high value.
  // we dont know how many records we may have and we chunk the execution if necessary.
  // set here to show the problem.
  for vParamIndex := 0 to OraSQL.Params.Count - 1 do
  begin
    OraSQL.Params[vParamIndex].Length := 1000;
  end;

  // we are setting the values of 2 fields for 2 records.
  for VRecordCount := 1 to 2 do
  begin
    for vParamIndex := 0 to OraSQL.Params.Count - 1 do
    begin
      if VParamIndex = 0 then
      begin

        // The fields are defined as LargeInt here.
        // IF YOU SET THESE FIELDS TO FTFLOAT EVEN THOUGH THE LENGTH DOESNT SET CORRECTLY THE PROBLEM ISNT SHOWN!
        OraSQL.Params[vParamIndex].DataType := ftLargeInt;
        // GetNewID returns a random large int ID from a sequence table
        OraSQL.Params[vParamIndex].ItemValue[VRecordCount] := GlobalDBLayer.GetNewID('CMS.CDS_SEQ');
      end
      else
      begin
        OraSQL.Params[vParamIndex].DataType := ftLargeInt;
        OraSQL.Params[vParamIndex].ItemValue[VRecordCount] := 99;
      end;
    end;
  end;

  /// setting this value out will fail on execute
  ///  if you check the length of the parameters after forcibly setting them to 2
  ///  you will see that the value of length is set to 3.  This happens when you change the length from a higher value.
  for vParamIndex := 0 to OraSQL.Params.Count - 1 do
  begin
    OraSQL.Params[vParamIndex].Length := 2;
  end;


  // your will av at this point when trying to execute for 2 records (if the field type is set to ftLargeInt.)
  OraSQL.Execute(2);
  GlobalDBLayer.Commit;


  OraSQL.Free;

  // Grid on my form which the data is linked to
  initialise;
  dvg64.RefreshData;
  dvg64.Refresh;



end;

I hope you will not that setting the array high and then correctly when used in conjuction with ftLargeInt fields causes the issue. It should be noted that the length is incorrect after the second correctly length set for all data types, only the ftLargeInt type has issue with this.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: DML Array with ftLargeInt on Delphi XE3

Post by MaximG » Thu 02 Mar 2017 14:55

Thank you for the information. We have reproduced the issue and will investigate its origin. We will inform you about the results shortly.

Shaun
Posts: 5
Joined: Thu 16 Feb 2017 13:49

Re: DML Array with ftLargeInt on Delphi XE3

Post by Shaun » Thu 02 Mar 2017 16:24

Thanks, good news.

FYI
To get around the length being incorrect and getting on with my conversion from 32 bit to 64 bit identity fields I have found that the length can be set correctly with the following bad fix... Setting to 1 less than you need, sets it to the correct value (it increments what you pass by 1) and the code works...

Code: Select all

     VLength := 2;
     vParam.Length := VLength;

      if vParam.Length > VLength then
      begin
        vParam.Length := (VLength - 1);
      end;
    end;

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: DML Array with ftLargeInt on Delphi XE3

Post by MaximG » Tue 25 Apr 2017 06:36

The new version of ODAC 10.0.1 with RAD Studio Delphi 10.2 Tokyo support was released. The fixes you are interested in are included in this build. The full list of changes is available by the link : https://www.devart.com/odac/history.html .

Post Reply