changed README.md
 
@@ -37,6 37,42 @@ iex> Tds.query!(pid, "INSERT INTO MyTable (MyColumn) VALUES (@my_value)",
37
37
* Automatic decoding and encoding of Elixir values to and from MSSQL's binary format
38
38
* Support of TDS Versions 7.3, 7.4
39
39
40
## Configuration
41
42
Example configuration
43
44
```elixr
45
import Mix.Config
46
47
config :your_app, tds_conn,
48
hostname: "localhost",
49
username: "test_user",
50
password: "test_password",
51
database: "test_db",
52
port: 1433
53
```
54
55
Then using `Application.get_env(:your_app, :tds_conn)` use this as first parameter in `Tds.start_link/1` function.
56
57
There is additional parameter that can be used in configuration and
58
can improve query execution in sql server. If you find out that
59
your queries suffer form "denst estimation" as described [here](https://www.brentozar.com/archive/2018/03/sp_prepare-isnt-good-sp_executesql-performance/)
60
61
you can try switching how tds executes queries as below:
62
63
```elixr
64
import Mix.Config
65
66
config :your_app, tds_conn,
67
hostname: "localhost",
68
username: "test_user",
69
password: "test_password",
70
database: "test_db",
71
port: 1433,
72
execution_mode: :executesql
73
```
74
This will skip calling `sp_prepare` and query will be executed using `sp_executesql` instead. Please note that only single execution mode can be set at the time and SQL will probably use single execution plan (since it is NOT estimated by checking data density!).
75
40
76
## Connecting to SQL Instances
41
77
42
78
Tds supports SQL instances by passing `instance: "instancename"` to the connection options.
changed hex_metadata.config
 
@@ -13,7 13,6 @@
13
13
<<"README.md">>]}.
14
14
{<<"licenses">>,[<<"Apache 2.0">>]}.
15
15
{<<"links">>,[{<<"Github">>,<<"https://github.com/livehelpnow/tds">>}]}.
16
- {<<"maintainers">>,[<<"Eric Witchin">>,<<"Milan Jaric">>]}.
17
16
{<<"name">>,<<"tds">>}.
18
17
{<<"requirements">>,
19
18
[[{<<"app">>,<<"decimal">>},
 
@@ -26,4 25,4 @@
26
25
{<<"optional">>,false},
27
26
{<<"repository">>,<<"hexpm">>},
28
27
{<<"requirement">>,<<"~> 1.1">>}]]}.
29
- {<<"version">>,<<"1.1.4">>}.
28
{<<"version">>,<<"1.1.5">>}.
changed lib/tds.ex
 
@@ -2,6 2,7 @@ defmodule Tds do
2
2
alias Tds.Query
3
3
4
4
@timeout 5000
5
@execution_mode :prepare_execute
5
6
6
7
def start_link(opts \\ []) do
7
8
DBConnection.start_link(Tds.Protocol, default(opts))
 
@@ -85,6 86,8 @@ defmodule Tds do
85
86
end
86
87
87
88
defp default(opts) do
88
- Keyword.put_new(opts, :idle_timeout, @timeout)
89
opts
90
|> Keyword.put_new(:idle_timeout, @timeout)
91
|> Keyword.put_new(:execution_mode, @execution_mode)
89
92
end
90
93
end
changed lib/tds/binary_utils.ex
 
@@ -1,116 1,17 @@
1
1
defmodule Tds.BinaryUtils do
2
2
@moduledoc false
3
3
4
- defmacro bit(), do: quote do: size(1)
5
-
6
- @doc """
7
- An unsigned single byte (8-bit) value. The range is 0 to 255.
8
- """
9
- defmacro byte(), do: quote do: unsigned-8
10
-
11
- @doc """
12
- An unsigned single byte (8-bit) value representing the length of the associated data. The range is 0 to 255.
13
- """
14
- defmacro bytelen(), do: quote do: unsigned-8
15
-
16
- @doc """
17
- An unsigned 2-byte (16-bit) value. The range is 0 to 65535.
18
- """
19
- defmacro ushort(), do: quote do: little-unsigned-16
20
-
21
- @doc """
22
- A signed 4-byte (32-bit) value. The range is -(2^31) to (2^31)-1.
23
- """
24
- defmacro long(), do: quote do: little-signed-32
25
-
26
- @doc """
27
- A signed 8-byte (64-bit) value. The range is –(2^63) to (2^63)-1.
28
- """
29
- defmacro longlong(), do: quote do: little-signed-64
30
-
31
- @doc """
32
- An unsigned 4-byte (32-bit) value. The range is 0 to (2^32)-1
33
- """
34
- defmacro ulong(), do: quote do: little-unsigned-32
35
-
36
- @doc """
37
- An unsigned 8-byte (64-bit) value. The range is 0 to (2^64)-1.
38
- """
39
- defmacro ulonglong(), do: quote do: little-unsigned-64
40
-
41
- @doc """
42
- An unsigned 4-byte (32-bit) value. The range when used as a numeric value is 0 to (2^32)- 1.
43
- """
44
- defmacro dword(), do: quote do: unsigned-32
45
-
46
- @doc """
47
- An unsigned single byte (8-bit) value representing a character. The range is 0 to 255.
48
- """
49
- defmacro uchar(), do: quote do: unsigned-8
50
-
51
- @doc """
52
- An unsigned 2-byte (16-bit) value representing the length of the associated data. The range is 0 to 65535.
53
- """
54
- defmacro ushortlen(), do: quote do: little-unsigned-16
55
-
56
- @doc """
57
- An unsigned 2-byte (16-bit) value representing the length of the associated character or binary data. The range is 0 to 8000.
58
- """
59
- defmacro ushortcharbinlen(), do: quote do: little-unsigned-16
60
-
61
- @doc """
62
- A signed 4-byte (32-bit) value representing the length of the associated data. The range is -(2^31) to (2^31)-1.
63
- """
64
- defmacro longlen(), do: quote do: little-signed-32
65
-
66
- @doc """
67
- An unsigned 8-byte (64-bit) value representing the length of the associated data. The range is 0 to (2^64)-1.
68
- """
69
- defmacro ulonglonglen(), do: quote do: little-unsigned-64
70
-
71
- @doc """
72
- An unsigned single byte (8-bit) value representing the precision of a numeric number.
73
- """
74
- defmacro precision(), do: quote do: unsigned-8
75
-
76
- @doc """
77
- An unsigned single byte (8-bit) value representing the scale of a numeric number.
78
- """
79
- defmacro scale(), do: quote do: unsigned-8
80
-
81
- @doc """
82
- A single byte (8-bit) value representing a NULL value.
83
- """
84
- defmacro gen_null(), do: quote do: 0x00::size(8)
85
-
86
- @doc """
87
- A 2-byte (16-bit) or 4-byte (32-bit) value representing a T-SQL NULL value for a character or binary data type.
88
-
89
- Please refer to TYPE_VARBYTE (see MS-TDS.pdf section 2.2.5.2.3) for additional details.
90
- """
91
- defmacro charbin_null(2), do: quote do: 0x0000::size(16)
92
- defmacro charbin_null(4), do: quote do: 0x00000000::size(32)
93
-
94
- @doc """
95
- A FRESERVEDBIT is a BIT value used for padding that does not transmit information.
96
-
97
- FRESERVEDBIT fields SHOULD be set to � and MUST be ignored on receipt.
98
- """
99
- defmacro freservedbit(), do: quote do: 0x0::size(1)
100
-
101
- @doc """
102
- A FRESERVEDBYTE is a BYTE value used for padding that does not transmit information. FRESERVEDBYTE fields SHOULD be set to %x00 and MUST be ignored on receipt.
103
- """
104
- defmacro freservedbyte(), do: quote do: 0x00::size(8)
105
-
106
-
107
- defmacro int16(), do: quote do: signed-16
108
-
109
- defmacro int32(), do: quote do: signed-32
110
-
111
- defmacro int64(), do: quote do: signed-64
4
defmacro int64 do
5
quote do: signed - 64
6
end
112
7
8
defmacro int32 do
9
quote do: signed - 32
10
end
113
11
12
defmacro int16 do
13
quote do: signed - 16
14
end
114
15
115
16
defmacro uint16 do
116
17
quote do: unsigned - 16
changed lib/tds/protocol.ex
 
@@ -40,6 40,7 @@ defmodule Tds.Protocol do
40
40
transaction: nil,
41
41
env: %{trans: <<0x00>>, savepoint: 0}
42
42
43
@impl DBConnection
43
44
def connect(opts) do
44
45
opts =
45
46
opts
 
@@ -66,6 67,7 @@ defmodule Tds.Protocol do
66
67
end
67
68
end
68
69
70
@impl DBConnection
69
71
def disconnect(_err, %{sock: {mod, sock}} = s) do
70
72
# If socket is active we flush any socket messages so the next
71
73
# socket does not get the messages.
 
@@ -73,6 75,7 @@ defmodule Tds.Protocol do
73
75
mod.close(sock)
74
76
end
75
77
78
@impl DBConnection
76
79
@spec ping(any) :: {:ok, any} | {:disconnect, Exception.t(), any}
77
80
def ping(state) do
78
81
case send_query(~s{SELECT 'pong' as [msg]}, state) do
 
@@ -97,18 100,21 @@ defmodule Tds.Protocol do
97
100
end
98
101
end
99
102
103
@impl DBConnection
100
104
def checkout(%{sock: {_mod, sock}} = s) do
101
105
:ok = :inet.setopts(sock, active: false)
102
106
103
107
{:ok, s}
104
108
end
105
109
110
@impl DBConnection
106
111
def checkin(%{sock: {_mod, sock}} = s) do
107
112
:ok = :inet.setopts(sock, active: :once)
108
113
109
114
{:ok, s}
110
115
end
111
116
117
@impl DBConnection
112
118
def handle_execute(
113
119
%Query{handle: handle, statement: statement} = query,
114
120
params,
 
@@ -134,20 140,36 @@ defmodule Tds.Protocol do
134
140
end
135
141
end
136
142
137
- def handle_prepare(%{statement: statement}, opts, %{sock: _sock} = s) do
138
- params =
139
- opts[:parameters]
140
- |> Parameter.prepared_params()
143
@impl DBConnection
144
def handle_prepare(%{statement: statement} = query, opts, %{sock: _sock} = s) do
145
case Keyword.get(opts, :execution_mode, :prepare_execute) do
146
:prepare_execute ->
147
params =
148
opts[:parameters]
149
|> Parameter.prepared_params()
141
150
142
- send_prepare(statement, params, s)
151
send_prepare(statement, params, s)
152
153
:executesql ->
154
{:ok, query, %{s | state: :ready}}
155
156
execution_mode ->
157
message =
158
"Unknown execution mode #{inspect(execution_mode)}, please check your config." <>
159
"Supported modes are :prepare_execute and :executesql"
160
161
{:error, %Tds.Error{message: message}, s}
162
end
143
163
end
144
164
165
@impl DBConnection
145
166
def handle_close(query, opts, %{sock: _sock} = s) do
146
167
params = opts[:parameters]
147
168
148
169
send_close(query, params, s)
149
170
end
150
171
172
@impl DBConnection
151
173
def handle_begin(opts, %{sock: _, env: env} = s) do
152
174
case Keyword.get(opts, :mode, :transaction) do
153
175
:transaction ->
 
@@ -161,6 183,7 @@ defmodule Tds.Protocol do
161
183
end
162
184
end
163
185
186
@impl DBConnection
164
187
def handle_commit(opts, %{transaction: transaction} = s) do
165
188
case Keyword.get(opts, :mode, :transaction) do
166
189
:transaction when transaction == :failed ->
 
@@ -179,6 202,7 @@ defmodule Tds.Protocol do
179
202
end
180
203
end
181
204
205
@impl DBConnection
182
206
def handle_rollback(opts, %{sock: _sock, env: env} = s) do
183
207
case Keyword.get(opts, :mode, :transaction) do
184
208
:transaction ->
 
@@ -194,18 218,22 @@ defmodule Tds.Protocol do
194
218
end
195
219
end
196
220
221
@impl DBConnection
197
222
def handle_first(_query, _cursor, _opts, state) do
198
223
{:error, RuntimeError.exception("Not supported yet."), state}
199
224
end
200
225
226
@impl DBConnection
201
227
def handle_deallocate(_query, _cursor, _opts, state) do
202
228
{:error, RuntimeError.exception("Not supported yet."), state}
203
229
end
204
230
231
@impl DBConnection
205
232
def handle_declare(_query, _params, _opts, state) do
206
233
{:error, RuntimeError.exception("Not supported yet."), state}
207
234
end
208
235
236
@impl DBConnection
209
237
def handle_next(_query, _cursor, _opts, state) do
210
238
{:error, RuntimeError.exception("Not supported yet."), state}
211
239
end
 
@@ -303,6 331,7 @@ defmodule Tds.Protocol do
303
331
end
304
332
end
305
333
334
@impl true
306
335
def handle_info({:udp_error, _, :econnreset}, _s) do
307
336
raise "Tds encountered an error while connecting to the Sql Server " <>
308
337
"Browser: econnreset"
changed lib/tds/query.ex
 
@@ -1,72 1,61 @@
1
1
defmodule Tds.Query do
2
@moduledoc false
3
defstruct [:statement, :handle]
4
end
5
6
defimpl DBConnection.Query, for: Tds.Query do
2
7
alias Tds.Parameter
3
8
alias Tds.Types
9
alias Tds.Query
10
alias Tds.Result
4
11
5
- defstruct [:statement, :handle]
6
-
7
- defimpl DBConnection.Query, for: Tds.Query do
8
- def encode(_statement, [], _opts) do
9
- []
10
- end
11
-
12
- def encode(
13
- %Tds.Query{
14
- statement: statement,
15
- handle: handle
16
- } = _,
17
- params,
18
- _opts
19
- ) do
20
- case handle do
21
- nil ->
22
- param_desc =
23
- params
24
- |> Enum.map(fn %Parameter{} = param ->
25
- Types.encode_param_descriptor(param)
26
- end)
27
-
28
- param_desc =
29
- param_desc
30
- |> Enum.join(", ")
31
-
32
- [
33
- %Parameter{value: statement, type: :string},
34
- %Parameter{value: param_desc, type: :string}
35
- ] params
36
-
37
- _ ->
38
- params
39
- end
40
- end
41
-
42
- def decode(_query, result, opts) do
43
- mapper = opts[:decode_mapper] || fn x -> x end
44
- %Tds.Result{rows: rows} = result
45
- rows = do_decode(rows, mapper, [])
46
- %Tds.Result{result | rows: rows}
47
- end
48
-
49
- def do_decode([row | rows], mapper, decoded) do
50
- decoded = [mapper.(row) | decoded]
51
- do_decode(rows, mapper, decoded)
52
- end
53
-
54
- def do_decode(_, _, decoded) do
55
- decoded
56
- end
57
-
58
- def parse(params, _) do
59
- params
60
- end
61
-
62
- def describe(query, _) do
63
- query
64
- end
12
def encode(_statement, [], _opts) do
13
[]
65
14
end
66
15
67
- defimpl String.Chars, for: Tds.Query do
68
- def to_string(%Tds.Query{statement: statement}) do
69
- IO.iodata_to_binary(statement)
70
- end
16
def encode(%Query{handle: nil}, params, _) do
17
params
18
end
19
20
def encode(%Query{statement: statement}, params, _) do
21
param_desc =
22
params
23
|> Enum.map(&Types.encode_param_descriptor/1)
24
|> Enum.join(", ")
25
26
[
27
%Parameter{value: statement, type: :string},
28
%Parameter{value: param_desc, type: :string}
29
] params
30
end
31
32
def decode(_query, %Result{rows: rows} = result, opts) do
33
mapper = opts[:decode_mapper] || fn x -> x end
34
rows = do_decode(rows, mapper, [])
35
%Result{result | rows: rows}
36
end
37
38
def do_decode([row | rows], mapper, decoded) do
39
decoded = [mapper.(row) | decoded]
40
do_decode(rows, mapper, decoded)
41
end
42
43
def do_decode(_, _, decoded) do
44
decoded
45
end
46
47
def parse(params, _) do
48
params
49
end
50
51
def describe(query, _) do
52
query
53
end
54
end
55
56
defimpl String.Chars, for: Tds.Query do
57
@spec to_string(Tds.Query.t()) :: String.t()
58
def to_string(%Tds.Query{statement: statement}) do
59
IO.iodata_to_binary(statement)
71
60
end
72
61
end
changed lib/tds/tokens.ex
 
@@ -39,6 39,8 @@ defmodule Tds.Tokens do
39
39
@tds_envtype_begintrans 8
40
40
@tds_envtype_committrans 9
41
41
@tds_envtype_rollbacktrans 10
42
@tds_envtype_defecttrans 12
43
@tds_envtype_mirroring_partner 13
42
44
@tds_envtype_routing 20
43
45
44
46
## Decode Token Stream
 
@@ -225,6 227,7 @@ defmodule Tds.Tokens do
225
227
tokens
226
228
) do
227
229
case env_type do
230
# 0x01
228
231
@tds_envtype_database ->
229
232
<<
230
233
new_value_size::unsigned-8,
 
@@ -240,6 243,13 @@ defmodule Tds.Tokens do
240
243
241
244
{tokens |> Keyword.put(:database, new_value), rest}
242
245
246
# 0x02
247
# @tds_envtype_language ->
248
249
# 0x03
250
# @tds_envtype_characterset ->
251
252
# 0x04
243
253
@tds_envtype_packetsize ->
244
254
<<
245
255
new_value_size::unsigned-8,
 
@@ -253,7 263,7 @@ defmodule Tds.Tokens do
253
263
if(new_value != old_value) do
254
264
Logger.debug(fn ->
255
265
"""
256
- Database server configured packetsize to #{new_value} where old value
266
Database server configured TDS packetsize to #{new_value} where old value
257
267
was #{old_value}
258
268
"""
259
269
end)
 
@@ -261,6 271,16 @@ defmodule Tds.Tokens do
261
271
262
272
{tokens |> Keyword.put(:packetsize, new_value), rest}
263
273
274
# 0x05
275
# @tds_envtype_unicode_data_storing_local_id ->
276
277
# 0x06
278
# @tds_envtype_uncode_data_string_comparison_flag ->
279
280
# 0x07
281
# @tds_envtype_sql_collation ->
282
283
# 0x08
264
284
@tds_envtype_begintrans ->
265
285
<<
266
286
value_size::unsigned-8,
 
@@ -271,6 291,7 @@ defmodule Tds.Tokens do
271
291
272
292
{tokens |> Keyword.put(:trans, new_value), rest}
273
293
294
# 0x09
274
295
@tds_envtype_committrans ->
275
296
<<
276
297
0x00,
 
@@ -281,6 302,7 @@ defmodule Tds.Tokens do
281
302
282
303
{tokens |> Keyword.put(:trans, <<0x00>>), rest}
283
304
305
# 0x0A
284
306
@tds_envtype_rollbacktrans ->
285
307
<<
286
308
0x00,
 
@@ -291,6 313,31 @@ defmodule Tds.Tokens do
291
313
292
314
{tokens |> Keyword.put(:trans, <<0x00>>), rest}
293
315
316
# 0x0B
317
# @tds_envtype_enlist_dtc_transaction ->
318
319
# 0x0C
320
@tds_envtype_defecttrans ->
321
<<
322
value_size::unsigned-8,
323
new_value::binary-little-size(value_size)-unit(8),
324
0x00,
325
rest::binary
326
>> = tail
327
Logger.warn("Defect transaction env change received #{inspect(new_value)}")
328
{tokens, rest}
329
330
# 0x0D
331
@tds_envtype_mirroring_partner ->
332
<<
333
0x00,
334
new_value_size::unsigned-8,
335
_new_value::binary(new_value_size, 16),
336
rest::binary
337
>> = tail
338
339
{tokens, rest}
340
294
341
@tds_envtype_routing ->
295
342
<<
296
343
_routing_data_len::little-uint16,
changed lib/tds/types.ex
 
@@ -265,7 265,7 @@ defmodule Tds.Types do
265
265
266
266
col_info =
267
267
def_col_info
268
- |> Map.put(:collation, collation)
268
|> Map.put(:collation, decode_collation(collation))
269
269
|> Map.put(
270
270
:data_reader,
271
271
if(length == 0xFFFF, do: :plp, else: :shortlen)
 
@@ -352,6 352,16 @@ defmodule Tds.Types do
352
352
end
353
353
end
354
354
355
def decode_collation(<<lcid::size(20), colFlags::size(6), _::size(2),
356
version::size(4), sortId::size(8)>>) do
357
%{
358
lcid: lcid,
359
col_flags: colFlags,
360
version: version,
361
sort_id: sortId
362
}
363
end
364
355
365
#
356
366
# Data Decoders
357
367
#
 
@@ -427,8 437,6 @@ defmodule Tds.Types do
427
437
decode_uuid(data)
428
438
429
439
data_type_code == @tds_data_type_intn ->
430
- data = data <> tail
431
-
432
440
case length do
433
441
1 ->
434
442
<<val::unsigned-8, _tail::binary>> = data
 
@@ -518,7 526,7 @@ defmodule Tds.Types do
518
526
@tds_data_type_nvarchar,
519
527
@tds_data_type_nchar
520
528
] ->
521
- decode_nchar(data)
529
decode_nchar(data_info, data)
522
530
523
531
data_type_code == @tds_data_type_udt ->
524
532
decode_udt(data_info, data)
 
@@ -543,8 551,8 @@ defmodule Tds.Types do
543
551
) do
544
552
value =
545
553
case data_type_code do
546
- @tds_data_type_text -> decode_char(data_info[:collation], data)
547
- @tds_data_type_ntext -> decode_nchar(data)
554
@tds_data_type_text -> decode_char(data_info, data)
555
@tds_data_type_ntext -> decode_nchar(data_info, data)
548
556
@tds_data_type_image -> data
549
557
_ -> nil
550
558
end
 
@@ -590,7 598,7 @@ defmodule Tds.Types do
590
598
@tds_data_type_nchar,
591
599
@tds_data_type_ntext
592
600
] ->
593
- decode_nchar(data)
601
decode_nchar(data_info, data)
863
602
595
603
data_type_code == @tds_data_type_udt ->
596
604
decode_udt(data_info, data)
 
@@ -622,7 630,7 @@ defmodule Tds.Types do
622
630
money_m::little-unsigned-32,
623
631
money_l::little-unsigned-32
624
632
>>) do
625
- <<money::signed-64>> = <<money_m::32>> <> <<money_l::32>>
633
<<money::signed-64>> = <<money_m::32, money_l::32>>
626
634
Float.round(money * 0.0001, 4)
627
635
end
628
636
 
@@ -651,12 659,12 @@ defmodule Tds.Types do
651
659
end
652
660
end
653
661
654
- def decode_char(_collation, <<data::binary>>) do
662
def decode_char(_data_info, <<data::binary>>) do
655
663
data
656
664
end
657
665
658
- def decode_nchar(<<data::binary>>) do
659
- data |> :unicode.characters_to_binary({:utf16, :little}, :utf8)
666
def decode_nchar(_data_info, <<data::binary>>) do
667
:unicode.characters_to_binary(data, {:utf16, :little}, :utf8)
660
668
end
661
669
662
670
def decode_xml(_data_info, <<_data::binary>>) do
changed lib/tds/types/uuid.ex
 
@@ -26,7 26,9 @@ defmodule Tds.Types.UUID do
26
26
else
27
27
casted -> {:ok, casted}
28
28
end
29
29
30
def cast(<< _::128 >> = binary), do: encode(binary)
31
30
32
def cast(_), do: :error
31
33
32
34
@doc """
 
@@ -89,6 91,7 @@ defmodule Tds.Types.UUID do
89
91
{:ok, binary}
90
92
end
91
93
end
94
92
95
def dump(_), do: :error
93
96
94
97
def dump!(value) do
 
@@ -130,10 133,12 @@ defmodule Tds.Types.UUID do
130
133
def load(<<_::128>> = uuid) do
131
134
encode(uuid)
132
135
end
136
133
137
def load(<<_::64, ?-, _::32, ?-, _::32, ?-, _::32, ?-, _::96>> = string) do
134
138
raise ArgumentError, "trying to load string UUID as Tds.Types.UUID: #{inspect string}. " <>
135
139
"Maybe you wanted to declare :uuid as your database field?"
136
140
end
141
137
142
def load(_), do: :error
138
143
139
144
@doc """
changed mix.exs
 
@@ -5,7 5,7 @@ defmodule Tds.Mixfile do
5
5
def project do
6
6
[
7
7
app: :tds,
8
- version: "1.1.4",
8
version: "1.1.5",
9
9
elixir: "~> 1.0",
10
10
deps: deps(),
11
11
test_coverage: [tool: ExCoveralls],