Batch Operations - Firebird - UniDAC 9.1.1

[leonardt.neugebauer]

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?

[pavelpd]

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

[leonardt.neugebauer]

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

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;

Is that enough for you to reproduce the problem?

[leonardt.neugebauer]

I have just noticed that the error

Dynamic SQL Error
Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256'.

occurs for updates with more than 128 statements, for inserts only with more than 256 statements.

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?

[leonardt.neugebauer]

Here a issue at Firebird GitHub about it:
https://github.com/FirebirdSQL/firebird/issues/7126

[FredS]

enough for to reproduce the problem?

It is for me on Win7..

---------------------------
Project1
---------------------------
Dynamic SQL Error
Too many Contexts of Relation/Procedure/Views. Maximum allowed is 256.
---------------------------
OK
---------------------------

[FredS]

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

[leonardt.neugebauer]

Yes, I can also confirm with the version 9.0.1 it has also worked like with the version 8.3.2.

[pavelpd]

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.

[FredS]

Ignore

[pavelpd]

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

[leonardt.neugebauer]

Nice, when can we expect the next version?

[pavelpd]

Hi,

We are expecting it during a month!

[leonardt.neugebauer]

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?

[pavelpd]

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.