Microsoft SQL server driver written in go language
Go
Latest commit 370263e Jan 11, 2017 @joliver joliver committed with kardianos sum row counts for RowsAffected
Sum the number of rows affected. Prior behavior was incorrect as it only counted the last message received.
Permalink
Failed to load latest commit information.
examples declared main func 2 times in same package Aug 2, 2016
LICENSE.txt added go license Apr 11, 2014
README.md mssql: add new driver type w/o pre-processing query Jan 7, 2017
buf.go Cancelable queries (#190) Dec 6, 2016
buf_test.go Added buf writing unit-tests Nov 24, 2016
charset.go derive code page from sortid May 5, 2014
collation.go formatting Apr 16, 2014
cp1250.go double byte character convertion May 4, 2014
cp1251.go double byte character convertion May 4, 2014
cp1252.go double byte character convertion May 4, 2014
cp1253.go double byte character convertion May 4, 2014
cp1254.go double byte character convertion May 4, 2014
cp1255.go double byte character convertion May 4, 2014
cp1256.go double byte character convertion May 4, 2014
cp1257.go double byte character convertion May 4, 2014
cp1258.go double byte character convertion May 4, 2014
cp437.go derive code page from sort order May 4, 2014
cp850.go derive code page from sort order May 4, 2014
cp874.go double byte character convertion May 4, 2014
cp932.go double byte character convertion May 4, 2014
cp936.go double byte character convertion May 4, 2014
cp949.go double byte character convertion May 4, 2014
cp950.go double byte character convertion May 4, 2014
decimal.go support money type May 2, 2014
decimal_test.go increase test coverage for decimal.go Nov 25, 2016
doc.go mssql: add new driver type w/o pre-processing query Jan 7, 2017
error.go Add SQLError... methods to Error type Nov 26, 2016
error_example_test.go Add SQLError... methods to Error type Nov 26, 2016
log.go Use interface for logging Dec 4, 2016
mssql.go sum row counts for RowsAffected Jan 11, 2017
mssql_go1.3.go support pluggable dialers for testing, update token response (#208) Jan 11, 2017
mssql_go1.3pre.go support pluggable dialers for testing, update token response (#208) Jan 11, 2017
mssql_go18.go mssql: update to latest go1.8 BeginTx Dec 16, 2016
mssql_test.go mssql: add new driver type w/o pre-processing query Jan 7, 2017
net.go Cancelable queries (#190) Dec 6, 2016
ntlm.go Use new import path for Go crypto sub repository Nov 10, 2014
ntlm_test.go support extended session security Aug 17, 2014
parser.go Implemented named parameters Dec 4, 2016
parser_test.go Implemented named parameters Dec 4, 2016
queries_go18_test.go support pluggable dialers for testing, update token response (#208) Jan 11, 2017
queries_test.go support pluggable dialers for testing, update token response (#208) Jan 11, 2017
rpc.go formatting Apr 16, 2014
sspi_windows.go fixes #62 64-bit windows heap corruption Sep 23, 2014
tds.go support pluggable dialers for testing, update token response (#208) Jan 11, 2017
tds_test.go mssql: add new driver type w/o pre-processing query Jan 7, 2017
token.go support pluggable dialers for testing, update token response (#208) Jan 11, 2017
tran.go mssql: add new driver type w/o pre-processing query Jan 7, 2017
types.go add UniqueIdentifier type (#203) Jan 6, 2017
uniqueidentifier.go add UniqueIdentifier type (#203) Jan 6, 2017
uniqueidentifier_test.go add UniqueIdentifier type (#203) Jan 6, 2017

README.md

A pure Go MSSQL driver for Go's database/sql package

Install

go get github.com/denisenkom/go-mssqldb

Connection Parameters and DSN

  • "server" - host or host\instance (default localhost)
  • "port" - used only when there is no instance in server (default 1433)
  • "failoverpartner" - host or host\instance (default is no partner).
  • "failoverport" - used only when there is no instance in failoverpartner (default 1433)
  • "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.
  • "password"
  • "database"
  • "connection timeout" - in seconds (default is 30)
  • "dial timeout" - in seconds (default is 5)
  • "keepAlive" - in seconds; 0 to disable (default is 0)
  • "log" - logging flags (default 0/no logging, 63 for full logging)
    • 1 log errors
    • 2 log messages
    • 4 log rows affected
    • 8 trace sql statements
    • 16 log statement parameters
    • 32 log transaction begin/end
  • "encrypt"
    • disable - Data send between client and server is not encrypted.
    • false - Data sent between client and server is not encrypted beyond the login packet. (Default)
    • true - Data sent between client and server is encrypted.
  • "TrustServerCertificate"
    • false - Server certificate is checked. Default is false if encypt is specified.
    • 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.
  • "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.
  • "hostNameInCertificate" - Specifies the Common Name (CN) in the server certificate. Default value is the server host.
  • "ServerSPN" - The kerberos SPN (Service Principal Name) for the server. Default is MSSQLSvc/host:port.
  • "Workstation ID" - The workstation name (default is the host name)
  • "app name" - The application name (default is go-mssqldb)
  • "ApplicationIntent" - Can be given the value "ReadOnly" to initiate a read-only connection to an Availability Group listener.

The connection string can be specified in one of three formats:

  1. ADO: key=value pairs separated by ;. Values may not contain ;, leading and trailing whitespace is ignored. Examples:

    • server=localhost\\SQLExpress;user id=sa;database=master;connection timeout=30
    • server=localhost;user id=sa;database=master;connection timeout=30
  2. ODBC: Prefix with odbc, key=value pairs separated by ;. Allow ; by wrapping values in {}. Examples:

    • odbc:server=localhost\\SQLExpress;user id=sa;database=master;connection timeout=30
    • odbc:server=localhost;user id=sa;database=master;connection timeout=30
    • odbc:server=localhost;user id=sa;password={foo;bar} // Value marked with {}, password is "foo;bar"
    • odbc:server=localhost;user id=sa;password={foo{bar} // Value marked with {}, password is "foo{bar"
    • odbc:server=localhost;user id=sa;password={foobar } // Value marked with {}, password is "foobar "
    • odbc:server=localhost;user id=sa;password=foo{bar // Literal {, password is "foo{bar"
    • odbc:server=localhost;user id=sa;password=foo}bar // Literal }, password is "foo}bar"
    • odbc:server=localhost;user id=sa;password={foo{bar} // Literal {, password is "foo{bar"
    • odbc:server=localhost;user id=sa;password={foo}}bar} // Escaped } with}}`, password is "foo}bar"
  3. URL: with sqlserver scheme. username and password appears before the host. Any instance appears as the first segment in the path. All other options are query parameters. Examples:

    • sqlserver://username:password@host/instance?param1=value&param2=value
    • sqlserver://username:password@host:port?param1=value&param2=value
    • sqlserver://sa@localhost/SQLExpress?database=master&connection+timeout=30 // `SQLExpress instance.
    • sqlserver://sa:mypass@localhost?database=master&connection+timeout=30 // username=sa, password=mypass.
    • sqlserver://sa:mypass@localhost:1234?database=master&connection+timeout=30" // port 1234 on localhost.
    • sqlserver://sa:my%7Bpass@somehost?connection+timeout=30 // password is "my{pass"

    A string of this format can be constructed using the URL type in the net/url package.

    query := url.Values{}
    query.Add("connection timeout", fmt.Sprintf("%d", connectionTimeout))
    
    u := &url.URL{
        Scheme:   "sqlserver",
        User:     url.UserPassword(username, password),
        Host:     fmt.Sprintf("%s:%d", hostname, port),
        // Path:  instance, // if connecting to an instance instead of a port
        RawQuery: query.Encode(),
    }
    
    connectionString := u.String()
    
    db, err := sql.Open("sqlserver", connectionString)
    // or
    db, err := sql.Open("mssql", connectionString)

Statement Parameters

The sqlserver driver uses normal MS SQL Server syntax and expects parameters in the sql query to be in the form of either @Name or @p1 to @pN (ordinal position).

db.QueryContext(ctx, `select * from t where ID = @ID;`, sql.Named("ID", 6))

For the mssql driver, the SQL statement text will be processed and literals will be replaced by a parameter that matches one of the following:

  • ?
  • ?nnn
  • :nnn
  • $nnn

where nnn represents an integer that specifies a 1-indexed positional parameter. Ex:

db.Query("SELECT * FROM t WHERE a = ?3, b = ?2, c = ?1", "x", "y", "z")

will expand to roughly

SELECT * FROM t WHERE a = 'z', b = 'y', c = 'x'

Features

  • Can be used with SQL Server 2005 or newer
  • Can be used with Microsoft Azure SQL Database
  • Can be used on all go supported platforms (e.g. Linux, Mac OS X and Windows)
  • Supports new date/time types: date, time, datetime2, datetimeoffset
  • Supports string parameters longer than 8000 characters
  • Supports encryption using SSL/TLS
  • Supports SQL Server and Windows Authentication
  • Supports Single-Sign-On on Windows
  • Supports connections to AlwaysOn Availability Group listeners, including re-direction to read-only replicas.
  • Supports query notifications

Tests

go test is used for testing. A running instance of MSSQL server is required. Environment variables are used to pass login information.

Example:

env HOST=localhost SQLUSER=sa SQLPASSWORD=sa DATABASE=test go test

Known Issues

  • SQL Server 2008 and 2008 R2 engine cannot handle login records when SSL encryption is not disabled. To fix SQL Server 2008 R2 issue, install SQL Server 2008 R2 Service Pack 2. To fix SQL Server 2008 issue, install Microsoft SQL Server 2008 Service Pack 3 and Cumulative update package 3 for SQL Server 2008 SP3. More information: http://support.microsoft.com/kb/2653857