The components needed to connect Go to an ODBC database are:
There are a number of Go drivers that can 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's described in the examples in this article. By default, the Go drivers attempt to use the unixODBC package that's included with the operating system (the one that's 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's installed in a non-standard location. For example, a unixODBC Driver Manager that's included with your ODBC driver or one you have built yourself.
The ODBC driver used throughout the article is Easysoft'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.
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.
The SQL Server ODBC driver installation script automatically installs the driver under unixODBC (that is, it places entries for the driver in the unixODBC configuration file odbcinst.ini, the location for which can be found by typing odbcinst -j).
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
LD_LIBRARY_PATH).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're unable to connect, refer to this article for assistance.
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 github.com/alexbrainman/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 and write data, call stored procedures 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's 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's 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) aren't 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()
}
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's 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's 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) aren't 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 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 and 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've 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's 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's 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) aren't 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
© 1993 - 2024 Easysoft Limited. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other trademarks and registered trademarks appearing on easysoft.com are the property of their respective owners.
