Microsoft SQL server driver written in go language

Overview

A pure Go MSSQL driver for Go's database/sql package

GoDoc Build status codecov

Install

Requires Go 1.8 or above.

Install with go get github.com/denisenkom/go-mssqldb .

Connection Parameters and DSN

The recommended connection string uses a URL format: sqlserver://username:[email protected]/instance?param1=value&param2=value Other supported formats are listed below.

Common parameters:

  • 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. The user domain sensitive to the case which is defined in the connection string.
  • password
  • database
  • connection timeout - in seconds (default is 0 for no timeout), set to 0 for no timeout. Recommended to set to 0 and use context to manage query and connection timeouts.
  • dial timeout - in seconds (default is 15), set to 0 for no timeout
  • 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.
  • app name - The application name (default is go-mssqldb)

Connection parameters for ODBC and ADO style connection strings:

  • server - host or host\instance (default localhost)
  • port - used only when there is no instance in server (default 1433)

Less common parameters:

  • keepAlive - in seconds; 0 to disable (default is 30)
  • failoverpartner - host or host\instance (default is no partner).
  • failoverport - used only when there is no instance in failoverpartner (default 1433)
  • 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
  • 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)
  • ApplicationIntent - Can be given the value ReadOnly to initiate a read-only connection to an Availability Group listener. The database must be specified when connecting with Application Intent set to ReadOnly.

The connection string can be specified in one of three formats:

  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:[email protected]/instance?param1=value&param2=value
  • sqlserver://username:[email protected]:port?param1=value&param2=value
  • sqlserver://[email protected]/SQLExpress?database=master&connection+timeout=30 // `SQLExpress instance.
  • sqlserver://sa:[email protected]?database=master&connection+timeout=30 // username=sa, password=mypass.
  • sqlserver://sa:[email protected]:1234?database=master&connection+timeout=30 // port 1234 on localhost.
  • sqlserver://sa:my%[email protected]?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("app name", "MyAppName")

  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(),
  }
  db, err := sql.Open("sqlserver", u.String())
  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;app name=MyAppName
  • server=localhost;user id=sa;database=master;app name=MyAppName
  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;app name=MyAppName
  • odbc:server=localhost;user id=sa;database=master;app name=MyAppName
  • 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"

Azure Active Directory authentication - preview

The configuration of functionality might change in the future.

Azure Active Directory (AAD) access tokens are relatively short lived and need to be valid when a new connection is made. Authentication is supported using a callback func that provides a fresh and valid token using a connector:

conn, err := mssql.NewAccessTokenConnector(
  "Server=test.database.windows.net;Database=testdb",
  tokenProvider)
if err != nil {
	// handle errors in DSN
}
db := sql.OpenDB(conn)

Where tokenProvider is a function that returns a fresh access token or an error. None of these statements actually trigger the retrieval of a token, this happens when the first statment is issued and a connection is created.

Executing Stored Procedures

To run a stored procedure, set the query text to the procedure name:

var account = "abc"
_, err := db.ExecContext(ctx, "sp_RunMe",
	sql.Named("ID", 123),
	sql.Named("Account", sql.Out{Dest: &account}),
)

Reading Output Parameters from a Stored Procedure with Resultset

To read output parameters from a stored procedure with resultset, make sure you read all the rows before reading the output parameters:

sqltextcreate := `
CREATE PROCEDURE spwithoutputandrows
	@bitparam BIT OUTPUT
AS BEGIN
	SET @bitparam = 1
	SELECT 'Row 1'
END
`
var bitout int64
rows, err := db.QueryContext(ctx, "spwithoutputandrows", sql.Named("bitparam", sql.Out{Dest: &bitout}))
var strrow string
for rows.Next() {
	err = rows.Scan(&strrow)
}
fmt.Printf("bitparam is %d", bitout)

Caveat for local temporary tables

Due to protocol limitations, temporary tables will only be allocated on the connection as a result of executing a query with zero parameters. The following query will, due to the use of a parameter, execute in its own session, and #mytemp will be de-allocated right away:

conn, err := pool.Conn(ctx)
defer conn.Close()
_, err := conn.ExecContext(ctx, "select @p1 as x into #mytemp", 1)
// at this point #mytemp is already dropped again as the session of the ExecContext is over

To work around this, always explicitly create the local temporary table in a query without any parameters. As a special case, the driver will then be able to execute the query directly on the connection-scoped session. The following example works:

conn, err := pool.Conn(ctx)

// Set us up so that temp table is always cleaned up, since conn.Close()
// merely returns conn to pool, rather than actually closing the connection.
defer func() {
	_, _ = conn.ExecContext(ctx, "drop table #mytemp")  // always clean up
	conn.Close() // merely returns conn to pool
}()


// Since we not pass any parameters below, the query will execute on the scope of
// the connection and succeed in creating the table.
_, err := conn.ExecContext(ctx, "create table #mytemp ( x int )")

// #mytemp is now available even if you pass parameters
_, err := conn.ExecContext(ctx, "insert into #mytemp (x) values (@p1)", 1)

Return Status

To get the procedure return status, pass into the parameters a *mssql.ReturnStatus. For example:

var rs mssql.ReturnStatus
_, err := db.ExecContext(ctx, "theproc", &rs)
log.Printf("status=%d", rs)

or

var rs mssql.ReturnStatus
_, err := db.QueryContext(ctx, "theproc", &rs)
for rows.Next() {
	err = rows.Scan(&val)
}
log.Printf("status=%d", rs)

Limitation: ReturnStatus cannot be retrieved using QueryRow.

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 and Name = @p2;`, sql.Named("ID", 6), "Bob")

Parameter Types

To pass specific types to the query parameters, say varchar or date types, you must convert the types to the type before passing in. The following types are supported:

  • string -> nvarchar
  • mssql.VarChar -> varchar
  • time.Time -> datetimeoffset or datetime (TDS version dependent)
  • mssql.DateTime1 -> datetime
  • mssql.DateTimeOffset -> datetimeoffset
  • "github.com/golang-sql/civil".Date -> date
  • "github.com/golang-sql/civil".DateTime -> datetime2
  • "github.com/golang-sql/civil".Time -> time
  • mssql.TVP -> Table Value Parameter (TDS version dependent)

Important Notes

  • LastInsertId should not be used with this driver (or SQL Server) due to how the TDS protocol works. Please use the OUTPUT Clause or add a select ID = convert(bigint, SCOPE_IDENTITY()); to the end of your query (ref SCOPE_IDENTITY). This will ensure you are getting the correct ID and will prevent a network round trip.
  • NewConnector may be used with OpenDB.
  • Connector.SessionInitSQL may be set to set any driver specific session settings after the session has been reset. If empty the session will still be reset but use the database defaults in Go1.10+.

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 SQLSERVER_DSN=sqlserver://user:[email protected]/instance?database=test1 go test

Deprecated

These features still exist in the driver, but they are are deprecated.

Query Parameter Token Replace (driver "mssql")

If you use the driver name "mssql" (rather then "sqlserver") the SQL text will be loosly parsed and an attempt to extract identifiers using one of

  • ?
  • ?nnn
  • :nnn
  • $nnn

will be used. This is not recommended with SQL Server. There is at least one existing won't fix issue with the query parsing.

Use the native "@Name" parameters instead with the "sqlserver" driver name.

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
Comments
  • Azure SQL. long query - driver: bad connection

    Azure SQL. long query - driver: bad connection

    got a headache with a strange behavior when working with SQL Azure.

    this code causes "driver: bad connection" error when executing second select

    var dbConnString = "server=ttt.database.windows.net;port=1433;user id=user;password=pass;database=db;log=63"
    
    func connect() (*sql.DB, error) {
        db, err := sql.Open("mssql", dbConnString)
        if err != nil {
            return nil, err
        }
        err = db.Ping()
        if err != nil {
            return nil, err
        }
        return db, nil
    }
    
    func main() {
        // open db in this function
        db, err := connect()
        if err != nil {
            return
        }
        defer db.Close() // close db in this function
    
        fmt.Println("SEL 1")
        _, err = db.Exec("SELECT 1")
        if err != nil {
                    fmt.Println("Sel error: " + err.Error())
            return
        }
    
        fmt.Println("SEL bad")
    
        _, err = db.Exec(`SELECT 2 -- long query FFD8FFE000104A46494600010100000100010000FFDB0043000302020302020303030304030304050805050404050A070706080C0A0C0C0B0A0B0B0D0E12100D0E110E0B0B1016101113141515150C0F171816141812141514FFDB00430103040405040509050509140D0B0D141414141414141414141414141414141414141414141414141414141414141414141414141414140419D47E2AFC78F89DA0F80FC2BAA412EB9ACCCD1406EA28228A3554692491D8A70A91A3B9C02C4290AACC403D67C50D4E0F0D780C78ABC07F1BEDBC656F16BF36853E99A86896DA56A2EAB10923BE82DBCD99DED1C6E5F3241132B6D5299276C1FB05F8A74CF82BFB6C781EFF00C7773FF08A5AE99797D61A83EAB1B43F629DED2E2DC24C08CC5895D558BE027258A8048F45FD0419D47E2AFC78F89DA0F80FC2BAA412EB9ACCCD1406EA28228A3554692491D8A70A91A3B9C02C4290AACC403D67C50D4E0F0D780C78ABC07F1BEDBC656F16BF36853E99A86896DA56A2EAB10923BE82DBCD99DED1C6E5F3241132B6D5299276C1FB05F8A74CF82BFB6C781EFF00C7773FF08A5AE99797D61A83EAB1B43F629DED2E2DC24C08CC5895D558BE027258A8048F45FDAE0F8907ECE7A38F8EF83FB409F18DF7F677DBB67F687FC23BB5FCCDDE4FCBF66FB7F9DE479BC6CDFF0067FDD66803E60FF85E9E38FF00A0E7FE4A41FF00C45765F06F52F8ADF1F3E27683E03F0C6AB04DAEEB3334507DAA2B78A28D551A49247629C2A468EE700B10B85566201E03C3FF0013758F0CF85AFF0040B4B3F0F4D617BE679B2EA1E1BD3AF2ED77A046F2     
        141414141414141414FFC00011080438078003012200021101031101FFC4001F0000010501010101010100000000000000000102030405060708090A0BFFC400B5100002010303020403050504040000017D01020300041105122131410613516107227114328191A1082342B1C11552D1F02433627282090A161718191A25262728292A3435363738393A434445464748494A530F8907ECE7A38F8EF83FB409F18DF7F677DBB67F687FC23BB5FCCDDE4FCBF66FB7F9DE479BC6CDFF0067FDD66803E60FF85E9E38FF00A0E7FE4A41FF00C45765F06F52F8ADF1F3E27683E03F0C6AB04DAEEB3334507DAA2B78A28D551A49247629C2A468EE700B10B85566201E03C3FF0013758F0CF85AFF0040B4B3F0F4D617BE679B2EA1E1BD3AF2ED77A046F2
            `)
        if err != nil {
            fmt.Println("error: " + err.Error())
        }
    }
    

    log:

    2016/08/31 16:46:36 (1 row(s) affected) 2016/08/31 16:46:36 (1 row(s) affected) 2016/08/31 16:46:36 SELECT 1 2016/08/31 16:46:36 (1 row(s) affected) 2016/08/31 16:46:36 SELECT 2 -- long query FFD8FFE000104A46494600010100000100010000FFDB004300030202030202030303030403030 ... cut ... ... cut ... ... cut ... ... cut ... 78A28D551A49247629C2A468EE700B10B85566201E03C3FF0013758F0CF85AFF0040B4B3F0F4D617BE679B2EA1E1BD3AF2ED77A046F2

    2016/08/31 16:46:36 got error: read tcp 172.17.0.35:49981->191.235.193.75:1433: wsarecv: An existing connection was forcibly closed by the remote host. 2016/08/31 16:46:36 (1 row(s) affected) 2016/08/31 16:46:36 (1 row(s) affected) 2016/08/31 16:46:36 SELECT 2 -- long query FFD8FFE000104A46494600010100000100010000FFDB004300030202030202030303030403030 4050805050404050A070706080C0A0C0C0B0A0B0B0D0E12100D0E110E0B0B1016101113141515150C0F171816141812141514FFDB004301030404050 ... cut ... ... cut ... ... cut ... ... cut ... 78A28D551A49247629C2A468EE700B10B85566201E03C3FF0013758F0CF85AFF0040B4B3F0F4D617BE679B2EA1E1BD3AF2ED77A046F2

    2016/08/31 16:46:36 got error: read tcp 172.17.0.35:49982->191.235.193.75:1433: wsarecv: An existing connection was forcibly closed by the remote host. 2016/08/31 16:46:37 (1 row(s) affected) 2016/08/31 16:46:37 (1 row(s) affected) 2016/08/31 16:46:37 SELECT 2 -- long query FFD8FFE000104A46494600010100000100010000FFDB004300030202030202030303030403030 4050805050404050A070706080C0A0C0C0B0A0B0B0D0E12100D0E110E0B0B1016101113141515150C0F171816141812141514FFDB004301030404050 ... cut ... ... cut ... ... cut ... ... cut ... 78A28D551A49247629C2A468EE700B10B85566201E03C3FF0013758F0CF85AFF0040B4B3F0F4D617BE679B2EA1E1BD3AF2ED77A046F2

    2016/08/31 16:46:37 got error: read tcp 172.17.0.35:49983->191.235.193.75:1433: wsarecv: An existing connection was forcibly closed by the remote host.

    error: driver: bad connection

    so as we see there is 3 attempt to execute long query (why???) and we have "wsarecv error by unknown reason" for each of them

    tried to connect and run with SQL2005 - no problems at all. wireshark doesn't hep because connection with sql azure is encrypted

    got problem only with long queries (I'm inserting images into database like 0x123456....), all the rest of db routine working well

    opened by sunnyque 43
  • Queries that fail due to bad connections are not automatically retried (problem with Issue #275 changes)

    Queries that fail due to bad connections are not automatically retried (problem with Issue #275 changes)

    Describe the bug Go's connection pool includes logic to discard bad connections and retry queries that failed due to a bad connection. This logic does not work with the mssql driver, because mssql does not return the standard driver.ErrBadConn error.

    To Reproduce Unfortunately, this bug cannot be reproduced with a simple code snippet, because it involves simulating transient network errors. A general guide to reproducing it is:

    1. Start a debug session
    2. Set a breakpoint on checkBadConn in mssql.go
    3. Establish a database connection and execute a query via sql.go's DB.QueryContext
    4. Observe that the query completes successfully
    5. Disable your network
    6. Repeat the query
    7. Step through, but not out of, checkBadConn and observe that it returns a detailed error rather than the standard driver.ErrBadConn error
    8. Re-enable your network
    9. Step out of checkBadConn up to DB.QueryContext
    10. Observe that the logic to discard the bad connection and retry with another is bypassed. The error propagates immediately to the caller

    Expected behavior

    1. mssql immediately returns driver.ErrBadConn when it encounters a bad connection, not later when the connection is used again
    2. Go's connection pool logic is then able to immediately discard the bad connection and retry with a new connection

    Further technical details

    SQL Server version: any Operating system: any Table schema: any

    Additional context I read through Issue #275 and understand the desire for detailed error messages. I also understand the limitations of Go's connection pooling and am disappointed that Go's Issue #20807 languished unresolved after hopeful initial attention.

    However, the changes made to address #275 violate the contract with Go's connection pool, break Go's widely advertised automatic retry logic, and interfere with the pool's connection management (even though bad connections will eventually be purged). I believe that these costs are not worth the benefit of the detailed message.

    Are the mssql maintainers amenable to rethinking Issue #275 and discussions of alternate approaches?

    For handy reference, here is the code for sql.go's QueryContext. You can see that by not returning driver.ErrBadConn, mssql causes the connection pool logic to break out of the loop and return an error without making any retry attempts:

    func (db *DB) QueryContext(ctx context.Context, query string, args ...interface{}) (*Rows, error) {
    	var rows *Rows
    	var err error
    	for i := 0; i < maxBadConnRetries; i++ {
    		rows, err = db.query(ctx, query, args, cachedOrNewConn)
    		if err != driver.ErrBadConn {
    			break
    		}
    	}
    	if err == driver.ErrBadConn {
    		return db.query(ctx, query, args, alwaysNewConn)
    	}
    	return rows, err
    

    Similar retry logic exists throughout sql.go, and it is all broken by returning specific errors rather than driver.ErrBadConn.

    opened by fineol 42
  • Azure Active Directory Authentication is not supported?

    Azure Active Directory Authentication is not supported?

    Hi,

    I am able to connect to Azure database using SQL authentication but when I use Azure AD credentials I receive TLS handshake error when pinging the database: Cannot read handshake packet: read tcp: wsarecv: An existing connection was forcibly closed by the remote host.

    Connection String: sqlserver://user:[email protected]:1433?app+name=MyAppName&database=dbname&encrypt=true&hostNameInCertificate=%2A.database.windows.net&trustservercertificate=true

    Is the Azure AD supported or not?

    Thanks

    opened by zbynekbotlo 39
  • TLS Handshake failed for Azure SQL Server on an azure VM

    TLS Handshake failed for Azure SQL Server on an azure VM

    The format of my connection string:

    sqlserver://<username>:<password>@<servername>.database.windows.net:1433?TrustServerCertificate=false&database=master&encrypt=true&hostNameInCertificate=%2A.database.windows.net
    

    It works on my local machine. But It just fails on an Azure VM, with following error message:

    TLS Handshake failed: x509: certificate is valid for tr34.eastus1-a.worker.database.windows.net, *.tr34.eastus1-a.worker.database.windows.net, not *.database.windows.net
    

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-architecture I think this issue is caused by the different connection policies between inside Azure machines and outside Azure machines.

    opened by zhongyi-zhang 37
  • MSSQL connection started failing on 2/6/20

    MSSQL connection started failing on 2/6/20

    Describe the bug

    In production, our ETLs started failing yesterday with "Login error: mssql: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

    One of the master commits yesterday, either bbfc9a55622eeb8e47808e0f7270efcf5ae3dce2 or 3f988484a57ee992b0febb2257ca939c58abfd2a started causing this issue.

    Reproduce We're just doing a simple sql.Open() using this driver and a connection string formatted like "server=%s;user id=%s;password=%s;port=%s"

    Fix for now I am manually checking out your last "known" working commit 0f454e2ecd6ad8fb4691cdbf10e399e05ca03784 in our docker container to run our ETLs.

    opened by arjitj2 32
  • go1.8: support for additional features

    go1.8: support for additional features

    Many new important features will land in go1.8. Please see document: https://docs.google.com/document/d/1F778e7ZSNiSmbju3jsEWzShcb8lIO4kDyfKDNm4PNd8/edit?usp=sharing for the full list.

    Because these features are new we want drivers to implement them before they are released. Ideally they should be implemented by 2016-12-12 to allow for any driver provided feedback from implementation if necessary.

    opened by kardianos 26
  • WIP table valued parameters (TVP)

    WIP table valued parameters (TVP)

    See: https://github.com/denisenkom/go-mssqldb/issues/263

    Heyo. This is very work in progress. doesn't work. isn't close to working. but, its a start. putting here in case anyone has any hints or tips(@denisenkom @kardianos @judwhite)

    I am muddling my way through, looking closely at the python driver and comparing output.

    If anyone wants to put me out of my misery and finish it, please do. I'm even happy to pay you to do it :) (please email elliot - at - identitii.com)

    It's failing with "panic: mssql: The incoming tabular data stream (TDS) protocol stream is incorrect. The stream ended unexpectedly." which is better than nothing. sort of.

    opened by elliots 22
  • SQL Server usernames and passwords with certain characters fail

    SQL Server usernames and passwords with certain characters fail

    SQL Server allows for most characters in the username and password. However, if a user changes their username and/or password to any of the following cases:

    • Contains a semi-colon
    • Starts with a space
    • Ends with a space

    Then any access from a Go program using this library will fail with this error: Login error: mssql: Login failed for user 'xyz'.

    opened by tartale 21
  • Output parameter support

    Output parameter support

    Hi guys, thanks for this awesome lib. It really helps me a lot for migrating a system from MS stack to go.

    I need OUTPUT parameter support for my project. So, I digg into things like https://msdn.microsoft.com/en-us/library/dd303881.aspx and http://bitbucket.org/kardianos/rdb and get a output parameter PoC working with go-mssqldb

    My implementation is like:

    row, err := conn.Query("exec [SpWithOutputParam] ? OUTPUT", XXX) ... parseParams will identify "OUTPUT" keyword and change paramTypeInfo / makeDecl etc.

    However, output parameter is not supported by database/sql interface at all. So, I'm now wondering how should I support output param with proper API before sending a PR.

    May I know go-mssqldb's opinion about output param? Thank.

    enhancement 
    opened by Wuvist 21
  • too many read tcp  xxx> xxx i/o timeout

    too many read tcp xxx> xxx i/o timeout

    when i use sql server to save data.but,when the app run some day.there is too many read tcp xxx> xxx i/o timeout.does i use it error?

    my code:

    func getSQLConn(databaseUri string) (*sql.DB, error) {
    	conn, ok := connections[databaseUri]
    	if ok {
    		return conn,nil
    	}
    	sqlLock.Lock()
    	defer sqlLock.Unlock()
    	conn, err := sql.Open("mssql", fmt.Sprintf(connectUri, databaseUri))
    	if err != nil {
    		return nil, err
    	}
    	conn.SetMaxOpenConns(config.EveryDBConnectionSize)
    	conn.SetMaxIdleConns(config.EveryDBConnectionSize)
    	connections[databaseUri] = conn
    	return conn, nil
    }
    
    func insert(conn *sql.DB, action *ContentCommitAction) error {
    	stmt, err := conn.Prepare(fmt.Sprintf(insertSQL, config.SqlDataDb, action.taskName))
    	if err != nil {
    		fmt.Printf("prepare stmt error,    info:%s\n", err.Error())
    		return err
    	}
    	defer stmt.Close()
    
    	_, err = stmt.Exec(action.content.Id, action.content.Url, action.content.Compress, action.content.Checksum, action.content.Data)
    	if err != nil {
    		if strings.Contains(err.Error(), "PRIMARY KEY") {
    		} else {
    			fmt.Printf("stmt exec error:%s    info:%s\n", action.taskName, err.Error())
    			return err
    		}
    	}
    	return nil
    }
    
    sqlConn, err := getSQLConn(databaseUri)
     if err != nil {
        fmt.Printf("task content commit service get sqlConnection error:,  info:%s\n", err)
        return
    }
    err = insert(sqlConn, action)
    

    i nerver close the sql.DB

    opened by haochun 17
  • Compilation error

    Compilation error

    Describe the bug Hi there, Your module requires github.com/golang-sql/sqlexp This module had no go.mod file until now, and one was added today (https://github.com/golang-sql/sqlexp/blob/master/go.mod) for go 1.16. After running go get -u ./... on my module (which requires yours) I saw:

    $ go get -u ./...
    go: upgraded github.com/golang-sql/sqlexp v0.0.0-20170517235910-f1bb20e5a188 => v0.0.0-20220518152126-d42ba59f5c11
    ...
    

    Trying to build my module, I get the following compilation error:

    # github.com/denisenkom/go-mssqldb
    ../../../../pkg/mod/github.com/denisenkom/[email protected]/token.go:790:79: cannot use info.Message (variable of type string) as type fmt.Stringer in struct literal:
    	string does not implement fmt.Stringer (missing String method)
    
    Compilation finished with exit code 2
    

    To Reproduce See above. Expected behavior Code that compiles. Thank you!

    Further technical details

    Operating system: Ubuntu 20.04.4 Golang version: go1.18.2 linux/amd64

    Additional context N/A

    opened by tsipo 16
  • Expose driverInstance and driverInstanceNoProcess or processQueryText for APM tracing

    Expose driverInstance and driverInstanceNoProcess or processQueryText for APM tracing

    I'm enabling dd-trace-go over "mssql" driver.

    It works by creating a tracing wrapping over the driver. In order to do this, you have to initialize a Driver and register it in the datadog package.

    sqltrace.Register(driverName, &mssql.Driver{})

    The issue i'm having is that statements with variables stopped working:

    Incorrect syntax near '?'."

    I know "?" for placeholder is not mssql standard but it used to work before tracing. After some digging I found that defining processQueryText as true correctly fixes this issue.

    As I cannot change every statement in the codebase, I am enabling tracing with the help of reflection:

    driver := &mssql.Driver{}
    dv := reflect.ValueOf(driver).Elem().FieldByName("processQueryText")
    dv = reflect.NewAt(dv.Type(), unsafe.Pointer(dv.UnsafeAddr())).Elem()
    
    if dv.IsValid() && dv.CanSet() {
        dv.SetBool(true)
    }
    sqltrace.Register("mssql", driver)
    

    Maybe it makes sense to expose the driver instances?

    opened by nacho692 2
  • Bug fix: Mark connection as not good when error on cancellation confirmation

    Bug fix: Mark connection as not good when error on cancellation confirmation

    This pr fixes bug when we were not able to get cancelation confirmation but connection is not marked as bad and returns to pool, so next time this connection is taken from pool, we get the same error instead of opening new good connection

    Example:

    r, err := db.QueryContext(ctx, query) <- context expired, then we got cancelation confirmation error

    ...

    r, err := db.QueryContext(ctx, query) <- we get same error because we are still using bad connection instead of opening new one

    opened by zcolleen 1
  • msdsn package should support azuresql:// driver name

    msdsn package should support azuresql:// driver name

    Attempting to switch to the new AD connector in azuread package instead of our in-house homegrown connector. When using msdsn.Parse with azuresql:// it parses the string the wrong way.

    opened by dagss 5
  • How to give a NULL time type(such as date,time,etc..) for TVP value?

    How to give a NULL time type(such as date,time,etc..) for TVP value?

    Hi @denisenkom , In a table, a filed type is Time type(such as Date, time, etc.), but the value is NULL, how to give a NULL value for them when using TVP? Thanks!

    opened by zhanghaiyang9999 0
  • Identity column in TVP

    Identity column in TVP

    Hi, is it possible to insert into TVP which has identity field.

    Now I get "INSERT into an identity column not allowed on table variables"

    Code example

    db, _ := createDbConn() defer db.Close()

    createTVP := `
    CREATE TYPE dbo.TestType AS TABLE
    (
    	id INT IDENTITY(1,1) PRIMARY KEY,
    	counter INT
    );`
    _, err := db.Exec(createTVP)
    
    createProc := `
    CREATE PROCEDURE dbo.TestProc
    	@TVP dbo.TestType READONLY
    AS
    	SELECT * from @TVP
    `
    _, err = db.Exec(createProc)
    
    type TestType struct {
    	Counter int
    }
    
    TestTableType := []TestType{
    	{
    		Counter: 1,
    	},
    	{
    		Counter: 3,
    	},
    }
    
    tvpType := mssql.TVP{
    	TypeName: "TestType",
    	Value:    TestTableType,
    }
    
    _, err = db.Exec("exec dbo.TestProc @TVP;", sql.Named("TVP", tvpType))
    
    if err != nil {
    	fmt.Println(err)
    }
    

    Error: mssql: Trying to pass a table-valued parameter with 1 column(s) where the corresponding user-defined table type requires 2 column(s).

    Using SQL Server Directly: ` declare @TVP dbo.TestType

    insert @TVP ([counter])
    values (1),(2)

    exec dbo.TestProc @TVP ` Works fine

    opened by saesols 1
Releases(v0.10.0)
Owner
null
Go MySQL Driver is a MySQL driver for Go's (golang) database/sql package

Go-MySQL-Driver A MySQL-Driver for Go's database/sql package Features Requirements Installation Usage DSN (Data Source Name) Password Protocol Address

Go SQL Drivers 12.8k Nov 24, 2022
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
Go Sql Server database driver.

gofreetds Go FreeTDS wrapper. Native Sql Server database driver. Features: can be used as database/sql driver handles calling stored procedures handle

minus5 106 Oct 19, 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 Nov 27, 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 183 Nov 18, 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 Nov 24, 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 19, 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 594 Nov 4, 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 28 Sep 26, 2022
SAP (formerly sybase) ASE/RS/IQ driver written in pure go

tds import "github.com/thda/tds" Package tds is a pure Go Sybase ASE/IQ/RS driver for the database/sql package. Status This is a beta release. This dr

Thomas 52 Aug 6, 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 403 Nov 25, 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.3k Nov 23, 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
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 587 Nov 19, 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 19, 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 20, 2022