DML Array with ftLargeInt on Delphi XE3
DML Array with ftLargeInt on Delphi XE3
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?
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?
Re: DML Array with ftLargeInt on Delphi XE3
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.
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.
Re: DML Array with ftLargeInt on Delphi XE3
To investigate the issue, please provide a small code snippet showing the DML Array work with ftLargeInt in your application.
Re: DML Array with ftLargeInt on Delphi XE3
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.
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.
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;
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.
Re: DML Array with ftLargeInt on Delphi XE3
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" )
Re: DML Array with ftLargeInt on Delphi XE3
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.
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.
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.
Re: DML Array with ftLargeInt on Delphi XE3
Thank you for the information. We have reproduced the issue and will investigate its origin. We will inform you about the results shortly.
Re: DML Array with ftLargeInt on Delphi XE3
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...
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;
Re: DML Array with ftLargeInt on Delphi XE3
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 .