Is there a way to use parameter with "WHERE LIKE %%" clause in Caqti's query template? [Solved]

Learning
1

I am trying to write something like:

Caqti_type.Std.(string ->* t2 int string)
"SELECT id, content from post WHERE content LIKE %?%"

and I got this exception “‘%’ is not allowed after parameter reference ‘?’”, which is the expected result as mentioned here.

Is there a way to make it work besides using sprintf or ^ to inject the search term myself?

(Edit: I wrote a self contained program for the case)

module type CONN = Caqti_blocking.CONNECTION

let sql_uri = "sqlite3::memory:"

module Q = struct
  open Caqti_request.Infix
  open Caqti_type.Std

  let create_table =
    (unit ->. unit)
    @@ {sql|
      CREATE TABLE post (
        id INTEGER PRIMARY KEY,
        content TEXT
      )
    |sql}

  let add = (string ->. unit) @@ "INSERT INTO post (content) VALUES (?)"
  let all = (unit ->* t2 int string) @@ "SELECT id, content from post"

  let search =
    (string ->* t2 int string)
    @@ "SELECT id, content from post WHERE content LIKE %$1%"
end

let connect () = Caqti_blocking.connect (Uri.of_string sql_uri)
let create_table (module Conn : CONN) = Conn.exec Q.create_table ()

let populate (module Conn : CONN) =
  let ( >>= ) = Result.bind in
  let add p () = Conn.exec Q.add p in
  add "Post 01" () >>= add "Post 02" >>= add "Post 03"

let list_all (module Conn : CONN) = Conn.collect_list Q.all ()
let search text (module Conn : CONN) = Conn.collect_list Q.search text
let print_posts = List.iter (fun (id, post) -> Printf.printf "%d\t%s\n" id post)

let run () =
  let ( let* ) = Result.bind in
  let* conn = connect () in
  let* () = create_table conn in
  let* () = populate conn in
  Result.map print_posts @@ list_all conn

let run2 () =
  let ( let* ) = Result.bind in
  let* conn = connect () in
  Result.map print_posts @@ search "Post" conn

let () =
  match run () with
  | Error e ->
      print_endline "Failed. ";
      print_endline (Caqti_error.show e)
  | Ok () -> print_endline "done."
  ;
  (* the following section will fail *)
  match run2 () with
  | Error e ->
      print_endline "Failed. ";
      print_endline (Caqti_error.show e)
  | Ok () -> print_endline "done."

Output:

$ dune exec ./q.exe
1       Post 01                  
2       Post 02
3       Post 03
done.
Failed. 
Request to <sqlite3::memory:> failed: Sqlite3.prepare: near "%": syntax error. Query: "SELECT id, content from post WHERE content LIKE %?1%".

(Edit: Solved version here, thanks to @paurkedal , I also added the missing part of populating the db in run2)

TLDR: use ‘%’ || ? || ‘%’

module type CONN = Caqti_blocking.CONNECTION

let sql_uri = "sqlite3::memory:"

module Q = struct
  open Caqti_request.Infix
  open Caqti_type.Std

  let create_table =
    (unit ->. unit)
    @@ {sql|
      CREATE TABLE post (
        id INTEGER PRIMARY KEY,
        content TEXT
      )
    |sql}

  let add = (string ->. unit) @@ "INSERT INTO post (content) VALUES (?)"
  let all = (unit ->* t2 int string) @@ "SELECT id, content from post"

  let search =
    (string ->* t2 int string)
    @@ "SELECT id, content from post WHERE content LIKE '%' || ? || '%'"
end

let connect () = Caqti_blocking.connect (Uri.of_string sql_uri)
let create_table (module Conn : CONN) = Conn.exec Q.create_table ()

let populate (module Conn : CONN) =
  let ( >>= ) = Result.bind in
  let add p () = Conn.exec Q.add p in
  add "Post 01" () >>= add "Post 02" >>= add "Post 03"

let list_all (module Conn : CONN) = Conn.collect_list Q.all ()
let search text (module Conn : CONN) = Conn.collect_list Q.search text
let print_posts = List.iter (fun (id, post) -> Printf.printf "%d\t%s\n" id post)

let run () =
  let ( let* ) = Result.bind in
  let* conn = connect () in
  let* () = create_table conn in
  let* () = populate conn in
  Result.map print_posts @@ list_all conn

let run2 () =
  let ( let* ) = Result.bind in
  let* conn = connect () in
  let* () = create_table conn in
  let* () = populate conn in
  Result.map print_posts @@ search "Post" conn

let () =
  match run () with
  | Error e ->
      print_endline "Failed. ";
      print_endline (Caqti_error.show e)
  | Ok () -> print_endline "done."
  ;
  match run2 () with
  | Error e ->
      print_endline "Failed. ";
      print_endline (Caqti_error.show e)
  | Ok () -> print_endline "done."
2

Could you use another kind of parameter placeholder?

3

Did you mean $1 ? I tried and failed.

4

Can you share the error?

5

I updated the original post with a small program

6

Can you try %:term%? SQLite supports an alternative placeholder syntax :foo.

7

Use the statement:

SELECT id, content from post WHERE content LIKE CONCAT ("%",?,"%")

The concat function works on MariaDB but not on my sqlite3 version. However it is documented on Sqlite3. Perhaps my version is too old.

You can also type: LIKE ? and let the caller function add the two “%”

8

Aren’t you forgetting single quotes around the pattern?

9

I don’t think so. Here PHP: SQLite3::prepare - Manual the :id is not quoted. (I have personal programs with unquoted ?). But the LIKE operator needs % to be quoted… then I had to use the CONCAT function.

If you quote the ?, you will have a constant string that can’t be bound to what you want.

Note: I have seen a CONCAT function in the official SQLite documentation : Built-In Scalar SQL Functions

But it is not supported in Sqlite3.40.1.

However, we can type instead WHERE content LIKE "%" || ? || "%"

In a way, it is like OCaml… let filter_string = %id% and let filter_string = "%id%" don’t work, we would have to type let filter_string = "%" ^ id ^ "%"

3 Likes
10

Yes, I see one of your proposed solutions is probably the way to go here. I would have used single quotes for strings since its more standard, but that’s a matter of taste if targeting SQLite3/MariaDB.

To elaborate, the Caqti_query parser treats quoted content specially, so wherever % is meant as a literal character i a text or pattern, it is accepted as such. It is also possible to pass a percent sign unquoted if needed for some reason, by expanding say percent in the environment function and referencing it as $(percent) in the query.

1 Like
11

It works, thank you.

12

The thing to remember with parameterised queries is that you are providing a value. This is not string-embedding like printf("abc%sghi", "def"). The placeholder in the SQL must be for a self-contained, validly typed value.

So, whatever the underlying database system in cases like this you will either need to append the “%” to the value before supplying it or append it as an expression in the sql.

The other one that frequently catches people out is the IN clause. This is a syntactic construct in SQL and ... IN (10, 20, 30) has a comma-separated list of three values.