[SOLVED] SQLite row returned via shell but not in Go

SQLite row returned via shell but not in Go

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?

Solution

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