I don't understand how to get the data types

[nbst84]

I am fairly new to Golang so I am possibly missing something. I want to run a query against a MySql table and along reading the results know the datatype of each column that was returned.

So first I run the query:

rows, _ := db.Query("SELECT * FROM Table")

Then to "read" the results I do the following:

columnPointers := make([]interface{}, columnCount)
for rows.Next() {
	if err = rows.Scan(columnPointers...); err != nil {
		// error handling
	}
	for i := 0; i < columnCount; i++ {
		preview.Columns[i].Data = append(preview.Columns[i].Data, columnPointers[i])
	}
}

Now columnPointers[i] is always an interface{} (I suppose because I declared columnPointers as an array of interface{}).
What I would like is columnPointers[i] to be a correct data type (string, int64, bool, etc.) instead of interface{}. Or find out the original type so that I can convert the interface{}.

Right now I try to parse and convert to various types in order to find one that matches for all the values of each column but this is far from ideal, especially because unlike what the code above shows I'm only reading the first X rows of a table so they may not be representative of the actual data type of the columns.

Thanks

[julienschmidt]

Much of the type information gets unfortunately lost in the database/sql package. Without additional queries it is not possible to get information about the type of the field in the database.

Internally the following happens: The driver the received data in binary or text form to the closest Go type, then the database/sql package tries to convert it in order to save it into the variables you provided as pointers (Scan() args).

In case of interface{}, no conversion is done at all. That is what you want. An interface{} is basically just a struct containing the type of the value and a pointer to the value inside (or the value itself if it requires not more space than a pointer).

You can use a "type switch" to find out the actual type inside the interface{}:

switch value := columnPointers[i].(type) {
case string:
   // value has type string and contains the string value
case int64:
   // value has type int64 and contains the value as int64
case float64:
  ...
}

[julienschmidt]

You should however be aware that MySQL uses two different protocols internally:
A binary protocol for prepared statements (which is used for queries with parameters) and a text binary protocol, which is used for queries without parameters and when the parameters can be interpolated into the query, i.e. a query without parameters is send.

In case of the text based protocol, the type will always be []byte as this is the type requiring the least conversions (none) from the received data.

[nbst84]

Thank you for the response but I am still unclear on whether it is possible for me to get the type or not. Considering my queries are not parameterized/prepared and the following code:

columnPointers := make([]interface{}, columnCount)
for rows.Next() && previewRowCount < utils.PREVIEW_ROW_COUNT {
	if err = rows.Scan(columnPointers...); err != nil {
		// error handling
	}
	for i := 0; i < columnCount; i++ {
		switch value := columnPointers[i].(type) {
			case string:
				fmt.Println(value)
			   	fmt.Println("string")
			case int64:
				fmt.Println(value)
			   	fmt.Println("int64")
			case int:
				fmt.Println(value)
				fmt.Println("int")
			default:
				fmt.Println(value)
				fmt.Println(reflect.TypeOf(value))
			}
		val := parseValue(columnPointers[i].(*interface{}), "")
		preview.Columns[i].Data = append(preview.Columns[i].Data, val)
	}
}

When selecting from my test table which has a text column and an int column, the switch is always going into the default clause and printing the following:

*interface {}
0xc4206889a0
*interface {}
0xc4206889c0
*interface {}
0xc4206889d0
*interface {}
0xc4206889f0
*interface {}

What am I doing wrong?

Thanks

[julienschmidt]

columnPointers is an array of pointers to interface{} vars then?

In that case try switch value := (*columnPointers[i]).(type). And the expected type is []byte. You are not checking for that type yet.

[nbst84]

This results in the following errors:

connectors/mysql/mysql.go:366: invalid indirect of columnPointers[i] (type interface {})
connectors/mysql/mysql.go:366: use of .(type) outside type switch

But - are you saying I would always get []byte, even for my string and int columns?

[julienschmidt]

Yes. MySQL uses the text protocol and even an int64 with the value 42 would be transferred as bytes '4' and '2' over the network. The driver tries to do as few conversions as possible and returns the value thus as []byte{'4', '2'}.

It seems like the go compiler does not like the attempt to dereference within the switch statement.
A fail-safe way would be to do:

columnValue := *columnPointers[i]
switch value := columnValue.(type) {
...

Maybe just leaving away the parentheses works too. I didn't test it.

[nbst84]

That still does not work but it does not matter if I always get []byte as the detected data type because this will not help me.

So if I understand correctly it is impossible to know with certainty the type of a column of the results set. The only way is to try converting the value to various types and see which is the most precise that works. Which means that I'm out of luck if the result set is large because there's no way I process all the rows - and if the sample is not representative then I will get the wrong types.

Thanks for the help anyway.

[julienschmidt]

In the driver we have some information about the actual column type in the database. However, there is (currently) no way to pass this information through the interface provided by the database/sql package.

We are aware of it and we are annoyed by it. @arnehormann used a very dirty way to get this information anyway: https://godoc.org/github.com/arnehormann/sqlinternals/mysqlinternals / https://github.com/arnehormann/sqlinternals

[julienschmidt]

That's actually not true anymore since Go 1.8. I just found https://go-review.googlesource.com/c/29961/

We even have an issue for that already: #496

I think that should be rather easy to implement. I'll take a look over the weekend.

[julienschmidt]

WIP here: https://github.com/go-sql-driver/mysql/tree/columntype

[nbst84]

Excellent - do you have any idea when this will make it to master?

[julienschmidt]

There are still a few things missing:

• RowsColumnTypeLength interface
• RowsColumnTypePrecisionScale interface
• tests
• some minor ToDos (see comments)

The PR will be ready during the next week, I think

[arnehormann]

...the dirty way needs an update for resultSet support. I already wrote it and I'll push it soon. Am 10.05.2017 10:39 schrieb "Julien Schmidt" <[email protected]>:

…

There are still a few things missing: - RowsColumnTypeLength interface - RowsColumnTypePrecisionScale interface - tests - some minor ToDos (see comments) The PR will be ready during the next week, I think — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#585 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA9u1a2kcR9a9Yqosc3yXSSYwxDimovYks5r4Xe6gaJpZM4NRu_G> .

[nbst84]

Anything new regarding this?