SQLite bad parameter or other API misuse

user_5725721 0
Viewed 2400

Error

[ERROR:flutter/lib/ui/ui_dart_state.cc(177)] Unhandled Exception: SqliteException(14): bad parameter or other API misuse, bad parameter or other API misuse (code 21)
#0      new DatabaseImpl.open (package:sqlite3/src/impl/database.dart:53:7)
#1      Sqlite3.open (package:sqlite3/src/api/sqlite3.dart:51:25)
#2      _VmDelegate.open (package:moor/src/ffi/vm_database.dart:126:21)
#3      DelegatedDatabase.ensureOpen.<anonymous closure> (package:moor/src/runtime/executor/helpers/engines.dart:255:22)
<asynchronous suspension>
#4      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:34:18)
<asynchronous suspension>
#5      QueryEngine.doWhenOpened.<anonymous closure> (package:moor/src/runtime/api/query_engine.dart)
<asynchronous suspension>

Database path

C:\Users\fosan\Documents\db.sqlite

Environment

  • moor: ^3.4.0
  • sqlite3_flutter_libs: ^0.3.0
  • path_provider: ^1.6.24
  • path: ^1.7.0
  • build_runner: ^1.10.11
  • moor_generator: ^3.4.0
  • sqlite3.dll in project's root path - "Using sqlite3 Version(libVersion: 3.34.0, sourceId: 2020-12-01 16:14:00 a26b6597e3ae272231b96f9982c3bcc17ddec2f2b6eb4df06a224b91089fed5b, number: 3034000)"

Flutter doctor

[√] Flutter (Channel dev, 1.26.0-1.0.pre, on Microsoft Windows [Version 10.0.19613.1005],
    locale pt-BR)
    • Flutter version 1.26.0-1.0.pre at C:\tools\flutter
    • Framework revision 63062a6443 (8 days ago), 2020-12-13 23:19:13 +0800
    • Engine revision 4797b06652
    • Dart version 2.12.0 (build 2.12.0-141.0.dev)

[√] Android toolchain - develop for Android devices (Android SDK version 30.0.3)
    • Android SDK at C:\Users\fosan\AppData\Local\Android\sdk
    • Platform android-30, build-tools 30.0.3
    • Java binary at: C:\Program Files\Android\Android Studio\jre\bin\java
    • Java version OpenJDK Runtime Environment (build 1.8.0_242-release-1644-b01)
    • All Android licenses accepted.

[√] Visual Studio - develop for Windows (Visual Studio Community 2019 16.8.3)
    • Visual Studio at C:\Program Files (x86)\Microsoft Visual Studio\2019\Community
    • Visual Studio Community 2019 version 16.8.30804.86
    • Windows 10 SDK version 10.0.18362.0

[√] Android Studio (version 4.1.0)
    • Android Studio at C:\Program Files\Android\Android Studio
    • Flutter plugin can be installed from:
       https://plugins.jetbrains.com/plugin/9212-flutter
    • Dart plugin can be installed from:
       https://plugins.jetbrains.com/plugin/6351-dart
    • Java version OpenJDK Runtime Environment (build 1.8.0_242-release-1644-b01)

[√] Connected device (1 available)
    • Windows (desktop) • windows • windows-x64 • Microsoft Windows [Version
      10.0.19613.1005]

• No issues found!

Code

import 'dart:ffi';
import 'dart:io';

...
import 'package:flutter/material.dart';
import 'package:sqlite3/open.dart';
import 'package:window_size/window_size.dart';

void main() {
  open.overrideFor(OperatingSystem.windows, _openOnWindows);
  ...
  runApp(const App(appName: appName));
}

DynamicLibrary _openOnWindows() {
  final sqlite3LibraryPath = join(Platform.script.toFilePath(), 'sqlite3.dll');
  return DynamicLibrary.open(sqlite3LibraryPath);
}

import 'dart:io';

import 'package:moor/ffi.dart';
import 'package:moor/moor.dart';
import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';

part 'local_database.g.dart';

@UseMoor(tables: [ClientsTable])
class LocalDatabase extends _$LocalDatabase {
  LocalDatabase() : super(_openConnection());

  @override
  int get schemaVersion => 1;

  Future<int> addClient(ClientRow newClient) =>
      into(clientsTable).insert(newClient);

  Stream<List<ClientRow>> get watchAllClients => select(clientsTable).watch();

  Future<int> deleteClient(int id) =>
      (delete(clientsTable)..where((tbl) => tbl.id.equals(id))).go();
}

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));
    return VmDatabase(file);
  });
}

@DataClassName("ClientRow")
class ClientsTable extends Table {
  IntColumn get id => integer().autoIncrement()();

  TextColumn get name => text()();

  TextColumn get phone => text()();

  TextColumn get street => text()();

  TextColumn get number => text()();

  TextColumn get city => text()();

  TextColumn get neighborhood => text()();

  TextColumn get uf => text()();

  TextColumn get reference => text()();
}
flutter-moor dart flutter sqlite windows
3 Answers

Should use sqflite: ^1.3.0 instead of sqlite3_flutter_libs: ^0.3.0

user_23020060

My problems were two things, both related to write permission. The first problem was that my application project was under the Desktop folder and the second one was that the await getApplicationDocumentsDirectory(); returned the Documents folder.

What solved for me was to create the database in the same folder of the project and to move to project folder itself to C:\StudioProjects

user_57257210

When SQLite throws an error, and you then try to use the same prepared statement again, without rebuilding it, SQLite will produce the General error: 21 bad parameter or other API misuse error. You must catch and rebuild the prepared statement from the start to avoid subsequent calls to a now invalid prepared statement stack. (By the way, the fact that this doesn't seg fault, deserves a round of applause to the SQLite devs. They did some awesome work here in a language that is very easy to make seg fault.) It should be noted that this will happen far more often in write statements (INSERT, UPDATE, DELETE, etc) where read only statements (SELECT) are much less likely to cause this error for showing up. So look at your write statements first.

Now anything can cause the error message from happening. My Googleing shows that this is happening in a lot of libraries that aren't handling folder permissions correctly or checking it at time of flight (when the query is actually being executed).

I ran into this problem head first when I was doing multiple INSERT statements one after the other, but the first statement poisoned the prepared statements due to a UNIQUE constraint resulting in subsequent statements giving me the General error: 21 bad parameter or other API misuse.

user_1368920
Related