search

alialtun14 / odbc

Automatically exported from code.google.com/p/odbc
BSD 3-Clause "New" or "Revised" License
0 stars 0 forks source link

Datetime field overflow with sql server 2008 #28

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 

SQLExecute: {22008} [Microsoft][SQL Server Native Client 11.0]Datetime 
字段溢出。秒的小数精度超出了在参数绑定中指定的小数位��
�。

PATCH:
--- a/param.go  Fri Nov 15 09:34:59 2013 +1100
+++ b/param.go  Tue Dec 10 15:34:00 2013 +0800
@@ -102,7 +102,7 @@
                        Hour:     api.SQLUSMALLINT(d.Hour()),
                        Minute:   api.SQLUSMALLINT(d.Minute()),
                        Second:   api.SQLUSMALLINT(d.Second()),
-                       Fraction: api.SQLUINTEGER(d.Nanosecond()),
+                       Fraction: 0,
                }
                p.Data = &b
                buf = unsafe.Pointer(&b)
@@ -113,7 +113,9 @@
                if decimal <= 0 {
                        // represented as yyyy-mm-dd hh:mm:ss.fff format in ms sql server
                        decimal = 3
+                       b.Fraction = api.SQLUINTEGER(d.Nanosecond() / 
int(time.Millisecond))
                }
                size = 20 + api.SQLULEN(decimal)
        case []byte:

Original issue reported on code.google.com by runner....@gmail.com on 10 Dec 2013 at 7:38

GoogleCodeExporter commented 9 years ago 
Please, provide a small example program to demonstrate the problem. Thank you.

Alex

Original comment by alex.bra...@gmail.com on 11 Dec 2013 at 12:41

GoogleCodeExporter commented 9 years ago

Original comment by alex.bra...@gmail.com on 11 Dec 2013 at 12:46

GoogleCodeExporter commented 9 years ago 
//  go test "-msdriver=sql server native client 11.0" -mssrv=192.168.1.2 
-msdb=tpt_data_386_test -msuser=tpt -m spass=extreme -v -run=MS
func TestMSSQLDatetimeParam(t *testing.T) {
    db, sc, err := mssqlConnect()
    if err != nil {
        t.Fatal(err)
    }
    defer closeDB(t, db, sc, sc)

    if !is2008OrLater(db) {
        t.Skip("skipping test; needs MS SQL Server 2008 or later")
    }

    db.Exec("drop table dbo.temp")
    exec(t, db, "create table dbo.temp (dt datetime)")

    expect := time.Date(2007, 5, 8, 12, 35, 29, 1234567e2, time.Local)
    _, err = db.Exec("insert into dbo.temp (dt) values (?)", expect)
    if err != nil {
        t.Fatal(err)
    }
    var got time.Time
    err = db.QueryRow("select top 1 dt from dbo.temp").Scan(&got)
    if err != nil {
        t.Fatal(err)
    }
    if expect != got {
        t.Fatalf("expect %v, but got %v", expect, got)
    }

    exec(t, db, "drop table dbo.temp")
}

Original comment by runner....@gmail.com on 7 Jan 2014 at 3:31

GoogleCodeExporter commented 9 years ago 
Your datatime overflows.

MS SQL Server only supports 3 digits after decimal point in seconds. See for 
example http://msdn.microsoft.com/en-us/library/ms187819.aspx.

If you need precision better then that, use new datetime2 type. If you don't 
need the precision, just round your time.Time values before posting them into 
your database server.

Alex

Original comment by alex.bra...@gmail.com on 7 Jan 2014 at 4:18

GoogleCodeExporter commented 9 years ago 
I think you should let it automatically adjust precision in driver.

Original comment by runner....@gmail.com on 7 Jan 2014 at 5:29

GoogleCodeExporter commented 9 years ago 
I think differently. Sorry.

Alex

Original comment by alex.bra...@gmail.com on 7 Jan 2014 at 5:43

GoogleCodeExporter commented 9 years ago 
Issue 45 has been merged into this issue.

Original comment by alex.bra...@gmail.com on 25 Jun 2014 at 4:12