? is not recognized as parameter in '?'

[alfianpp]

Hello,
I am using sqflite: ^1.1.7+2
I experienced something strange when I run this code:

db.query(
   tableName,
   where: 'title LIKE \'%?%\'',
   whereArgs: [search],
);

I got error:

DatabaseException(Cannot bind argument at index 1 because the index is out of range. The statement has 0 parameters.) sql 'SELECT * FROM anime WHERE title LIKE '%?%'' args [a]}

I tried to change it to:

db.query(
   tableName,
   where: "title LIKE '%?%'",
   whereArgs: [search],
);

but still got same error.
But, if I do like this:

db.query(
   tableName,
   where: 'title LIKE \'%$search%\'',
);

It working.
Sorry if this question asked already,

[alextekartik]

I think the percent character should be in the parameter, not in the query. Can you try something like this:

db.query(
   tableName,
   where: "title LIKE ?",
   whereArgs: ['%$search%'],
);

[alfianpp]

Okay, it working, thank you.

[liemvo]

@alextekartik
My query as below:

db.query(tableName,
          columns: [_columnId, _columnWord, _columnContent],
          where: "$_columnWord LIKE ?",
          whereArgs: ['$query%'],
          orderBy: '$_columnId desc');

Then I got the error:
E/SQLiteLog(22392): (11) statement aborts at 11: [SELECT id, word, content FROM table WHERE word LIKE ? ORDER BY id desc] database disk image is malformed

[alextekartik]

That sounds like a different issue. database disk image is malformed means your database is not a SQLite file or corrupted. To isolate the issue, does calling await db.getVersion() work?

[liemvo]

I can query the items from this table without any errors, but after performing the above command then I got the error.

[alextekartik]

It looks like a database corruption issue: https://sqliteviewer.com/blog/database-disk-image-malformed

Your query looks fine to me.

[liemvo]

When I replace the where condition:

 where: "$_columnWord = ?",
 whereArgs: [query]

It works without error but the result is only one item that is the reason I want to use the like :(.

Thank you for your message.

[alextekartik]

It seems that when the database is corrupted it does not fail for all queries. Why it fails on this one I don't know. You should try to fix the database (https://www.sqlite.org/faq.html#q21) first. If you want can either attach your db file or send it to me (alex@tekartik.com) so I can take a closer look.

This is different from the original issue so it might be better to create a new issue to continue tracking this.

[liemvo]

Thank you for helping, I solve the problem by fixing my database.

[alextekartik]

@liemvo Great! I'll be interested in knowing the steps (external tool, pragma...) you did in case someone else encounter this issue. Thanks!