Error on SQL Server 2008 and datetime2

[GoogleCodeExporter]

What steps will reproduce the problem?
Connect to a SQL Server 2008 database and perform an insert on a table that has 
a column defined as datetime2(0) using a bind parameter.

What is the expected output? What do you see instead?
Expected - The insert will work
Actual - SQL Execute error 22008 - Datetime field overflow.  Fractional second 
precision exceeds the scale specified in the parameter binding.

What version of the product are you using? On what operating system?
Go 1.1.2 X64 on Windows X64.

Please provide any additional information below.
I modified param.go to always set the value of decimal in the call to 
api.SQLBindParameter to 7 and that seems to resolve the problem.  I would like 
to get the value of supported fractional seconds from the call to 
api.SQLDescribeParam but I don't know if SQLDescribeParam returns that 
information.  If you want, I can submit change request with the fix.

Original issue reported on code.google.com by lukemaul...@gmail.com on 21 Aug 2013 at 5:05

[GoogleCodeExporter]

I don't have SQL Server 2008. Let me find one, so I can investigate this. But, 
please, send the change, if you like. Thank you.

Alex

Original comment by alex.bra...@gmail.com on 22 Aug 2013 at 7:01

• Changed state: Accepted

[GoogleCodeExporter]

Luke,

I issued these

select cast('20151225' as datetime)
select cast('20151225' as datetime2)

and here is what SQLDescribeCol returns

datetime : sqltype=93 size=23 decimal=3
datetime2: sqltype=-9 size=27 decimal=0

so datetime2 is returned as just SQL_WVARCHAR (it is a simple string). Do you 
see things any different?

As to parameters, I didn't try anything. Instead I have found this

http://stackoverflow.com/questions/6256302/how-to-bind-a-value-to-a-stored-proc-
parameter-of-type-datetime27-using-odbc?rq=1

which kind of repeat what you said. datetime parameter code is already has some 
hardcoding, I am not sure what to do until I se what your real problem is.

I will wait for you to send some code before decideing what to do next.

Alex

Original comment by alex.bra...@gmail.com on 23 Aug 2013 at 1:37

[GoogleCodeExporter]

Alex,

Thanks for looking into this.  I think that your driver is returning datetime2 
as a "SQL_WVARCHAR" because you are using an older version of SQL Native Client 
that doesn't support datetime2.  Try upgrading to a more recent version of SQL 
Native Client.  You can try: 
http://www.microsoft.com/en-us/download/details.aspx?id=35580 (Click on 
"Download" and then ENU\x64\sqlncli.msi   Then set your connection string to 
include: "Driver={SQL Server Native Client 11.0}"  After completing that, 
SQLDescribeCol should report SQL_TYPE_TIMESTAMP.  
I read the stackoverflow post and I did something very similar.  In param.go in 
the "time.Time" case I added:
size = 27   // 20 + s (the number of characters in the yyyy-mm-dd 
hh:mm:ss[.fff...] format, where s is the seconds precision).
decimal = 7 //Setting to maximum possible fraction of a second -- cannot 
determine how to read this from api.SQLDescribeParam

I also added a check after the switch to only set the decimal and size if they 
were not set in the switch.  I have attached the file.  I will submit a change 
request if you think it looks good.

Original comment by lukemaul...@gmail.com on 28 Aug 2013 at 12:50

Attachments:

• param.go

[GoogleCodeExporter]

Luke,

This https://codereview.appspot.com/13270047/ is to fetch datatime2 types from 
the server. Please, review.

I will do parameter handling later

Alex

Original comment by alex.bra...@gmail.com on 30 Aug 2013 at 6:30

[GoogleCodeExporter]

Reviewed the change to the tests.

Original comment by lukemaul...@gmail.com on 3 Sep 2013 at 4:03

[GoogleCodeExporter]

Luke,

I have this test:

diff -r fdeace4e5288 mssql_test.go
--- a/mssql_test.go Wed Sep 04 16:49:55 2013 +1000
+++ b/mssql_test.go Wed Sep 04 17:27:08 2013 +1000
@@ -965,3 +965,53 @@

    exec(t, db, "drop table dbo.temp")
 }
+
+// https://code.google.com/p/odbc/issues/detail?id=14
+func TestMSSQLDatetime2Param(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 datetime2)")
+
+   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")
+}
+

And it is not failing regardless if I make changes to param.go or not. Do you 
have a reproducible test that is broken. Thank you.

Alex

Original comment by alex.bra...@gmail.com on 4 Sep 2013 at 7:30

[GoogleCodeExporter]

Luke, how about this https://codereview.appspot.com/14426055/ ?

Alex

Original comment by alex.bra...@gmail.com on 14 Oct 2013 at 3:01

• Changed state: Started

[GoogleCodeExporter]

This issue was closed by revision d05da338d8c0.

Original comment by alex.bra...@gmail.com on 21 Oct 2013 at 12:19

• Changed state: Fixed