SQLite row returned via shell but not in Go

Question

I have a SQLite query which returns expected results in the shell. However, when I run the same query in my Go program, no values are scanned.

Here is my query:

sqlite> select html, text from messages where id="17128ab240e7526e";
|Hey there

In this case, html is NULL and text has the string "Hey there". The table has other columns and indexes.

Here is my equivalent Go code:

package main

import (
    "database/sql"
    "log"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    filename := "emails.db"
    conn, err := sql.Open("sqlite3", filename)
    if err != nil {
        log.Fatal(err)
    }
    row, err := conn.Query("select html, text from messages where id = ?", "17128ab240e7526e")
    defer row.Close()

    if err != nil {
        log.Fatal(err)
    }
    hasRow := row.Next()
    log.Println("Has row:", hasRow)

    var html, text string
    row.Scan(&html, &text)

    log.Println("HTML:", html)
    log.Println("TEXT:", text)
}

The output is:

$ go run main.go
2020/07/05 21:10:14 Has row: true
2020/07/05 21:10:14 HTML: 
2020/07/05 21:10:14 TEXT:

Interestingly, this only happens when the column html is null. If html is not null, then the data is returned as expected, regardless of whether or not the value of the text column is null.

What might explain this behavior?

Tried to test this in my debian box, while searching for documents to install, found `sqlite` and `sqlite3` . I think you are using `sqlite`. If that is the case can you try with `sqlite3` ? ( Sorrry if I am wrong !!) — Arun, Jul 06 '20 at 01:52
You cannot scan `NULL` directly into `string`, that will cause `Scan` to return an error which, if you didn't ignore it, would let you know and may have saved you from having to write the question in the first place. You can scan into a `*string` or an `sql.NullString` to handle this case, or, if sqlite has `COALESCE`, you could use that in the query. — mkopriva, Jul 06 '20 at 03:04
Whichever way you choose to handle `NULL`, you should try hard to never ignore errors. — mkopriva, Jul 06 '20 at 03:06
Thanks @mkopriva! It seems I missed the fact that Scan() returned an error, so I didn't think to check it. Indeed, the error message is pretty clear. — poundifdef, Jul 07 '20 at 00:21

Arun · Accepted Answer · 2020-07-06T06:10:13.147

Based on the comments I modified the program using COALESCEand is working fine.

Key Point is : Cannot scan NULL, directly into string, can overcome this by utilize Coalesce function in Query.

row, err := conn.Query("select coalesce(html,'is-null'),text from messages where id =?", "17128ab240e7526e")
defer row.Close()

Output:

arun@debian:stackoverflow$ go run main.go
2020/07/06 10:08:08 Has row: true
HTML: is-null
TEXT: Hey there

SQLite row returned via shell but not in Go

1 Answers1