Go中的函数在数据库上执行选择查询并返回json输出

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
}

Answer 1

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.