benwilson512

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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.

1
Post #11
user974881

user974881 OP

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…

0
Post #12
user974881

user974881 OP

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

0
Post #13
user974881

user974881 OP

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.

0
Post #14
Schultzer

Schultzer

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.

1
Post #15
user974881

user974881 OP

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]
0
Post #16
Schultzer

Schultzer

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.

0
Post #17
user974881

user974881 OP

I had also tried without, and got the same result.
But, in the sql library, should I use the question mark or not?

0
Post #18
user974881

user974881 OP

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)>
0
Post #19
Schultzer

Schultzer

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

0
Post #20