• Home
  • History
  • Annotate
Name Date Size #Lines LOC

..03-May-2022-

.github/ISSUE_TEMPLATE/H28-Apr-2020-6747

batch/H28-Apr-2020-422365

doc/H28-Apr-2020-270206

examples/H28-Apr-2020-676566

internal/H28-Apr-2020-65,18165,048

README.mdH A D28-Apr-202013.1 KiB310241

accesstokenconnector.goH A D28-Apr-20201.3 KiB5234

accesstokenconnector_test.goH A D28-Apr-20202.4 KiB9387

appveyor.ymlH A D28-Apr-20201.1 KiB5142

buf.goH A D28-Apr-20205.2 KiB263212

buf_test.goH A D28-Apr-20208.4 KiB317265

bulkcopy.goH A D28-Apr-202014.2 KiB584491

bulkcopy_sql.goH A D28-Apr-20201.7 KiB9472

bulkcopy_test.goH A D28-Apr-20208.1 KiB267235

bulkimport_example_test.goH A D28-Apr-20202.5 KiB11796

conn_str.goH A D28-Apr-202011.3 KiB472384

conn_str_test.goH A D28-Apr-20207.9 KiB169153

convert.goH A D28-Apr-20207 KiB307268

datetimeoffset_example_test.goH A D28-Apr-20204 KiB122108

doc.goH A D28-Apr-2020643 151

error.goH A D28-Apr-20201.3 KiB7452

error_example_test.goH A D28-Apr-2020722 3928

go.modH A D28-Apr-2020182 96

go.sumH A D28-Apr-2020549 65

lastinsertid_example_test.goH A D28-Apr-20201.9 KiB7254

log.goH A D28-Apr-2020473 3125

mssql.goH A D28-Apr-202023.6 KiB963763

mssql_go110.goH A D28-Apr-20201.1 KiB5339

mssql_go110pre.goH A D28-Apr-2020565 3227

mssql_go19.goH A D28-Apr-20205.2 KiB197163

mssql_go19pre.goH A D28-Apr-2020357 1711

mssql_test.goH A D28-Apr-2020676 4035

net.goH A D28-Apr-20203.5 KiB169134

newconnector_example_test.goH A D28-Apr-20203.6 KiB144112

ntlm.goH A D28-Apr-202012.8 KiB405326

ntlm_test.goH A D28-Apr-20205 KiB127112

queries_go110_test.goH A D28-Apr-20206 KiB252221

queries_go110pre_test.goH A D28-Apr-20201.1 KiB5847

queries_go19_test.goH A D28-Apr-202032.6 KiB1,017857

queries_test.goH A D28-Apr-202057 KiB2,1391,872

rpc.goH A D28-Apr-20201.8 KiB9080

sspi_windows.goH A D28-Apr-20206.8 KiB267241

tds.goH A D28-Apr-202025.4 KiB1,038905

tds_go110_test.goH A D28-Apr-2020320 2015

tds_go110pre_test.goH A D28-Apr-2020307 1915

tds_test.goH A D28-Apr-202013 KiB537471

token.goH A D28-Apr-202020 KiB824676

token_string.goH A D28-Apr-20201.3 KiB5447

tran.goH A D28-Apr-20202.5 KiB11199

tvp_example_test.goH A D28-Apr-20202.6 KiB11594

tvp_go19.goH A D28-Apr-20206.2 KiB232204

tvp_go19_db_test.goH A D28-Apr-202018.4 KiB752683

tvp_go19_test.goH A D28-Apr-202010.3 KiB579556

types.goH A D28-Apr-202037.5 KiB1,5811,418

types_test.goH A D28-Apr-20204.2 KiB151132

uniqueidentifier.goH A D28-Apr-20201.7 KiB8162

uniqueidentifier_test.goH A D28-Apr-20202.1 KiB8068

README.md

1# A pure Go MSSQL driver for Go's database/sql package
2
3[![GoDoc](https://godoc.org/github.com/denisenkom/go-mssqldb?status.svg)](http://godoc.org/github.com/denisenkom/go-mssqldb)
4[![Build status](https://ci.appveyor.com/api/projects/status/jrln8cs62wj9i0a2?svg=true)](https://ci.appveyor.com/project/denisenkom/go-mssqldb)
5[![codecov](https://codecov.io/gh/denisenkom/go-mssqldb/branch/master/graph/badge.svg)](https://codecov.io/gh/denisenkom/go-mssqldb)
6
7## Install
8
9Requires Go 1.8 or above.
10
11Install with `go get github.com/denisenkom/go-mssqldb` .
12
13## Connection Parameters and DSN
14
15The recommended connection string uses a URL format:
16`sqlserver://username:password@host/instance?param1=value&param2=value`
17Other supported formats are listed below.
18
19### Common parameters:
20
21* `user id` - enter the SQL Server Authentication user id or the Windows Authentication user id in the DOMAIN\User format. On Windows, if user id is empty or missing Single-Sign-On is used. The user domain sensitive to the case which is defined in the connection string.
22* `password`
23* `database`
24* `connection timeout` - in seconds (default is 0 for no timeout), set to 0 for no timeout. Recommended to set to 0 and use context to manage query and connection timeouts.
25* `dial timeout` - in seconds (default is 15), set to 0 for no timeout
26* `encrypt`
27  * `disable` - Data send between client and server is not encrypted.
28  * `false` - Data sent between client and server is not encrypted beyond the login packet. (Default)
29  * `true` - Data sent between client and server is encrypted.
30* `app name` - The application name (default is go-mssqldb)
31
32### Connection parameters for ODBC and ADO style connection strings:
33
34* `server` - host or host\instance (default localhost)
35* `port` - used only when there is no instance in server (default 1433)
36
37### Less common parameters:
38
39* `keepAlive` - in seconds; 0 to disable (default is 30)
40* `failoverpartner` - host or host\instance (default is no partner).
41* `failoverport` - used only when there is no instance in failoverpartner (default 1433)
42* `packet size` - in bytes; 512 to 32767 (default is 4096)
43  * Encrypted connections have a maximum packet size of 16383 bytes
44  * Further information on usage: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-network-packet-size-server-configuration-option
45* `log` - logging flags (default 0/no logging, 63 for full logging)
46  *  1 log errors
47  *  2 log messages
48  *  4 log rows affected
49  *  8 trace sql statements
50  * 16 log statement parameters
51  * 32 log transaction begin/end
52* `TrustServerCertificate`
53  * false - Server certificate is checked. Default is false if encypt is specified.
54  * true - Server certificate is not checked. Default is true if encrypt is not specified. If trust server certificate is true, driver accepts any certificate presented by the server and any host name in that certificate. In this mode, TLS is susceptible to man-in-the-middle attacks. This should be used only for testing.
55* `certificate` - The file that contains the public key certificate of the CA that signed the SQL Server certificate. The specified certificate overrides the go platform specific CA certificates.
56* `hostNameInCertificate` - Specifies the Common Name (CN) in the server certificate. Default value is the server host.
57* `ServerSPN` - The kerberos SPN (Service Principal Name) for the server. Default is MSSQLSvc/host:port.
58* `Workstation ID` - The workstation name (default is the host name)
59* `ApplicationIntent` - Can be given the value `ReadOnly` to initiate a read-only connection to an Availability Group listener. The `database` must be specified when connecting with `Application Intent` set to `ReadOnly`.
60
61### The connection string can be specified in one of three formats:
62
63
641. URL: with `sqlserver` scheme. username and password appears before the host. Any instance appears as
65    the first segment in the path. All other options are query parameters. Examples:
66
67  * `sqlserver://username:password@host/instance?param1=value&param2=value`
68  * `sqlserver://username:password@host:port?param1=value&param2=value`
69  * `sqlserver://sa@localhost/SQLExpress?database=master&connection+timeout=30` // `SQLExpress instance.
70  * `sqlserver://sa:mypass@localhost?database=master&connection+timeout=30`     // username=sa, password=mypass.
71  * `sqlserver://sa:mypass@localhost:1234?database=master&connection+timeout=30` // port 1234 on localhost.
72  * `sqlserver://sa:my%7Bpass@somehost?connection+timeout=30` // password is "my{pass"
73
74  A string of this format can be constructed using the `URL` type in the `net/url` package.
75
76```go
77  query := url.Values{}
78  query.Add("app name", "MyAppName")
79
80  u := &url.URL{
81      Scheme:   "sqlserver",
82      User:     url.UserPassword(username, password),
83      Host:     fmt.Sprintf("%s:%d", hostname, port),
84      // Path:  instance, // if connecting to an instance instead of a port
85      RawQuery: query.Encode(),
86  }
87  db, err := sql.Open("sqlserver", u.String())
88```
89
902. ADO: `key=value` pairs separated by `;`. Values may not contain `;`, leading and trailing whitespace is ignored.
91     Examples:
92
93  * `server=localhost\\SQLExpress;user id=sa;database=master;app name=MyAppName`
94  * `server=localhost;user id=sa;database=master;app name=MyAppName`
95
963. ODBC: Prefix with `odbc`, `key=value` pairs separated by `;`. Allow `;` by wrapping
97    values in `{}`. Examples:
98
99  * `odbc:server=localhost\\SQLExpress;user id=sa;database=master;app name=MyAppName`
100  * `odbc:server=localhost;user id=sa;database=master;app name=MyAppName`
101  * `odbc:server=localhost;user id=sa;password={foo;bar}` // Value marked with `{}`, password is "foo;bar"
102  * `odbc:server=localhost;user id=sa;password={foo{bar}` // Value marked with `{}`, password is "foo{bar"
103  * `odbc:server=localhost;user id=sa;password={foobar }` // Value marked with `{}`, password is "foobar "
104  * `odbc:server=localhost;user id=sa;password=foo{bar`   // Literal `{`, password is "foo{bar"
105  * `odbc:server=localhost;user id=sa;password=foo}bar`   // Literal `}`, password is "foo}bar"
106  * `odbc:server=localhost;user id=sa;password={foo{bar}` // Literal `{`, password is "foo{bar"
107  * `odbc:server=localhost;user id=sa;password={foo}}bar}` // Escaped `} with `}}`, password is "foo}bar"
108
109### Azure Active Directory authentication - preview
110
111The configuration of functionality might change in the future.
112
113Azure Active Directory (AAD) access tokens are relatively short lived and need to be
114valid when a new connection is made. Authentication is supported using a callback func that
115provides a fresh and valid token using a connector:
116``` golang
117conn, err := mssql.NewAccessTokenConnector(
118  "Server=test.database.windows.net;Database=testdb",
119  tokenProvider)
120if err != nil {
121	// handle errors in DSN
122}
123db := sql.OpenDB(conn)
124```
125Where `tokenProvider` is a function that returns a fresh access token or an error. None of these statements
126actually trigger the retrieval of a token, this happens when the first statment is issued and a connection
127is created.
128
129## Executing Stored Procedures
130
131To run a stored procedure, set the query text to the procedure name:
132```go
133var account = "abc"
134_, err := db.ExecContext(ctx, "sp_RunMe",
135	sql.Named("ID", 123),
136	sql.Named("Account", sql.Out{Dest: &account}),
137)
138```
139
140## Reading Output Parameters from a Stored Procedure with Resultset
141
142To read output parameters from a stored procedure with resultset, make sure you read all the rows before reading the output parameters:
143```go
144sqltextcreate := `
145CREATE PROCEDURE spwithoutputandrows
146	@bitparam BIT OUTPUT
147AS BEGIN
148	SET @bitparam = 1
149	SELECT 'Row 1'
150END
151`
152var bitout int64
153rows, err := db.QueryContext(ctx, "spwithoutputandrows", sql.Named("bitparam", sql.Out{Dest: &bitout}))
154var strrow string
155for rows.Next() {
156	err = rows.Scan(&strrow)
157}
158fmt.Printf("bitparam is %d", bitout)
159```
160
161## Caveat for local temporary tables
162
163Due to protocol limitations, temporary tables will only be allocated on the connection
164as a result of executing a query with zero parameters. The following query
165will, due to the use of a parameter, execute in its own session,
166and `#mytemp` will be de-allocated right away:
167
168```go
169conn, err := pool.Conn(ctx)
170defer conn.Close()
171_, err := conn.ExecContext(ctx, "select @p1 as x into #mytemp", 1)
172// at this point #mytemp is already dropped again as the session of the ExecContext is over
173```
174
175To work around this, always explicitly create the local temporary
176table in a query without any parameters. As a special case, the driver
177will then be able to execute the query directly on the
178connection-scoped session. The following example works:
179
180```go
181conn, err := pool.Conn(ctx)
182
183// Set us up so that temp table is always cleaned up, since conn.Close()
184// merely returns conn to pool, rather than actually closing the connection.
185defer func() {
186	_, _ = conn.ExecContext(ctx, "drop table #mytemp")  // always clean up
187	conn.Close() // merely returns conn to pool
188}()
189
190
191// Since we not pass any parameters below, the query will execute on the scope of
192// the connection and succeed in creating the table.
193_, err := conn.ExecContext(ctx, "create table #mytemp ( x int )")
194
195// #mytemp is now available even if you pass parameters
196_, err := conn.ExecContext(ctx, "insert into #mytemp (x) values (@p1)", 1)
197
198```
199
200## Return Status
201
202To get the procedure return status, pass into the parameters a
203`*mssql.ReturnStatus`. For example:
204```
205var rs mssql.ReturnStatus
206_, err := db.ExecContext(ctx, "theproc", &rs)
207log.Printf("status=%d", rs)
208```
209
210or
211
212```
213var rs mssql.ReturnStatus
214_, err := db.QueryContext(ctx, "theproc", &rs)
215for rows.Next() {
216	err = rows.Scan(&val)
217}
218log.Printf("status=%d", rs)
219```
220
221Limitation: ReturnStatus cannot be retrieved using `QueryRow`.
222
223## Parameters
224
225The `sqlserver` driver uses normal MS SQL Server syntax and expects parameters in
226the sql query to be in the form of either `@Name` or `@p1` to `@pN` (ordinal position).
227
228```go
229db.QueryContext(ctx, `select * from t where ID = @ID and Name = @p2;`, sql.Named("ID", 6), "Bob")
230```
231
232### Parameter Types
233
234To pass specific types to the query parameters, say `varchar` or `date` types,
235you must convert the types to the type before passing in. The following types
236are supported:
237
238 * string -> nvarchar
239 * mssql.VarChar -> varchar
240 * time.Time -> datetimeoffset or datetime (TDS version dependent)
241 * mssql.DateTime1 -> datetime
242 * mssql.DateTimeOffset -> datetimeoffset
243 * "github.com/golang-sql/civil".Date -> date
244 * "github.com/golang-sql/civil".DateTime -> datetime2
245 * "github.com/golang-sql/civil".Time -> time
246 * mssql.TVP -> Table Value Parameter (TDS version dependent)
247
248## Important Notes
249
250 * [LastInsertId](https://golang.org/pkg/database/sql/#Result.LastInsertId) should
251    not be used with this driver (or SQL Server) due to how the TDS protocol
252	works. Please use the [OUTPUT Clause](https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql)
253	or add a `select ID = convert(bigint, SCOPE_IDENTITY());` to the end of your
254	query (ref [SCOPE_IDENTITY](https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql)).
255	This will ensure you are getting the correct ID and will prevent a network round trip.
256 * [NewConnector](https://godoc.org/github.com/denisenkom/go-mssqldb#NewConnector)
257    may be used with [OpenDB](https://golang.org/pkg/database/sql/#OpenDB).
258 * [Connector.SessionInitSQL](https://godoc.org/github.com/denisenkom/go-mssqldb#Connector.SessionInitSQL)
259	may be set to set any driver specific session settings after the session
260	has been reset. If empty the session will still be reset but use the database
261	defaults in Go1.10+.
262
263## Features
264
265* Can be used with SQL Server 2005 or newer
266* Can be used with Microsoft Azure SQL Database
267* Can be used on all go supported platforms (e.g. Linux, Mac OS X and Windows)
268* Supports new date/time types: date, time, datetime2, datetimeoffset
269* Supports string parameters longer than 8000 characters
270* Supports encryption using SSL/TLS
271* Supports SQL Server and Windows Authentication
272* Supports Single-Sign-On on Windows
273* Supports connections to AlwaysOn Availability Group listeners, including re-direction to read-only replicas.
274* Supports query notifications
275
276## Tests
277
278`go test` is used for testing. A running instance of MSSQL server is required.
279Environment variables are used to pass login information.
280
281Example:
282
283    env SQLSERVER_DSN=sqlserver://user:pass@hostname/instance?database=test1 go test
284
285## Deprecated
286
287These features still exist in the driver, but they are are deprecated.
288
289### Query Parameter Token Replace (driver "mssql")
290
291If you use the driver name "mssql" (rather then "sqlserver") the SQL text
292will be loosly parsed and an attempt to extract identifiers using one of
293
294* ?
295* ?nnn
296* :nnn
297* $nnn
298
299will be used. This is not recommended with SQL Server.
300There is at least one existing `won't fix` issue with the query parsing.
301
302Use the native "@Name" parameters instead with the "sqlserver" driver name.
303
304## Known Issues
305
306* SQL Server 2008 and 2008 R2 engine cannot handle login records when SSL encryption is not disabled.
307To fix SQL Server 2008 R2 issue, install SQL Server 2008 R2 Service Pack 2.
308To fix SQL Server 2008 issue, install Microsoft SQL Server 2008 Service Pack 3 and Cumulative update package 3 for SQL Server 2008 SP3.
309More information: http://support.microsoft.com/kb/2653857
310