Could not retrieve the column types from the rows of db.Query

[eaglebush]

I was making a wrapper around a query and I came to the point of retrieving the column types to know what type a row field should be cast to, but I cannot get field types from a db.Query result. Here is a test function taken from the sqlite3_test.go file to reproduce:

func TestTimestamp(t *testing.T) {
	tempFilename := TempFilename(t)
	defer os.Remove(tempFilename)
	db, err := sql.Open("sqlite3", tempFilename)
	if err != nil {
		t.Fatal("Failed to open database:", err)
	}
	defer db.Close()

       _, err = db.Exec("DROP TABLE foo")
	_, err = db.Exec("CREATE TABLE foo(id INTEGER, ts timeSTAMP, dt DATETIME)")
	if err != nil {
		t.Fatal("Failed to create table:", err)
	}

	timestamp1 := time.Date(2012, time.April, 6, 22, 50, 0, 0, time.UTC)
	timestamp2 := time.Date(2006, time.January, 2, 15, 4, 5, 123456789, time.UTC)
	timestamp3 := time.Date(2012, time.November, 4, 0, 0, 0, 0, time.UTC)
	tzTest := time.FixedZone("TEST", -9*3600-13*60)
	tests := []struct {
		value    interface{}
		expected time.Time
	}{
		{"nonsense", time.Time{}},
		{"0000-00-00 00:00:00", time.Time{}},
		{time.Time{}.Unix(), time.Time{}},
		{timestamp1, timestamp1},
		{timestamp2.Unix(), timestamp2.Truncate(time.Second)},
		{timestamp2.UnixNano() / int64(time.Millisecond), timestamp2.Truncate(time.Millisecond)},
		{timestamp1.In(tzTest), timestamp1.In(tzTest)},
		{timestamp1.Format("2006-01-02 15:04:05.000"), timestamp1},
		{timestamp1.Format("2006-01-02T15:04:05.000"), timestamp1},
		{timestamp1.Format("2006-01-02 15:04:05"), timestamp1},
		{timestamp1.Format("2006-01-02T15:04:05"), timestamp1},
		{timestamp2, timestamp2},
		{"2006-01-02 15:04:05.123456789", timestamp2},
		{"2006-01-02T15:04:05.123456789", timestamp2},
		{"2006-01-02T05:51:05.123456789-09:13", timestamp2.In(tzTest)},
		{"2012-11-04", timestamp3},
		{"2012-11-04 00:00", timestamp3},
		{"2012-11-04 00:00:00", timestamp3},
		{"2012-11-04 00:00:00.000", timestamp3},
		{"2012-11-04T00:00", timestamp3},
		{"2012-11-04T00:00:00", timestamp3},
		{"2012-11-04T00:00:00.000", timestamp3},
		{"2006-01-02T15:04:05.123456789Z", timestamp2},
		{"2012-11-04Z", timestamp3},
		{"2012-11-04 00:00Z", timestamp3},
		{"2012-11-04 00:00:00Z", timestamp3},
		{"2012-11-04 00:00:00.000Z", timestamp3},
		{"2012-11-04T00:00Z", timestamp3},
		{"2012-11-04T00:00:00Z", timestamp3},
		{"2012-11-04T00:00:00.000Z", timestamp3},
	}
	for i := range tests {
		_, err = db.Exec("INSERT INTO foo(id, ts, dt) VALUES(?, ?, ?)", i, tests[i].value, tests[i].value)
		if err != nil {
			t.Fatal("Failed to insert timestamp:", err)
		}
	}

	rows, err := db.Query("SELECT id, ts, dt FROM foo ORDER BY id ASC")
	if err != nil {
		t.Fatal("Unable to query foo table:", err)
	}
	defer rows.Close()

	//Try to retrieve column types
	colt, err := rows.ColumnTypes()
	for k := range colt {
               //This part fails
		t.Logf("Column type: %s\r\n", colt[k].ScanType().Name())
	}

	seen := 0
	for rows.Next() {

		var id int
		var ts, dt time.Time

		if err := rows.Scan(&id, &ts, &dt); err != nil {
			t.Error("Unable to scan results:", err)
			continue
		}
		if id < 0 || id >= len(tests) {
			t.Error("Bad row id: ", id)
			continue
		}
		seen++
		if !tests[id].expected.Equal(ts) {
			t.Errorf("Timestamp value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt)
		}
		if !tests[id].expected.Equal(dt) {
			t.Errorf("Datetime value for id %v (%v) should be %v, not %v", id, tests[id].value, tests[id].expected, dt)
		}
		if timezone(tests[id].expected) != timezone(ts) {
			t.Errorf("Timezone for id %v (%v) should be %v, not %v", id, tests[id].value,
				timezone(tests[id].expected), timezone(ts))
		}
		if timezone(tests[id].expected) != timezone(dt) {
			t.Errorf("Timezone for id %v (%v) should be %v, not %v", id, tests[id].value,
				timezone(tests[id].expected), timezone(dt))
		}
	}

	if seen != len(tests) {
		t.Errorf("Expected to see %d rows", len(tests))
	}
}

I have inserted code in the function to check if I can retrieve the column types from the test result. When I retrieved the ScanType from the column, it raises an error. In debug mode, I see the value of the scanType is void.

Is this how it should be used or are there any work around?

Environment:

OS: Windows 7 SP1 64 bit
GO version: go1.11.1 windows/amd64
C Compiler: TDM-GCC Compiler Suite for Windows MinGW-w64 64/32-bit Edition

[rittneje]

Column types cannot be retrieved until you've called row.Next(). Put your range colt loop inside your rows.Next() loop.

[eaglebush]

I moved the range colt loop under the row.Next() and retrieved the column types from it. However, I can't get the column type of a column when the result is null.

Thanks!

[rittneje]

What do you mean by "when the result is null"?

[eaglebush]

In this example: CREATE TABLE foo(id INTEGER, ts timeSTAMP, dt DATETIME), if I didn't fill the dt column with a value and instead leave it null, the dt column's type will be void or nil.

[rittneje]

Ah, I see. SQLite is dynamically typed, so the type information is tied to individual values as opposed to result columns. In fact the type of a column can be different from one row to the next. Be sure to keep this in mind with whatever you're doing.
More to the point, if the column in question is NULL, you get back reflect.TypeOf(nil). As per the documentation, reflect.TypeOf(nil) == nil. So just be sure to handle that case before any other processing on the reflect.Type.

[eaglebush]

Thank you for the additional information.

[Auler]

How did you deal with it? @eaglebush

[eaglebush]

How did you deal with it? @eaglebush

Assuming the query called all the columns to return, I had to define the structure manually in a struct and used its field index to identify what column would the null value would be. I was trying to put the driver in a wrapper as I did with SQL Server and Postgres. Ultimately, I forgo getting the column types and did not consider adding it in my wrapper struct.