mssqlh

README

mssqlh

SQL Server Helper Library

Documentation

Overview

Package mssqlh provides connection string building and helper routines for working with Microsoft SQL Server.

This package provides support for connecting to SQL Server using either https://github.com/denisenkom/go-mssqldb (mssql driver) or https://github.com/alexbrainman/odbc (odbc driver).

Using the Connection type, you should be able to switch seamlessly between the two. The package defaults to the "mssql" driver usless you specify the "odbc" driver.

Example using Open

db, err := mssqlh.Open(fqdn)

This uses a trusted connection to the designated server using the "mssql" driver. It accepts server.domain.com, server\instance, server,port, or server:port.

Example code using NewConnection:

cxn := mssqlh.NewConnection("localhost", "", "", "myapp")
db, err := sql.Open("mssql", cxn.String())

If you don't pass a user and password it defaults to a trusted connection.

Example using the Connection type:

cxn := mssqlh.Connect{
	FQDN:        "db-txn.corp.loc",
	Application: "myapp",
	DialTimeout: 15,
}
cxn.Database = "TXNDB"
db, err := cxn.Open()

Defaults

The package provides the following defaults (1) if no server is specified, it will use localhost, (2) if no user is specified, it will default to a trusted connection (3) if no application name is specied, it will default to the name of the executable

Using the ODBC driver

The subpackage odbch provides additional support for using ODBC driver (https://github.com/alexbrainman/odbc)

Example code using the Connection object:

cxn := mssqlh.Connect{
	Driver:     mssqlh.DriverODBC,
	ODBCDriver: odbch.NativeClient11,
	FQDN:       "localhost",
}
db, err := cxn.Open()

This connects using the ODBC driver.

Version Support

GetServer and GetSession should support SQL Server 2005 and beyond. They have been tested on SQL Server 2014 through SQL Server 2019.

There is limited testing with Azure SQL Databases. The GetSession method requires VIEW DATABASE STATE permission.

Index

• Variables
• func Open(fqdn, database string) (*sql.DB, error)
• func QuoteName(s string) string
• func QuoteString(s string) string
• type Connection
  • func NewConnection(server, user, password, database, app string) Connection
  • func (c Connection) Computer() string
  • func (c Connection) Instance() string
  • func (c Connection) Open() (*sql.DB, error)
  • func (c Connection) Port() int
  • func (c Connection) Redacted(n int) string
  • func (c Connection) ServerName() string
  • func (c Connection) String() string
• type Server
  • func GetServer(ctx context.Context, db *sql.DB) (Server, error)
• type Session
  • func GetSession(ctx context.Context, db *sql.DB) (Session, error)

Variables

var DriverMSSQL = "mssql"

DriverMSSQL uses the https://github.com/denisenkom/go-mssqldb library. This is the driver for this package.

var DriverODBC = "odbc"

DriverODBC uses the https://github.com/alexbrainman/odbc library

Functions

func Open

func Open(fqdn, database string) (*sql.DB, error)

Open connects to a SQL Server. It accepts "host[\instance]", "host:port", or "host,port".

func QuoteName

func QuoteName(s string) string

QuoteName wraps a string in [brackets]. It tries to match the functionality of SQL Server's QUOTENAME()

func QuoteString

func QuoteString(s string) string

QuoteString wraps a string in single-quotes and tries to handle embedded quotes

Types

type Connection

type Connection struct {
	// Driver sets the GO driver that will be used.
	// Leaving this blank defaults to DriverMSSQL.
	Driver         string
	FQDN           string
	User           string
	Password       string
	Database       string
	Application    string
	DialTimeout    int
	ConnectTimeout int
	ODBCDriver     string
}

Connection is the basis for building connection strings

func NewConnection

func NewConnection(server, user, password, database, app string) Connection

NewConnection returns a connection with sane defaults. You can specify the server "host[\instance]", "host:port", or "host,port" format.

func (Connection) Computer

func (c Connection) Computer() string

Computer returns the computer (or host) name from FQDN

func (Connection) Instance

func (c Connection) Instance() string

Instance returns the instance from FQDN

func (Connection) Open

func (c Connection) Open() (*sql.DB, error)

Open connects to the SQL Server

func (Connection) Port

func (c Connection) Port() int

Port returns the port from FQDN. It returns 0 if no port.

func (Connection) Redacted

func (c Connection) Redacted(n int) string

Redacted returns a connection string with the password replaced with "redacted". Optionally, you can specify how many characters of the password to include

func (Connection) ServerName

func (c Connection) ServerName() string

ServerName buids a string in the format server\instance or server:host. Most likely you won't have an instance and a port. Plus I don't think that works. This should be roughly what it tries to connect to.

func (Connection) String

func (c Connection) String() string

String returns a connection string for the given connection. Setting Driver to an invalid type returns an unusable connection string but not an error. It should be caught on Open

type Server

type Server struct {
	// Name holds the result of @@SERVERNAME
	Name      string
	Computer  string
	Instance  string
	Domain    string
	DNSSuffix string
	FQDN      string

	EngineEdition       int
	ProductVersion      string
	ProductMajorVersion int
}

Server holds information about an instance of SQL Server

func GetServer

func GetServer(ctx context.Context, db *sql.DB) (Server, error)

GetServer gets details on the SQL Server TODO Create a queryer interface and accept that (suppport sqlx)

type Session

type Session struct {
	Server          string    `db:"atat_server_name"`
	ID              int       `db:"session_id"`
	ConnectTime     time.Time `db:"connect_time"`
	LoginTime       time.Time `db:"login_time"`
	ClientInterface string    `db:"client_interface_name"`
	ClientVersion   int       `db:"client_version"`
	AuthScheme      string    `db:"auth_scheme"`
	Application     string
	Login           string
	Database        string
}

Session stores information about the connection to SQL Server

func GetSession

func GetSession(ctx context.Context, db *sql.DB) (Session, error)

GetSession gets details on the current connection to SQL Server TODO Create a queryer interface and accept that This requires VIEW DATABASE STATE in Azure