Error saving Null dates to datetime2 field in SQL Server 2008 R2

[GoogleCodeExporter]

The goal is as follows:
To be able to insert/update a datetime2 field in a SQL Server 2008 database to 
NULL. Searching the internet I found that people created a NullTime structure 
that implemented the Value and Scan interfaces from the database/sql/driver 
package in the standard library. Doing this will allow a null date to be read, 
but it fails when trying to submit a Null date to the database.

If you set up a database with the following sample table and adjust the 
connection information in the sample code below, you should see how I received 
the following error 'SQLExecute: {07002} [Microsoft][SQL Server Native Client 
11.0]COUNT field incorrect or syntax'

CREATE TABLE [dbo].[Sample](
    [SampleId] [int] IDENTITY(1,1) NOT NULL,
    [SomeDate] [datetime2](7) NULL,
 CONSTRAINT [PK_Sample] PRIMARY KEY CLUSTERED 
(
    [SampleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The following code was compiled using go 1.2 windows/amd64:

package main

import (
    _ "code.google.com/p/odbc"
    "database/sql"
    "database/sql/driver"
    "log"
    "time"
)

//ConnectionInfo contains the sql server connection information
type ConnectionInfo struct {
    Driver     string
    SrvName    string
    DbName     string
    UseTrusted bool
    UserName   string
    Pwd        string
}

func (ci *ConnectionInfo) GetConnectionString() string {
    var connstr string
    connstr += "Driver={" + ci.Driver + "};"
    connstr += "Server=" + ci.SrvName + ";"
    connstr += "Database=" + ci.DbName + ";"

    if ci.UseTrusted {
        connstr += `Trusted_Connection=yes;`
    } else {
        connstr += `Uid=` + ci.UserName + `;`
        connstr += `Pwd=` + ci.Pwd
    }
    return connstr
}

//NullTime structure will represent a nullable datetime2 field
//within the database
type NullTime struct {
    Time  time.Time
    Valid bool // Valid is true if Time is not NULL
}

// Scan implements the Scanner interface.
func (nt *NullTime) Scan(value interface{}) error {
    nt.Time, nt.Valid = value.(time.Time)
    return nil
}

// Value implements the driver Valuer interface.
func (nt NullTime) Value() (driver.Value, error) {
    if !nt.Valid {
        return nil, nil
    }
    return nt.Time, nil
}

//String implements Stringer interface
func (nt NullTime) String() string {
    if nt.Valid {
        return nt.Time.String()
    }

    return "NULL Date"
}

//Sample is a struct that maps to the sample table
//in the database
type Sample struct {
    SampleId int
    SomeDate NullTime
}

var ci ConnectionInfo

func main() {

    //set connection information
    ci.Driver = "SQL Server Native Client 10.0"
    ci.DbName = "SomeDatabae"
    ci.SrvName = "SomeServer"
    ci.UseTrusted = true
    //ci.UName = "username"
    //ci.Pwd = "password"

    //connect to database
    db, err := sql.Open("odbc", ci.GetConnectionString())

    if err != nil {
        log.Fatalln("Unable to open database! ", err)
    } else {
        log.Println("Connected to database")
    }
    defer db.Close()

    //add sample data
    var sql string
    //sample row with date
    sql = "insert into dbo.Sample ([SomeDate]) values(GetDate())"
    _, err = db.Exec(sql)
    if err != nil {
        log.Println("Cannot save sample data. ", err)
    }

    //sample row without date
    sql = "insert into dbo.Sample ([SomeDate]) values(Null)"
    _, err = db.Exec(sql)
    if err != nil {
        log.Println("Cannot save sample data. ", err)
    }

    //now there should be two rows. one with a date and one wil NULL.
    //We will read from the database and display the results.
    //the NullTime structure works just fine here.

    sql = "select [SampleId], [SomeDate] from dbo.Sample"

    if rows, err := db.Query(sql); err != nil {
        log.Println("Cannot execute query! ", err)
    } else {
        var results []Sample
        //scan rows
        for rows.Next() {
            var row Sample
            err = rows.Scan(&row.SampleId, &row.SomeDate)
            if err != nil {
                log.Println("Error during row scan! ", err)
            }
            results = append(results, row)
        }
        //print rows to screen
        for _, r := range results {
            log.Println("Row in Database:", r.SampleId, r.SomeDate)
        }

    }

    //now lets submit some new rows using the Sample structure

    //With a date
    var withDate Sample
    withDate.SomeDate.Time = time.Now()
    withDate.SomeDate.Valid = true

    sql = "insert into dbo.Sample ([SomeDate]) values(?)"

    _, err = db.Exec(sql, withDate.SomeDate)
    if err != nil {
        log.Println("Cannot add a new row with a date! ", err)
    }

    //Without a date
    var noDate Sample //the zero value of the struct is what we want to use
    _, err = db.Exec(sql, noDate.SomeDate)
    if err != nil {
        log.Fatalln("Cannot add a new row without a date! ", err)
        //at this point we should see the error
        //SQLExecute: {07002} [Microsoft][SQL Server Native Client 10.0]COUNT field incorrect or syntax error
    }

}

Original issue reported on code.google.com by floyd.ra...@gmail.com on 18 Dec 2013 at 8:01

Attachments:

• odbc_sample.go

[GoogleCodeExporter]

Your program runs fine for me:

C:\go\path\mine\src\t>go run main.go
2013/12/19 16:49:27 Connected to database
2013/12/19 16:49:28 Row in Database: 1 2013-12-19 16:49:28.53 +1100 EST
2013/12/19 16:49:28 Row in Database: 2 NULL Date

and running

select * from Sample

displays

SampleId    SomeDate
----------- ---------------------------
1           2013-12-19 16:49:28.5300000
2           NULL
3           2013-12-19 16:49:28.2289916
4           NULL

(4 row(s) affected)

So everything is as expected here. I am using windows-386 and odbc package hg 
id e7603e584cea.

Not sure how I can help you.

Alex

Original comment by alex.bra...@gmail.com on 19 Dec 2013 at 6:01

• Changed state: WaitingForReply

[GoogleCodeExporter]

Alex,

Thanks for the quick response. I apologize for wasting your time, it turns out 
that this was completely due to the fact that I was using an older version of 
the code. I failed to use the -u flag when running 'go get' in order to force 
the tool to update the codebase.

Everything works as expected.  Thanks again.

Floyd

Original comment by floyd.ra...@gmail.com on 19 Dec 2013 at 3:47

[GoogleCodeExporter]

Original comment by alex.bra...@gmail.com on 19 Dec 2013 at 10:06

• Changed state: Invalid