Error when using TIBCLoadder

[Denis WILLARD]

[IBDac 5.0.2]
I'm using a TIBCLoader component for loading data in a table.
Depending on the "Batchsize" property, i get an error message :

"EIBCError:
Dynamic SQL Error
SQL error code = -204
Implementation limit exceeded
block size exceeds implementation restriction"

I understand that the problem comes from the size of the generated "EXECUTE BLOCK...".

In Firebird FAQ I found this explanation:
"This Means That your SQL statement has breached the limit for SQL statement size.
The limit is 64kB for statement text"

But in my case, the SQL statement size is only 20kB.

What are the limitations for using TIBCLoader ?

[ZEuS]

The error message you receive is due to the way the Firebird calculates the "EXECUTE BLOCK" statement size. The final statement size is composed from the length of the SQL expression itself and the total size of its parameters.
For example, for the statement like

Code: Select all

EXECUTE BLOCK (P0_0 INTEGER =?, P0_1 VARCHAR(1000) =?) AS
BEGIN
  INSERT INTO TEST_TABLE(ID, VARCHAR_FIELD) VALUES (:P0_0, :P0_1);
END

the total size is: 111 bytes (SQL length) + 4 bytes (the P0_0 parameter size) + 1000*4 bytes (the P0_1 parameter size, if the database has the UTF8 encoding) = 4115 bytes.
So, try to gradually reduce the RowsPerBatch property value until the error no longer occurs.