Ecto.Adapters.SQL.query/4 does not set correct type for parameters.

[CaldwellYSR]

Crossposting from Ecto_SQL: issue#302

Environment

• Elixir version (elixir -v): 1.10.4
• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) 11.0.7462.6 (X64) Jan 5 2018 22:11:56
• Ecto version (mix deps): 3.4.5
• Database adapter and version (mix deps): TDS 2.1.1
• Operating system: Elixir:1.10.4 Docker Container

Current behavior

We are passing in a string param to be declared as a varchar and Tds seems to think it is money.

iex(32)> Rossi.Repo.query("declare @RaceDate varchar; set @RaceDate = $1; select @RaceDate;", ["somegarbage"], timeout: 480_000)
[debug] QUERY ERROR db=95.1ms queue=46.0ms idle=1416.4ms
declare @RaceDate varchar; set @RaceDate = $1; select @RaceDate; ["somegarbage"]
{:error,                            
 %Tds.Error{ 
   message: nil,
   mssql: %{
     class: 16,                                                                                                                                                                                                      
     line_number: 1,
     msg_text: "There is insufficient result space to convert a money value to varchar.",
     number: 234,
     proc_name: "",
     server_name: "EC2AMAZ-AHVHNC0",
     state: 2
   }
 }}

When passing a string into a variable expecting a smalldatetime it's returning 1 row but then rows shows as nil.

iex(29)> Rossi.Repo.query("declare @RaceDate smalldatetime; set @RaceDate = $1; select @RaceDate;", ["somegarbage"], timeout: 480_000)
[debug] QUERY OK db=91.3ms queue=60.6ms idle=1305.5ms 
declare @RaceDate smalldatetime; set @RaceDate = $1; select @RaceDate; ["somegarbage"]
{:ok, %Tds.Result{columns: nil, num_rows: 1, rows: nil}}

Expected behavior

With Rossi.Repo using Ecto.Adapters.Tds we would expect the following to respond with the parameter input.

iex(32)> Rossi.Repo.query("declare @RaceDate varchar; set @RaceDate = $1; select @RaceDate;", ["somegarbage"], timeout: 480_000)
[debug] QUERY ERROR db=95.1ms queue=46.0ms idle=1416.4ms
declare @RaceDate varchar; set @RaceDate = $1; select @RaceDate; ["somegarbage"]
{:ok,                            
 %Tds.Result{ 
   columns: 1,
   num_rows: 1,
   rows: ["somegarbage"]
 }}

Then we would expect the follow to respond with some type of error.

iex(29)> Rossi.Repo.query("declare @RaceDate smalldatetime; set @RaceDate = $1; select @RaceDate;", ["somegarbage"], timeout: 480_000)
[debug] QUERY OK db=91.3ms queue=60.6ms idle=1305.5ms 
declare @RaceDate smalldatetime; set @RaceDate = $1; select @RaceDate; ["somegarbage"]
{:error, %Tds.Error{mssql: %{
    msg_text: "Something about data types not lining up"
  }
}

[mjaric]

Hi @CaldwellYSR, I'd like to point out that $1 is not correct parameter name, it should have been @1 that is why it is complaining about money converting into varchar. Can you change your query and try again?

[CaldwellYSR]

iex(1)> Rossi.Repo.query("declare @RaceDate varchar; set @RaceDate = @1; select @RaceDate;", ["somegarbage"])
[debug] QUERY OK db=120.1ms decode=2.1ms queue=58.3ms idle=1728.9ms
declare @RaceDate varchar; set @RaceDate = @1; select @RaceDate; ["somegarbage"]
{:ok, %Tds.Result{columns: nil, num_rows: 1, rows: nil}}

Ok that makes sense. Still returning the num_rows: 1, rows: nil response but probably closer. I got to this issue from having $1 not being replaced correctly in a query we are running. I am going to go retest that with @1. I was following the docs on Ecto.Adapters.SQL for this query. https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4

Are there docs somewhere that I missed that would have led me to @1 as opposed to $1?

[mjaric]

NP. I also created test case and saw that issue too. I have idea what the issue could be so I should be able to fix this quickly. In mean time, if you can, avoid declaring and setting variables within query text it will temporary solve your problem until I fix the issue.

[mjaric]

There is another option here as temporary solution, but you need to use TDS driver directly and use query_multi, it should return all results in a list. In your case (query), the last one would be Tds.Result from select @RaceDate batch

[mjaric]

Are there docs somewhere that I missed that would have led me to @1 as opposed to $1?

It is not documented in ecto_sql since this is regular SQL Server syntax. If you are using ectos query macros you don't have to worry about naming, adapter will do it for you with exception to fragments, there parameters are also handled by adapter but syntax is ODBC like this

fragment("cast (? as DATETIME2)", unquote(date_str)))

note ? this is placeholder where adapter should place parameter name

[CaldwellYSR]

Are there docs somewhere that I missed that would have led me to @1 as opposed to $1?

It is not documented in ecto_sql since this is regular SQL Server syntax. If you are using ectos query macros you don't have to worry about naming, adapter will do it for you with exception to fragments, there parameters are also handled by adapter but syntax is ODBC like this

fragment("cast (? as DATETIME2)", unquote(date_str)))

note ? this is placeholder where adapter should place parameter name

Ok that makes some sense. I'm not very experienced with SQL Server so that's probably where my confusion came from. Thanks for the advice.

[mjaric]

@CaldwellYSR
I think it is best that I don't change anything in driver code. If you are not using ecto query macros, and you have in query variable declaration, just put at the top of the query SET NOCOUNT ON; and should be ok

iex(1)> Rossi.Repo.query("SET NOCOUNT ON; declare @RaceDate varchar; set @RaceDate = @1; select @RaceDate;", ["somegarbage"])