Could not retrieve the column types from the rows of db.Query
Nobody has claimed this yet.
- Dominant language
- C
- Stars
- 9.2k
- Forks
- 1.2k
- Avg merge
- 19m
- Merged PRs (30d)
- 4
Description
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
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with sqlite3_test.go and the TestTimestamp function, reproducing the db.Query call and the rows.ColumnTypes().ScanType() access shown in the report. Compare the observed void ScanType with the expected column metadata and establish whether the test should retrieve usable types or document the current behavior and workaround.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sqlite
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100