Repository files navigation

MSSQL / TDS Adapter for Ecto2

# In your config/config.exs file add MSSQL connection options# By default it use port 1433, different port can be specified with# port: 1434# option and named instance with# instance: "instance_name"config:my_app,Repo,database:"ecto_simple",username:"mssql",password:"mssql",hostname:"localhost"# In your application codedefmoduleRepodouseEcto.Repo,otp_app::my_app,adapter:Tds.EctoenddefmoduleWeatherdouseEcto.Schemaschema"weather"dofield:city# Defaults to type :stringfield:temp_lo,:integerfield:temp_hi,:integerfield:prcp,:float,default:0.0endenddefmoduleSimpledoimportEcto.Querydefsample_querydoquery=fromwinWeather,where:w.prcp>0oris_nil(w.prcp),select:wRepo.all(query)endend

Also, if you need to map table to schema other than [dbo], simple use @schema_prefix "your_schema", eg:

defmodule Invoices.Invoice do  use Ecto.Schema  @schema_prefix :invoices  schema "invoices" do    field :due_date, :datetime    field :sum, :float  endend

Add Tds as a dependency in yourmix.exs file.

defdepsdo[{:tds_ecto,"~> 2.2"}]end

You should also update your applications list to include both projects:

defapplicationdo[applications:[:logger,:tds_ecto,:ecto]]end

To use the adapter in your repo:

defmoduleMyApp.RepodouseEcto.Repo,otp_app::my_app,adapter:Tds.Ectoend

tds_ecto relies on pattern matching against error messages to extract constraint names.If your SQL server is not configured to English as its default language you may add anafter_connect hook to your repo and set English as the language for Ecto connections.

defafter_connect(conn)doTds.Ecto.Connection.query(conn,"SET LANGUAGE English",[],[])end

For additional information on usage please see the documentation forEcto.

MSSQL             Ecto/Tds.Ecto----------        ------nvarchar            :stringvarchar             Tds.VarCharchar                :binaryvarbinary           :binaryfloat               :floatdecimal             :decimalinteger             :integerbit                 :booleanuniqueidentifier    :uuiddatetime            :datetimedate                :datetime                :time

To contribute you need to compile Tds from source and test it:

$ git clone https://github.com/livehelpnow/tds_ecto.git$ cd tds_ecto$ mix test

Tests will try to connect tolocalhost usingsa as username with predefined passwordmssql, but you can set environment variables to override any, like so:SQL_USERNAME=myuser; SQL_PASSWORD=mypassword; SQL_HOSTNAME=sqlserver.local; mix test or if you are using windows thenSET SQL_USERNAME=myuser && SET SQL_PASSWORD=mypassword && SET SQL_HOSTNAME=sqlserver.local && mix test. Please note that tests will run againes default sql server instance.

Additionally SQL authentication needs to be used for connecting and testing.If you override default test settings, make sure either that yor user has sysadmin privilegies to add the usertest_user with access to the databasetest_db, or simply add manualy test_user on your server. See one of the test files for the connection information and port number.

Copyright 2014, 2015, 2016, 2017 LiveHelpNow

Licensed under the Apache License, Version 2.0 (the "License");you may not use this file except in compliance with the License.You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, softwaredistributed under the License is distributed on an "AS IS" BASIS,WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.See the License for the specific language governing permissions andlimitations under the License.