Building requests using pre-built strings

[Gbury]

It seems that inserting conditions via a preformatted string in a statement does not work. More precisely, consider the follwing piece of code:

module Sqlexpr = Sqlexpr_sqlite.Make(Sqlexpr_concurrency.Id)

let () =
  let db = Sqlexpr.open_db ":memory:" in
  Sqlexpr.execute db [%sqlc "CREATE TABLE foo (name TEXT, price INT)"];
  Sqlexpr.execute db [%sqlc "INSERT INTO foo VALUES (%s,%d)"] "table" 5;

  begin match Sqlexpr.select db [%sqlc "SELECT @d{price} FROM foo WHERE name=%s"] "table" with
  | [ p ] -> Printf.printf "ok\n"
  | _ -> Printf.printf "KO !\n"
  end;

  begin match Sqlexpr.select db [%sqlc "SELECT @d{price} FROM foo WHERE %s"] (Printf.sprintf "name=%s" "table") with
  | [ p ] -> Printf.printf "ok\n"
  | _ -> Printf.printf "KO !\n"
  end;

Which upon execution returns:

ok
KO !

One could expect the two output lines to both be ok, however, it seems preformatting the condition into a string outside the sql query induces some kind of weird behavior. Would it be possible to support this kind of sql statements ?

[j0sh]

Don't think that is possible, since placeholders can only represent values (eg, substituting for literals), not predicates or other fragments of a SQL string.

[mfp]

Not possible, for reasons similar to PG'OCaml's. The way to support this would be to encode relational algebra using the type system and make queries composable, etc., which is diametrically opposed to sqlexpr's simple approach (accept anything you can express in Sqlite3's SQL dialect and just make prepared statements really easy to use while bringing some type-safety).

If you're interested in a composable approach, there's [https://github.com/ocsigen/macaque]. It works with PGOcaml, but it should be possible to adapt it to Sqlite3: it essentially builds a "raw" SQL query string and sends it to the DB. Replacing the string evaluation + output parsing function and tweaking the query generator would give you composable queries for Sqlite3.