elixir-ecto / tds Goto Github PK

View Code? Open in Web Editor NEW

115.0 115.0 77.0 1.23 MB

TDS Driver for Elixir

Home Page: https://hexdocs.pm/tds/

License: Apache License 2.0

Elixir 99.80% Shell 0.20%

tds's People

Contributors

Stargazers

Watchers

Forkers

bryanhunter cloudhousetech hectorsq charlesokwuagwu mattweldon rhowell graves danturn kaizen-gaming malandrina petpartnersllc drbawb twinfire javimolla smokinguns danielicardopcnt brendonpierson sadesyllas hrtoolbox 276361270 vegg89 cristianberneanu kevinsheffield drowzy nickneck wingstand pressrelations mbxsystems marpo60 benmorganio trbngr kevinkoltz derbywars vanrobermore ekstrom naaano whossname sztheory wojtekmach bwittenbrook3 schultzer leandrocp andrei-mihaila oforce mattias01 deepankar-j modelabcl uhrovat vlopez346 kianmeng intelimetrica deathklok-97 q1-energie-ag moogle19 pulzzedavid arked17 danrday zerobatu pinkdiamond1 therealbenstein rvnash westarete fnicastri rschenk nocodo-io user20230119 simonmcconnell funneljoe funnelcloudinc janhendrik-rust whilehappy sstoltze preciz

tds's Issues

[error] Tds.Protocol (#PID<0.891.0>) failed to connect: ** (Tds.Error) Connection failed to receive packet due :closed

I'm getting the following error when connecting to a SQL Server 2012:

 [error] Tds.Protocol (#PID<0.891.0>) failed to connect: ** (Tds.Error) Connection failed to receive packet due :closed

And I can confirm the connection is working:

% mssql-cli -S [REDACTED] -U [REDACTED] -d [REDACTED]
Password:
[REDACTED]> select @@VERSION
Time: 1.480s (a second)
+--------------------+
| (No column name)   |
|--------------------|
| Microsoft SQL Server 2012 [REDACTED] - 11.0.7462.6 (X64)
        [REDACTED]
                    |
+--------------------+

Libs in use:

:tds, "2.1.0"
:db_connection, "2.2.1"
:ecto, "3.4.1"

Elixir/Erlang:

% elixir --version
Erlang/OTP 22 [erts-10.6.4] [source] [64-bit] [smp:12:12] [ds:12:12:10] [async-threads:1] [hipe]

Elixir 1.10.1 (compiled with Erlang/OTP 22)

Is that version supported? And how can I help?

ATTN packets

When or under what conditions are they to be sent?

Cannot connect to a mssql in the same network.

Hi,

I am trying to connect to a ms sql 2012 server in my network. I can connect to other ms sql servers, but this one quickly errors with tcp closed:

** (EXIT from #PID<0.307.0>) %Tds.Error{message: "tcp closed", mssql: nil}
iex(2)> 
16:04:42.552 [error] GenServer #PID<0.309.0> terminating
Last message: {:tcp_closed, #Port<0.8158>}
State: %{attn_timer: nil, env: %{trans: <<0>>}, ireq: {#PID<0.307.0>, #Reference<0.0.3.399>}, itcp: 1433, opts: [password: :REDACTED, hostname: "192.168.10.5", username: "sa", instance: REDACTED, database: "master", port: 1433], pak_data: "", pak_header: "", queue: {[{{:connect, [hostname: "192.168.10.5", username: "sa", password: REDACTED, instance: REDACTED, database: "master", port: 1433]}, {#PID<0.307.0>, #Reference<0.0.3.402>}, #Reference<0.0.3.403>}], []}, sock: {:gen_tcp, #Port<0.8158>}, state: :executing, statement: nil, tail: "", usock: #Port<0.8157>}
** (exit) %Tds.Error{message: "tcp closed", mssql: nil}

Any ideas ?

Convert datetime encoders to remove dep from Timex

Value exceeding maximum query length on insert?

Good morning. I am encountering an error when inserting data via the tds ecto adapter:

(Tds.Error) 1 (3971): The server failed to resume the transaction. Desc:3c00000001.
(ecto) lib/ecto/adapters/sql.ex:560: Ecto.Adapters.SQL.transaction/3

when trying to insert a series of values into Sql Server 2008 using Ecto 1.1.8 and tds_ecto (master). When I insert the values via Ecto.Adapters.SQL.query, it works fine:

query = "insert into history(inserted_at, updated_at, action_id,change_id,comment,content,task_id,user_id)
values ('2016-10-12 18:24:02.626300','2016-10-12 18:24:02.626300',9,
'QaZYH1btLea9cO5daCk5L+GvmtGTN4nuLhCeCZH01u8jbcAr5rycqJx+C/6g75lKk2zqjAJ/9y24tZj5gAbfimBdm9gjZB80C8QGp6Z/7q4b5gY113fQgrgSxtPnLBmmorTaDYmnrw4vJl2JFrY=',
'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of - this is the end of the text','Content field',34,307)"

Ecto.Adapters.SQL.query!(Repo, query, [])

but when I try to insert the record using the tds adapter:

cl = History.changeset(%Moosehead.Model.History{change_id: "QaZYH1bt1fku4u6YaSk5L+GvmtGTN4nuLhCeCZH01u8jbcAr5rycqJx+C/6g75lKk2zqjAJ/9y24tZj5gAbfimBdm9gjZB80C8QGp6Z/7q4b5gY113fQgrgSxtPnLBmmorTaDYmnrw4vJl2JFrY=",task_id: 34,
action_id: 9, user_id: 307,
comment: "Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of - this is the end of the text",
content: "test"},%{})

Repo.insert! cl

it fails with the previously stated error. If I decrease the size of the "comment" field to just a few lines of text, it will insert fine via the tds ecto adapter.

I recognize that there is little activity on this project but I figured I would reach out to see if anybody has any thoughts on this before I dive in.

Thanks for any help.

Geoff

The only one of the original tests that fails is "test Max and Large PLP returns (PLPTest)” in test/plp_test.ex. I haven’t dug deep enough into how the original TDS code handled this to discover the breaking change.

How to call stored procedure?

I'm currently testing tds library but have some initial problems. My communication with MSSQL is primarly based on calling stored procedures. I did connection setup properly and now when calling SP with name 'sp_P3DodavExpo' I'm getting no results

iex(22)> Tds.query(dbPid, "sp_P3DodavExpo", [])                                      
{:ok, %Tds.Result{columns: nil, num_rows: 0, rows: []}}

When I misspel procedure name in Tds.query calling I'm getting a correct error message from server so connection is working fine. But dont udesrtand why cant see any rows in result set. Is this a proper stored procedure calling syntax?

stored procs

are there plans to support stored procs? Would be willing to help out in whatever way is needed.

Exception processing small tail

Sometimes what is left over after completing a packet is not long enough to be the packet header for the next packet, leading to seemingly random errors. The same query may even succeed the next time.

The larger the return data the more likely this bug could slip in.

Example exception looks like:

Tds.Connection.query(pid, "SELECT TOP(1000) * FROM [Clients] ORDER BY [ClientId] ASC", [])
** (EXIT from #PID<0.110.0>) an exception was raised:
  ** (MatchError) no match of right hand side value: <<4, 0, 16, 0, 0, 52>>
    (tds) lib/tds/connection.ex:286: Tds.Connection.new_data/2
    (tds) lib/tds/connection.ex:229: Tds.Connection.handle_info/2
    (stdlib) gen_server.erl:593: :gen_server.try_dispatch/4
    (stdlib) gen_server.erl:659: :gen_server.handle_msg/5
    (stdlib) proc_lib.erl:237: :proc_lib.init_p_do_apply/3

uniqueidentifier columns return incorrect binary

If I select a uniqueidentifier column, the binary I get back isn't in the correct format.
Here's a database query using mssql-cli:

> select top 1 UserId from dbo."aspnet_Users" where UserName = 'elathrop';
+--------------------------------------+
| UserId                               |
|--------------------------------------|
| 1557b0fd-40ad-4c6e-9651-161136ad68d2 |
+--------------------------------------+

Here's what I get using tds:

iex(46)> Tds.query!(pid, "select top 1 UserId from dbo.\"aspnet_Users\" where UserName = 'elathrop'", [])
%Tds.Result{
  columns: ["UserId"],
  num_rows: 1,
  rows: [
    [<<253, 176, 87, 21, 173, 64, 110, 76, 150, 81, 22, 17, 54, 173, 104, 210>>]
  ]
}
iex(47)> UUID.binary_to_string!(<<253, 176, 87, 21, 173, 64, 110, 76, 150, 81, 22, 17, 54, 173, 104, 210>>)
"fdb05715-ad40-6e4c-9651-161136ad68d2"

Here's what the binary should look like:

iex(50)> UUID.string_to_binary!("1557b0fd-40ad-4c6e-9651-161136ad68d2")
<<21, 87, 176, 253, 64, 173, 76, 110, 150, 81, 22, 17, 54, 173, 104, 210>>

Notice that the second half is correct, but the first half has all the same bytes, just in the wrong order.

The way parameters are handled for prepared queries feels wrong

I don’t like the way I had to hack parameters into handle_prepare because the handle_prepare callback only takes the query, opts, and state. It made me feel like I was doing something wrong.

Code duplication and code no longer used should be removed if possible

There are places where code is duplicated and can probably be extracted into a module as long as it lessens complexity rather than adds it. The example that sticks out the most to me is the similarity between the fix_data_type functions in lib/tds/paramater.ex and the encode_param_descriptor functions in lib/tds/types.ex.

There is also code commented out or code not touched at all anymore that could be cleaned up.

Fail to connect if database name contains special character such as "-"

Hi, i have the next issue with a database name with "-"

config :my_app, MyApp.Repo,
adapter: Tds.Ecto,
username: "sa",
password: "password",
database: "db-name",
hostname: "local.db.sqlserver",
pool_size: 10

The dependencies are:

Resolving Hex dependencies...
Dependency resolution completed:
connection 1.0.4
db_connection 1.1.3
ecto 2.2.8
postgrex 0.13.3
tds 1.0.11
tds_ecto 2.0.7

And the error is:

[error] Tds.Protocol (#PID<0.4038.0>) failed to connect: ** (Tds.Error) Line 10 (Error 102): Incorrect syntax near '-'.

The database is en SQL Server 2008 R2

Elixir 1.4.5
Erlang/OTP 18 [erts-7.3.1] [source] [64-bit] [smp:4:4] [async-threads:10] [hipe] [kernel-poll:false] [dtrace]

Eshell V7.3.1

Thanks for your time !

date format dependency on default language

I added a user with default language German and ran the tests:

2) test Decode Fixed Length Data types (QueryTest)
     test/query_test.exs:21
     match (=) failed
     code: [{1, false, 12, 100, {{2014, 10, 1}, {12, 30, 0}}, 5.2, -40532.5367, {{2014, 1, 11}, {11, 34, 25}}, 5.6, 6.3452, 1000}] = query("SELECT TOP(1) * FROM FixedLength", [])
     rhs:  [{1, false, 12, 100, {{2014, 1, 10}, {12, 30, 0}}, 5.2, -40532.5367, {{2014, 11, 1}, {11, 34, 25}}, 5.6,
             6.3452, 1000}]
     stacktrace:
       test/query_test.exs:38

After changing the user's default language to English, the test succeeded.
Another workaround was to use a "T" between date and time in the insert query:

query("INSERT INTO FixedLength VALUES(1, 0, 12, 100, '2014-10-01T12:30:00', 5.2, '$-40,532.5367', '2014-01-11T11:34:25', 5.6, '$6.3452', 1000)", []);

This makes MSSQL believe that the string is ISO8601 formatted which fixes the sequence to y-m-d independent of language and DATEFORMAT.

Set internal socket buffer

See: elixir-ecto/postgrex#77

(Tds.Error) 1 (131): The size (10826) given to the type 'varbinary' exceeds the maximum allowed for any data type (8000).

I'm seeing this error message trying to save large amounts of text to an existing MS SQL database. The current solution works fine, but I'm struggling to do the same with tds.

Here's the schema of the class:

schema "somedata" do
    field :analysis, :string
    field :available, :naive_datetime
    field :contents, :string
    field :created, :naive_datetime
    field :heading, :string
    field :onhold, :integer
    field :typecode, :integer
    field :url, :string
    field :userid, :integer

    timestamps(inserted_at: :date, updated_at: false)
  end

The only fields that need to be big are analysis and contents, and they're set to nvarchar(max) on the database.

Is there a way to fix this? I'm starting to think that if the size exceeds 8000 for VARCHAR or 4000 for NVARCHAR, the driver should not use the real size but "MAX" instead as hinted here -> http://www.sql-server-helper.com/error-messages/msg-131.aspx

Confusion with README for ecto_sql support.

The README states: "Since TDS version 2.0, tds_ecto package is deprecated, this version supports ecto_sql since version 3.1.0.", but there is no adapter option listed that replaces tds_ecto.
https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#module-built-in-adapters

Error when attempting to restore

I'm trying to restore a backup file. The restore succeeds, but TDS seems to be failing to handle the response:

** (EXIT from #PID<0.73.0>) an exception was raised:
    ** (DBConnection.ConnectionError) client #PID<0.73.0> stopped: ** (FunctionClauseError) no function clause matching in Keyword.delete_key/3
    (elixir) lib/keyword.ex:498: Keyword.delete_key([[%{class: 0, length: 204, line_number: 2, msg_text: "Processed 105008 pages for database 'mydb', file 'mydb_data' on file 1.", number: 4035, proc_name: "", server_name: "589042304e43", state: 1}]], :done, false)
    (elixir) lib/keyword.ex:493: Keyword.put/3
    lib/tds/tokens.ex:381: Tds.Tokens.decode_token/2
    lib/tds/tokens.ex:49: Tds.Tokens.decode_tokens/2
    lib/tds/messages.ex:99: Tds.Messages.parse/4
    lib/tds/protocol.ex:326: Tds.Protocol.new_data/2
    lib/tds/protocol.ex:505: Tds.Protocol.send_param_query/3
    lib/db_connection.ex:958: DBConnection.handle/4
    lib/db_connection.ex:1078: DBConnection.describe_run/5
    lib/db_connection.ex:1199: DBConnection.run_begin/3
    lib/db_connection.ex:1133: DBConnection.run_meter/3
    lib/db_connection.ex:584: DBConnection.prepare_execute/4
    lib/tds.ex:24: Tds.query!/4

Compiling in Windows

I'm attempting to compile from the master branch from Windows, but I'm getting the following error when running mix deps.compile

===> Compiling credentials_obfuscation
==> gettext
Compiling 1 file (.erl)
Compiling 20 files (.ex)
Generated gettext app
===> Compiling ranch
===> Compiling telemetry
warning: found quoted keyword "coveralls" but the quotes are not required. Note that keywords are always atoms, even when quoted. Similar to atoms, keywords made exclusively of Unicode letters, numbers, underscore, and @ do not require quotes
  c:/Users/ben/Development/rti_processor/deps/parallel_stream/mix.exs:17

==> parallel_stream
Compiling 11 files (.ex)
warning: Stream.chunk/4 is deprecated. Use Stream.chunk_every/4 instead
  lib/parallel_stream/producer.ex:16

Generated parallel_stream app
==> recon
Compiling 6 files (.erl)
Generated recon app
==> csv
Compiling 7 files (.ex)
warning: function CSV.Decoding.Decoder.decode/2 is undefined (module CSV.Decoding.Decoder is not available)
Found at 2 locations:
  lib/csv.ex:103
  lib/csv.ex:187

warning: function CSV.Decoding.Preprocessing.Lines.process/2 is undefined (module CSV.Decoding.Preprocessing.Lines is not available)
  lib/csv.ex:196

Generated csv app
warning: function CSV.Decoding.Preprocessing.None.process/2 is undefined (module CSV.Decoding.Preprocessing.None is not available)
  lib/csv.ex:193

==> jsx
Compiling 9 files (.erl)
Generated jsx app
==> ecto
Compiling 55 files (.ex)
Generated ecto app
==> phoenix_pubsub
Compiling 13 files (.ex)
Generated phoenix_pubsub app
===> Compiling cowlib
===> Compiling cowboy
==> tds
Compiling NIF crate :tds_encoding (native/tds_encoding)...
   Compiling cc v1.0.37
   Compiling libc v0.2.60
   Compiling encoding_index_tests v0.1.4
   Compiling cfg-if v0.1.9
   Compiling rustc-demangle v0.1.15
   Compiling proc-macro2 v0.4.30
   Compiling unicode-xid v0.1.0
   Compiling erlang_nif-sys v0.6.4
   Compiling void v1.0.2
   Compiling syn v0.15.39
   Compiling lazy_static v1.3.0
   Compiling unicode-segmentation v1.3.0
   Compiling encoding-index-simpchinese v1.20141219.5
   Compiling encoding-index-korean v1.20141219.5
   Compiling encoding-index-singlebyte v1.20141219.5
   Compiling encoding-index-japanese v1.20141219.5
   Compiling encoding-index-tradchinese v1.20141219.5
   Compiling unreachable v0.1.1
   Compiling heck v0.3.1
   Compiling encoding v0.2.33
error: failed to run custom build command for `erlang_nif-sys v0.6.4`

Caused by:
  process didn't exit successfully: `c:/Users/ben/Development/rti_processor/_build/prod/rustler_crates/tds_encoding\release\build\erlang_nif-sys-432ea327326f7dfc\build-script-build` (exit code: 101)
--- stdout
Unsupported Erlang version.

Is the erlang_nif-sys version up to date in the Cargo.toml?
Does 'cargo update' fix it?
If not please report at https://github.com/goertzenator/erlang_nif-sys.

--- stderr
thread 'main' panicked at 'gen_api.erl encountered an error.', C:\Users\ben\.cargo\registry\src\github.com-1ecc6299db9ec823\erlang_nif-sys-0.6.4\build.rs:28:13
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace.

warning: build failed, waiting for other jobs to finish...
error: build failed
could not compile dependency :tds, "mix compile" failed. You can recompile this dependency with "mix deps.compile tds", update it with "mix deps.update tds" or clean it with "mix deps.clean tds"
** (RuntimeError) Rust NIF compile error (rustc exit code 101)
    lib/mix/tasks/compile.rustler.ex:67: Mix.Tasks.Compile.Rustler.compile_crate/1
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
    lib/mix/tasks/compile.rustler.ex:14: Mix.Tasks.Compile.Rustler.run/1
    (mix) lib/mix/task.ex:331: Mix.Task.run_task/3
    (mix) lib/mix/tasks/compile.all.ex:73: Mix.Tasks.Compile.All.run_compiler/2
    (mix) lib/mix/tasks/compile.all.ex:53: Mix.Tasks.Compile.All.do_compile/4
    (mix) lib/mix/tasks/compile.all.ex:24: anonymous fn/1 in Mix.Tasks.Compile.All.run/1
    (mix) lib/mix/tasks/compile.all.ex:40: Mix.Tasks.Compile.All.with_logger_app/1

I think it might be related to this issue, but I'm not sure how to resolve it in Windows.

If I can provide any additional information, please don't hesitate to let me know.

Sending Float with more than 9 characters fails to insert data

Tds.Connection.query(pid, "INSERT INTO hades_sealed_cfdis ([total] ,[inserted_at], [updated_at]) VALUES (@1,@2,@3)", [%Tds.Parameter{name: "@1", value: 762213236.1},%Tds.Parameter{name: "@2", value: {{2016, 12, 20}, {23, 59, 23, 0}}},%Tds.Parameter{name: "@3", value: {{2016, 12, 20}, {23, 59, 23, 0}}}])

{:error, %Tds.Error{message: nil, mssql: %{class: 16, length: 404, line_number: 1, msg_text: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (\"@.1.\"): Data type 0x6D has an invalid data length or metadata length.", number: 8016, proc_name: "", server_name: "WIN-96BL2N05ERO", state: 46}}}

Why do you stop using sp_executeSQL?

Why do you stop using sp_executeSQL?, In my past experience with sp_prepare, doing this in separate steps typically increases the total time involved because you are forcing more network round-trips to the statement execution: 1) for the prepare, 1 for the statement, and 1 for the unprepared. While I am no expert, it is my opinion that with using sp_executeSQL, the other two steps are not needed here and is more faster.

(DBConnection.ConnectionError) client #PID<0.369.0> stopped: ** (FunctionClauseError) no function clause matching in Decimal.abs/1

When trying to insert negative value into INT column, error is rised

19:57:02.413 [error] GenServer #PID<0.354.0> terminating
** (DBConnection.ConnectionError) client #PID<0.369.0> stopped: ** (FunctionClauseError) no function clause matching in Decimal.abs/1
    (decimal) lib/decimal.ex:242: Decimal.abs(-1)
    (tds) lib/tds/types.ex:903: Tds.Types.encode_decimal_type/1
    (tds) lib/tds/messages.ex:435: Tds.Messages.encode_rpc_param/1
    (tds) lib/tds/messages.ex:428: Tds.Messages.encode_rpc_params/2
    (tds) lib/tds/messages.ex:416: Tds.Messages.encode_rpc/2
    (tds) lib/tds/messages.ex:316: Tds.Messages.encode/2
    (tds) lib/tds/protocol.ex:656: Tds.Protocol.msg_send/2
    (tds) lib/tds/protocol.ex:505: Tds.Protocol.send_param_query/3

    (db_connection) lib/db_connection/connection.ex:243: DBConnection.Connection.handle_cast/2
    (connection) lib/connection.ex:488: Connection.handle_call/3
    (stdlib) gen_server.erl:629: :gen_server.try_handle_call/4
    (stdlib) gen_server.erl:661: :gen_server.handle_msg/5
    (stdlib) proc_lib.erl:240: :proc_lib.init_p_do_apply/3

Ecto 3 support

Thanks for creating this package.
Is Ecto-3 supported?
We are getting compile time error when we mention adapter: Tds.Ecto in Ecto-3 Repo.

Tds.Result struct: command?

The struct definition of Tds.Result includes the field :command, which is missing from the type and the moduledoc and which doesn't seem to be used anywhere in the library (if I haven't overlooked it somewhere). Can I safely remove it from the struct definition?

Is money supposed to be a decimal?

When the value comes out of the database, it's a float. Would this value be better represented as a decimal?

The ruby library tiny_tds seems to treat this value as a decimal.

What versions of SQL Server are supported?

I tried to connect to SQL Server 2000 (8.0) and got this:

** (FunctionClauseError) no function clause matching in Tds.Tokens.decode_token/2
    (tds) lib/tds/tokens.ex:98: Tds.Tokens.decode_token(<<253, 0, 0, 0, 0, 0, 0, 0, 0>>, [packetsize: "4096", login_ack: %{program: "Microsoft SQL Server", t_sql_only: true, tds_version: "0x71000001", version: "8.0.7.247"}])
    (tds) lib/tds/tokens.ex:78: Tds.Tokens.decode_tokens/2
    (tds) lib/tds/messages.ex:93: Tds.Messages.parse/4
    (tds) lib/tds/protocol.ex:517: Tds.Protocol.new_data/2
    (tds) lib/tds/protocol.ex:583: Tds.Protocol.login/1
    (tds) lib/tds/protocol.ex:378: Tds.Protocol.connect/2
    (db_connection) lib/db_connection/connection.ex:66: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
Last message: nil
State: Tds.Protocol
** (EXIT from #PID<0.475.0>) shell process exited with reason: killed

I'm guessing there's an issue connecting to SQL Server 2000, but I couldn't find which versions are officially supported by this package.

MatchError upon Connecting

Using Tds 1.1.2 on Windows 10 using SQL Express

I keep getting this error when trying to connect. It looks like it's blowing up when trying to receive data back from the UDP connection on 204. I ran each of these, line by line, and I was able to reproduce this error.

** (RuntimeError) Connect raised a MatchError error. The exception details are hidden, as
they may contain sensitive data such as database credentials.

    (tds) lib/tds/protocol.ex:204: Tds.Protocol.instance/2
    (tds) lib/tds/protocol.ex:56: Tds.Protocol.connect/1
    (db_connection) lib/db_connection/connection.ex:135: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message: nil
State: Tds.Protocol

ALTER DATABASE statement on session start is problematic

Hi @mjaric,
when running tds using a user that does not have any ALTER privileges (production settings), the statement

ALTER DATABASE [#{database}] SET ALLOW_SNAPSHOT_ISOLATION ON;

in protocol.ex that runs after logging in returns this error:

19:36:31.913 [error] Tds.Protocol (#PID<0.202.0>) failed to connect: ** (Tds.Error) Line 1 (Error 5011): User does not have permission to alter database 'XXX', the database does not exist, or the database is not in a state that allows access checks

The reason no-one's seen it until now probably is that the current implementation stops when parsing the first result set (ie SET ANSI_NULLS ON;)- I noticed this when running some tests using a non-privileged user under the multiple_datasets_v2 branch

Is this statement required for the driver to work properly? My guess it that since this is a statement with potentially far reaching repercussions it should be avoided anyway.

What are your thoughts on this?

Unable to connect to local sqlexpress instance

Please i get this error on Elixir 1.3

iex(1)> {:ok, pid} = Tds.Connection.start_link([hostname: "localhost", instance: "sqlexpress", username: "sa", password: "test", database: "test", port: 1433])
** (EXIT from #PID<0.124.0>) an exception was raised:
    ** (FunctionClauseError) no function clause matching in Integer.parse_in_base/2
        (elixir) lib/integer.ex:145: Integer.parse_in_base(nil, 10)
        lib/tds/connection.ex:210: Tds.Connection.handle_info/2
        (stdlib) gen_server.erl:615: :gen_server.try_dispatch/4
        (stdlib) gen_server.erl:681: :gen_server.handle_msg/5
        (stdlib) proc_lib.erl:240: :proc_lib.init_p_do_apply/3

Looking at the source line: https://github.com/livehelpnow/tds/blob/master/lib/tds/connection.ex#L210

{port, _} = Integer.parse(serv[:tcp])

Not sure if this is from Elixir itself or from Tds.
Seems we get serv[:tcp] = nil for some reason, hence this error.

A few db_connection callbacks are only returning state

There are some db_connection callbacks in lib/tds/protocol.ex I’m unsure of how to handle: checkin, checkout, and ping. Right now they are not doing anything besides returning the original state.

Any input or pull requests are greatly appreciated.

Decimal type should yield rounded values as e.g. Decimal<4> instead of Decimal<4.0>

In order to reuse ecto_sql and ecto integration tests for MSSQL adapter, tds decoder should cut leading decimal zero if number is equal to its integer value.

For an instance:
Db value 4.0 should be returned as Decimal<4> but currently it is Decimal<4.0>

Missing DBConnection callbacks for cursors

There are some warnings about undefined behaviour functions related to cursors: handle_deallocate/4, handle_declare/4, handle_first/4 and handle_next/4.

Callback definitions

T-SQL documentation

Encounter some of errors in test/datetime_test.exs

Errors were as next.

  1) test datetime offset (DatetimeTest)
     test/datetime_test.exs:215
     ** (MatchError) no match of right hand side value: ~N[2015-04-08 15:16:23.000000]
     code: Types.decode_datetimeoffset(7, enc)
     stacktrace:
       (tds) lib/tds/types.ex:1789: Tds.Types.decode_datetimeoffset/2
       test/datetime_test.exs:220: (test)

.

  2) test implicit params (DatetimeTest)
     test/datetime_test.exs:274
     ** (CaseClauseError) no case clause matching: {:error, %MatchError{term: ~N[2015-04-08 15:16:23.000000]}}
     code: query("SELECT @n1", [
     stacktrace:
       test/datetime_test.exs:296: (test)



  3) test datetime2 (DatetimeTest)
     test/datetime_test.exs:168
     ** (ArgumentError) cannot convert {{2015, 4, 8}, {131, 56, 23}} to naive datetime, reason: :invalid_time
     code: assert {@date, {131, 56, 23, 0}} == Types.decode_datetime2(0, enc)
     stacktrace:
       (elixir) lib/calendar/naive_datetime.ex:742: NaiveDateTime.from_erl!/3
       test/datetime_test.exs:175: (test)

Unexpected Environment Change message kills the connection

Hi,

Thanks for this great library!

While connecting to one SQL server, the connection was being killed right after connecting. I dig into the actual message that the client was receiving and I found out that, in my case I was receving shortly after a successful login a Environment Change message with the code 13.

After reading this I found out that the code belong to "Real Time Log Shipping/Database Mirroring
Partner" messages.

In the meanwhile I adjusted the function that decodes this kind of message https://github.com/livehelpnow/tds/blob/00a899f47d2649c33c5b08980c86837b8617913e/lib/tds/tokens.ex#L218 and added the "13" case as a no-op.

If you think is useful I can open a PR.

Tds.Utils.ucs2_to_utf fails on umlauts

Running mix test on a German SqlServer throws many exceptions like

    ** (UnicodeConversionError) invalid encoding starting at <<246, 0, 115, 0, 99, 0, 104, 0, 101, 0, 110, 0, 32, 0, 100, 0, 101, 0, 115, 0, 32, 0, 84, 0, 97, 0, 98, 0, 101, 0, 108, 0, 108, 0, 101, 0, 45, 0, 79, 0, 98, 0, 106, 0, 101, 0, 107, 0, 116, 0, ...>>
        (elixir) lib/string.ex:1338: String.to_char_list/1
        (tds) lib/tds/utils.ex:26: Tds.Utils.ucs2_to_utf/1
        (tds) lib/tds/tokens.ex:65: Tds.Tokens.decode_token/2
        (tds) lib/tds/tokens.ex:33: Tds.Tokens.decode_tokens/2
        (tds) lib/tds/messages.ex:85: Tds.Messages.parse/4
        (tds) lib/tds/connection.ex:183: Tds.Connection.new_data/2
        (tds) lib/tds/connection.ex:117: Tds.Connection.handle_info/2
        (stdlib) gen_server.erl:593: :gen_server.try_dispatch/4

The problems disappears after patching ucs2_to_utf:

   def ucs2_to_utf(s) do
-    to_char_list(s) |> Enum.reject(&(&1 == 0)) |> to_string
+    :binary.bin_to_list(s) |> Enum.reject(&(&1 == 0)) |> to_string
   end

The documentation for String.to_char_list confirms this:

  Notice that this function expects a list of integers representing
  UTF-8 codepoints. If you have a raw binary, you must instead use
  [the `:binary` module](http://erlang.org/doc/man/binary.html).

There is no Decimal.new/0

Decimal.new() throws an error: https://github.com/livehelpnow/tds/blob/master/lib/tds/types.ex#L941

  def encode_decimal_descriptor(%Parameter{type: :decimal} = param) do
    encode_decimal_descriptor(%{param | value: Decimal.new()})
  end

This works: #19

  def encode_decimal_descriptor(%Parameter{type: :decimal, value: value} = param) do
    encode_decimal_descriptor(%{param | value: Decimal.new(value)})
  end

Cannot connect to Azure SQL Server instance

I'm an utter MSSQL noob, but I think an encrypted connection is required to connect to sql server instances running on azure. I can use the pymssql python library to connect, so I don't think the issue is network related.

Example of how to use with `ecto_sql`?

The README says this library is compatible with ecto_sql from 3.1.0 on, but it doesn't describe how to use it. I found the fork where the Ecto adapter is being created, but it doesn't look like it's been merged into mainline ecto_sql yet.

Am I missing something? Is the README correct? Is there some way to use this with Ecto 3.x? Is there some way I can help?

unused expression in decode_decimal

I went through the code tagging unused variables and came upon this:

lib/tds/types.ex raises a warning during compilation:

lib/tds/types.ex:549: warning: the result of the expression is ignored (suppress the warning by assigning the expression to the _ variable)

Should that be value = case sign do...? I have neither tried it nor checked the tests, but it would make sense to me.

Query input parameters of type `char`, `text` and `varchar` should use collation set to connected database by default

With the addition of Tds.Encoding , which supports other codepages, parameters of type char, varchar, and text should be encoded into the codepage set to the database by default. The database collation is received from the SQL server on the Login7 response. Since language and collation can be defined during database creation, we need to add more tests where additional databases are created before tests are run. The session will pick up these settings and apply them to the connection, so TDS should respect these settings on login and keep them in the connection state.

When decoding results, MSSQL returns ColumnInfo before rows. Column info, in almost all cases, contains collation, so this value should have higher priority than the one received during login.

However, it's difficult to use different collations for column and database settings. As a workaround for inserting, you can manually encode varchar and binary data sent as varbinary parameters during insertion. The column that should store this value must be in the used collation (CODEPAGE) to save it properly. While this workaround will allow you to read such columns, attempting to compare them (parameter and column) will likely fail, or the query will be over 10 times slower. The execution plan will generate undesired reads since the SQL server will convert the varchar column to varbinary to compare it, and this action will spill into tempdb.

Could not find prepared statement with handle 0

Hello,
Tds.query raises the exception "Could not find prepared statement with handle 0".
I have added a test for this in PR #44.

It seems that the exception will be raised if sp_prepare returns a @handle with value 0.

TDS should cache prepared queries per connection session to speed up query execution

As discussed in elixir-ecto/ecto_sql

Getting CaseClauseError in Tds.Protocol.ping

Firing up a new phoenix app and receiving the following every few seconds in the console.

[error] GenServer #PID<0.318.0> terminating
** (CaseClauseError) no case clause matching: {:ok, %Tds.Result{columns: ["msg"], command: nil, num_rows: 1, rows: [["pong"]]}, %Tds.Protocol{env: %{trans: <<0>>}, itcp: nil, opts: [password: :REDACTED, idle_timeout: 5000, name: Home.Repo.Pool, otp_app: :home, repo: Home.Repo, timeout: 15000, pool_timeout: 5000, adapter: Tds.Ecto, database: "redacted", username: "redacted", hostname: "redacted", pool_size: 10, pool: DBConnection.Poolboy], pak_data: "", pak_header: "", query: nil, result: %Tds.Result{columns: ["msg"], command: nil, num_rows: 1, rows: [["pong"]]}, sock: {:gen_tcp, #Port<0.8983>}, state: :ready, tail: "", transaction: nil, usock: nil}}
(tds) lib/tds/protocol.ex:62: Tds.Protocol.ping/1
(db_connection) lib/db_connection/connection.ex:358: DBConnection.Connection.handle_info/2
(connection) lib/connection.ex:810: Connection.handle_async/3
(stdlib) gen_server.erl:601: :gen_server.try_dispatch/4
(stdlib) gen_server.erl:667: :gen_server.handle_msg/5
(stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message: :timeout
State: {Tds.Protocol, %Tds.Protocol{env: %{trans: <<0>>}, itcp: nil, opts: [password: :REDACTED, idle_timeout: 5000, name: Home.Repo.Pool, otp_app: :home, repo: Home.Repo, timeout: 15000, pool_timeout: 5000, adapter: Tds.Ecto, database: "redacted", username: "redacted", hostname: "redacted", pool_size: 10, pool: DBConnection.Poolboy], pak_data: "", pak_header: "", query: nil, result: %Tds.Result{columns: nil, command: nil, num_rows: 0, rows: []}, sock: {:gen_tcp, #Port<0.8983>}, state: :executing, tail: "", transaction: nil, usock: nil}}

Project status and Erlang ODBC alternative

Just curious if Elixir tds driver is still a thing, and whether there will be Ecto 2 support any time soon (if ever). Not to prod, just an honest question if anyone's still interested in working on this.

A separate but related question, what prompted you to write a native tds driver versus going with a wrapper for Erlang's own ODBC application? Are there issues (stability? performance?) with Erlang's ODBC application? (Also a nice writeup here - it even shows using Windows authentication in the connection string!)

The rationale for moving forward with native tds route instead of using ODBC would also make a great addition to the readme...

FunctionClauseError: reply/2

I'm getting a function clause error when the tcp connection fails.

15:00:28.700 [error] GenServer #PID<0.8450.0> terminating
** (FunctionClauseError) no function clause matching in Tds.Utils.reply/2
    (tds) lib/tds/utils.ex:50: Tds.Utils.reply(%Tds.Error{message: "tcp connect: nxdomain", mssql: nil}, %Tds.Protocol{env: %{trans: <<0>>}, itcp: nil, opts: [<redacted>], pak_data: "", pak_header: "", query: nil, result: nil, sock: nil, state: :ready, tail: "", transaction: nil, usock: nil})
    (tds) lib/tds/utils.ex:46: Tds.Utils.error/2
    (db_connection) lib/db_connection/connection.ex:134: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:247: :proc_lib.init_p_do_apply/3
Last message: nil
State: Tds.Protocol

Where to find list of config options?

The README includes an example configuration section which shows some of the available config options. It will be nice to have a section showing all available config options and possible values where applicable.

I am executing a long running query for an ETL pipeline but i get 08:26:45.253 [error] Tds.Protocol (#PID<0.453.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.407.0> timed out because it queued and checked out the connection for longer than 15000ms {:error, %DBConnection.ConnectionError{ message: "connection is closed (the connection was closed by the pool, possibly due to a timeout or because the pool has been terminated)" }}

I would like to know how to increase the timeout

Add support for datetime2 encoding

when sending a param as datetime2 type or when sending a datetime where
{{_,_,_},{_,_,_,us}}
where us is a number > 0 the driver attempts to encode as a string type instead of datetime2.

pins missing in rpc_test.exs?

$ mix test
...
test/rpc_test.exs:32: warning: variable num is unused
test/rpc_test.exs:43: warning: variable num is unused
test/rpc_test.exs:54: warning: variable num is unused
test/rpc_test.exs:65: warning: variable str is unused
test/rpc_test.exs:76: warning: variable str is unused

After checking the docs, I am wondering if the asserts there should not be using ^num and ^str on the left hand side.

[error] Tds.Protocol (#PID<0.3173.0>) failed to connect: ** (Tds.Error) tcp connect: timeout

This error started spamming my console about once every 10 seconds:

[error] Tds.Protocol (#PID<0.3173.0>) failed to connect: ** (Tds.Error) tcp connect: timeout

I'm recently updated from tds 2.0.0 -> 2.0.4 and db_connection 2.0.6 -> 2.2.1, but even after switching back to the old versions, the log message remains.

The database connection seems to be working fine, so I'm not sure what's timing out.

Also, there's no stack trace, so it's really hard to track this down.

Tds.Parameter.prepare_params/1 returns the same :name for all params

Should the prepare_params/1 function return unique names for each parameter?

eg:

iex(1)> Tds.Parameter.prepare_params(["test1", "test2"])
[
  %Tds.Parameter{
    direction: :input,
    length: nil,
    name: "@1",
    type: :string,
    value: "test2"
  },
  %Tds.Parameter{
    direction: :input,
    length: nil,
    name: "@1",
    type: :string,
    value: "test1"
  }
]

I thought "test1" would get "@1" and "test2" would get "@2".
Is there a better way to do this?

Thanks!