Firebird 3 batch insert -- "Prepare" throws exception

[oh_ha]

Hello,
I want to perform a batch insert using the methods described in your blog post "Using Batch Updates in Delphi Data Access Components" (http://blog.devart.com/using-batch-oper ... nents.html).

My table is defined as follows:

Code: Select all

ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  VALUE_1 INTEGER NOT NULL,
  VALUE_2 INTEGER NOT NULL,
 CONSTRAINT PK_ID PRIMARY KEY (ID)

"ID" is a Firebird 3 autoincrement field.

To perform the batch insert I have defined the TUniQuery SQL property as

Code: Select all

INSERT INTO TABLE VALUES (:Value_1, :Value_2)

and defined the two parameters as type "Integer".

When I now try to prepare the query I get the following error message:
Project XXX.exe raised exception class EIBCError with message 'Dynamic SQL Error
SQL error code = -804. Count of read-write columns does not equal count of values'.

Please advise on how to properly perform the bulk insert with the autoincrement field.
I am using Unidac v6.4 with Delphi XE Pro; the database server is Firebird v3.0.2 x64 on Windows.

Thanks and regards, Olaf

[ertank]

You can try;

Code: Select all

INSERT INTO TABLE(VALUE_1, VALUE_2) VALUES (:Value_1, :Value_2)

[oh_ha]

Thanks for the prompt answer. This fixed the problem.

[ViktorV]

Hello,
I want to perform a batch insert using the methods described in your blog post "Using Batch Updates in Delphi Data Access Components" (http://blog.devart.com/using-batch-oper ... nents.html).

My table is defined as follows:

Code: Select all

ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  VALUE_1 INTEGER NOT NULL,
  VALUE_2 INTEGER NOT NULL,
 CONSTRAINT PK_ID PRIMARY KEY (ID)

"ID" is a Firebird 3 autoincrement field.

To perform the batch insert I have defined the TUniQuery SQL property as

Code: Select all

INSERT INTO TABLE VALUES (:Value_1, :Value_2)

and defined the two parameters as type "Integer".

When I now try to prepare the query I get the following error message:
Project XXX.exe raised exception class EIBCError with message 'Dynamic SQL Error
SQL error code = -804. Count of read-write columns does not equal count of values'.

Please advise on how to properly perform the bulk insert with the autoincrement field.
I am using Unidac v6.4 with Delphi XE Pro; the database server is Firebird v3.0.2 x64 on Windows.

Thanks and regards, Olaf

This is a correct error returned by the server.
If you want when inserting a record not to fill all the table fields, then after the INTO expression you should explicitly specify the list of fields to which VALUES are passed. Otherwise, as in the sample, after the VALUES expression the list of values for all table fields is expected and as a result, an error is generated.

You can try;

Code: Select all

INSERT INTO TABLE(VALUE_1, VALUE_2) VALUES (:Value_1, :Value_2)

ertank provided the correct solution to the problem.