Kee Golang database Query encapsulated pits

This is a creation in Article, where the information may have evolved or changed.

Previous article

golang接触也有一段时间，项目中有用到web api，基本上就是post json格式的，本想用java来写，刚下手想到java太臃肿，各种繁琐。觉得用golang小试一把，于是github一把，还是发现很多go rest 插件，选了一个https://github.com/ant0ine/go-json-rest一根烟后，go-json-rest demo开始跑起来，使用curl命令模拟了一把，正确运行。关于go-json-rest的使用，本文不做描述，官方文档有很详细的说明https://github.com/ant0ine/go-json-rest

Body

这是封装数据库的连接的核心代码（其实大部分是网上copy的）此方法是将sql的查询结果封装成json格式输出（当然是方便post返回值）

funcOpendbstring (SqlStringstring)string{conn: = Opendb ()deferConn. Close () stmt, err: = conn. Prepare (SqlString)ifErr! =Nil{FMT. Println ("Query Error", err)return "Error"}deferstmt. Close () rows, err: = stmt. Query ()ifErr! =Nil{FMT. Println ("Query Error", err)return "Error"}deferRows. Close ()//Get column namesColumns, err: = rows. Columns ()ifErr! =Nil{Panic(Err. Error ())//Proper error handling instead of panic in your app}//Make a slice for the valuesValues: = Make([]sql. Rawbytes,Len(columns))//rows. Scan wants ' []interface{} ' as a argument, so we must copy the    //references into such a slice    //See Http://code.google.com/p/go-wiki/wiki/InterfaceSlice for detailsScanargs: = Make([]Interface{},Len(values)) forI: =RangeValues {Scanargs[i] = &values[i]}//Fetch rows    varJsonstringstringJsonstring ="{\" timestamp\ ": \" "+ Time. Now (). Format ("2006-01-02 15:04:05") +"\", \ "data\": ["Allcount: =0     forRows. Next () {jsonstring + ="{"        //Get rawbytes from DataErr = rows. Scan (Scanargs ...)ifErr! =Nil{Panic(Err. Error ())//Proper error handling instead of panic in your app}// now does something with the data.        //Here we just print each column as a string.        varValuestring         forI, col: =RangeValues {//Here we can check if the value is nil (NULL value)            ifCol = =Nil{value ="NULL"}Else{value =string(COL)}//FMT. Println (Columns[i], ":", value)            ifi = =Len(Values)-1{jsonstring + ="\""+ Columns[i] +"\":\""+ Value +"\""}Else{jsonstring + ="\""+ Columns[i] +"\":\""+ Value +"\","}//FMT. Println (":", I, ":", col, Len (values))}//fmt. PRINTLN ("Replace before:", jsonstring, ":", Len (jsonstring))        //jsonstring = strings. Replace (jsonstring, ",", "", Len (jsonstring))        //fmt. PRINTLN ("Replace after:", jsonstring, ":", Len (jsonstring))        //FMT. Println ("-----------------------------------", Allcount)Jsonstring + ="},"allcount++}ifAllcount >0{jsonstring = Substr (jsonstring,0,Len(jsonstring)-1)} jsonstring + ="]}"    ifErr = rows. ERR (); Err! =Nil{Panic(Err. Error ())//Proper error handling instead of panic in your app}returnJsonstring}

Next to log the results of the SQL query pit

There are many golang on the Internet to query the database of chestnuts, but are simple to use
And I have been in the pit several times, described as follows

1. Pit of the time function

Because of the datetime defined in the SQL field, the error is run directly with GETDATE ().

erroron0time.Time -> *sql.RawBytes

OK, take a root, convert the datetime to char, so

returndata := openDbString("select top 1 CONVERT(CHAR(23), createtime, 121) as createtime from ATRes ")    fmt.Println("result:", returndata)

The returned results are as follows

 {"timestamp"2015-06-11 11:51:22","data":[{"createtime":"2015-05-06 1"}]}

The result is that time has been truncated, try again

returndata := openDbString("select top 1 CONVERT(CHAR(36), createtime, 121) as createtime from ATRes ")    fmt.Println("result:", returndata)

The returned results are as follows, this time OK

{"timestamp"2015-06-11 11:53:53","data":[{"createtime":"2015-05-06 16:15:42"}]}

2. Long text truncated pits

returndata := openDbString("select top 1 data from ATRes ")    fmt.Println("result:", returndata)

The returned results are as follows

  {"timestamp"2015-06-11 11:57:10","data":[{"data":"http://jixieshi999.github.io/ilife/images/mamabeat."}]}

The data field is actually a URL to the image in the database, but the URL is truncated in the output (. jpg is missing)
Guess, may be SQL data type and Golang read data type inconsistency caused, because SQL inside data is nvacher (100) type, and output is truncated length, brother I decisively counted the length of data, just is 50,so 50 =? 100/2
Modify query statement validation conjecture again

returndata := openDbString("select top 1 cast(data as CHAR(200)) as datacopy,data from ATRes ")    fmt.Println("result:", returndata)

The returned results are as follows

  {"timestamp"2015-06-11 12:01:54","data":[{"datacopy":"http://jixieshi999.github.io/ilife/images/mamabeat.jpg。。。。。。。。。。。。。。。","data":"http://jixieshi999.github.io/ilife/images/mamabeat."}]}

Note that some of the spaces behind the datacopy I use. Instead of convenient viewing, this result is still not satisfied with my appetite, there can be so many spaces in the JSON, this unscientific
So I daoteng another one.

returndata := openDbString("select top 1 rtrim(cast(data as CHAR(200))) as datacopy,data from ATRes ")    fmt.Println("result:", returndata)

The return result is as follows, finally OK

 {"timestamp"2015-06-11 12:05:22","data":[{"datacopy":"http://jixieshi999.github.io/ilife/images/mamabeat.jpg","data":"http://jixieshi999.github.io/ilife/images/mamabeat."}]}

About Golang Get the current time of the pit, Baidu has a

Postscript

Summarize down Golang for database support or not Java convenient, also have my understanding of Golang not enough in-depth problem
But it's so convenient to use Golang to do the RESTful API I said earlier.
The smelly, long source code will not be uploaded.