xlin March 18, 2015 at 10:50

Working with MS SQL Database with Go Tools for Beginners

From the sandbox

At the moment, the Go language is becoming more and more popular every day. On Habré, articles on the topic that are interesting to read not only to the burnt specialists-programmers, but also to system administrators are increasingly appearing.

I work as a system administrator and show interest in Go, as we often have to write scripts in bash (shell) to automate our actions and increase the time for eating cookies and pouring coffee into our feeble organism.

I would like to share a little experience on how a programmer did not write a small program on Go.

Let's get started.

The company in which I work has a certain program that is used by everyone. It is based on a database on the MS SQL 2008 server. At a wonderful moment (not so much for me), the boss declares that we need to write a program in which the right people will look at certain data from that database.

There are database / sql and code.google.com/p/odbc packages for working with databases . We will use them. Also, do not forget that you need to install the ODBC driver, through which we will work with MS SQL. For Windows, this is done through Odbcad32.exe by adding a client DSN. For Linux, it’s a bit more complicated, but this is not part of the article. I think Google will help you.

This is how we get the list of packages that we will use in the first stage.

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

We are trying to connect to the database and complete the request in order to get the data we need.

package main
import (
	_ "code.google.com/p/odbc"
	"database/sql"
	"fmt"
	"log"
)
var (
	name_otdel string
	query      string
)
func main() {
	db, err := sql.Open("odbc", "DSN=DBS0")
	if err != nil {
		fmt.Println("Error in connect DB")
		log.Fatal(err)
	}
	query = "select t.DepartmentNAME from dbo.oms_Department t where t.rf_LPUID = 1078"
	rows, err := db.Query(query)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		if err := rows.Scan(&name_otdel); err != nil {
			log.Fatal(err)
		}
		fmt.Println(name_otdel)
	}
	defer rows.Close()
}

As you can see from the program, everything is not so complicated and even people like me (little understanding of programming) can gradually learn to write in Go.

True, there is one caveat. When you deploy a database server, MS SQL Server usually does not touch the encoding settings, and it costs windows1251 in most places. This causes some inconvenience, since everything works in Go in UTF8. In this regard, we will use additional packages that are not part of Go to transcode windows1251 into UTF8.

Therefore, if you run our program, you will see a belebah in the console instead of Russian letters. To avoid this, let's use the packages golang.org/x/text/encoding/charmap, golang.org/x/text/transform

Full program with conversion from cp1251 to UTF8

package main
import (
	"database/sql"
	"fmt"
	"io/ioutil"
	"log"
	"strings"
	"golang.org/x/text/encoding/charmap"
	"golang.org/x/text/transform"
	_ "code.google.com/p/odbc"
)
var (
	name_otdel string
	name_utf   string
	query      string
)
func main() {
	db, err := sql.Open("odbc", "DSN=DBS0")
	if err != nil {
		fmt.Println("Error in connect DB")
		log.Fatal(err)
	}
	query = "select t.DepartmentNAME from dbo.oms_Department t where t.rf_LPUID = 1078"
	rows, err := db.Query(query)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		if err := rows.Scan(&name_otdel); err != nil {
			log.Fatal(err)
		}
		sr := strings.NewReader(name_otdel)
		tr := transform.NewReader(sr, charmap.Windows1251.NewDecoder())
		buf, err := ioutil.ReadAll(tr)
		if err != nil {
			log.Fatal(err)
		}
		name_utf = string(buf)
		fmt.Println(name_utf)
	}
	defer rows.Close()
}

End result:

We see and rejoice that everything is being read and the data is pulled out of the database. I want to note that if there are two or more columns in the query, for example, last name, first name and middle name, then do not forget to specify them in rows.Scan in the same order as in the SQL query.

What have we learned after reading this article? Got basic knowledge for working with databases. Further, it will be possible to process, sort or display them in a browser, etc. etc. This article is not intended for experienced programmers and is written specifically for people who are just starting to learn a rather interesting Go language.

Tags:

Working with MS SQL Database with Go Tools for Beginners

Also popular now: