tds

package module
v0.1.8 Latest Latest
Warning

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

 Go to latest Published: Jun 18, 2021 License: BSD-3-Clause Imports: 33 Imported by: 0

README

tds

import "github.com/neweric2021/tds"

Package tds is a pure Go Sybase ASE/IQ/RS driver for the database/sql package.

Status

This is a beta release. This driver has yet to be battle tested on production workload. Version 1.0 will be released when this driver will be production ready

Requirements

- Sybase ASE 12.5 or higher
- go 1.8 or higher.
Installation

Package installation is done via go-get:

$ go get -u github.com/neweric2021/tds
Usage

It implements most of the database/sql functionalities. To connect to a sybase instance, import the package and use the regular database/sql APIs:

import (
	"database/sql"

	_ "github.com/neweric2021/tds"
)

func main() {
	cnxStr := "tds://my_user:my_password@dbhost.com:5000/pubs?charset=utf8"
	db, err := sql.Open("tds", cnxStr)
	if err != nil {
		log.Fatal(err)
	}

	id := 2
	rows, err := db.Query("select * from authors where id = ?", id)
	…
}
Connection String

The connection string is pretty standard and uses the URL format:

tds://username:password@host:port/database?parameter=value&parameter2=value2
Connection parameters

The most common ones are:

- username - the database server login. Mandatory.
- password - The login's password. Mandatory.
- host - The host to connect to. Mandatory.
- port - The port to bind to. Mandatory.
- database - The database to use. You will connect to the login's
  default database if not specified.
- charset - The client's character set. Default to utf8.
  Please refer to the character sets section.
- readTimeout - read timeout in seconds.
- writeTimeout - write timeout in seconds.
- textSize - max size of textsize fields in bytes.
  It is suggested to raise it to avoid truncation.

Less frequently used ones:

- tls-enable - Enforce TLS use
- tls-hostname - Remote hostname to validate against SANs
- tls-skip-validation - Skip TLS validation. Accepts any TLS certificate
- tls-ca-file - Path to CA file to validate server certificate against
- encryptPassword - Can be "yes" to require password encryption,
  "no" to disable it, and "try" to try encrytping password an falling back
  to plain text password. Password encryption works on Sybase ASE 15.5
  or higher and uses RSA.
- packetSize - Network packet size. Must be less than or equal the server's
  max network packet size. The default is the server's default network
  packet size.
- applicationName - the name of your application.
  It is a best practice to set it.
Query parameters

Most of the database/sql APIs are implemented, with a major one missing: named parameters. Please use the question mark '?' as a placeholder for parameters :

res, err = tx.Exec("insert into author (id, name) values (?, ?)", 2, "Paul")
Supported data types

Almost all of the sybase ASE datatypes are supported, with the exception of lob locators. The type mapping between the server and the go data types is as follows:

- varchar/text/char/unichar/univarchar/xml => string
- int/smalling/bigint => int64.
  Unsigned bigints with a value > Math.MaxInt64 will be returned as uint64
- date/datetime/bigdate/bigdatetime => time.Time
- image/binary/varbinary/image => []byte
- real/float => float64
- decimal/numeric/money/smallmoney => tds.Num.
  Please see the  "precise numerical types" section.
Precise numerical types

decimal/numeric/money/smallmoney data can be given as parameters using any of the go numerical types. However one should never use float64 if a loss of precision is not tolerated. To implement precise floating point numbers, this driver provides a "Num" datatype, which is a wrapper around big.Rat.

It implements the value.Scanner interface, you can thus instanciate it this way:

num := tds.Num{precision: 16, scale: 2}
num.Scan("-10.4")
num.Scan(1023)

To access the underlying big.Rat:

rat := num.Rat()

Num also implements the stringer interface to pretty print its value. Please refer to the tds.Num godoc for more information.

Character set encoding

This driver assumes by default that the client uses utf8 strings and will ask the server to convert back and forth to/from this charset.

If utf8 charset conversion is not supported on the server, and if the charset conversion is not supported by golang.org/x/text/encoding, you can add client-side character set conversion with the following code:

var e encoding.Encoding
tds.RegisterEncoding("name", e)

You will have to handle it yourself and use a charset supported by the server.

Custom error handling

One can set a custom error callback to process server errors before the regular error processing routing. This allows handling showplan messages and print statement.

The following demonstrates how to handle showplan and print messages:

conn := sql.Open("tds", url)

// print showplan messages and all
conn.Driver().(tds.ErrorHandler).SetErrorhandler(func(m tds.SybError) bool {
	if m.Severity == 10 {
		if (m.MsgNumber >= 3612 && m.MsgNumber <= 3615) ||
			(m.MsgNumber >= 6201 && m.MsgNumber <= 6299) ||
			(m.MsgNumber >= 10201 && m.MsgNumber <= 10299) {
			fmt.Printf(m.Message)
		} else {
			fmt.Println(strings.TrimRight(m.Message, "\n"))
		}
	}

	if m.Severity > 10 {
		fmt.Print(m)
	}
	return m.Severity > 10
})
Limitations

As of now the driver does not support bulk insert and named parameters. Password encryption only works for Sybase ASE > 15.5.

Testing

You can use stmt_test.go and session_test.go for sample usage, as follows:

export TDS_USERNAME=test_user
export TDS_PASSWORD=test_password
export TDS_SERVER=localhost:5000
go test
License

This driver is released under the go license

Credits

- the freetds and jtds protocol documentation.
- Microsoft for releasing the full tds specification.
  There are differences, however a lot of it is relevant.
- github.com/denisenkom/go-mssqldb for most of the tests.
- The Sybase::TdsServer perl module for capabilities handling.

Documentation

Overview

Package tds is a pure Go Sybase ASE/IQ/RS driver for the database/sql package.

Status

This is a beta release. This driver has yet to be battle tested on production workload. Version 1.0 will be released when this driver will be production ready

Requirements

  • Sybase ASE 12.5 or higher
  • go 1.8 or higher.

Installation

Package installation is done via go-get: 

$ go get -u github.com/neweric2021/tds

Usage

It implements most of the database/sql functionalities. To connect to a sybase instance, import the package and use the regular database/sql APIs: 

import (
	"database/sql"

	_ "github.com/neweric2021/tds"
)

func main() {
	cnxStr := "tds://my_user:my_password@dbhost.com:5000/pubs?charset=utf8"
	db, err := sql.Open("tds", cnxStr)
	if err != nil {
		log.Fatal(err)
	}

	id := 2
	rows, err := db.Query("select * from authors where id = ?", id)
	…
}

Connection String

The connection string is pretty standard and uses the URL format: 

tds://username:password@host:port/database?parameter=value&parameter2=value2

Connection parameters

The most common ones are:

  • username - the database server login. Mandatory.
  • password - The login's password. Mandatory.
  • host - The host to connect to. Mandatory.
  • port - The port to bind to. Mandatory.
  • database - The database to use. You will connect to the login's default database if not specified.
  • charset - The client's character set. Default to utf8. Please refer to the character sets section.
  • readTimeout - read timeout in seconds.
  • writeTimeout - write timeout in seconds.
  • textSize - max size of textsize fields in bytes. It is suggested to raise it to avoid truncation.

Less frequently used ones:

  • tls-enable - Enforce TLS use
  • tls-hostname - Remote hostname to validate against SANs
  • tls-skip-validation - Skip TLS validation. Accepts any TLS certificate
  • tls-ca-file - Path to CA file to validate server certificate against
  • encryptPassword - Can be "yes" to require password encryption, "no" to disable it, and "try" to try encrytping password an falling back to plain text password. Password encryption works on Sybase ASE 15.5 or higher and uses RSA.
  • packetSize - Network packet size. Must be less than or equal the server's max network packet size. The default is the server's default network packet size.
  • applicationName - the name of your application. It is a best practice to set it.

Query parameters

Most of the database/sql APIs are implemented, with a major one missing: named parameters. Please use the question mark '?' as a placeholder for parameters : 

res, err = tx.Exec("insert into author (id, name) values (?, ?)", 2, "Paul")

Supported data types

Almost all of the sybase ASE datatypes are supported, with the exception of lob locators. The type mapping between the server and the go data types is as follows:

  • varchar/text/char/unichar/univarchar/xml => string
  • int/smalling/bigint => int64. Unsigned bigints with a value > Math.MaxInt64 will be returned as uint64
  • date/datetime/bigdate/bigdatetime => time.Time
  • image/binary/varbinary/image => []byte
  • real/float => float64
  • decimal/numeric/money/smallmoney => tds.Num. Please see the "precise numerical types" section.

Precise numerical types

decimal/numeric/money/smallmoney data can be given as parameters using any of the go numerical types. However one should never use float64 if a loss of precision is not tolerated. To implement precise floating point numbers, this driver provides a "Num" datatype, which is a wrapper around big.Rat.

It implements the value.Scanner interface, you can thus instanciate it this way: 

num := tds.Num{precision: 16, scale: 2}
num.Scan("-10.4")
num.Scan(1023)

To access the underlying big.Rat: 

rat := num.Rat()

Num also implements the stringer interface to pretty print its value. Please refer to the tds.Num godoc for more information.

Character set encoding

This driver assumes by default that the client uses utf8 strings and will ask the server to convert back and forth to/from this charset.

If utf8 charset conversion is not supported on the server, and if the charset conversion is not supported by golang.org/x/text/encoding, you can add client-side character set conversion with the following code: 

var e encoding.Encoding
tds.RegisterEncoding("name", e)

You will have to handle it yourself and use a charset supported by the server.

Custom error handling

One can set a custom error callback to process server errors before the regular error processing routing. This allows handling showplan messages and print statement.

The following demonstrates how to handle showplan and print messages: 

conn := sql.Open("tds", url)

// print showplan messages and all
conn.Driver().(tds.ErrorHandler).SetErrorhandler(func(m tds.SybError) bool {
	if m.Severity == 10 {
		if (m.MsgNumber >= 3612 && m.MsgNumber <= 3615) ||
			(m.MsgNumber >= 6201 && m.MsgNumber <= 6299) ||
			(m.MsgNumber >= 10201 && m.MsgNumber <= 10299) {
			fmt.Printf(m.Message)
		} else {
			fmt.Println(strings.TrimRight(m.Message, "\n"))
		}
	}

	if m.Severity > 10 {
		fmt.Print(m)
	}
	return m.Severity > 10
})

Limitations

As of now the driver does not support bulk insert and named parameters. Password encryption only works for Sybase ASE > 15.5.

Testing

You can use stmt_test.go and session_test.go for sample usage, as follows: 

export TDS_USERNAME=test_user
export TDS_PASSWORD=test_password
export TDS_SERVER=localhost:5000
go test

License

This driver is released under the go license

Credits

  • the freetds and jtds protocol documentation.
  • Microsoft for releasing the full tds specification. There are differences, however a lot of it is relevant.
  • github.com/denisenkom/go-mssqldb for most of the tests.
  • The Sybase::TdsServer perl module for capabilities handling.

Index

Constants

This section is empty.

Variables

View Source 
var ErrBadType = errors.New("invalid type given")

ErrBadType is raised when trying to convert a value to an incompatible data type.

View Source 
var ErrInvalidIsolationLevel = errors.New("tds: invalid or unsupported isolation level")

ErrInvalidIsolationLevel is raised when an unsupported isolation level is asked.

View Source 
var ErrNoReadOnly = errors.New("tds: readonly is unsupported")

ErrNoReadOnly is raise when readonly attribute of driver.TxOptions is set. Readonly sessions are not supported by sybase.

View Source 
var ErrNonNullable = errors.New("trying to insert null values into non null column")

ErrNonNullable is raised when trying to insert null values to non-null fields

View Source 
var ErrOverFlow = errors.New("overflow when converting to database type")

ErrOverFlow is raised when there is an overflow when converting the parameter to a database type.

View Source 
var ErrUnsupportedPassWordEncrytion = errors.New("tds: login failed. Unsupported encryption")

ErrUnsupportedPassWordEncrytion is caused by an unsupported password encrytion scheme (used by ASE <= 15.0.1)

Functions

func RegisterEncoding 

func RegisterEncoding(sybaseCharsetName string, e encoding.Encoding)

RegisterEncoding register encoding for the charset

Types

type Conn 

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

Conn encapsulates a tds session and satisties driver.Connc

func NewConn 

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

NewConn returns a TDS session

func (Conn) Begin 

func (s Conn) Begin() (driver.Tx, error)

func (Conn) BeginTx 

func (s Conn) BeginTx(ctx context.Context, opts driver.TxOptions) (driver.Tx, error)

BeginTx implements driver.ConnBeginTx interface

func (Conn) Close 

func (s Conn) Close() error

Close terminates the session by sending logout message and closing tcp connection.

func (Conn) Commit 

func (s Conn) Commit() error

func (Conn) Exec 

func (s Conn) Exec(query string, args []driver.Value) (driver.Result, error)

Exec implements the Querier interface. The aim is to use language queries when no parameters are given

func (Conn) ExecContext 

func (s Conn) ExecContext(ctx context.Context, query string,
	namedArgs []driver.NamedValue) (driver.Result, error)

Implement the "ExecerContext" interface

func (Conn) GetEnv 

func (c Conn) GetEnv() map[string]string

GetEnv return a map of environments variables. The following keys are garanteed to be present:

  • server
  • database
  • charset

func (Conn) Ping 

func (s Conn) Ping(ctx context.Context) error

Ping implements driver.Pinger interface

func (Conn) Prepare 

func (s Conn) Prepare(query string) (driver.Stmt, error)

Prepare prepares a statement and returns it

func (Conn) PrepareContext 

func (s Conn) PrepareContext(ctx context.Context, query string) (driver.Stmt, error)

Prepare prepares a statement and returns it

func (Conn) Query 

func (s Conn) Query(query string, args []driver.Value) (driver.Rows, error)

Exec implements the Execer interface. The aim is to use language queries when no parameters are given

func (Conn) QueryContext 

func (s Conn) QueryContext(ctx context.Context, query string,
	namedArgs []driver.NamedValue) (driver.Rows, error)

Implement the "QueryerContext" interface

func (Conn) Rollback 

func (s Conn) Rollback() error

func (Conn) SelectValue 

func (s Conn) SelectValue(ctx context.Context, query string) (value interface{}, err error)

Reads exactly one value from an sql query

func (*Conn) SetErrorhandler 

func (c *Conn) SetErrorhandler(fn func(s SybError) bool)

SetErrorhandler allows setting a custom error handler. The function shall accept an SQL Message and return a boolean indicating if this message is indeed a critical error.

type ErrorHandler 

type ErrorHandler interface {
	SetErrorhandler(fn func(s SybError) bool)
}

ErrorHandler is a connection which support defines sybase error handling

type Num 

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

Num represents a sybase numeric data type

func (Num) Rat 

func (n Num) Rat() big.Rat

Rat returns the underlying big.Rat value

func (*Num) Scan 

func (n *Num) Scan(src interface{}) error

Scan implements the Scanner interface. Allows initiating a tds.Num from a string, or any golang numeric type. When providing a string, it must be in decimal form, with an optional sign, ie -50.40 The dot is the separator.

Example: 

num := Num{precision: p, scale: s}
num.Scan("-10.4")

A loss of precision should alway cause an error (except for bugs, of course).

func (Num) String 

func (n Num) String() string

implement the stringer interface

type Result 

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

Result information

func (*Result) LastInsertId 

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

LastInsertId returns the id of the last insert. TODO: handle context

func (Result) RowsAffected 

func (r Result) RowsAffected() (int64, error)

RowsAffected returns the number of rows affected by the last statement

type Rows 

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

Rows information, columns and data

func (Rows) AffectedRows 

func (r Rows) AffectedRows() (count int, ok bool)

AffectedRows returns the number of affected rows Satisfies the driver.Rows interface

func (*Rows) Close 

func (r *Rows) Close() (err error)

Close skips all remaining rows NB: only return error on unexpected failure.

func (Rows) ColumnAutoIncrement 

func (r Rows) ColumnAutoIncrement(index int) (bool, bool)

ColumnAutoIncrement returns a boolean indicating if the column is auto-incremented.

func (Rows) ColumnHidden 

func (r Rows) ColumnHidden(index int) (bool, bool)

ColumnHidden returns a boolean indicating if the column is hidden. Sybase returns hidden columns when using "for browse"

func (Rows) ColumnKey 

func (r Rows) ColumnKey(index int) (bool, bool)

ColumnKey returns a boolean indicating if the column is in the primary key.

func (Rows) ColumnTypeDatabaseTypeName 

func (r Rows) ColumnTypeDatabaseTypeName(index int) string

ColumnTypeDatabaseTypeName returns the sybase type name as a string. Satisfies the driver.Rows interface

func (Rows) ColumnTypeLength 

func (r Rows) ColumnTypeLength(index int) (int64, bool)

ColumnTypeLength returns the length of a column given by its index. Satisfies the driver.Rows interface

func (Rows) ColumnTypeNullable 

func (r Rows) ColumnTypeNullable(index int) (bool, bool)

ColumnTypeNullable returns the nullability of a column given by its index. Satisfies the driver.Rows interface

func (Rows) ColumnTypePrecisionScale 

func (r Rows) ColumnTypePrecisionScale(index int) (int64, int64, bool)

ColumnTypePrecisionScale returns the precision and scale of a numeric column given by its index. Satisfies the driver.Rows interface

func (Rows) ColumnTypeScanType 

func (r Rows) ColumnTypeScanType(index int) reflect.Type

ColumnTypeScanType returns the value type to scan into. Satisfies the driver.Rows interface

func (Rows) Columns 

func (r Rows) Columns() (columns []string)

Columns returns the resultset's columns Satisfies the driver.Rows interface

func (Rows) ComputeByList 

func (r Rows) ComputeByList() (list []int, ok bool)

ComputeByList the list of columns in the "by" clause of a compute

the result is an array containing the indices. This result is valid only after the computed row was returned. See ComputedColumnInfo() for the reason

func (Rows) ComputedColumnInfo 

func (r Rows) ComputedColumnInfo(index int) (operator string, operand int, ok bool)

ComputedColumnInfo returns the operator and the operand for a computed column, given its index

This result is valid only after the computed row was returned. Indeed, a statement can contain several compute clause. Sybase sends compute inforamtion tokens, along with an ID to match the row and the relevant columns' information. Here we only handle the last computed result received from the wire, as those are overriden in the row handling routine.

func (Rows) HasNextResultSet 

func (r Rows) HasNextResultSet() bool

HasNextResultSet indicates of there is a second result set pending

func (*Rows) Next 

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

Next implements the driver.Result Next method to fetch the next row

It will return io.EOF at the end of the result set If another resultset is found, sets the hasNextResultSet property to true.

func (*Rows) NextResultSet 

func (r *Rows) NextResultSet() error

NextResultSet resets the hasNextResultSet to trigger the processing at the next call to Next()

func (Rows) ReturnStatus 

func (r Rows) ReturnStatus() (returnStatus int, ok bool)

ReturnStatus returns the last return status for the current resultset. Satisfies the driver.Rows interface

type Stmt 

type Stmt struct {
	ID int64
	// contains filtered or unexported fields
}

Stmt is a prepared statement implementing the driver.Stmt interface

func (*Stmt) Close 

func (st *Stmt) Close() error

Close drops the prepared statement from the database

func (Stmt) ColumnConverter 

func (st Stmt) ColumnConverter(idx int) driver.ValueConverter

ColumnConverter returns converters which check min, max, nullability, precision, scale and then convert to a valid sql.Driver value.

func (*Stmt) Exec 

func (st *Stmt) Exec(args []driver.Value) (res driver.Result, err error)

Exec executes a prepared statement. Implements the database/sql/Stmt interface

func (*Stmt) ExecContext 

func (st *Stmt) ExecContext(ctx context.Context, namedArgs []driver.NamedValue) (res driver.Result, err error)

ExecContext executes a prepared statement, along with a context. Implements the database/sql/Stmt interface

func (Stmt) NumInput 

func (st Stmt) NumInput() int

NumInput returns the number of expected parameters

func (*Stmt) Query 

func (st *Stmt) Query(args []driver.Value) (driver.Rows, error)

Query executes a prepared statement and returns rows.

func (*Stmt) QueryContext 

func (st *Stmt) QueryContext(ctx context.Context, namedArgs []driver.NamedValue) (driver.Rows, error)

QueryContext executes a prepared statement and returns rows

type SybDriver 

type SybDriver struct {
	sync.Mutex
	IsError func(s SybError) bool
}

SybDriver is the driver implementing driver.Driver interface

func (*SybDriver) Open 

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

Open opens a connection to the server. See https://github.com/neweric2021/tds#connection-string for the dsn formatting. It also set the custum error handler if any.

func (*SybDriver) SetErrorhandler 

func (d *SybDriver) SetErrorhandler(fn func(s SybError) bool)

SetErrorhandler allows setting a custom error handler. The function shall accept an SQL Message and return a boolean indicating if this message is indeed a critical error.

Example: 

// Print showplan messages
conn.Driver().(tds.ErrorHandler).SetErrorhandler(func(m tds.SybError) bool {
	if m.Severity == 10 {
		if (m.MsgNumber >= 3612 && m.MsgNumber <= 3615) ||
			(m.MsgNumber >= 6201 && m.MsgNumber <= 6299) ||
			(m.MsgNumber >= 10201 && m.MsgNumber <= 10299) {
			fmt.Printf(m.Message)
		} else {
			fmt.Println(strings.TrimRight(m.Message, "\n"))
		}
	}

	if m.Severity > 10 {
		fmt.Print(m)
	}
	return m.Severity > 10
})

type SybError 

type SybError struct {
	MsgNumber  int32
	State      int8
	Severity   int8
	SQLState   string // 1 byte size
	HasEed     uint8
	TranState  uint16
	Message    string // 2 bytes size
	Server     string // 1 byte size
	Procedure  string // 1 byte size
	LineNumber int16
}

SybError is the struct containing sybase error information

func (SybError) Error 

func (e SybError) Error() string

implement the error interface

Source Files

View all Source files

Directories

Path Synopsis
test package not for production use
 test package not for production use

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.