mssql

package module
v0.0.0-...-7df28d1 Latest Latest
Warning

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

Go to latest
Published: Oct 15, 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 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 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 Float64ToDecimalScale

func Float64ToDecimalScale(f float64, scale uint8) (Decimal, error)

func (Decimal) BigInt

func (d Decimal) BigInt() big.Int

func (Decimal) Bytes

func (d Decimal) Bytes() []byte

func (Decimal) String

func (d Decimal) String() string

func (Decimal) ToFloat64

func (d Decimal) ToFloat64() float64

func (Decimal) UnscaledBytes

func (d Decimal) UnscaledBytes() []byte

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) CheckNamedValue

func (c *MssqlConn) CheckNamedValue(nv *driver.NamedValue) error

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)

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

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL