insertMany doesn't work if the model doesn't provide id field

[junjizhi]

Looking at the generated code:

Future<void> insertMany(List<Equipment> models,
      {bool cascade: false, bool onlyNonNull: false, Set<String> only}) async {
    if (cascade) {
      final List<Future> futures = [];
      for (var model in models) {
        futures.add(insert(model, cascade: cascade));
      }
      await Future.wait(futures);
      return;
    } else {
      final List<List<SetColumn>> data = models
          .map((model) =>
              toSetColumns(model, only: only, onlyNonNull: onlyNonNull))
          .toList();
      final InsertMany insert = inserters.addAll(data);
      await adapter.insertMany(insert);
      return;
    }
  }

when setting cascade as true, it uses the insert path which handles the case without id field. When setting cascade as false, there's a bug in the above else block that doesn't handle id as null. This caught us by surprise.

[tejainece]

Can you please provide Equipment class declaration?

Thanks.

[tejainece]

What DB are you using? Postgres, MYSQL or sqflite?

[tejainece]

Do you get any error?

[tejainece]

I assume you are using sqflite.

@jaumard

[junjizhi]

yes, sqflite. I got the db error that id column is empty when cascade is set as false.

[andrei-cimpan]

I also have this issue with using insertMany with sqflite. I can provide you more info if needed. To avoid this, I'm inserting manually each entry of the list, but it is very slow... It takes about 7 seconds for about 1300 entries, each with 17 fields, on a Samsung S8

[tejainece]

@jaumard Can you look into this?

@andrei-cimpan Why are you not providing id? Is it because the id is auto incrementing?

The code for models and beans would be of huge help.

Thanks!

[jaumard]

Yes guys please share the model and the generated file associated with it. It will be easier to see the problem :) thanks !!

[jaumard]

Also what's the error you receive ?

[andrei-cimpan]

@jaumard Can you look into this?

@andrei-cimpan Why are you not providing id? Is it because the id is auto incrementing?

The code for models and beans would be of huge help.

Thanks!

Hi @jaumard! I'm not providing id beacuse the id is auto incrementing, just like you said. The pubspec.yaml part that involves Jaguar is:
dependencies:
sqflite:
jaguar_orm: ^2.2.5
jaguar_query: ^2.2.6
jaguar_query_sqflite: ^2.2.5

dev_dependencies:
flutter_test:
sdk: flutter

jaguar_orm_gen: ^2.2.25
build_runner: ^1.1.0

Actually I have two errors:

1. if I call the _entryBean.insertMany(list) and the list contains 1275 entries, I get the error:
   too many terms in compound SELECT (code 1): , while compiling: INSERT INTO entries SELECT 30143 AS 'remote_id', 1 AS 'user_id', "2019-02-08 01:07:02.000" AS 'date_recorded', "2019-02-08 01:07:06.000" AS 'date_updated', 0 AS 'bool_field_one', 0 AS 'bool_field_two', 'curved' AS 'string_field_one', 'manual' AS 'string_field_two', 100 AS 'int_field_one', 0 AS 'int_field_two', 0 AS 'int_field_three', 0 AS 'int_field_four', 0.0 AS 'double_field_one', null AS 'int_field_five', null AS 'string_field_three', null AS 'string_field_four' followed by a large list of UNION ALL SELECT ...

2. if I limit the list to 200 entries, I get the error described by the GitHub issue:
   (1) table entries has 17 columns but 16 values were supplied E/flutter (31526): [ERROR:flutter/shell/common/shell.cc(186)] Dart Error: Unhandled exception: E/flutter (31526): DatabaseException(table entries has 17 columns but 16 values were supplied (code 1): , while compiling: INSERT INTO entries SELECT 30143 AS 'remote_id', 1 AS 'user_id', "2019-02-08 01:07:02.000" AS 'date_recorded', "2019-02-08 01:07:06.000" AS 'date_updated', 0 AS 'bool_field_one', 0 AS 'bool_field_two', 'curved' AS 'string_field_one', 'manual' AS 'string_field_two', 100 AS 'int_field_one', 0 AS 'int_field_two', 0 AS 'int_field_three', 0 AS 'int_field_four', 0.0 AS 'double_field_one', null AS 'int_field_five', null AS 'string_field_three', null AS 'string_field_four' followd by the large list of UNION ALL SELECT ...

I've attached the two source files, model + generated.
Entry.dart.txt
Entry.jorm.dart.txt

Calling method:
Future<void> saveEntries(List<Entry> list) async { if (list == null || list.isEmpty) { return Future.value(true); } var userId = list.first.userId; await _entryBean.removeWhere(_entryBean.userId.eq(userId)); return await _entryBean.insertMany(list.take(200).toList()); }

Thanks!

[ziakhan110]

i also have this problem, my model id is auto incremented insert many doesnt work, i insert it using loop,
i am using sqflite

[tejainece]

A small reproducable repo will be very useful.

[dongjian]

same problem . and i use upsertMany instead for temporary .

[junjizhi]

Sorry to get to this late. I added a repo to reproduce this.

Running the below app and the test function:

Future testInsertMany() async {
  String dbPath = await getDatabasesPath();
  SqfliteAdapter adapter = SqfliteAdapter(path.join(dbPath, "test"));
  await adapter.connect();

  UserBean userBean = UserBean(adapter);
  PostBean postBean = PostBean(adapter);

  List<User> users = [
    User(name: "1"),
    User(name: "2"),
  ];

  userBean.insertMany(users, cascade: true);
}

You would see this error right away:

Text error messages:

Exception has occurred.
SqfliteDatabaseException (DatabaseException(Error Domain=FMDatabase Code=19 "NOT NULL constraint failed: users.id" UserInfo={NSLocalizedDescription=NOT NULL constraint failed: users.id}) sql 'INSERT INTO users(id, name) VALUES (null, '1')' args []})

@tejainece @jaumard Hope this helps.

[JamesMcIntosh]

@tejainece @jaumard When inserting into the table the following error is shown in the Android log

table my_table has 15 columns but 14 values were supplied

The insertMany() code uses the generated toSetColumns() method which explicitly excludes the id value from the returned Set if the supplied value for id is null.

This causes the error because the generated insert statement in InsertMany does not specify the columns explicitly thus it is expecting all table columns.

To fix this the id column should not be excluded toSetColumns so that the value NULL can be set in the insert statement.

[Skuallpa]

same problem . and i use upsertMany instead for temporary .

Using upsertMany also worked for me.