Elixir Phoenix error in generated query

Question

I'm an elixir/phoenix newbie trying to figure out the basics so I can get up to speed. I'm having a hard time understanding what is wrong with the following schema definition that is making the generated query incorrect.

Side note: I am using the Tds.Ecto adapter to access a read only MS SQL Server 2005 database.

defmodule Rodes.Sigcdpro do
    use Ecto.Schema
    import Ecto.Changeset
    alias Rodes.Sigcdpro

    @primary_key {:cpros, :string, []}
    @derive {Phoenix.Param, key: :cpros}

    schema "sigcdpro" do
        # field :cpros, :string
        field :dpros, :string
    end

    @doc """
    Tabela de produtos
    """

    def changeset( %Sigcdpro{} = sigcdpro, attrs ) do
        sigcdpro
        |> cast( attrs, [:cpros, :dpros] )
    end
end

Then, when I run the following command on the interactive shell, I get an error.

iex(8)> Rodes.Repo.all(from p in Rodes.Sigcdpro, where: p.cpros == 'R1156CRY.R1')
[debug] QUERY ERROR source="sigcdpro" db=227.0ms
SELECT s0.[cpros], s0.[dpros] FROM [sigcdpro] AS s0 WHERE (s0.[cpros] = 82, 49, 49, 53, 54, 67, 82, 89, 46, 82, 49) []
** (Tds.Error) Line 1 (Error 102): Incorrect syntax near ','.
    (ecto) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4
iex(8)>

Sidenote: the schema postoed has it’s cpros field commented out. Also, @Dogbert’s suggestion should work: single quotes are absolutely different from double quotes in Elixir. There is many written about it in all the tutorials, basically 'R11' is a list, not a string. — Aleksei Matiushkin

Dogbert Dogbert · Accepted Answer · 2017-12-16T11:15:35

Changing single quotes to double should work:

Rodes.Repo.all(from p in Rodes.Sigcdpro, where: p.cpros == "R1156CRY.R1")

Single quoted strings are charlists which are lists of integers ('foo' == [102, 111, 111]). I think the behavior of Tds.Ecto is not good here -- it should handle charlists in a better way, either throwing a proper error when the types don't match or somehow passing the array in such a way that it doesn't cause a syntax error (I have no idea about what queries MSSQL supports). You might want to report this in their repo if it hasn't already been reported.

Elixir Phoenix error in generated query

1 Answers