Batch Operations - Firebird - UniDAC 9.1.1
-
- Posts: 32
- Joined: Wed 27 May 2020 06:22
Batch Operations - Firebird - UniDAC 9.1.1
When I run a batch update with more than 128 entries, an Execute Block with more than 128 entries is generated in some constellations.
However, for Execute blocks with more than 128 entries, Firebird displays the error message "Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256."
I can work around this by executing the execute in steps of 128 with.
With UniDAC 8.3.2 you didn't have this problem, the execute blocks were divided into smaller steps, since 9.1.1 these steps are bigger.
Could this be limited to 128?
However, for Execute blocks with more than 128 entries, Firebird displays the error message "Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256."
I can work around this by executing the execute in steps of 128 with.
With UniDAC 8.3.2 you didn't have this problem, the execute blocks were divided into smaller steps, since 9.1.1 these steps are bigger.
Could this be limited to 128?
Re: Batch Operations - Firebird - UniDAC 9.1.1
Hi Leonard,
Thanks for contacting us!
We were unable to reproduce the issue you mentioned in our environment.
What version of FireBird are you using?
Pease provide us with a sample application that demonstrates the difference in the behavior you specified between UniDAC versions 9.1.1 and 8.3.2, including all the necessary scripts for creating and populating the database, and send it to us via the e-support form: https://www.devart.com/company/contactform.htm
Thanks for contacting us!
We were unable to reproduce the issue you mentioned in our environment.
What version of FireBird are you using?
Pease provide us with a sample application that demonstrates the difference in the behavior you specified between UniDAC versions 9.1.1 and 8.3.2, including all the necessary scripts for creating and populating the database, and send it to us via the e-support form: https://www.devart.com/company/contactform.htm
-
- Posts: 32
- Joined: Wed 27 May 2020 06:22
Re: Batch Operations - Firebird - UniDAC 9.1.1
Hi,
The contact form link is not available. "We are sorry, the page you requested cannot be found".
However, I was able to recreate the problem with an anonymous database structure.
I use Firebird
- 3.0.8.33535
- 4.0.1.2692
with both versions I have the same problem.
Here once the script and then the batch execution.
Databse-Script
sourcecode example
the only special options in the connection are:
'InterBase.Charset=UTF8'
'InterBase.UseUnicode=True'
Is that enough for you to reproduce the problem?
The contact form link is not available. "We are sorry, the page you requested cannot be found".
However, I was able to recreate the problem with an anonymous database structure.
I use Firebird
- 3.0.8.33535
- 4.0.1.2692
with both versions I have the same problem.
Here once the script and then the batch execution.
Databse-Script
Code: Select all
CREATE TABLE TEST (
INT1 INTEGER NOT NULL,
INT2 INTEGER NOT NULL,
INT3 INTEGER NOT NULL,
INT4 INTEGER NOT NULL,
INT5 INTEGER NOT NULL,
INT6 INTEGER NOT NULL,
DOUBLE1 DOUBLE PRECISION,
DOUBLE2 DOUBLE PRECISION,
DOUBLE3 DOUBLE PRECISION,
DOUBLE4 DOUBLE PRECISION,
DOUBLE5 DOUBLE PRECISION,
DOUBLE6 DOUBLE PRECISION,
INT7 INTEGER NOT NULL,
DOUBLE7 DOUBLE PRECISION
);
sourcecode example
the only special options in the connection are:
'InterBase.Charset=UTF8'
'InterBase.UseUnicode=True'
Code: Select all
procedure TTest.Button1Click(Sender: TObject);
const
// unidac 9.1.1
// The execute blocks are not separated in this example
// but in other cases it works sometimes and then it separates to e.g. 100 statements.
// cValues = 128; //is ok
cValues = 129; //don't work - errormessage: 'Dynamic SQL Error Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256'
// unidac 8.3.2
// the execute blocks are separated in steps of 21 statements
// cValues = 128; //is ok
// cValues = 129; //is ok
var
i: Integer;
begin
UniQuery1.SQL.Text := 'update Test ' +
'set DOUBLE7 = :DOUBLE7 ' +
'where INT5 = :INT5 and ' +
'INT6 = :INT6 and ' +
'INT1 = :INT1 and ' +
'INT2 = :INT2 and ' +
'INT3 = :INT3 and ' +
'INT4 = :INT4 ';
UniQuery1.Params[ 0].DataType := ftFloat;
UniQuery1.Params[ 1].DataType := ftInteger;
UniQuery1.Params[ 2].DataType := ftInteger;
UniQuery1.Params[ 3].DataType := ftInteger;
UniQuery1.Params[ 4].DataType := ftInteger;
UniQuery1.Params[ 5].DataType := ftInteger;
UniQuery1.Params[ 6].DataType := ftInteger;
UniQuery1.Params.ValueCount := cValues;
for i := 0 to cValues-1 do
begin
UniQuery1.Params[ 0][ i].AsFloat := 100;
UniQuery1.Params[ 1][ i].AsInteger := i;
UniQuery1.Params[ 2][ i].AsInteger := i;
UniQuery1.Params[ 3][ i].AsInteger := i;
UniQuery1.Params[ 4][ i].AsInteger := i;
UniQuery1.Params[ 5][ i].AsInteger := i;
UniQuery1.Params[ 6][ i].AsInteger := i;
end;
UniQuery1.Execute(UniQuery1.Params.ValueCount);
end;
-
- Posts: 32
- Joined: Wed 27 May 2020 06:22
Re: Batch Operations - Firebird - UniDAC 9.1.1
I have just noticed that the error
If you replace the update statement with an insert statement and make cValues higher than 256, the execute blocks are separated correctly.
The problem seems to be only with update statements?
occurs for updates with more than 128 statements, for inserts only with more than 256 statements.Dynamic SQL Error
Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256'.
If you replace the update statement with an insert statement and make cValues higher than 256, the execute blocks are separated correctly.
The problem seems to be only with update statements?
-
- Posts: 32
- Joined: Wed 27 May 2020 06:22
Re: Batch Operations - Firebird - UniDAC 9.1.1
Here a issue at Firebird GitHub about it:
https://github.com/FirebirdSQL/firebird/issues/7126
https://github.com/FirebirdSQL/firebird/issues/7126
Re: Batch Operations - Firebird - UniDAC 9.1.1
It is for me on Win7..enough for to reproduce the problem?
---------------------------
Project1
---------------------------
Dynamic SQL Error
Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256.
---------------------------
OK
---------------------------
Re: Batch Operations - Firebird - UniDAC 9.1.1
And all works with 9.0.1 (14-Sep-2021), the oldest I have on that machine..
In 9.0.1 the calculation drops BatchSize=21 while in 9.1.1 BatchSize=129
In 9.0.1 the calculation drops BatchSize=21 while in 9.1.1 BatchSize=129
-
- Posts: 32
- Joined: Wed 27 May 2020 06:22
Re: Batch Operations - Firebird - UniDAC 9.1.1
Yes, I can also confirm with the version 9.0.1 it has also worked like with the version 8.3.2.
Re: Batch Operations - Firebird - UniDAC 9.1.1
Hi all,
Thank you for the info provided!
We have reproduced the issue and we are currently investigating its origin.
We will inform you about the results shortly.
Thank you for the info provided!
We have reproduced the issue and we are currently investigating its origin.
We will inform you about the results shortly.
Re: Batch Operations - Firebird - UniDAC 9.1.1
Hi,
We've reproduced the issue and fixed it. The fix will be included in the next build of our product.
As a workaround, we can send you a nightly UniDAC build including the required changes.
Please specify your license number, IDE version and send us via this contact form: https://www.devart.com/company/contactform.html
We've reproduced the issue and fixed it. The fix will be included in the next build of our product.
As a workaround, we can send you a nightly UniDAC build including the required changes.
Please specify your license number, IDE version and send us via this contact form: https://www.devart.com/company/contactform.html
-
- Posts: 32
- Joined: Wed 27 May 2020 06:22
Re: Batch Operations - Firebird - UniDAC 9.1.1
Nice, when can we expect the next version?
Re: Batch Operations - Firebird - UniDAC 9.1.1
Hi,
We are expecting it during a month!
We are expecting it during a month!
-
- Posts: 32
- Joined: Wed 27 May 2020 06:22
Re: Batch Operations - Firebird - UniDAC 9.1.1
Hello,
I noticed some more things in connection with batch operations.
Batch operations with execute procedure is not possible.
Error message: "The SQL statement is not allowable for a bulk operation".
Why is this not possible? With Firebird this is not really a problem. I can also call stored procedures in an execute block. With these, even the limit within an execute block is not so low. Several thousand procedure executions are possible without any problems.
With Update or Insert statements a maximum of 85 statements are possible within one Execute block. Has this been taken into account for the next update?
I noticed some more things in connection with batch operations.
Batch operations with execute procedure is not possible.
Error message: "The SQL statement is not allowable for a bulk operation".
Why is this not possible? With Firebird this is not really a problem. I can also call stored procedures in an execute block. With these, even the limit within an execute block is not so low. Several thousand procedure executions are possible without any problems.
With Update or Insert statements a maximum of 85 statements are possible within one Execute block. Has this been taken into account for the next update?
Re: Batch Operations - Firebird - UniDAC 9.1.1
Hi!
No, currently we do not support batch operations with execute procedure, but we plan to support them in the nearest future.
Should you have any other questions, do not hesitate to ask.
No, currently we do not support batch operations with execute procedure, but we plan to support them in the nearest future.
Should you have any other questions, do not hesitate to ask.