"error :sql_not_iolist" on use `Exqlite.Sqlite3.execute` insert emoji

[hxgdzyuyi]

example code:

    :ok = Exqlite.Sqlite3.execute(conn, "create table test (id integer primary key, stuff text)");
    Exqlite.Sqlite3.execute(conn, "insert into test (stuff) values ('😝')")
    |> IO.inspect

output

{:error, :sql_not_iolist}

elixir version: Elixir 1.10.2 (compiled with Erlang/OTP 22) / Erlang/OTP 22 [erts-10.7.2.12] [source] [64-bit] [smp:12:12] [ds:12:12:10] [async-threads:1] [hipe] system: Darwin Kernel Version 19.6.0

Thanks!

[warmwaffles]

This is due to enif_inspect_iolist_as_binary not liking utf8 characters even though what elixir outputs

iex(1)> String.to_charlist("insert into test (stuff) values ('😝')")
[105, 110, 115, 101, 114, 116, 32, 105, 110, 116, 111, 32, 116, 101, 115, 116,
 32, 40, 115, 116, 117, 102, 102, 41, 32, 118, 97, 108, 117, 101, 115, 32, 40,
 39, 128541, 39, 41]

I'll look into this further when I get more time.

https://github.com/elixir-sqlite/exqlite/blob/ea4a39b38960c5c7ea9e6ff677c40edc4fd02951/c_src/sqlite3_nif.c#L203-L205

[warmwaffles]

@hxgdzyuyi I still have not looked into this properly yet. I have not forgotten.

[SpirosMakris]

Same thing here when running a raw SQL query with Repo.query(q):

%Exqlite.Error{message: :sql_not_iolist, statement: "SELECT *, rowid AS id, rank FROM patients_fts WHERE patients_fts MATCH 'Δημητρίου OR John' ORDER BY rank;"}

It works fine without the Greek characters but same error as @hxgdzyuyi when unicode letters are present.

Thanks!

[warmwaffles]

Alright, over the weekend I'll take a deeper dive into this. It sounds like a unicode issue where once it is present in the charlist, the list becomes something else in the C land.

[warmwaffles]

@hxgdzyuyi and @SpirosMakris try pulling down the latest version v0.8.1 and let me know if your issue has been resolved.

[SpirosMakris]

Hey @warmwaffles. I tried v0.8.1 and it works in an execute:

iex(27)> :ok = Exqlite.Sqlite3.execute(conn, "SELECT *, rowid AS id, rank FROM patients_fts WHERE patients_fts MATCH 'Δημήτρης' ORDER BY rank;")
:ok

but not in a 'prepare' (which I believe is what should be used in a query?):

:ok = Exqlite.Sqlite3.prepare(conn, "SELECT *, rowid AS id, rank FROM patients_fts WHERE patients_fts MATCH 'Δημήτρης' ORDER BY rank;")
** (MatchError) no match of right hand side value: {:error, :sql_not_iolist}

Thanks for looking into this!

[SpirosMakris]

Hi @warmwaffles . After some digging it seems to work if I replace:

def prepare(conn, sql) do
    Sqlite3NIF.prepare(conn, String.to_charlist(sql))
  end

in sqlite3.ex line 65 with

def prepare(conn, sql) do
    Sqlite3NIF.prepare(conn, sql)
  end

Same thing you did for execute for version 0.8.1. No changes to nif glue code seem to be necessary.

Cheers!

[warmwaffles]

Yea I forgot that execute and prepare do not go through the same code path.

[warmwaffles]

@SpirosMakris just fixed it and updated in v0.8.2

[SpirosMakris]

@SpirosMakris just fixed it and updated in v0.8.2

Just tested it and it works fine. Thanks @warmwaffles!