Your problem is the SQL. Take the SQL you are writing and put it in Postgres, you will get an error. Make sure to use parameters and not just write out the IN manually.
You can’t use parameters with IN like that; this is a Postgres limitation not an Elixir one. This is why you should be using any and not in when using parameters.
Well, if this test is valid in sql, in should be converted to any:
https://github.com/elixir-dbvisor/sql/blob/35d743c7715da9336269dfd7ea8426f4d2fa7ec6/test/string_test.exs#L214
It’s in the select tho…
Oh now I see what you mean, I did
{query, params} = ~SQL[from pages]
|> ~SQL[where not url = any?(#{state.lock}) and not host = any?(#{state.politeness})]
|> ~SQL"select url"
|> to_sql()
{:ok, %Postgrex.Result{rows: [[response]]}} = Postgrex.query(:postgrex, query, params)
but what sql turns it into is:
select url from pages where not url = any? ($1) and not host = any? ($1)
which is incorrect, so that is a bug in sql.
I’ll try doing it without sql and see if it works
The workaround I have found right now is doing the following query:
SELECT url FROM pages WHERE NOT url = ANY($1) AND NOT host = ANY($2) LIMIT 1
without using the sql library, but it would be nice to be able to do it with the library.
Hey there, I’ve been hard at work on the next version. Do you mind trying out main with the new adapters:
use SQL, adapter: SQL.Adapters.Postgres
~SQ[....]
I’m aiming to get a release out next week after I have updated all the docs, but as we speak the library is conformant to SQL 2016 standard, with over 900 generated tests.
The version after this will focus on improving the API. So that we might be able to accumelate where clauses as you tried in your prior attempt.
I did
{query, params} = ~SQL[from pages]
|> ~SQL[where not url = any?(#{state.lock}) and not host = any?(#{state.politeness})]
|> ~SQL"select url limit 1"
|> to_sql()
IO.inspect({query, params})
but it just hangs…
My sql query does work, however:
query = "SELECT url FROM pages WHERE NOT url = ANY($1) AND NOT host = ANY($2) LIMIT 1"
params = [state.lock, state.politeness]
Thanks, I’m believe the issue is the question mark after any. https://stackoverflow.com/questions/34627026/in-vs-any-operator-in-postgresql PostgreSQL: Documentation: 18: 9.24. Subquery Expressions
I look into the the hanging part.
I had also tried without, and got the same result.
But, in the sql library, should I use the question mark or not?
Now I updated it, and I get
iex(1)> DBHandler.page_to_scrape()
14:44:34.635 [error] GenServer DBHandler terminating
** (UndefinedFunctionError) function nil.token_to_string/1 is undefined
nil.token_to_string({:select, [line: 0, column: 6, file: nil], [{:ident, [line: 0, column: 10, file: nil], [~c"url"]}]})
(elixir 1.18.3) lib/enum.ex:1714: Enum."-map/2-lists^map/1-1-"/2
(sql 0.1.0) lib/sql.ex:113: String.Chars.SQL.to_string/1
(sql 0.1.0) lib/sql.ex:41: SQL.to_sql/1
(dbhandler 0.1.0) lib/dbhandler.ex:136: DBHandler.handle_call/3
(stdlib 4.3.1.6) gen_server.erl:1149: :gen_server.try_handle_call/4
(stdlib 4.3.1.6) gen_server.erl:1178: :gen_server.handle_msg/6
(stdlib 4.3.1.6) proc_lib.erl:240: :proc_lib.init_p_do_apply/3
Last message (from #PID<0.573.0>): :page_to_scrape
State: %{lock: [], politeness: []}
Client #PID<0.573.0> is alive
(stdlib 4.3.1.6) gen.erl:256: :gen.do_call/4
(elixir 1.18.3) lib/gen_server.ex:1125: GenServer.call/3
(elixir 1.18.3) src/elixir.erl:386: :elixir.eval_external_handler/3
(stdlib 4.3.1.6) erl_eval.erl:748: :erl_eval.do_apply/7
(elixir 1.18.3) src/elixir.erl:364: :elixir.eval_forms/4
(elixir 1.18.3) lib/module/parallel_checker.ex:120: Module.ParallelChecker.verify/1
(iex 1.18.3) lib/iex/evaluator.ex:336: IEx.Evaluator.eval_and_inspect/3
(iex 1.18.3) lib/iex/evaluator.ex:310: IEx.Evaluator.eval_and_inspect_parsed/3
Database handler started!
** (exit) exited in: GenServer.call(DBHandler, :page_to_scrape, 60000)
** (EXIT) an exception was raised:
** (UndefinedFunctionError) function nil.token_to_string/1 is undefined
nil.token_to_string({:select, [line: 0, column: 6, file: nil], [{:ident, [line: 0, column: 10, file: nil], [~c"url"]}]})
(elixir 1.18.3) lib/enum.ex:1714: Enum."-map/2-lists^map/1-1-"/2
(sql 0.1.0) lib/sql.ex:113: String.Chars.SQL.to_string/1
(sql 0.1.0) lib/sql.ex:41: SQL.to_sql/1
(dbhandler 0.1.0) lib/dbhandler.ex:136: DBHandler.handle_call/3
(stdlib 4.3.1.6) gen_server.erl:1149: :gen_server.try_handle_call/4
(stdlib 4.3.1.6) gen_server.erl:1178: :gen_server.handle_msg/6
(stdlib 4.3.1.6) proc_lib.erl:240: :proc_lib.init_p_do_apply/3
(elixir 1.18.3) lib/gen_server.ex:1128: GenServer.call/3
iex:1: (file)
iex(1)>
My bad, I’ve not documented the change in interpolation, instead of #{} then you should use double braces {{}} like how it’s done in heex. I only mention it here: Add generated lexer and parser by Schultzer · Pull Request #5 · elixir-dbvisor/sql · GitHub