Easysoft Data Access

How do I connect Go on Linux to an ODBC Database?

Article:
01045
Last Reviewed:
17th July 2014
Revision:
2

The components needed to connect Go to an ODBC database are:

Go -> Go driver for ODBC databases -> unixODBC -> ODBC driver -> ODBC database

There are a number of Go drivers that will connect Go on Linux to an ODBC database. This article covers:

All three Go drivers are compatible with unixODBC and this is the Driver Manager that is described in the examples in this article. By default, the Go drivers will attempt to use the unixODBC package that is included with the operating system (i.e. the one that will be installed in one of the standard system directories for program libraries). To minimise disruption to your system, you may prefer to use the unixODBC Driver Manager included with the operating system. However, this version may be considerably older than the current version of unixODBC, and one that is not recommended for use with the Go driver. For example, the author of the odbc Go driver recommends unixODBC 2.3.1+. The instructions in this article therefore cover building a Go driver against a version of unixODBC that is installed in a non standard location. For example, a unixODBC Driver Manager that is included with your ODBC driver or one you have built yourself.

The ODBC driver used throughout the article is Easysoft's SQL Server ODBC driver. To use a different ODBC driver with odbc / mgodbc, replace the SQL Server ODBC driver specific connection string with one that is appropriate for your driver.

odbc and mgodbc (unlike go-odbc) implement Go's database/sql and database/sql/driver packages. The database/sql package provides a generic interface around SQL (or SQL-like) databases and must be used in conjunction with a database driver.

Before You Install the Go Driver

To connect your Go application to your target database, you need to obtain an ODBC driver for your database and Linux platform. Easysoft produce a range of Linux ODBC drivers, all of which are compatible with the unixODBC Driver Manager. We used our SQL Server ODBC driver to connect Go to a SQL Server database. Although the following instructions are based around the Easysoft ODBC driver, the process is the same for any unixODBC compatible ODBC driver.

  1. Install the ODBC driver under unixODBC.

    The SQL Server ODBC driver installation script automatically installs the driver under unixODBC (i.e. it places entries for the driver in the unixODBC configuration file odbcinst.ini, the location of which can be found by typing odbcinst -j).

  2. Add an ODBC data source that specifies the connection details for your target database.

    SYSTEM ODBC data sources (accessible to all users on the Linux machine) are stored in odbc.ini. USER data sources are stored in .odbc.ini. (Again, use odbcinst -j to find out the location of these files.)

    The SQL Server ODBC driver creates a sample data source in odbc.ini, and we edited this data source to connect to our SQL Server instance:

    [SQLSERVER_SAMPLE]
    Driver=Easysoft ODBC-SQL Server
    Server=mymachine\sqlexpress
    Database=AdventureWorks
    User=myuser
    Password=mypassword
    
  3. Set and export any environment variables needed by your ODBC driver (for example, LD_LIBRARY_PATH).
  4. Use isql, the sample ODBC application included with unixODBC, to verify that you can connect to your data source. For example:
    $ isql -v SQLSERVER_SAMPLE

    If you are unable to connect, refer to this article for assistance.

odbc

To download and install odbc use the go get command. Remember to set and export the environment variables required by Go before running this command. For example:

$ export GOROOT=$HOME/golang/go
$ export PATH=$PATH:$GOROOT/bin
$ export GOPATH=$HOME/golang/packages
$ go get code.google.com/p/odbc

odbc includes a test suite for the SQL Server database, and if connecting to this database you should run the tests to ensure that you can connect, read / write data, call stored procedures etc. from go. We used this command line to test Easysoft's SQL Server ODBC driver:

$ cd $HOME/golang/src/code.google.com/p/odbc
$ go test -mssrv=192.0.2.1:50217 -msdb=AdventureWorks -msuser=sa -mspass=p455w0rd \
-msdriver="Easysoft ODBC-SQL Server" -v -run=MS

Note that you need to connect a SQL Server database where the user specified on the command line has permission to create and drop tables and stored procedures.

Initially, the SQL Server ODBC driver failed some tests, where string data was inserted into SQL Server. Using the SQL Server ODBC driver's iconv conversion mechanism to convert the data from UTF-8 solved this. To do this we set the driver's Client_Cset in the SQL Server test script, mssql_test.go:

if len(*msuser) == 0 {
	params["trusted_connection"] = "yes"
} else {
	params["uid"] = *msuser
	params["pwd"] = *mspass
	params["Client_CSet"] = "UTF-8"
}

After doing this the SQL Server ODBC driver passed all tests.

By default, odbc attempts to link against a unixODBC Driver Manager library that is located in one of the standard system directories for program libraries, such as /usr/local/lib. To link odbc against a version of unixODBC that is located in a non-standard directory, append -Lunixodbcdir to the #cgo directive in $GOPATH/src/code.google.com/p/odbc/api/api_unix.go that specifies the unixODBC library. For example:

// #cgo linux LDFLAGS: -lodbc -L/usr/local/easysoft/unixODBC/lib

If your unixODBC header files (for example, sql.h) are not installed in a standard location (for example, /usr/local/include), you need to tell odbc where to find them. Do this by adding this #cgo directive to api_unix.go #cgo linux CFLAGS: -Iincludedir. For example:

// #cgo linux CFLAGS: -I/usr/local/easysoft/unixODBC/include

To create a compiled Go package for odbc under $GOPATH, run the following command:

$ go install code.google.com/p/odbc

We used this Go code to access SQL Server:

package main

import (
	_ "code.google.com/p/odbc"
	"database/sql"
        "log"
)

func main() {
        // Replace the DSN value with the name of your ODBC data source.
	db, err := sql.Open("odbc",
		"DSN=SQLSERVER_SAMPLE")
	if err != nil {
		log.Fatal(err)
	}

	var (
		id int
		name string
	)

        // This is a SQL Server AdventureWorks database query.
	rows, err := db.Query("select departmentid, name from humanresources.department where departmentid = ?", 1)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	for rows.Next() {
		err := rows.Scan(&id, &name)
		if err != nil {
			log.Fatal(err)
		}
		log.Println(id, name)
	}
	err = rows.Err()
	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()
}

mgodbc

To download and install mgodbc it may be possible to use the go get command. Remember to set and export the environment variables required by Go before running this command. For example:

$ export GOROOT=$HOME/golang/go
$ export PATH=$PATH:$GOROOT/bin
$ export GOPATH=$HOME/golang/packages
$ go get bitbucket.org/miquella/mgodbc

Using this method failed with an SSL certificate error, so we had to try an alternative approach:

$ cd $GOPATH/src
$ mkdir -p bitbucket.org/miquella
$ cd bitbucket.org/miquella
$ hg clone https://bitbucket.org/miquella/mgodbc
$ cd mgodbc
$ ls
LICENSE  mgodbc.go

By default, mgodbc attempts to link against a unixODBC Driver Manager library that is located in one of the standard system directories for program libraries, such as /usr/local/lib. To link odbc against a version of unixODBC that is located in a non-standard directory, append -Lunixodbcdir to the #cgo directive in mgodbc.go that specifies the unixODBC library. For example:

#cgo linux LDFLAGS: -lodbc -L/usr/local/easysoft/unixODBC/lib

If your unixODBC header files (for example, sql.h) are not installed in a standard location (for example, /usr/local/include), you need to tell mgodbc where to find them. Do this by adding this #cgo directive to mgodbc.go #cgo linux CFLAGS: -Iincludedir. For example:

#cgo linux CFLAGS: -I/usr/local/easysoft/unixODBC/include

To create a compiled Go package for odbc under $GOPATH, run the following command:

$ go install bitbucket.org/miquella/mgodbc

As both mgodbc and odbc implement Go's generic SQL database interface, we could use the same Go script to test them. We just had to change the driver from odbc to mgodbc:

package main

import (
        _ "bitbucket.org/miquella/mgodbc"
	"database/sql"
        "log"
)

func main() {
        // Replace the DSN value with the name of your ODBC data source.
	db, err := sql.Open("mgodbc",
		"DSN=SQLSERVER_SAMPLE")
.
.
.

go-odbc

go-odbc enables a Go program on Linux to access a database whose ODBC driver is installed under the unixODBC Driver Manager. For example, we connected a Go program on Linux to a SQL Server database by using go-odbc/unixODBC with our SQL Server ODBC driver.

To install go-odbc, use a Git or Subversion client to copy the go-odbc repository to the Linux machine on which you have installed Go or download the go-odbc .zip file under your $GOPATH/src directory. For example:

$ cd $GOPATH/src
$ git clone git://github.com/weigj/go-odbc.git odbc

–Or–

$ cd $GOPATH/src
$ mkdir odbc
$ svn co https://github.com/weigj/go-odbc/trunk odbc

–Or–

$ cd $GOPATH/src
$ unzip /tmp/go-odbc-master.zip
$ mv go-odbc-master odbc

Before you can compile go-odbc, you need to have the unixODBC Driver Manager installed on your Linux machine. Most Linux distributions include the unixODBC Driver Manager (you need both the Driver Manager and its header files). unixODBC is also available as source code. All Easysoft ODBC drivers for Linux and Unix platforms include unixODBC.

By default, go-odbc attempts to link against a unixODBC Driver Manager library that is located in one of the standard system directories for program libraries, such as /usr/local/lib. To link go-odbc against a version of unixODBC that is located in a non-standard directory, append -Lunixodbcdir to the #cgo directive in $GOPATH/src/odbc/odbc.go that specifies the unixODBC library. For example:

#cgo linux LDFLAGS: -lodbc -L/usr/local/easysoft/unixODBC/lib

If your unixODBC header files (for example, sql.h) are not installed in a standard location (for example, /usr/local/include), you need to tell go-odbc where to find them. Do this by adding this #cgo directive to odbc.go #cgo linux CFLAGS: -Iincludedir. For example:

#cgo linux CFLAGS: -I/usr/local/easysoft/unixODBC/include

To create a compiled Go package for go-odbc under $GOPATH, run the following commands:

$ cd odbc
$ go install

Use the following sample Go program to connect to your database from Go. Edit the connection string with the relevant values for your data source.

package main

import (
    "odbc"
    "log"
)

// 
// CREATE TABLE USERS( ID INTEGER, USERNAME VARCHAR( 50 ));
// INSERT INTO USERS VALUES( 1, 'admin' );
// INSERT INTO USERS VALUES( 2, 'sid' );
// INSERT INTO USERS VALUES( 3, 'joe' );
//

func main() {
    conn, err := odbc.Connect("DSN=MYDSN")
    if err != nil {
        log.Fatal(err)
    }

    stmt, err := conn.Prepare("SELECT * FROM USERS WHERE USERNAME = ? OR USERNAME = ? ")
    if err != nil {
        log.Fatal(err)
    }

    err = stmt.Execute("admin", "sid" )
    if err != nil {
        log.Fatal(err)
    }

    nfields, err := stmt.NumFields();
    if err != nil {
        log.Fatal(err)
    }

    println( "Number of fields", nfields );

    for i := 0; i < nfields; i ++ {
        field, err := stmt.FieldMetadata( i + 1 );
        if err != nil {
            log.Fatal(err)
        }
        println( "\tField:", i + 1, "Name:", field.Name );
    }

    println( "" );

    row, err := stmt.FetchOne()
    if err != nil {
        log.Fatal(err)
    }

    for row != nil {

        println( "Row" )

        ival := row.GetInt( 0 )
        println( "\tField:", 1, "Int:", ival );

        sval := row.GetString( 1 )
        println( "\tField:", 2, "String:", sval );

        row, err = stmt.FetchOne()
        if err != nil {
            log.Fatal(err)
        }
    }

    stmt.Close()
    conn.Close()
}

Set and export the environment variables required by Go and then run the sample program. For example:

$ export GOROOT=$HOME/golang/go
$ export PATH=$PATH:$GOROOT/bin
$ export GOPATH=$HOME/golang/packages
$ go run odbc.go
Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)