mssql

package module
v0.0.0-...-2c65448 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Sep 13, 2017 License: BSD-3-Clause Imports: 32 Imported by: 0

README

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

GoDoc Build status codecov

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)
  • "packet size" - in bytes; 512 to 32767 (default is 4096)
  • "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
  1. 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"
  1. 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

Documentation

Overview

package mssql implements the TDS protocol used to connect to MS SQL Server (sqlserver) database servers.

This package registers two drivers: 

sqlserver: uses native "@" parameter placeholder names and does no pre-processing.
mssql: expects identifiers to be prefixed with ":" and pre-processes queries.

If the ordinal position is used for query parameters, identifiers will be named "@p1", "@p2", ... "@pN".

Please refer to the README for the format of the DSN.

Index

Constants

View Source 
const (
	NEGOTIATE_MESSAGE    = 1
	CHALLENGE_MESSAGE    = 2
	AUTHENTICATE_MESSAGE = 3
)
View Source 
const (
	NEGOTIATE_UNICODE                  = 0x00000001
	NEGOTIATE_OEM                      = 0x00000002
	NEGOTIATE_TARGET                   = 0x00000004
	NEGOTIATE_SIGN                     = 0x00000010
	NEGOTIATE_SEAL                     = 0x00000020
	NEGOTIATE_DATAGRAM                 = 0x00000040
	NEGOTIATE_LMKEY                    = 0x00000080
	NEGOTIATE_NTLM                     = 0x00000200
	NEGOTIATE_ANONYMOUS                = 0x00000800
	NEGOTIATE_OEM_DOMAIN_SUPPLIED      = 0x00001000
	NEGOTIATE_OEM_WORKSTATION_SUPPLIED = 0x00002000
	NEGOTIATE_ALWAYS_SIGN              = 0x00008000
	NEGOTIATE_TARGET_TYPE_DOMAIN       = 0x00010000
	NEGOTIATE_TARGET_TYPE_SERVER       = 0x00020000
	NEGOTIATE_EXTENDED_SESSIONSECURITY = 0x00080000
	NEGOTIATE_IDENTIFY                 = 0x00100000
	REQUEST_NON_NT_SESSION_KEY         = 0x00400000
	NEGOTIATE_TARGET_INFO              = 0x00800000
	NEGOTIATE_VERSION                  = 0x02000000
	NEGOTIATE_128                      = 0x20000000
	NEGOTIATE_KEY_EXCH                 = 0x40000000
	NEGOTIATE_56                       = 0x80000000
)
View Source 
const NEGOTIATE_FLAGS = NEGOTIATE_UNICODE |
	NEGOTIATE_NTLM |
	NEGOTIATE_OEM_DOMAIN_SUPPLIED |
	NEGOTIATE_OEM_WORKSTATION_SUPPLIED |
	NEGOTIATE_ALWAYS_SIGN |
	NEGOTIATE_EXTENDED_SESSIONSECURITY
View Source 
const PLP_NULL = 0xFFFFFFFFFFFFFFFF
View Source 
const PLP_TERMINATOR = 0x00000000
View Source 
const UNKNOWN_PLP_LEN = 0xFFFFFFFFFFFFFFFE

Variables

View Source 
var (
	Sp_Cursor          = ProcId{1, ""}
	Sp_CursorOpen      = ProcId{2, ""}
	Sp_CursorPrepare   = ProcId{3, ""}
	Sp_CursorExecute   = ProcId{4, ""}
	Sp_CursorPrepExec  = ProcId{5, ""}
	Sp_CursorUnprepare = ProcId{6, ""}
	Sp_CursorFetch     = ProcId{7, ""}
	Sp_CursorOption    = ProcId{8, ""}
	Sp_CursorClose     = ProcId{9, ""}
	Sp_ExecuteSql      = ProcId{10, ""}
	Sp_Prepare         = ProcId{11, ""}
	Sp_PrepExec        = ProcId{13, ""}
	Sp_PrepExecRpc     = ProcId{14, ""}
	Sp_Unprepare       = ProcId{15, ""}
)

Functions

func CopyIn 

func CopyIn(table string, options MssqlBulkOptions, columns ...string) string

func NewTimeoutConn 

func NewTimeoutConn(conn net.Conn, timeout time.Duration) *timeoutConn

func SetLogger 

func SetLogger(logger Logger)

Types

type Auth 

type Auth interface {
	InitialBytes() ([]byte, error)
	NextBytes([]byte) ([]byte, error)
	Free()
}

type DataValue 

type DataValue interface{}

type Decimal 

type Decimal struct {
	// contains filtered or unexported fields
}

http://msdn.microsoft.com/en-us/library/ee780893.aspx

func Float64ToDecimal 

func Float64ToDecimal(f float64) (Decimal, error)

func (Decimal) Bytes 

func (d Decimal) Bytes() []byte

func (Decimal) String 

func (d Decimal) String() string

func (Decimal) ToFloat64 

func (d Decimal) ToFloat64() float64

type Error 

type Error struct {
	Number     int32
	State      uint8
	Class      uint8
	Message    string
	ServerName string
	ProcName   string
	LineNo     int32
}

Error represents an SQL Server error. This type includes methods for reading the contents of the struct, which allows calling programs to check for specific error conditions without having to import this package directly.

func (Error) Error 

func (e Error) Error() string

func (Error) SQLErrorClass 

func (e Error) SQLErrorClass() uint8

func (Error) SQLErrorLineNo 

func (e Error) SQLErrorLineNo() int32

func (Error) SQLErrorMessage 

func (e Error) SQLErrorMessage() string

func (Error) SQLErrorNumber 

func (e Error) SQLErrorNumber() int32

SQLErrorNumber returns the SQL Server error number.

func (Error) SQLErrorProcName 

func (e Error) SQLErrorProcName() string

func (Error) SQLErrorServerName 

func (e Error) SQLErrorServerName() string

func (Error) SQLErrorState 

func (e Error) SQLErrorState() uint8

type KeySlice 

type KeySlice []uint8

func (KeySlice) Len 

func (p KeySlice) Len() int

func (KeySlice) Less 

func (p KeySlice) Less(i, j int) bool

func (KeySlice) Swap 

func (p KeySlice) Swap(i, j int)

type Logger 

type Logger interface {
	Printf(format string, v ...interface{})
	Println(v ...interface{})
}

type MssqlBulk 

type MssqlBulk struct {
	Options MssqlBulkOptions
	Debug   bool
	// contains filtered or unexported fields
}

func (*MssqlBulk) AddRow 

func (b *MssqlBulk) AddRow(row []interface{}) (err error)

AddRow immediately writes the row to the destination table. The arguments are the row values in the order they were specified.

func (*MssqlBulk) Done 

func (b *MssqlBulk) Done() (rowcount int64, err error)

type MssqlBulkOptions 

type MssqlBulkOptions struct {
	CheckConstraints  bool
	FireTriggers      bool
	KeepNulls         bool
	KilobytesPerBatch int
	RowsPerBatch      int
	Order             []string
	Tablock           bool
}

type MssqlConn 

type MssqlConn struct {
	// contains filtered or unexported fields
}

func (*MssqlConn) Begin 

func (c *MssqlConn) Begin() (driver.Tx, error)

func (*MssqlConn) BeginTx 

func (c *MssqlConn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error)

BeginTx satisfies ConnBeginTx.

func (*MssqlConn) Close 

func (c *MssqlConn) Close() error

func (*MssqlConn) Commit 

func (c *MssqlConn) Commit() error

func (*MssqlConn) CreateBulk 

func (cn *MssqlConn) CreateBulk(table string, columns []string) (_ *MssqlBulk)

func (*MssqlConn) Ping 

func (c *MssqlConn) Ping(ctx context.Context) error

Ping is used to check if the remote server is available and satisfies the Pinger interface.

func (*MssqlConn) Prepare 

func (c *MssqlConn) Prepare(query string) (driver.Stmt, error)

func (*MssqlConn) PrepareContext 

func (c *MssqlConn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)

func (*MssqlConn) Rollback 

func (c *MssqlConn) Rollback() error

type MssqlDriver 

type MssqlDriver struct {
	// contains filtered or unexported fields
}

func (*MssqlDriver) Open 

func (d *MssqlDriver) Open(dsn string) (driver.Conn, error)

func (*MssqlDriver) OpenConnection 

func (d *MssqlDriver) OpenConnection(dsn string) (*MssqlConn, error)

func (*MssqlDriver) SetLogger 

func (d *MssqlDriver) SetLogger(logger Logger)

type MssqlResult 

type MssqlResult struct {
	// contains filtered or unexported fields
}

func (*MssqlResult) LastInsertId 

func (r *MssqlResult) LastInsertId() (int64, error)

func (*MssqlResult) RowsAffected 

func (r *MssqlResult) RowsAffected() (int64, error)

type MssqlRows 

type MssqlRows struct {
	// contains filtered or unexported fields
}

func (*MssqlRows) Close 

func (rc *MssqlRows) Close() error

func (*MssqlRows) ColumnTypeDatabaseTypeName 

func (r *MssqlRows) ColumnTypeDatabaseTypeName(index int) string

RowsColumnTypeDatabaseTypeName may be implemented by Rows. It should return the database system type name without the length. Type names should be uppercase. Examples of returned types: "VARCHAR", "NVARCHAR", "VARCHAR2", "CHAR", "TEXT", "DECIMAL", "SMALLINT", "INT", "BIGINT", "BOOL", "[]BIGINT", "JSONB", "XML", "TIMESTAMP".

func (*MssqlRows) ColumnTypeLength 

func (r *MssqlRows) ColumnTypeLength(index int) (int64, bool)

RowsColumnTypeLength may be implemented by Rows. It should return the length of the column type if the column is a variable length type. If the column is not a variable length type ok should return false. If length is not limited other than system limits, it should return math.MaxInt64. The following are examples of returned values for various types: 

TEXT          (math.MaxInt64, true)
varchar(10)   (10, true)
nvarchar(10)  (10, true)
decimal       (0, false)
int           (0, false)
bytea(30)     (30, true)

func (*MssqlRows) ColumnTypeNullable 

func (r *MssqlRows) ColumnTypeNullable(index int) (nullable, ok bool)

The nullable value should be true if it is known the column may be null, or false if the column is known to be not nullable. If the column nullability is unknown, ok should be false.

func (*MssqlRows) ColumnTypePrecisionScale 

func (r *MssqlRows) ColumnTypePrecisionScale(index int) (int64, int64, bool)

It should return the precision and scale for decimal types. If not applicable, ok should be false. The following are examples of returned values for various types: 

decimal(38, 4)    (38, 4, true)
int               (0, 0, false)
decimal           (math.MaxInt64, math.MaxInt64, true)

func (*MssqlRows) ColumnTypeScanType 

func (r *MssqlRows) ColumnTypeScanType(index int) reflect.Type

It should return the value type that can be used to scan types into. For example, the database column type "bigint" this should return "reflect.TypeOf(int64(0))".

func (*MssqlRows) Columns 

func (rc *MssqlRows) Columns() (res []string)

func (*MssqlRows) HasNextResultSet 

func (rc *MssqlRows) HasNextResultSet() bool

func (*MssqlRows) Next 

func (rc *MssqlRows) Next(dest []driver.Value) error

func (*MssqlRows) NextResultSet 

func (rc *MssqlRows) NextResultSet() error

type MssqlStmt 

type MssqlStmt struct {
	// contains filtered or unexported fields
}

func (*MssqlStmt) Close 

func (s *MssqlStmt) Close() error

func (*MssqlStmt) Exec 

func (s *MssqlStmt) Exec(args []driver.Value) (driver.Result, error)

func (*MssqlStmt) ExecContext 

func (s *MssqlStmt) ExecContext(ctx context.Context, args []driver.NamedValue) (driver.Result, error)

func (*MssqlStmt) NumInput 

func (s *MssqlStmt) NumInput() int

func (*MssqlStmt) Query 

func (s *MssqlStmt) Query(args []driver.Value) (driver.Rows, error)

func (*MssqlStmt) QueryContext 

func (s *MssqlStmt) QueryContext(ctx context.Context, args []driver.NamedValue) (driver.Rows, error)

func (*MssqlStmt) QueryMeta 

func (s *MssqlStmt) QueryMeta() (cols []columnStruct, err error)

QueryMeta is almost the same as MssqlStmt.Query, but returns all the columns info.

func (*MssqlStmt) SetQueryNotification 

func (s *MssqlStmt) SetQueryNotification(id, options string, timeout time.Duration)

type NTLMAuth 

type NTLMAuth struct {
	Domain      string
	UserName    string
	Password    string
	Workstation string
}

func (*NTLMAuth) Free 

func (auth *NTLMAuth) Free()

func (*NTLMAuth) InitialBytes 

func (auth *NTLMAuth) InitialBytes() ([]byte, error)

func (*NTLMAuth) NextBytes 

func (auth *NTLMAuth) NextBytes(bytes []byte) ([]byte, error)

type Param 

type Param struct {
	Name  string
	Flags uint8
	// contains filtered or unexported fields
}

type ProcId 

type ProcId struct {
	// contains filtered or unexported fields
}

func MakeProcId 

func MakeProcId(name string) (res ProcId)

type SerializableBulkConfig 

type SerializableBulkConfig struct {
	TableName   string
	ColumnsName []string
	Options     MssqlBulkOptions
}

type StreamError 

type StreamError struct {
	Message string
}

func (StreamError) Error 

func (e StreamError) Error() string

type UniqueIdentifier 

type UniqueIdentifier [16]byte

func (*UniqueIdentifier) Scan 

func (u *UniqueIdentifier) Scan(v interface{}) error

func (UniqueIdentifier) String 

func (u UniqueIdentifier) String() string

func (UniqueIdentifier) Value 

func (u UniqueIdentifier) Value() (driver.Value, error)

Source Files

View all Source files

Directories

Path Synopsis
bach splits a single script containing multiple batches separated by a keyword into multiple scripts.
 bach splits a single script containing multiple batches separated by a keyword into multiple scripts.
examples
bulk
simple
tsql

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.