Uniloader with Firebird -> "Implementation Limit Exceeded"

[tonymeadors]

I get an error when using UNILOADER.LOADFROMDATASET(q1) with Firebird.
Some tables are fine, but if the record size is even
slightly larger than tiny the error occurs.
A Single varchar(1000) field will work but increase the
size to varchar(1400) and your stuck.

The error is:
SQL Error Code= -204
Implementation Limit Exceeded
Block size exceeds implementation restriction

Yes, I have seen one other poster mention this and he found that this
is a Firebird limitation related to insert statement size & block size.

The Firebird FAQ explains the cause of the error:
"This Means That your SQL statement has breached the limit for SQL statement size.
The limit is 64kB for statement text..."

In my stripped-down test (shown below) I receive the error when
trying to send 1 record with ['Tom', 10].


1) while I understand that the error occurs on the Firebird side
my simple 1 record/2 field example should not be sending anywhere
near 64k statement text after uniloader has transformed it.

2) Firebird accepts tUNIQuery SQL insert statements that simultaneously
fill dozens of large varchars at the same time - so it would seem
that the limitation is not the Firebird server alone.

//create two tables and add 1 record


CREATE TABLE TEST_SOURCE(
A VARCHAR(1400) DEFAULT '' ,
B INTEGER DEFAULT 0 );

INSERT INTO TEST_SOURCE (A,B) VALUES('TOM',10);

CREATE TABLE TEST_DEST(
A VARCHAR(1400) DEFAULT '' ,
B INTEGER DEFAULT 0 );


//simple example code that generates FIREBIRD Error for varchar(1400)

q1:=txquery.create(nil);
uniloader:= tuniloader.create(nil);
try
q1.sqlconnection:=mconnection1;
uniloader.connection:=mconnection1;
uniloader.tablename:='test_dest';
uniloader.CreateColumns;
//
q1.sql.text:='select a,b from test_source';
q1.open; //pulls exactly 1 record
//
uniloader.LoadFromDataSet(q1);


The earlier poster fixed this problem by splitting the uniloader
operations - filling some fields, then the rest of the fields
to avoid this mysterious size limitation.
With a varchar(1400) one could not do that.

How is such a simple little table and tiny bit of data
exceeding *anything* when sent using UniLoader?

Thanks for your time,

tonyM

[AndreyZ]

The point is UniDAC uses 50 rows per batch by default. It means that the SQL statement for 50 rows is generated when you call the LoadFromDataSet method. To avoid this problem, you should decrease number of rows per batch. For this, you should use the RowsPerBatch specific option. Here is a code example:

Code: Select all

uniloader.SpecificOptions.Values['RowsPerBatch'] := '1';
uniloader.LoadFromDataSet(q1);

[tonymeadors]

A.

Yes lowering that value does satisfy Firebird.
Thank you.

We are doing some tests to see how the "fewer rows"
affects the speed advantage that uniloader gives us
in the first place.

In firebird cases, when uniloader is involved, we are
will likely standardize on an intermediate value of 10.

Thank you

[AndreyZ]

I am glad I could help. If any other questions come up, please contact us.

[chkaufmann]

I just run into the same problem and could solve it by lowering "RowsPerBatch".

Since I'm writing a generic loader for a table in my backup/restore library, I wonder if it is possible to "calculate" the maximum size for RowsPerBatch in order to have maximal performance.

I need that for Firebird 2.5, MS Sql Server and Oracle.

cu Christian

[ViktorV]

We have fixed this issue in UniDAC version 5.1.3. Please upgrade to the latest version of UniDAC (6.0.1) and check if the problem still persists. If this doesn't help solve the problem, please send a small sample to demonstrate the issue to viktorv*devart*com, including a script to create database objects.