Function in Go to execute select query on database and return json output

Question

I am writing a function in Go to execute select query on database.

Input: String e.g. "Select id, name, age from sometable" This query changes everytime.

Output: Output of select query in json format.

Sample Expected output: {"Data":[{"id":1,"name":"abc", "age":40},{"id":2,"name":"xyz", "age":45}]}

Sample Actual output: {"Data":[[1,"abc",40],[2,"xyz",45]]}

Instead of i.e. column_name:value, I get only values. How do I get the expected output?

func executeSQL(queryStr string) []byte {
connString := createConnectString()
conn, err := sql.Open("mssql", connString)
if err != nil {
    log.Fatal("Error while opening database connection:", err.Error())
}
defer conn.Close()

rows, err := conn.Query(queryStr)
if err != nil {
    log.Fatal("Query failed:", err.Error())
}
defer rows.Close()

columns, _ := rows.Columns()
count := len(columns)

var v struct {
    Data []interface{} // `json:"data"`
}

for rows.Next() {
    values := make([]interface{}, count)
    valuePtrs := make([]interface{}, count)
    for i, _ := range columns {
        valuePtrs[i] = &values[i]
    }
    if err := rows.Scan(valuePtrs...); err != nil {
        log.Fatal(err)
    }
    v.Data = append(v.Data, values)
}
jsonMsg, err := json.Marshal(v)
return jsonMsg
}

Akshay Bhat · Accepted Answer · 2017-04-12 11:36:07Z

Got the solution. Here is what I did.

func executeSQL(queryStr string) []byte {
connString := createConnectString()
conn, err := sql.Open("mssql", connString)
if err != nil {
    log.Fatal("Error while opening database connection:", err.Error())
}
defer conn.Close()

rows, err := conn.Query(queryStr)
if err != nil {
    log.Fatal("Query failed:", err.Error())
}
defer rows.Close()

columns, _ := rows.Columns()
count := len(columns)

var v struct {
    Data []interface{} // `json:"data"`
}

for rows.Next() {
    values := make([]interface{}, count)
    valuePtrs := make([]interface{}, count)
    for i, _ := range columns {
        valuePtrs[i] = &values[i]
    }
    if err := rows.Scan(valuePtrs...); err != nil {
        log.Fatal(err)
    }

//Created a map to handle the issue
    var m map[string]interface{}
    m = make(map[string]interface{})
    for i := range columns {
        m[columns[i]] = values[i]
    }
    v.Data = append(v.Data, m)
}
jsonMsg, err := json.Marshal(v)
return jsonMsg
}

Let me know if there exists a better solution.

Cetin Basoz · Accepted Answer · 2017-04-12 12:10:24Z

This code is directly from my "sandbox" for MsSQL (using denisenkom/go-mssqldb and jmoiron/sqlx) - I think it helps showing different approaches and probably QueryIntoMap is what you were looking for:

package main

import (
    "log"
    "fmt"
    _ "github.com/denisenkom/go-mssqldb"
    "time"
    "github.com/jmoiron/sqlx"
    "encoding/json"
)

type Customer struct {
    CustomerId string `db:"customerID" json:"customer_id"`
    Company    interface{} `db:"companyName" json:"company_name"`
    Contact    interface{} `db:"contactName" json:"contact_name"`
}

func main() {
    connection := "server=192.168.55.3\\SqlExpress2012;database=Northwind;user id=me;Password=secret"

    //QueryIntoMap(connection)
    ScanIntoSlice(connection)
}

func QueryIntoMap(connection string) {

    fmt.Println("QueryIntoMap sample")
    fmt.Println("--------------------")

    sel := `select customerId, companyName, contactName
    from customers
    where customerId = :id`

    values := make(map[string]interface{})


    db, err := sqlx.Open("mssql", connection)
    //db.MapperFunc(strings.ToUpper)
    e(err)
    defer db.Close()
    tx := db.MustBegin()
    stmt, err := tx.PrepareNamed(sel)
    e(err)

    stmt.QueryRowx(map[string]interface{}{"id": "BONAP"}).MapScan(values)
    tx.Commit()

    for k, v := range values {
        fmt.Printf("%s %v\n", k, v)
    }

    js, err := json.Marshal(values)
    if err != nil {
        fmt.Println(err)
    }
    fmt.Println(string(js))
    fmt.Println("--------------------")
}

func ScanIntoStruct(connection string) {
    fmt.Println("Scan into struct sample")
    fmt.Println("--------------------")

    db, err := sqlx.Open("mssql", connection)
    e(err)
    defer db.Close()

    customer := Customer{}

    rows, err := db.Queryx(`select customerID, companyName, contactName
    from Customers`)

    for rows.Next() {
        err = rows.StructScan(&customer)
        if err != nil {
            log.Fatalln(err)
        }
        //fmt.Printf("%#v\n", user)
        fmt.Printf("%-10s %-50v %-50v\n",
            customer.CustomerId,
            customer.Company,
            customer.Contact)
        js, err := json.Marshal(customer)
        e(err)
        fmt.Println(string(js))
    }
    fmt.Println("--------------------")

}

func ScanIntoSlice(connection string) {
    fmt.Println("Scan into slice sample")
    fmt.Println("--------------------")
    start := time.Now()
    db, err := sqlx.Open("mssql", connection)
    e(err)
    defer db.Close()

    customers := []Customer{}

    err = db.Select(&customers, `select customerID, companyName, contactName from customers`)
    e(err)

    for i, customer := range customers {
        fmt.Printf("%3d. %-10s %-50v %-50v\n",
            i,
            customer.CustomerId,
            customer.Company,
            customer.Contact)
    }
    js, err := json.Marshal(customers)
    e(err)

    fmt.Println(string(js))

    fmt.Printf("%s", time.Since(start))
    fmt.Println("--------------------")

}

func e(err error) {
    if err != nil {
        log.Fatal(err)
    }
}

Collectives™ on Stack Overflow

Function in Go to execute select query on database and return json output

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related