UniLoader error with Firebird

stevel · Post by **stevel** » Mon 25 Jul 2011 15:01

I am using UniLoader to insert data from UniQueryMySQL embedded into Firebird (server connection).

UniLoader.LoadFromDataSet method is giving this errror:

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

Following is my code:

Code: Select all

    //Operation stage 3

    UniConnection1.Database := 'mydb'; //xxxxx----
    UniConnection1.Open;               //xxxxx----

    UniConnection2.Open;

    UniConnection2.ExecSQL('DELETE FROM Individual;',[]);
    UniLoader1.TableName := 'Individual';
   // UniLoader1.CreateColumns;
    UniQuery3.SQL.Text := 'SELECT id, first_name, middle_name, last_name,' +
                           'suffix, id_number, street_line, city, state, zip_code,' +
                           'sex, eye_color, hair_color, height, weight, issued_date,' +
                           'expires_date, birth_date, last_scanned FROM individual;';
   //  UniQuery3.SQL.Text := 'SELECT id FROM individual;';
    UniQuery3.Open;
    UniLoader1.LoadFromDataSet(UniQuery3);

What is wrong?

Regards,
Steve Faleiro

AndreyZ · Post by **AndreyZ** » Tue 26 Jul 2011 09:54

Hello,

This is a Firebird error and not UniDAC. You can learn more about this error here: http://www.firebirdfaq.org/faq299

stevel · Post by **stevel** » Tue 26 Jul 2011 11:19

Dear Andrey,

Yes I already saw that error. But you can see my code - and the SQL statement. Even if I use 1 field (just the ID field) this error happens.

Could you tell me how it can be resolved?

As you can see from the code, I am trying to transfer data from a UniQuery that is connected to a MySQL embedded database to a similar table in Firebird 2.5.

Regards,
Steve

stevel · Post by **stevel** » Tue 26 Jul 2011 14:37

Hi Andrey

This error is happening only on 1 table. 3 other tables are transfering successfully.

This is the structure of the table (in destination Firebird 2.50 database) on which the error is happening:

CREATE TABLE `individual` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`full_name` varchar(90) DEFAULT NULL,
`first_name` varchar(45) DEFAULT NULL,
`middle_name` varchar(45) DEFAULT NULL,
`last_name` varchar(45) DEFAULT NULL,
`suffix` varchar(45) DEFAULT NULL,
`id_number` varchar(45) DEFAULT NULL,
`street_line` varchar(150) DEFAULT NULL,
`city` varchar(45) DEFAULT NULL,
`state` varchar(45) DEFAULT NULL,
`zip_code` varchar(45) DEFAULT NULL,
`sex` varchar(45) DEFAULT NULL,
`eye_color` varchar(45) DEFAULT NULL,
`hair_color` varchar(45) DEFAULT NULL,
`height` varchar(45) DEFAULT NULL,
`weight` varchar(45) DEFAULT NULL,
`issued_date` date DEFAULT NULL,
`expires_date` date DEFAULT NULL,
`birth_date` date DEFAULT NULL,
`last_scanned` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6964 DEFAULT CHARSET=utf8;

stevel · Post by **stevel** » Tue 26 Jul 2011 15:22

Hi Andrey,

This is indeed a Firebird limitation about the size of data that can be passed in an INSERT. (<---- not sure, please confirm)

I solved this problem by using two UniLoaders with some fields in the first one, and the remaining fileds in the second one. Also the second one has SpecificOptions[InsertMode] := imUpdateOrInsert

Thanks.

AndreyZ · Post by **AndreyZ** » Wed 27 Jul 2011 08:06

It's good to see that you've found a solution. Yes, this is a Firebird limit for any SQL statement you are trying to execute. We will investigate the ways of avoiding such problem in the future.