windowssqliteflutterdartflutter-moor

SQLite bad parameter or other API misuse

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

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()();
}
Solution

  • 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