SQLite React Native Error: Too many bind arguments (0 needed)

[jwallet]

An error occurs when querying in both expo-sqlite-storage and react-native-sqlite-2 using expo on rxdb-premium@1.14.9 and rxdb@1.14.9. Also, I'm using rxdb-hooks@5.0.2

1. Database created
2. Database synced
3. Database fetch an item ❌ [error]

Too many bind arguments. 2 arguments were provided but the statement needs 0 arguments

From the SQLite lib, it comes after deserializing the exec() query, so maybe the error is inside the query itself.

I recently switched to SQLite plugin, before that I was on RxDB 13 using the pouchdb storage through a pouchdb-adapter-sqlite for expo-sqlite, and there was no error for the same case

 LOG  ## RxStorage SQLite log: run(259) {"query":"COMMIT;","params":[]}
 LOG  ## RxStorage SQLite log: run(259) DONE
 LOG  ## RxStorage SQLite log: run(260) {"query":"BEGIN;","params":[]}
 INFO  RxDB: Initial sync done ------------------
 LOG  ## RxStorage SQLite log: all(261) {"query":"SELECT data FROM \"order-0\"  WHERE deleted=0 ORDER BY JSON_EXTRACT(data, '$.|o') ASC, id ASC LIMIT 50  OFFSET 0","params":["2023-03-13T03:22:41.679Z","upcoming"]}
 LOG  ## RxStorage SQLite log: all(262) {"query":"SELECT data FROM \"order-0\"  WHERE deleted=0 ORDER BY JSON_EXTRACT(data, '$.|o') ASC, id ASC LIMIT 50  OFFSET 0","params":["ongoing"]}
 LOG  ## RxStorage SQLite log: all(263) {"query":"SELECT data FROM \"order-0\"  WHERE deleted=0 ORDER BY JSON_EXTRACT(data, '$.|o') ASC, id ASC LIMIT 50  OFFSET 0","params":["2023-03-13T03:22:41.679Z","cancelled"]}
 LOG  ## RxStorage SQLite log: run(260) DONE
 LOG  ## RxStorage SQLite log: all(264) {"query":"SELECT data FROM \"contract-rx-replication-graphqlce964b6cd41c7a8efdc5dbf7926cc72ca1cf92f48b506ce02712d0ac4b376468-0\" WHERE id IN (?)","params":["down|1"]}
 LOG  ## RxStorage SQLite log: all(261) ERROR
 LOG  ## RxStorage SQLite log: all(262) ERROR
 LOG  ## RxStorage SQLite log: all(263) ERROR
 LOG  ## RxStorage SQLite log: all(264) DONE
 LOG  ## RxStorage SQLite log: run(265) {"query":"\n    UPDATE \"contract-rx-replication-graphqlce964b6cd41c7a8efdc5dbf7926cc72ca1cf92f48b506ce02712d0ac4b376468-0\"\n    SET \n        revision = ?,\n        deleted = ?,\n        lastWriteTime = ?,\n        data = json(?)\n    WHERE\n        id = ?\n    ","params":["3-rxdbreplicationgraphqlce964b6cd41c7a8efdc5dbf7926cc72ca1cf92f48b506ce02712d0ac4b376468",0,1678677774463.01,"{\"id\":\"down|1\",\"isCheckpoint\":\"1\",\"itemId\":\"down\",\"_deleted\":false,\"_attachments\":{},\"data\":{\"id\":\"f07c69cc-56a0-4f46-8142-33b25006bfcf\",\"updatedAt\":1678473932382},\"_meta\":{\"lwt\":1678677774463.01},\"_rev\":\"3-rxdbreplicationgraphqlce964b6cd41c7a8efdc5dbf7926cc72ca1cf92f48b506ce02712d0ac4b376468\"}","down|1"]}
 ERROR  Error: Too many bind arguments.  2 arguments were provided but the statement needs 0 arguments.
 ERROR  Error: Too many bind arguments.  1 arguments were provided but the statement needs 0 arguments.
 ERROR  Error: Too many bind arguments.  2 arguments were provided but the statement needs 0 arguments.
 LOG  ## RxStorage SQLite log: run(265) DONE
 LOG  ## RxStorage SQLite log: run(266) {"query":"COMMIT;","params":[]}
 LOG  ## RxStorage SQLite log: run(266) DONE
 LOG  ## RxStorage SQLite log: run(267) {"query":"BEGIN;","params":[]}
 LOG  ## RxStorage SQLite log: run(267) DONE

the database was set like so for both lib:

function getRxStorage(
  key: 'expo' | 'memory' | 'sqlite2'
): RxSupportedStorage {
  console.info('RxDB: Initiating RxDB storage');
  switch (key) {
    case 'expo':
      return getRxStorageSQLite({
        sqliteBasics: getSQLiteBasicsExpoSQLite(ExpoSqliteStorage.openDatabase), // same result with ''getSQLiteBasicsWebSQL''
        log: console.log.bind(console),
      });
    case 'sqlite2':
      return getRxStorageSQLite({
        sqliteBasics: getSQLiteBasicsWebSQL(SQLite2.openDatabase),
        log: console.log.bind(console),
      });
    case 'memory':
      return getRxStorageMemory();
    default:
      throw new Error(`RxDB: Crashing... Storage key "${key}" is not supported.`);
  }
}

[pubkey]

From the logs it is clearly something wrong in the SQLite RxStorage. I will check.

[pubkey]

@jwallet This is strange. I added some tests to ensure there is no unequal params count
but it never failed on the whole RxDB unit tests.

[jwallet]

@pubkey what I tried

• it works using memory storage from 'rxdb'
• it's not coming from 'rxdb-hooks', I disabled its provider
• it throws on sqlite query right after the initial sync is done, I did a manual db.x.find().exec() before the sync = no error, and after = no error.
• if I disable the replication no error occurs, even if I manually insert item after that.
• I replaced my 4 main collections schema with examples/schema/heroes, and did not throw error, but again there were no replication possible just manual insert.

i paid for premium but only for the react-native sqlite package, did you share code between your premium features, so by disabling one of it, it throws that error to me... I guess the access key point to a premium repo with other module resulting to noop fn.

i know that on classic rxdb you did your database.prepare. but i found that issue that explain what's happening, pretty sure you already know and check those places in the sqlite storage instance WiseLibs/better-sqlite3#576, like if you use ?1 is not supported.

I use :

• key compression
• reduceEvent: true,
• no conflict handler
• no auto migration
• no local doc
• no clean up policy
• no multi instance
• ignoreDuplicate: false,
• one of my schema uses a key combinaison for primary key: ['employeeId', 'year', 'weekNumber']
• replication use modifier, to map updatedOn DateTime (backend) to integer (frontend)
• schema uses indexes on updatedOn (number)
• _deleted is named isDeleted
• we have schema with deep properties, mainschema.timesheet[0].weeks[0].days[0].entries[0].timeRanges[0] (I know it's bad)
• on push, graphql mutation, we do not watch for conflict, the backend does not return anything except always a 200 [] even if it fails (still WIP)

[pubkey]

Please try out the latest version. I have added some dev-mode check which will help in debugging this.

The sqlite storage always uses the plain ? char as variable placeholder because that is the only one which is supported by all sqlite bindings.

[jwallet]

Updated to 14.3.4 and the error is ensureParamsCountIsCorrect() wrong param count

 LOG  ## RxStorage SQLite log: run(258) DONE
 LOG  ## RxStorage SQLite log: run(259) {"query":"COMMIT;","params":[]}
 LOG  ## RxStorage SQLite log: run(259) DONE
 LOG  ## RxStorage SQLite log: run(260) {"query":"BEGIN;","params":[]}
 INFO  RxDB: Initial sync done
 LOG  ## RxStorage SQLite log: run(260) DONE
 LOG  ## RxStorage SQLite log: all(261) {"query":"SELECT data FROM \"contract-rx-replication-graphqlce964b6cd41c7a8efdc5dbf7926cc72ca1cf92f48b506ce02712d0ac4b376468-0\" WHERE id IN (?)","params":["down|1"]}
 ERROR  Error: ensureParamsCountIsCorrect() wrong param count
 ERROR  Error: ensureParamsCountIsCorrect() wrong param count
 ERROR  Error: ensureParamsCountIsCorrect() wrong param count
 LOG  ## RxStorage SQLite log: all(261) DONE
 LOG  ## RxStorage SQLite log: run(262) {"query":"\n    UPDATE \"contract-rx-replication-graphqlce964b6cd41c7a8efdc5dbf7926cc72ca1cf92f48b506ce02712d0ac4b376468-0\"\n    SET \n        revision = ?,\n        deleted = ?,\n        lastWriteTime = ?,\n        data = json(?)\n    WHERE\n        id = ?\n    ","params":["3-rxdbreplicationgraphqlce964b6cd41c7a8efdc5dbf7926cc72ca1cf92f48b506ce02712d0ac4b376468",0,1678891217380.01,"{\"id\":\"down|1\",\"isCheckpoint\":\"1\",\"itemId\":\"down\",\"_deleted\":false,\"_attachments\":{},\"data\":{\"id\":\"6624afec-c43d-48a0-9ab2-a7235fac84a8\",\"updatedAt\":1678819859382},\"_meta\":{\"lwt\":1678891217380.01},\"_rev\":\"3-rxdbreplicationgraphqlce964b6cd41c7a8efdc5dbf7926cc72ca1cf92f48b506ce02712d0ac4b376468\"}","down|1"]}

I see this might be related to indexCreationPromise so is it allowed in your RxSQLiteStorage to have indexes shaped like so in the schema,

 indexes: ['updatedAt', ['year', 'weekNumber']],

[jwallet]

So I logged which queries I send to rxdb-hook to perform, and it always fails using$elemMatch combined in a query.

 LOG  RxDB Hooks: Query mangoQuery sent @L348 ''performQuery(query)'' {
  "limit": 5,
  "selector": {
    "operators": {
      "$elemMatch": {
        "employeeId": ""
      }
    }
  },
  "sort": [
    {
      "departureDate": "desc"
    }
  ]
}
 L

since cvara does not allow undefined query I send a $elemMatch of an id: "" so this will always return nothing. did you manage $elemMatch in RxSQLiteStorage ?

These are the others mangoQuery operators that I use:

  AND = '$and',
  OR = '$or',
  NOR = '$nor',
  EQUALS = '$eq',
  NOT_EQUALS = '$ne',
  GREATER_THAN = '$gt',
  GREATER_THAN_OR_EQUAL = '$gte',
  LESS_THAN = '$lt',
  LESS_THAN_OR_EQUAL = '$lte',
  IN = '$in',
  NOT_IN = '$nin',
  REGEX = '$regex',
  EXISTS = '$exists',
  TYPE = '$type',
  MOD = '$mod',
  NOT = '$not',
  ALL = '$all',
  SIZE = '$size',
  ELEM_MATCH = '$elemMatch',
  ALL_MATCH = '$allMatch',

[pubkey]

Hi @jwallet
Sorry I did not have much time today.
This very likely has something todo with $elemMatch queries. They should work and there are tests for that, I am still not able to reproduce your problem but I have some clues on what is going wrong.
I am working on it..

[pubkey]

Found the root cause. This only occured when other operators in the query come before the $elemMatch.
This works

{
                            selector: {
                                list: {
                                    $elemMatch: {
                                        stringValue: 'five'
                                    }
                                },
                                numberValue: {
                                    $gt: 0
                                }
                            }
                        }

while this is broken:

{
                            selector: {
                                numberValue: {
                                    $gt: 0
                                },
                                list: {
                                    $elemMatch: {
                                        stringValue: 'five'
                                    }
                                }
                            }
                        }

[pubkey]

Please try version 14.3.6.
I found out that there are some more performance optimizations that can be done, but your query should work now. I also added some context to the logging so that the logs are more helpful.

[jwallet]

I'm currently moving my queries to fit this format, "$elemMatch" always in first condition.

const customSelector = { departureDateTimestamp: { $gte: new Date().getTime() } };
return collection.find({
  selector: {
    $and: [
      { operators: { $elemMatch: { employeeId: '1234' } } },
      customSelector
    ],
  }
});

I will leave one to try your new release

[jwallet]

all good it works @pubkey