Go Sql Server database driver.

Overview

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.

Comments
  • Executing stored procedure doesn't return data

    Executing stored procedure doesn't return data

    I have the following code

    rst, err := conn.ExecSp("sp_help", "ABS")
    if err != nil {
        panic(err)
    }
    
    
    if rst.NextResult() {
        for rst.Next() {
        var v1, v2, v3, v4, v5, v6 string
        rst.Scan(&v1, &v2, &v3, &v4, &v5, &v6)
        fmt.Printf("%s, %s, %s, %s, %s, %s", v1, v2, v3, v4, v5, v6)
        }
    }
    
    

    Running exec sp_help ABS; in the management studio work it returns data, but whenever I do this it never returns data.

    Also running sp_fkeys crashes with an error

    Msg 8009, Level 16, State 1
    Server 'EGL-IRV-ASP07', Line 1
        The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@pktable_name"): Data type 0x00 is unknown.
    

    Executing regular SQLs work and data is returned, and all the stored procedure work if executed from the Management Studio.

    opened by ilijamt 9
  • fix(fetch): don't allocate tons of memory for varchar(max), don't crash on large dbdata

    fix(fetch): don't allocate tons of memory for varchar(max), don't crash on large dbdata

    Use freetds's dbdata() for all variable data. Read the data each row. This fixes both memory allocation of all varchars and crash on size+1,because size now cannot be > int32 for varchar(max), because we don't need to add that +1 now.

    opened by daliborfilus 8
  • Removing setting login version to allow version to be set from freetds

    Removing setting login version to allow version to be set from freetds

    dbsetlversion(login, DBVERSION_72);
    

    This line in the conn.go file is forcing the version of the connection to 7.2. If you have a scenario where you need a specific version, this call will override it. In my case, Sybase ASE 16 requires me to use TDS Version 5.0.

    Confirmed that it's overridden by setting up the TDSDUMP file and attempting a connection from gofreetds.

    When this line was commented out, the connection went through (although your starting options are incompatible with ASE 16 so I'll need to work with that as another issue).

    Also, it appears my PR updated some lines with trailing spaces - hopefully that is ok.

    opened by caledhwa 8
  • Truncated strings from Stored Procedure with varchar(max) output parameters

    Truncated strings from Stored Procedure with varchar(max) output parameters

    Is anyone having issues with receiving stored procedure output parameters that are varchar(max) when using gofreetds? For me they get truncated to 255 characters. However, if my DBA changes the type of the parameter to varchar(4000) (for example), I get up to the full 4000 characters returned.

    According to FreeTDS, varchar(max) support should be in TDS protocol version 7.2: http://www.freetds.org/userguide/choosingtdsprotocol.htm (gofreetds forces use of TDS 7.2)

    Yet I can’t get varchar(max) to work in gofreetds. We're using SQL Server 2012, FreeTDS 0.95.19 CentOS 7 x86_64

    Please can anyone offer any assistance? Many thanks in advance.

    opened by MarkSonghurst 7
  • Recent breaking changes

    Recent breaking changes

    Recent pull requests merged in to this repo have caused a regression.

    Change 1: https://github.com/minus5/gofreetds/commit/dc2777a304d61b33486d77d39672d55aea3fb61e Change 2: https://github.com/minus5/gofreetds/commit/b2a58917a6f72b09cf1f2fe8c79448673bc8bde6

    Change 1 (@myself) was introduced to solve an issue with empty strings breaking the db. Change 2 (@thatguystone) was introduced 3 months later to solve the same issue. Both pull requests were then merged at the same time. Both changes address the same issue using different methods, but change 2 seems to have caused a regression in my code, causing errors in a codebase that was not erroring out before the change. Example error is included below. I apologize for not having better information to reproduce this at the moment. I actually like the way change 2 solved the issue better than change 1, and I'm not yet sure how change 2 is causing the issue, I just know that if I roll back to before it got pulled in then I no longer get this error.

    Server Error: Msg 1001, Level 15 General SQL Server error: Check messages from the SQL Server Msg 137, Level 15 General SQL Server error: Check messages from the SQL Server Msg 1001, Level 15, State 1 Server 'LUR-SRV-01\SQLEXPRESS', Line 1 Line 1: Length or precision specification 0 is invalid. Msg 137, Level 15, State 2 Server 'LUR-SRV-01\SQLEXPRESS', Line 2 Must declare the scalar variable "@p1".

    opened by gregtzar 7
  • Added map to count occurrances of FreeTDS message numbers

    Added map to count occurrances of FreeTDS message numbers

    I have a need to intercept certain FreeTDS Message Numbers in my daemon, and if they occur act upon them (specifically, delay and then retry if I get 1204 or 1205).

    This pull request adds a map to count occurrences of Message Numbers as they are reported and a function to get the count of a specific Message Number. I've also added a necessary mutex which is required to pass "go test --race"

    I've also made the Conn.reconnect() function public, so I can reconnect before retrying my procedure call. I could not see another way to do this when using a pooled connection as you cannot actually close a pooled connection, only return it to the pool.

    "go fmt" has also been run.

    This addresses issue #36

    opened by MarkSonghurst 6
  • Fix inability to use ExecSp against Sybase ASE

    Fix inability to use ExecSp against Sybase ASE

    Fixes the inability to use ExecSp against a Sybase ASE database when calling a stored procedure.

    This PR uses the newly-introduced Sybase compatibility mode setting on the connection string to decide whether to use a MSSQL-specific or Sybase-specific SQL command to use in getting a list of stored procedure parameters for a given stored procedure

    While you can just use the Exec method to execute a stored procedure that only involves returned result sets, you are forced to use ExecSp when your stored procedure returns data in output parameters. In initial usage of ExecSp, I received this error:

    Msg 20018, Level 16
    General SQL Server error: Check messages from the SQL Server
    
    
    Msg 20018, Level 16
    General SQL Server error: Check messages from the SQL Server
    
    
    Msg 208, Level 16, State 1
    Server 'SYBASE', Line 2
        sys.all_parameters not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
    
    
    Msg 208, Level 16, State 1
    Server 'SYBASE', Line 2
        sys.all_objects not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
    

    Added a couple of unit tests to exercise the conditional logic as well.

    opened by marzolfb 5
  • Upgrade to FreeTDS 0.95-19 introduced gofreetds issues with nvarchar trailing spaces

    Upgrade to FreeTDS 0.95-19 introduced gofreetds issues with nvarchar trailing spaces

    Hello. Platform: CentOS 7 x86_64, Go 1.5.1

    I've been reliably using gofreetds with FreeTDS 0.91 (from epel RPM freetds-0.91-12.git0a42888.el7.x86_64) which was recently upgraded to FreeTDS 0.95.19 (freetds-0.95.19-1.el7.x86_64). Once I performed the update, I cleaned built and installed gofreetds (having also installed the 0.95.19 freetds-devel RPM) but found my Go daemon's database unit tests were failing against both my SQL Server 2014 and 2008 servers, with the same issue.

    Upon inspection I could see that trailing spaces were appearing in the data returned from columns of the nvarchar type. So where a returned column was correctly empty (from me pushing up an empty Go string) when using FreeTDS 0.91, when using FreeTDS 0.95 I am now getting a " " (single space) returned. The data within the tables also shows the trailing spaces present.

    You can probably guess what I've changed to resolve this :-) convert_sql_buf.go /* if datatype == XSYBNVARCHAR || datatype == XSYBNCHAR { //FIXME - adding len bytes to the end of the buf // realy don't understand why this is necessary // come to this solution by try and error l := len(data) for i := 0; i < l; i++ { data = append(data, byte(0)) }
    } */

    Commenting out that block of code removes the trailing spaces and resolves my issue on 0.95 However, without it, gofreetds will break on 0.91, with the following error being reported:

    dbutil.c:86:msgno 8016: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 5 ("@nvcFirstname"): Data type 0xE7 has an invalid data length or metadata length."

    It appears to me that the FIXME is no longer required in 0.95 but is still required in 0.91 Perhaps the FreeTDS version can be detected at connection time (?) and then the FIXME logic applied as necessary? Or maybe time to draw a line in the sand and say only 0.95 is supported? Any advice on how to proceed would be welcome. I'm new to GitHub and contributing, so I'm not sure what the etiquette is :-)

    My /etc/freetds.conf file is as per initially installed by the FreeTDS RPM, which has no client charset setting in it. Everything in the freetds.log file generated suggests the data is being converted from UTF-8 okay.

    opened by MarkSonghurst 5
  • Cross compilation failed?

    Cross compilation failed?

    VSCode mac -> windows

    ➜  Sendsms CGO_ENABLED=0 GOOS=windows GOARCH=amd64 go build main.go
    # github.com/minus5/gofreetds
    ../../../../go/pkg/mod/github.com/minus5/[email protected]/conn_pool.go:33:19: undefined: Conn
    ../../../../go/pkg/mod/github.com/minus5/[email protected]/params_cache.go:8:22: undefined: spParam
    
    opened by baa-god 4
  • Fix for issue #42

    Fix for issue #42

    Proposed fix for issue #42 I've run results_test.go successfully, but I don't have access to a pubs database to run the complete set of tests. Please can someone do this for me. Thanks.

    opened by MarkSonghurst 4
  • Data type 0x2B is unknown. FreeTDS version?

    Data type 0x2B is unknown. FreeTDS version?

    I am attempting to execute a stored procedure on my database, and I'm getting the following error:

    2014/08/07 15:30:04 Msg 8009, Level 16 General SQL Server error: Check messages from the SQL Server

    Msg 8009, Level 16, State 1
    Server 'server', Line 1
    The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@Parameter1"): Data type 0x2B is unknown.
    /home/jackman/code/edge-api/src/rolist/ro.go:34 (rolist.ROList)
    

    Why is this data type unknown? Is the database responding to a reference of data type 0x2B by FreeTDS? I know that my server supports this data type (DateTimeOffset) because that's the expected type of the parameter.

    This post mentions/verifies the data type in question: http://lists.ibiblio.org/pipermail/freetds/2011q2/026938.html

    Code snippet:

    nullDate := "3/10/1997 12:00:00 AM"                                                                 
    rst, err := conn.ExecSp("Procedure", nullDate, nullDate, 1, 291, 1)     
    if err != nil {                                                                                     
            return rolist, errors.Stack(err)                                                            
    }  
    

    I can successfully perform select statements and other things on the DB.

    I thought perhaps my version of FreeTDS is outdated, so I built the latest version locally, and then executed with the following:

    [email protected]:~/code/edge-api$ LD_LIBRARY_PATH=$HOME/opt/lib GOPATH=$PWD:$GOPATH go run ./src/main.go
    panic: dbopen error
    
    goroutine 16 [running]:
    runtime.panic(0x5010a0, 0xc2080002b0)
            /home/jackman/code/go/src/pkg/runtime/panic.c:279 +0xf5
    core.connect(0x7cb160)
            /home/jackman/code/edge-api/src/core/db.go:21 +0x85
    core.init·1()
            /home/jackman/code/edge-api/src/core/db.go:14 +0x1e
    core.init()
            /home/jackman/code/edge-api/src/core/db.go:28 +0x4b
    rolist.init()
            /home/jackman/code/edge-api/src/rolist/ro.go:40 +0x50
    main.init()
            /home/jackman/code/edge-api/src/main.go:33 +0x46
    
    goroutine 19 [finalizer wait]:
    runtime.park(0x415520, 0x7db678, 0x7cf289)
            /home/jackman/code/go/src/pkg/runtime/proc.c:1369 +0x89
    runtime.parkunlock(0x7db678, 0x7cf289)
            /home/jackman/code/go/src/pkg/runtime/proc.c:1385 +0x3b
    runfinq()
            /home/jackman/code/go/src/pkg/runtime/mgc0.c:2644 +0xcf
    runtime.goexit()
            /home/jackman/code/go/src/pkg/runtime/proc.c:1445
    
    goroutine 17 [syscall]:
    runtime.goexit()
            /home/jackman/code/go/src/pkg/runtime/proc.c:1445
    exit status 2
    

    Without more specific output from gofreetds, I don't know how to proceed. Please help. Thank you.

    opened by jackman0 4
  • ftds.NewConnPool does not return, once in a long while

    ftds.NewConnPool does not return, once in a long while

    Last few months, two times we have seen in a live system that after some network event, ftds.NewConnPool does not return. I was expecting that if it could not connect, it would return within some time, but somehow it just does not.

    I still can not determine the cause of this happening but could at least see from logs this symptom - as this is a prod system.

    opened by abkhan 0
  • fix: add sybase 12.5 LastInsertedId() support

    fix: add sybase 12.5 LastInsertedId() support

    adds (some) LastInsertedId() support for the sybase 12.5 compatibility mode. statusRowSybase125 will now be used in the executed sql. it will cast the returned values to int64s that can be cast from in mssql_stmt.go

    Support is not perfect because LastInsertedId() returns the last inserted id from the whole connection, not just the scope of the sql statements.

    opened by Jesse0Michael 0
  • Handle nil params in ExecuteSql

    Handle nil params in ExecuteSql

    Issue #29

    I have tested this with Sybase 12.5, However not with other versions of Sybase/MSSQL.

    I took a tip from go-mssqldb where it associates null with "nvarchar(1)", though I am not exactly sure if it's the same context. https://github.com/denisenkom/go-mssqldb/blob/b91950f658ecd54342d783495e1aadf48a55e967/types.go#L1124

    opened by drewlesueur 5
  • Bugfix go2SqlDataType was returning too large of a varbinary

    Bugfix go2SqlDataType was returning too large of a varbinary

    The go2sqlDataType function would return a varbinary of any length, however, 8000 is the maximum size any varbinary data type can be. Source: https://docs.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql?view=sql-server-ver15

    To be larger than 8000, it must be a varbinary (MAX). Otherwise, this would cause an error when calling conn.ExecuteSql with a byte slice with a greater length than 8000. I've made this adjustment and added a test to make sure it keeps working.

    opened by nehoffman 0
  • ExecuteSql Sybase - No sp_executesql in Sybase

    ExecuteSql Sybase - No sp_executesql in Sybase

    Greetings,

    I would like to propose making the below line that checks for sybase 12.5, should be if conn.sybaseMode125() || conn.sybaseMode() {

    Due to Sybase ASE not having stored procedure sp_executesql like MS SQL Server does. Without that, one must avoid using methods that hit ExecuteSql method if using Sybase ASE.

    Thanks.

    https://github.com/minus5/gofreetds/blob/1da6dafd22b23a54c6eb7c53ea9d402013a057f7/executesql.go#L24

    opened by andyedison 0
Owner
minus5
minus5
Go driver for PostgreSQL over SSH. This driver can connect to postgres on a server via SSH using the local ssh-agent, password, or private-key.

pqssh Go driver for PostgreSQL over SSH. This driver can connect to postgres on a server via SSH using the local ssh-agent, password, or private-key.

mattn 52 Nov 6, 2022
Microsoft ActiveX Object DataBase driver for go that using exp/sql

go-adodb Microsoft ADODB driver conforming to the built-in database/sql interface Installation This package can be installed with the go get command:

mattn 132 Dec 5, 2022
Oracle driver for Go using database/sql

go-oci8 Description Golang Oracle database driver conforming to the Go database/sql interface Installation Install Oracle full client or Instant Clien

mattn 596 Nov 13, 2022
sqlite3 driver for go using database/sql

go-sqlite3 Latest stable version is v1.14 or later not v2. NOTE: The increase to v2 was an accident. There were no major changes or features. Descript

mattn 6.2k Dec 6, 2022
Pure Go Postgres driver for database/sql

pq - A pure Go postgres driver for Go's database/sql package Install go get github.com/lib/pq Features SSL Handles bad connections for database/sql S

null 7.7k Nov 29, 2022
Attach hooks to any database/sql driver

sqlhooks Attach hooks to any database/sql driver. The purpose of sqlhooks is to provide a way to instrument your sql statements, making really easy to

Gustavo Chaín 595 Nov 30, 2022
Qmgo - The Go driver for MongoDB. It‘s based on official mongo-go-driver but easier to use like Mgo.

Qmgo English | 简体中文 Qmgo is a Go driver for MongoDB . It is based on MongoDB official driver, but easier to use like mgo (such as the chain call). Qmg

Qiniu Cloud 1k Dec 5, 2022
Microsoft SQL server driver written in go language

A pure Go MSSQL driver for Go's database/sql package Install Requires Go 1.8 or above. Install with go get github.com/denisenkom/go-mssqldb . Connecti

null 1.7k Dec 6, 2022
Mirror of Apache Calcite - Avatica Go SQL Driver

Apache Avatica/Phoenix SQL Driver Apache Calcite's Avatica Go is a Go database/sql driver for the Avatica server. Avatica is a sub-project of Apache C

The Apache Software Foundation 103 Nov 3, 2022
Firebird RDBMS sql driver for Go (golang)

firebirdsql (Go firebird sql driver) Firebird RDBMS http://firebirdsql.org SQL driver for Go Requirements Firebird 2.5 or higher Golang 1.13 or higher

Hajime Nakagami 184 Nov 25, 2022
GO DRiver for ORacle DB

Go DRiver for ORacle godror is a package which is a database/sql/driver.Driver for connecting to Oracle DB, using Anthony Tuininga's excellent OCI wra

null 406 Dec 3, 2022
PostgreSQL driver and toolkit for Go

pgx - PostgreSQL Driver and Toolkit pgx is a pure Go driver and toolkit for PostgreSQL. pgx aims to be low-level, fast, and performant, while also ena

Jack Christensen 6.4k Dec 5, 2022
Lightweight Golang driver for ArangoDB

Arangolite Arangolite is a lightweight ArangoDB driver for Go. It focuses on pure AQL querying. See AranGO for a more ORM-like experience. IMPORTANT:

Fabien Herfray 73 Sep 26, 2022
Go language driver for RethinkDB

RethinkDB-go - RethinkDB Driver for Go Go driver for RethinkDB Current version: v6.2.1 (RethinkDB v2.4) Please note that this version of the driver on

RethinkDB 1.6k Nov 22, 2022
goriak - Go language driver for Riak KV

goriak Current version: v3.2.1. Riak KV version: 2.0 or higher, the latest version of Riak KV is always recommended. What is goriak? goriak is a wrapp

Gustav Westling 29 Nov 22, 2022
Mongo Go Models (mgm) is a fast and simple MongoDB ODM for Go (based on official Mongo Go Driver)

Mongo Go Models Important Note: We changed package name from github.com/Kamva/mgm/v3(uppercase Kamva) to github.com/kamva/mgm/v3(lowercase kamva) in v

kamva 588 Nov 20, 2022
The MongoDB driver for Go

The MongoDB driver for Go This fork has had a few improvements by ourselves as well as several PR's merged from the original mgo repo that are current

GlobalSign 2k Nov 30, 2022
The Go driver for MongoDB

MongoDB Go Driver The MongoDB supported driver for Go. Requirements Installation Usage Bugs / Feature Reporting Testing / Development Continuous Integ

mongodb 7.1k Nov 30, 2022
RethinkDB-go - RethinkDB Driver for Go

Go language driver for RethinkDB

RethinkDB 1.6k Nov 22, 2022