ExecuteSql() does not support any kind of nil/null values.

[daliborfilus]

Using sql.NullString (or custom NullTime which implements driver.Valuer interface (methods Value, Scan)) as a parameter to ExecuteSql results in SQL error. Using *time.Time, *string, etc. results in the same error:

Server 'xxx', Line 1
        Conversion failed when converting date and/or time from character string.

This happens when I try to use datetime passed by user in the query. The query looks like this:

-- ....
(@DateFrom IS NULL OR CAST(o.CreatedAt AS Date) >= CAST(@DateFrom AS Date))

Which my custom QueryWithNamedParameters will conver to this:

(? IS NULL OR CAST(o.CreatedAt AS Date) >= CAST(? AS Date))

and then I pass my NullTime type to the query, expecting it to behave like this:

(NULL IS NULL OR CAST(o.CreatedAt AS Date) >= CAST(NULL AS Date))

but it doesn't and results in the error above.

I'm currently trying to work around this issue by patching executesql.go, function "go2SqlDataType" like this, which works:

    default:
        log.Printf("unknown dataType %T", t)
        // support for sql.NullString, sql.NullWhatever...
        // TODO: return correct type for parameter? we don't know it, the Valuer interface will not tell us.
        // Maybe we can go deeper and pass the value again to go2SqlDataType
        if val, ok := value.(driver.Valuer); ok {
            log.Printf("...but it implements Valuer, using that")
            val2, err := val.Value()
            if err != nil {
                log.Printf("value(): %+v, err: %+v", val2, err)
            } else {
                if val2 != nil {
                    // Value() is something, let's go deeper to get what it is
                    log.Printf("go2SqlDataType: Valuer.Value(): %+v, recursing", val2)
                    return go2SqlDataType(val2)
                } else {
                    log.Printf("go2SqlDataType: Valuer.Value(): returning VARCHAR NULL")
                    return fmt.Sprintf("nvarchar (%d)", 4), "NULL"
                }
            }
        } else {
            log.Printf("and it does not implement Valuer")
        }
    }

... but I don't know if passing "nvarchar(4), NULL" is a good idea. Does the type of the parameter matter if the value is NULL?

[ianic]

You can Scan nullable varchar into *string type.
If resultset value is null pointer will be nil.

Here is the example.
p1 is returned as null p2 has non null value:

func TestNullValueScan(t *testing.T) {
    conn := ConnectToTestDb(t)
    err := createProcedure(conn, "test_sp_null_value_scan", `
    as
    DECLARE @p1 varchar(255)
    DECLARE @p2 varchar(255)
    set @p2 = 'p2'
    SELECT @p1, @p2
    `)
    assert.Nil(t, err)
    rst, err := conn.ExecSp("test_sp_null_value_scan")
    assert.Nil(t, err)
    assert.NotNil(t, rst)
    rst.Next()
    var p1, p2 *string
    err = rst.Scan(&p1, &p2)
    assert.Nil(t, err)
    assert.Nil(t, p1)
    assert.NotNil(t, p2)
    assert.Equal(t, "p2", *p2)
}

[daliborfilus]

Scan maybe does work (i think it really does), but this issue is about passing nil *string to conn.ExecuteSql :-) The parameter was converted to something NVARCHAR(x), 'NULL', instead of NVARCHAR(x), NULL and the query crashed on that.

[ianic]

Sorry,
I totally missed the point.

[drewlesueur]

I have made a small PR for the fix: #75

Does the type of the parameter matter if the value is NULL?

I don't know for sure. Based on this code it seems nvarchar(1) would work, but I am not sure I understand that full context of that code.

For my use case (sybase 12.5) it doesn't need the type so I know my change works there.