11

In my golang app i need to do SQL query to MySQL to get single row and put result in a map[string]string keys are column names.

But i don't know what will be columns. Query is like 

SELECT * FROM mytable

I use "database/sql" .

I found only Scan function 

db.QueryRow("SELECT * FROM mytable").Scan(&var1, &var2,...)

but this doesn't work for my condition. I don't know how many variables will be there. And also I need column names.

Is it possible to do with database/sql ?

Update. I found how to solve part of this problem. How to get column names from a result set.

rows, err := db.Query(sqlcommand)

cols, err := rows.Columns()

So, i can use to make keys for a map. But i still don't know how to get values. Because, values can have different type.

data = make(map[string]string)

if rows.Next() {
    columns := make([]interface{}, len(cols))
    columnPointers := make([]interface{}, len(cols))
    for i, _ := range columns {
        columnPointers[i] = &columns[i]
    }

    err = rows.Scan(columnPointers...)

    for i, colName := range cols {
        // value is in columns[i] of interface type.
        // How to extract it from here? 
        // ....
        data[colName] = val
    }
}

P.S. This question is not duplicate of "Get table column names in mysql? ". I wanted to get columns of returned data set, not just a table.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Roman Gelembjuk
  • 1,797
  • 2
  • 25
  • 50
  • 1
    @Shadow that is not an apt duplicate. This question is specifically about Go. – Adrian Aug 07 '18 at 16:35
  • @Adrian let me quote the question: "Is it possible to do with database/sql ?" The duplicate shows how to do this with sql. If that was not the intention of the OP, then the question should not have included sql and probably should not have been tagged as MySQL either. – Shadow Aug 07 '18 at 16:46
  • 5
    @Shadow `database/sql` is the name of the Go library for interacting with SQL databases. I see no reason it shouldn't have been tagged mysql, since the question is about using MySQL with Go. Using both tags seems entirely appropriate, and I see no reason they shouldn't have included the relevant query they're executing from their Go code. Just because the question isn't *only* about SQL doesn't mean it isn't about SQL *at all*. – Adrian Aug 07 '18 at 16:48
  • 3
    [Rows.Columns](https://golang.org/pkg/database/sql/#Rows.Columns) returns the column names and [Rows.ColumnTypes](https://golang.org/pkg/database/sql/#Rows.ColumnTypes) the types along with the name. QueryRow is not appropriate without `LIMIT 1`; use Query instead. – Peter Aug 07 '18 at 16:55
  • I updated the question. I don't agree it is duplicated of "Get table column names in mysql? " – Roman Gelembjuk Aug 08 '18 at 04:20
  • @Adrian disagree. If you do not want to see a solution specific to a certain technology, then you must not tag your question with it. If you want an answer specific to a library in a language, then use that to tag the question. – Shadow Aug 10 '18 at 05:30
  • 2
    Pretty sure they're looking for a solution specific to *all* the tags they used, combined. That's how tags work, that's why you're able to put multiple tags on a question. – Adrian Aug 10 '18 at 11:40

1 Answers1

24

You seem to be on the right track. According to the definition of Rows.Scan, you can supply values of the desired destination type, which would be string here. So changing the type of columns to []string should work:

var db sql.DB
var sqlcommand string

rows, _ := db.Query(sqlcommand)
cols, _ := rows.Columns()

data := make(map[string]string)

if rows.Next() {
    columns := make([]string, len(cols))
    columnPointers := make([]interface{}, len(cols))
    for i, _ := range columns {
        columnPointers[i] = &columns[i]
    }

    rows.Scan(columnPointers...)

    for i, colName := range cols {
        data[colName] = columns[i]
    }
}
robx
  • 2,221
  • 1
  • 14
  • 31
  • Yes. This is what i finally did. My solution is little different. I cast columns to bytes, if len() is more 0 than convert to string – Roman Gelembjuk Aug 13 '18 at 15:02
  • 1
    This worked great for me too, except, if you're using the gorm package (https://github.com/jinzhu/gorm) and if your `db` object is of type `*gorm.DB`, then, replace `db.Query(sqlcommand)` above with `db.Raw(sqlcommand).Rows()`. – Vikram Hosakote Feb 09 '19 at 00:31
  • There is one problem if cols do have null values rows.Scan(columnPointers...) breaks with error "converting null to string is unsupported" – Najam Awan Jun 16 '23 at 20:03
  • Also even if i get one column instead of select * i get only one row using this. – Najam Awan Jun 16 '23 at 20:27