Handling time fields from SQL Server

[Omortis]

Hello,

pyodbc has the option to use output converters to convert the strange SQL-S return types for dates to something python can parse (I do this a lot). Does this go odbc package have something similar? How should I deal with these return types?

var (
    mTime time.Time
    value float64
)

rows, err := db.Query("select MessageTime, Value from [table] where Subsystem='subsys' and Field='field'")
if err != nil {
    fmt.Println(err)
}

MessageTime is of SQL-Server type "time". Output (no matter what type I assign to mTime):

unsupported column type -154

Suggestions?

[alexbrainman]

nsupported column type -154

I google for that and I find SQL_SS_TIME2 in https://www.easysoft.com/developer/languages/c/examples/ListDataTypes.html

My package does not support SQL_SS_TIME2. If you provide full example demonstrating the problem (including all the SQL code for me to create these tables) and describe your environment (SQL server version and client driver and OS), I will try to implement required code.

Thank you.

Alex

[Omortis]

Alex: I am not at my desk again until Monday but I will put together the info for you over the weekend. The only example code I have for this is in python 2.7 but it does show how to shred the return values correctly.

[alexbrainman]

I will put together the info for you over the weekend.

Sounds good. When you have time.

The only example code I have for this is in python 2.7 but it does show how to shred the return values correctly.

I would like to see your Go code instead - the code that displays "unsupported column type -154" error. I would need to be able to run that code here, and see that error message here. So whatever else I will need to produce that, please, provide. For example, I will need the SQL statements that create required tables with whatever data it will have.

Thank you.

Alex

[Omortis]

@alexbrainman ,

I created a simple test database that uses the time column type. I have included the SQL and Go code below. This is on a Windows 10 64-bit client - I can reproduce on Linux as well. Note that the time type is deprecated by MS for new work but we have TB of extant tables using it as I'm sure do others....

Product Version:          12.0.5000.0
Product Name:             SQL Server 2014
Product Level:            SP2 
Product Edition:          Enterprise Edition (64-bit)

Output:

john@C:\Users\john\test_prj\go
> go build -o test.exe
john@C:\Users\john\test_prj\go
> test
Connection ok!
unsupported column type -154
panic: runtime error: invalid memory address or nil pointer dereference
        panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0x0 pc=0x4b950c]

goroutine 1 [running]:
database/sql.(*Rows).close(0x0, 0x0, 0x0, 0x0, 0x0)
        C:/Go/src/database/sql/sql.go:2748 +0x7c
database/sql.(*Rows).Close(0x0, 0x1d, 0x0)
        C:/Go/src/database/sql/sql.go:2744 +0x44
panic(0x4eb200, 0x596b30)
        C:/Go/src/runtime/panic.go:491 +0x291
sync.(*RWMutex).RLocker(...)
        C:/Go/src/database/sql/sql.go:2447
database/sql.(*Rows).Next(0x0, 0x513878)
        C:/Go/src/database/sql/sql.go:2447 +0x37
main.main()
       C:\Users\john\test_prj\go/test.go:37 +0x35e

SQL used to create and populate the test DB (you probably won't need the waitfor but here our network and DB server are always battling to see who can return more slowly):

use master;
go

if exists(select * from sys.databases where name='TimeTestDB')
begin
  drop database TimeTestDB;
end

create database TimeTestDB;
begin
  waitfor delay '00:00:03';
end;
go

use TimeTestDB
go

create table TimeTestDB.dbo.TimeTest (
  Value      varchar(10) not null,
  MessageTime  time(7) not null
);

create role TestUser;

grant select, insert, update, delete on TimeTestDB.dbo.TimeTest to TestUser;

create user Test_User_Account for login Test_User_Account;
alter role TestUser add member Test_User_Account;

insert into TimeTest (Value, MessageTime) values ('Value #1', '11:58:46.957000000');
insert into TimeTest (Value, MessageTime) values ('Value #2', '14:10:47.895000000');
insert into TimeTest (Value, MessageTime) values ('Value #3', '14:43:35.927000000');

A simple query returning the data:

use TimeTestDB
go
select * from TimeTest

Value      MessageTime
---------- ----------------
Value #1   11:58:46.9570000
Value #2   14:10:47.8950000
Value #3   14:43:35.9270000

(3 rows affected)

Go code that returns the -154 (includes the driver version):

package main

import (
    "database/sql"
    "fmt"
    "time"

    _ "github.com/alexbrainman/odbc"
)

func main() {

    connStr := "DRIVER={ODBC Driver 13 for SQL Server};SERVER=dbhost;DATABASE=TimeTestDB;UID=Test_User_Account;PWD=SomePassword"

    db, err := sql.Open("odbc", connStr)
    if err != nil {
        fmt.Println(err)
    }

    err = db.Ping()
    if err != nil {
        fmt.Println("No good")
    } else {
        fmt.Println("Connection ok!")
    }

    var (
        mTime time.Time
        value string
    )

    rows, err := db.Query("select MessageTime, Value from [TimeTest]")
    if err != nil {
        fmt.Println(err)
    }
    defer rows.Close()
    for rows.Next() {
        err := rows.Scan(&mTime, &value)
        if err != nil {
            fmt.Println(err)
        }
        fmt.Println(mTime, value)
    }
    err = rows.Err()
    if err != nil {
        fmt.Println(err)
    }

    defer db.Close()
}

For grins, the python code for the output converter I use with pyodbc to handle this return type is here - maybe the HHHI will help you:

def handleSQL_SS_TIME2(dateObj):
    tuple   = struct.unpack("HHHI", dateObj)
    tweaked = "{:02d}:{:02d}:{:02d}.{:08d}".format(tuple[0], tuple[1], tuple[2], tuple[3])
    return tweaked

Thanks and let me know if you need anything else. I had hoped to open your odbc code up this weekend and check it out but ran out of time...

JB

[Omortis]

Note that I edited the issue topic/name - I tested your odbc driver with the datetime field and it worked just fine. Will try with datetime2 later on...

[alexbrainman]

@Omortis this is plenty to have me started. I will try to reproduce this here. I suspect my problem will be getting the same ODBC driver ({ODBC Driver 13 for SQL Server}) or the same MS SQL Server version. I will report back.

Thank you.

Alex

[Omortis]

@alexbrainman I may be able to reproduce with a different ODBC driver - is 13 not the most recent version available from MS? Unfortunately it will be a while before we can upgrade from SQL-S 2014 however I doubt the on-the-wire return value/struct for SQL_SS_TIME2 has changed ;-) ....

[alexbrainman]

@Omortis I think I fixed your problem. Please, reopen if it is still broken for you.

time(7) MS SQL type was working for me with {ODBC Driver 10 for SQL Server}, But once I installed {ODBC Driver 13 for SQL Server}, I could reproduce your problem.

Thank you for your help.

[Omortis]

@alexbrainman Kind of a github neophyte w/r/t golang -go get github.com/alexbrainman/odbc and then building my test file leads to the same -154 result. How do I get the new release? Or do I need to simply clone this source code and go install?

[alexbrainman]

Kind of a github neophyte w/r/t golang -go get github.com/alexbrainman/odbc and then building my test file leads to the same -154 result.

If you already have $GOPATH/github.com/alexbrainman/odbc directory on your disk

go get github.com/alexbrainman/odbc

will do nothing. You want -u flag (that forces update to the latest version)

go get -u github.com/alexbrainman/odbc

Or you can delete $GOPATH/github.com/alexbrainman/odbc directory, then

go get github.com/alexbrainman/odbc

should get you the latest version.

Or you could change to $GOPATH/github.com/alexbrainman/odbc directory, and use git command to get latest version:

git checkout master
git pull

and then build with

go install github.com/alexbrainman/odbc

I hope it helps.

Alex

[Omortis]

@alexbrainman I looked at go help get about 5 times yesterday and -u didn't register. That's what I get for hurrying. Thanks for the whack with the clue stick, go odbc works just fine now:

> gotest
Connection ok!
0001-01-01 11:58:46 -0500 EST Value #1
0001-01-01 14:10:47 -0500 EST Value #2
0001-01-01 14:43:35 -0500 EST Value #3

I will be porting a large project from python 2.7 to go in a few weeks with this driver. Will let you know if anything else pops up.

Thanks again!