How to pass argument in custom SQL Query?

[jaydip-pawar]

Stream<List<Password>> watchSearchedPasswords(String query) {
    return customSelectStream('SELECT * FROM Password ORDER BY wName ASC WHERE wName CONTAINS "$query" OR wAddress CONTAINS "$query";',
    readsFrom: {passwords},).map((rows) {
      return rows.map((row) => Password.fromData(row.data, db)).toList();
    });
  }

I have created following method in which i want to return list of rows that contains string which i provide when method is called but how to pass it?

[simolus3]

You can pass variables like this:

Stream<List<Password>> watchSearchedPasswords(String query) {
  return customSelectStream('SELECT * FROM Password ORDER BY wName ASC WHERE wName CONTAINS ?1 OR wAddress CONTAINS ?1;',
    variables: [Variable.withString(query)],
    readsFrom: {passwords},
  ).map((rows) {
    return rows.map((row) => Password.fromData(row.data, db)).toList();
  });
}

But in that case I recommend to use build-time checked queries or moor files which can generate those methods for you.

Also, feel free to ask questions like that in the discussions.

[jaydip-pawar]

I am getting following exception:
Unhandled Exception: DatabaseException(near "WHERE": syntax error (code 1): , while compiling: SELECT * FROM Password ORDER BY wName ASC WHERE wName CONTAINS ?1 OR wAddress CONTAINS ?1;) sql 'SELECT * FROM Password ORDER BY wName ASC WHERE wName CONTAINS ?1 OR wAddress CONTAINS ?1;' args [v]}

[simolus3]

Ah yeah, the WHERE must come before the ORDER BY, I didn't see that at first. Build-time queries or moor files would have spotted that problem ;)

[jaydip-pawar]

But Still I am getting Exception :-(

E/SQLiteLog(20484): (1) near "CONTAINS": syntax error
E/flutter (20484): [ERROR:flutter/lib/ui/ui_dart_state.cc(177)] Unhandled Exception: DatabaseException(near "CONTAINS": syntax error (code 1): , while compiling: SELECT * FROM Password WHERE wName CONTAINS ?1 OR wAddress CONTAINS ?1 ORDER BY wName ASC ;) sql 'SELECT * FROM Password WHERE wName CONTAINS ?1 OR wAddress CONTAINS ?1 ORDER BY wName ASC ;' args [f]}

[simolus3]

Ah yeah, CONTAINS isn't a thing in sqlite3. instr(wName, ?1) > 0 OR instr(wAddress, ?1) > 0 might work?

[jaydip-pawar]

I have solved my issue, in Query I am typing instr that's why I'm getting exception, I solved it by writing it in upper case like INSTR, but it is case sensitive so there is any way to find the substring of data with case insensitive .

[simolus3]

but it is case sensitive so there is any way to find the substring of data with case insensitive

You could use wName LIKE ('%' || ?1 || '%'), which is case-insensitive for ascii chars.