freetds

package module
v1.0.0 Latest Latest
Warning

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

 Go to latest Published: Jun 7, 2021 License: MIT Imports: 17 Imported by: 0

README

gofreetds

Go FreeTDS wrapper. Native Sql Server database driver.

Features:

  • can be used as database/sql driver
  • handles calling stored procedures
  • handles multiple resultsets
  • supports database mirroring
  • connection pooling
  • scaning resultsets into structs

Get started

Install dependencines

FreeTDS libraries must be installed on the system.

Mac

brew install freetds

Ubuntu, Debian...

sudo apt-get install freetds-dev

Go get

go get github.com/minus5/gofreetds

Docs

http://godoc.org/github.com/minus5/gofreetds

Using as database/sql driver

Name of the driver is mssql.

db, err := sql.Open("mssql", connStr)
...
row := db.QueryRow("SELECT au_fname, au_lname name FROM authors WHERE au_id = ?", "172-32-1176")
..
var firstName, lastName string
err = row.Scan(&firstName, &lastName)

Full example in example/mssql.

Stored Procedures

What I'm missing in database/sql is calling stored procedures, handling return values and output params. And especially handling multiple result sets. Which is all supported by FreeTDS and of course by gofreetds.

Connect:

pool, err := freetds.NewConnPool("user=ianic;pwd=ianic;database=pubs;host=iow")
defer pool.Close()
...
//get connection
conn, err := pool.Get()
defer conn.Close()

Execute stored procedure:

rst, err := conn.ExecSp("sp_help", "authors")

Read sp return value, and output params:

returnValue := rst.Status()
var param1, param2 int
rst.ParamScan(&param1, &param2)

Read sp resultset (fill the struct):

author := &Author{}
rst.Scan(author)

Read next resultset:

if rst.NextResult() {
    for rst.Next() {
        var v1, v2 string
        rst.Scan(&v1, &v2)
    }
}

Full example in example/stored_procedure

Other usage

Executing arbitrary sql is supported with Exec or ExecuteSql.

Execute query:

rst, err := conn.Exec("select au_id, au_lname, au_fname from authors")

Rst is array of results. Each result has Columns and Rows array. Each row is array of values. Each column is array of ResultColumn objects.

Full example in example/exec.

Execute query with params:

rst, err := conn.ExecuteSql("select au_id, au_lname, au_fname from authors where au_id = ?", "998-72-3567")

Sybase Compatibility Mode

Gofreetds now supports Sybase ASE 16.0 through the driver. In order to support this, this post is very helpful: Connect to MS SQL Server and Sybase ASE from Mac OS X and Linux with unixODBC and FreeTDS (from Internet Archive)

To use a Sybase ASE server with Gofreetds, you simply need to set a compatibility mode on your connection string after you've configured your .odbc.ini file and .freetds.conf file.

This mode uses TDS Version 5.

Connection String Parameter

You can set your connection string up for Sybase by using the 'compatibility_mode' Parameter. The parameter can be named 'compatibility', 'compatibility mode', 'compatibility_mode' or 'Compatibility Mode'. Currently this mode only supports Sybase. To specify you can use 'sybase' or 'Sybase'.

Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;Failover Partner=myMirror;Max Pool Size=200;Compatibility Mode=Sybase

Testing

Tests depend on the pubs database.

Pubs sample database install script could be downloaded. After installing that package you will find instpubs.sql on the disk (C:\SQL Server 2000 Sample Databases). Execute that script to create pubs database.

Tests and examples are using environment variable GOFREETDS_CONN_STR to connect to the pubs database.

export GOFREETDS_CONN_STR="user=ianic;pwd=ianic;database=pubs;host=iow"
export GOFREETDS_MIRROR_HOST="iow-mirror"

If you don't want to setup and test database mirroring than don't define GOFREETDS_MIRROR_HOST. Mirroring tests will be skipped.

Documentation

Overview

Package freetds provides interface to Microsoft Sql Server database by using freetds C lib: http://www.freetds.org.

Index

Constants

View Source 
const (
	//name               database type   go type
	SYBINT1 = 48  //tinyint       uint8
	SYBINT2 = 52  //smallint      int16
	SYBINT4 = 56  //int           int32
	SYBINT8 = 127 //bigint        int64

	SYBCHAR      = 47
	SYBVARCHAR   = 39  //varchar       string
	SYBNVARCHAR  = 103 //nvarchar      string
	XSYBNVARCHAR = 231 //nvarchar      string
	XSYBNCHAR    = 239 //nchar         string
	XSYBXML      = 241 //XML           string

	SYBREAL = 59 //real          float32
	SYBFLT8 = 62 //float(53)     float64

	SYBBIT  = 50  //bit           bool
	SYBBITN = 104 //bit           bool

	SYBMONEY4 = 122 //smallmoney    float64
	SYBMONEY  = 60  //money         float64

	SYBDATETIME  = 61 //datetime      time.Time
	SYBDATETIME4 = 58 //smalldatetime time.Time

	SYBIMAGE      = 34  //image         []byte
	SYBBINARY     = 45  //binary        []byte
	SYBVARBINARY  = 37  //varbinary     []byte
	XSYBVARBINARY = 165 //varbinary     []byte

	SYBNUMERIC = 108
	SYBDECIMAL = 106

	SYBUNIQUE = 36 //uniqueidentifier string
)
View Source 
const (
	DBVERSION_UNKNOWN byte = iota
	DBVERSION_46
	DBVERSION_100
	// DBVERSION_42 TDS 4.2 Sybase and Microsoft
	//The version in use at the time of the Sybase/Microsoft split.
	DBVERSION_42
	// DBVERSION_70 TDS 7.0 Microsoft
	//Introduced for SQL Server 7.0. Includes support for the extended datatypes in SQL Server 7.0 (such as char/varchar fields of more than 255 characters). It also includes support for Unicode.
	DBVERSION_70
	// DBVERSION_71 TDS 7.1 Microsoft, was 8.0 [2]
	//Introduced for SQL Server 2000. Includes support for big integer (64-bit int) and “variant” datatypes.
	DBVERSION_71
	// DBVERSION_72 TDS 7.2 Microsoft, was 9.0
	//Introduced for SQL Server 2005. Includes support for varchar(max), varbinary(max), xml datatypes and MARS.
	DBVERSION_72
	// DBVERSION_73 TDS 7.3 Microsoft
	//Introduced for SQL Server 2008. Includes support for extended date/time, table as parameters.
	DBVERSION_73
	// DBVERSION_74 TDS 7.4 Microsoft
	//Introduced for SQL Server 2012. Includes support for session recovery.
	DBVERSION_74
)
View Source 
const SYBASE string = "sybase"
View Source 
const SYBASE_12_5 string = "sybase_12_5"

Variables

This section is empty.

Functions

func NewCredentials 

func NewCredentials(connStr string) *credentials

NewCredentials fills credentials stusct from connection string

Types

type Conn 

type Conn struct {
	Error   string
	Message string
	// contains filtered or unexported fields
}

Connection to the database.

func NewConn 

func NewConn(connStr string) (*Conn, error)

NewConn Connect to the database with connection string, returns new connection or error. Example: 

conn, err := NewConn("host=myServerA;database=myDataBase;user=myUsername;pwd=myPassword;mirror=myMirror")

Mirror is optional, other params are mandatory.

func (*Conn) Begin 

func (conn *Conn) Begin() error

Begin database transaction.

func (*Conn) Close 

func (conn *Conn) Close()

If conn belongs to pool release connection to the pool. If not close connection.

func (*Conn) Commit 

func (conn *Conn) Commit() error

Commit database transaction.

func (*Conn) DbUse 

func (conn *Conn) DbUse() error

Change database.

func (*Conn) Exec 

func (conn *Conn) Exec(sql string) ([]*Result, error)

Execute sql query.

func (*Conn) ExecSp 

func (conn *Conn) ExecSp(spName string, params ...interface{}) (*SpResult, error)

Execute stored procedure by name and list of params.

Example: 

conn.ExecSp("sp_help", "authors")

func (*Conn) ExecuteSql 

func (conn *Conn) ExecuteSql(query string, params ...driver.Value) ([]*Result, error)

Execute sql query with arguments. ? in query are arguments placeholders. 

ExecuteSql("select * from authors where au_fname = ?", "John")

func (*Conn) HasMessageNumber 

func (conn *Conn) HasMessageNumber(msgno int) int

Returns the number of occurances of a supplied FreeTDS message number.

func (*Conn) MirrorStatus 

func (conn *Conn) MirrorStatus() (bool, bool, bool, error)

Checking database mirroring status: 

isDefined - is mirror defined (mirror parametar passed in connection string)
isActive  - is mirroring active for this database
isMaster  - is the current host master for this database

Returns error if could not execute query to get current mirroring status.

func (*Conn) Rollback 

func (conn *Conn) Rollback() error

Rollback database transaction.

func (*Conn) SelectValue 

func (conn *Conn) SelectValue(sql string) (interface{}, error)

Query database and return first column in the first row as result.

type ConnPool 

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

ConnPool - connection pool for the maxCount connections.

Connection can be acquired from the pool by pool.Get().

Release conn to the pool by caling conn.Close() or pool.Release(conn).

Destroy pool and all connections by calling pool.Close().

Connections will be removed from the pool if not active for poolExpiresInterval. But there is always one connection in the pool.

Example: 

pool, err := NewConnPool("host=myServerA;database=myDataBase;user=myUsername;pwd=myPassword")
...
conn, err := pool.Get()
//use conn
conn.Close()
...
pool.Close()

func NewConnPool 

func NewConnPool(connStr string) (*ConnPool, error)

NewCoonPool creates new connection pool. Connection will be created using provided connection string. Max number of connections in the pool is controlled by max_pool_size connection string parameter, default is 100.

New connections will be created when needed. There is always one connection in the pool.

Returns err if fails to create initial connection. Valid connection string examples: 

"host=myServerA;database=myDataBase;user=myUsername;pwd=myPassword;"
"host=myServerA;database=myDataBase;user=myUsername;pwd=myPassword;max_pool_size=500"
"host=myServerA;database=myDataBase;user=myUsername;pwd=myPassword;mirror=myMirror"

func (*ConnPool) Close 

func (p *ConnPool) Close()

Close connection pool. Closes all existing connections in the pool.

func (*ConnPool) Do 

func (p *ConnPool) Do(handler func(*Conn) error) error

Get connection from pool and execute handler. Release connection after handler is called.

func (*ConnPool) DoInTransaction 

func (p *ConnPool) DoInTransaction(handler func(*Conn) error) error

Get new connection from pool, and execute handler in transaction. If handler returns error transaction will be rolled back. Release connection after handerl is called.

func (*ConnPool) Get 

func (p *ConnPool) Get() (*Conn, error)

Get returns connection from the pool. Blocks if there are no free connections, and maxConn is reached.

func (*ConnPool) Release 

func (p *ConnPool) Release(conn *Conn)

Release connection to the pool.

func (*ConnPool) Stat 

func (p *ConnPool) Stat() (max, count, active int)

Statistic about connections in the pool.

type MssqlConn 

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

MssqlConn implements Conn interface from http://golang.org/src/pkg/database/sql/driver/driver.go

func (*MssqlConn) Begin 

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

Begin implements Begin for Conn interface from http://golang.org/src/pkg/database/sql/driver/driver.go

func (*MssqlConn) Close 

func (c *MssqlConn) Close() error

Close implements Close for Conn interface from http://golang.org/src/pkg/database/sql/driver/driver.go

func (*MssqlConn) Prepare 

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

Prepare implements Prepare for Conn interface from http://golang.org/src/pkg/database/sql/driver/driver.go

type MssqlConnTx 

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

MssqlConnTx implements Tx interface from http://golang.org/src/pkg/database/sql/driver/driver.go

func (*MssqlConnTx) Commit 

func (t *MssqlConnTx) Commit() error

Commit implements Commit for Tx interface from http://golang.org/src/pkg/database/sql/driver/driver.go

func (*MssqlConnTx) Rollback 

func (t *MssqlConnTx) Rollback() error

Rollback implements Rollback for Tx interface from http://golang.org/src/pkg/database/sql/driver/driver.go

type MssqlDriver 

type MssqlDriver struct{}

MssqlDriver implements Driver interface from http://golang.org/src/pkg/database/sql/driver/driver.go

func (*MssqlDriver) Open 

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

Open implements Open for Driver interface from http://golang.org/src/pkg/database/sql/driver/driver.go

type MssqlResult 

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

implements Result interface from http://golang.org/src/pkg/database/sql/driver/driver.go

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
}

implements Rows interface from http://golang.org/src/pkg/database/sql/driver/driver.go

func (*MssqlRows) Close 

func (r *MssqlRows) Close() error

func (*MssqlRows) Columns 

func (r *MssqlRows) Columns() []string

func (*MssqlRows) Next 

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

type MssqlStmt 

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

implements Stmt interface from http://golang.org/src/pkg/database/sql/driver/driver.go

func (*MssqlStmt) Close 

func (s *MssqlStmt) Close() error

func (*MssqlStmt) Exec 

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

func (*MssqlStmt) NumInput 

func (s *MssqlStmt) NumInput() int

func (*MssqlStmt) Query 

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

type ParamsCache 

type ParamsCache struct {
	sync.RWMutex
	// contains filtered or unexported fields
}

func NewParamsCache 

func NewParamsCache() *ParamsCache

func (*ParamsCache) Get 

func (pc *ParamsCache) Get(spName string) ([]*spParam, bool)

func (*ParamsCache) Set 

func (pc *ParamsCache) Set(spName string, params []*spParam)

type RawBytes 

type RawBytes []byte

RawBytes is a byte slice that holds a reference to memory owned by the database itself. After a Scan into a RawBytes, the slice is only valid until the next call to Next, Scan, or Close.

type Result 

type Result struct {
	Columns      []*ResultColumn
	Rows         [][]interface{}
	ReturnValue  int
	RowsAffected int
	Message      string
	// contains filtered or unexported fields
}

func NewResult 

func NewResult() *Result

func (*Result) CurrentRow 

func (r *Result) CurrentRow() int

CurrentRow() returns current row (set by Next()). Returns -1 as an error if Next() wasn't called.

func (*Result) FindColumn 

func (r *Result) FindColumn(name string) (int, error)

FindColumn returns an index of a column, found by name. Returns error if the column isn't found.

func (*Result) HasNext 

func (r *Result) HasNext() bool

HasNext returns true if we have more rows to process.

func (*Result) MustScan 

func (r *Result) MustScan(cnt int, dest ...interface{}) error

Must Scan exactly cnt number of values from result. Useful when scanning into structure, to know whether are all expected fields filled with values. cnt - number of values assigned to fields

func (*Result) Next 

func (r *Result) Next() bool

Advances to the next row. Returns false if there is no more rows (i.e. we are on the last row).

func (*Result) Scan 

func (r *Result) Scan(dest ...interface{}) error

Scan copies the columns in the current row into the values pointed at by dest.

func (*Result) ScanColumn 

func (r *Result) ScanColumn(name string, dest interface{}) error

Find column with given name and scan it's value to the result. Returns error if the column isn't found, otherwise returns error if the scan fails.

type ResultColumn 

type ResultColumn struct {
	Name   string
	DbSize int
	DbType int
	Type   string
}

type SpOutputParam 

type SpOutputParam struct {
	Name  string
	Value interface{}
}

Stored procedure output parameter name and value.

type SpResult 

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

Stored procedure execution result.

func NewSpResult 

func NewSpResult() *SpResult

func (*SpResult) HasOutputParams 

func (r *SpResult) HasOutputParams() bool

Does the stored procedure has any output params.

func (*SpResult) HasResults 

func (r *SpResult) HasResults() bool

Does the stored procedure returned any resultsets.

func (*SpResult) MustScan 

func (r *SpResult) MustScan(cnt int, dest ...interface{}) error

func (*SpResult) Next 

func (r *SpResult) Next() bool

Call Next on current result. True if next row in current result exists, otherwise false.

func (*SpResult) NextResult 

func (r *SpResult) NextResult() bool

Navigate to next result. True if sucessfull, false if no more resutls.

func (*SpResult) ParamScan 

func (r *SpResult) ParamScan(values ...interface{}) error

Sacaning output parameters of stored procedure

func (*SpResult) Result 

func (r *SpResult) Result() *Result

Returns current result

func (*SpResult) ResultsCount 

func (r *SpResult) ResultsCount() int

Number of returned Results

func (*SpResult) Scan 

func (r *SpResult) Scan(dest ...interface{}) error

Scan current result.

func (SpResult) Status 

func (r SpResult) Status() int

Stored procedure return value.

Source Files

View all Source files

Directories

Path Synopsis
example
exec
mssql
scan_struct
sp_help
stored_procedure

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.