Unrecognized Data Type on Column

[nicksaroki]

Running a proprietary ODBC driver for NetSuite. For columns that contain what should be integers, numbers are being returned as strings in scientific notation. If I perform TO_CHAR() on the column, it comes in as normal digits and can be assigned as an integer. Sounds like the data type just needs to be mapped to integer?

Using a GUI for this ODBC driver it shows me the type as "NUMBER" (for both integer and decimal numbers). After doing a little debugging it looks like the type is always coming back as 8 (SQL_DOUBLE).

[alexbrainman]

It is difficult to help you without some small example program that demonstrates your problem. You could also debug this yourself - you have source code for the driver. Just print interesting things as your program runs. You probably should start here https://github.com/alexbrainman/odbc/blob/master/column.go#L53

I hope it helps.

Alex

[nicksaroki]

That's how I got the sqltype of 8 :).

What I can figure out is how both integers and decimals are getting the same type of SQL_DOUBLE. Can you point me in the direction of where to find the raw response of the column type?

The code is really simple (this happens with or without GORM):

type ItemPrice struct {
    ItemId  int `gorm:"column:ITEM_ID"`
    Id  int     `gorm:"column:ITEM_PRICE_ID"`
    Price   float32 `gorm:"column:ITEM_UNIT_PRICE"`
}

func (ItemPrice) TableName() string {
    return "ITEM_PRICES"
}

func main() {
    db, err := gorm.Open("odbc", "connectionStringHere")
    if err != nil {
        panic(err)
    }

    var item_price []ItemPrice
    db.LogMode(true)
    db.Select("TO_CHAR(ITEM_ID) AS ITEM_ID, TO_CHAR(ITEM_PRICE_ID) AS ITEM_PRICE_ID, ITEM_UNIT_PRICE").Where("ITEM_ID = 19746").Find(&item_price)
    for _, v := range item_price {
        fmt.Println(v.ItemId, v.Id, v.Price)
    }
}

So if I don't TO_CHAR() those columns, they'll be brought in as a string that is something like 4.7549197e+07

[2016-04-23 09:34:13] sql: Scan error on column index 0: converting string "4.7549197e+07" to a int: strconv.ParseInt: parsing "4.7549197e+07": invalid syntax

Example of the data: ItemId: 47549197 Id: 76543210 Price: 9.99

Edit: Shorter integers come in just fine.

[alexbrainman]

What I can figure out is how both integers and decimals are getting the same type of SQL_DOUBLE. Can you point me in the direction of where to find the raw response of the column type?

Perhaps I didn't understand your question. But if you want to know how Go code determines type of the column used in the result - it uses SQLDescribeCol (grep for SQLDescribeCol). Whatever ODBC driver you use is suppose to implement that function.

The code is really simple (this happens with or without GORM):

I really don't know what GORM is. I suggest (for the purpose of fixing this bug) you write plain SQL. You have to make your example as simple as possible to understand what your problem is. And mixing a tool that is magically generating SQL is not helpful here.

Alex

[mqsoh]

I'm also using a proprietary ODBC driver, but I after some debugging, I don't believe the issue is in this code. This is how I tested it using the Postgres driver; a similar test with the proprietary driver I'm using has the same problem reported in this issue.

[mqsoh]

The vendor responded with a "works for me". Do you think you could help me find the root cause?

I added some debug statements in my fork of your project.

This is the ouput with the Postgresql driver:

ODBC debugging. Column "?column?" is described as: %!s(api.SQLSMALLINT=4)
ODBC debugging. ...interpreted as api.SQL_TINYINT, api.SQL_SMALLINT, api.SQL_INTEGER
ODBC debugging. Column "?column?" is described as: %!s(api.SQLSMALLINT=4)
ODBC debugging. ...interpreted as api.SQL_TINYINT, api.SQL_SMALLINT, api.SQL_INTEGER
ODBC debugging. Column "?column?" is described as: %!s(api.SQLSMALLINT=-5)
ODBC debugging. ...interpreted as api.SQL_BIGINT

smallint = 32767, integer = -2147483648, bigint = 9223372036854775807
PASS
ok      app     0.013s

This is what I get using the vendor's driver.

ODBC debugging. Column "32767" is described as: %!s(api.SQLSMALLINT=3)
ODBC debugging. ...interpreted as api.SQL_NUMERIC, api.SQL_DECIMAL, api.SQL_FLOAT, api.SQL_REAL, api.SQL_DOUBLE
ODBC debugging. Column "-2147483648" is described as: %!s(api.SQLSMALLINT=3)
ODBC debugging. ...interpreted as api.SQL_NUMERIC, api.SQL_DECIMAL, api.SQL_FLOAT, api.SQL_REAL, api.SQL_DOUBLE
ODBC debugging. Column "9223372036854775807" is described as: %!s(api.SQLSMALLINT=3)
ODBC debugging. ...interpreted as api.SQL_NUMERIC, api.SQL_DECIMAL, api.SQL_FLOAT, api.SQL_REAL, api.SQL_DOUBLE

smallint = 32767, integer = 0, bigint = 0
--- FAIL: TestMarshalling (1.79s)
      main_test.go:30: sql: Scan error on column index 0: converting driver.Value type float64 ("-2.147483648e+09") to a int64: invalid syntax
      main_test.go:36: sql: Scan error on column index 0: converting driver.Value type float64 ("9.223372036854776e+18") to a int64: invalid syntax
FAIL
exit status 1
FAIL  app       1.791s

They're all SQLSMALLINT (even though they're not literally small ints). What is the significance of 3, 4, and -5?

[alexbrainman]

What is the significance of 3, 4, and -5?

As you can see from the code, we use SQLDescribeCol ODBC API to determine the type of the column.

The driver at the top returns 4 (SQL_INTEGER) and -5 (SQL_BIGINT).

While the driver on the bottom always returns 3 (SQL_DECIMAL). SQL_DECIMAL means we have to interpret the data it sends us as float64. You on the other hand requested result to be int64 (https://gist.github.com/mqsoh/99a4f84061ec4a024bd49ef161ea8f42#file-main_test-go-L29), so the driver have to convert float64 into int64. The conversion happens in database/sql package and it uses strconv.FormatFloat to make string out of float64 and then strconv.ParseUint to convert that string into int64. Just like here https://play.golang.org/p/7uTjxIf-ax If you run that example, you can see the same error as yours.

I am not sure where the fix should be. Maybe you can change your code to receive float64 and then do conversion yourself. Maybe driver author can change their code to return proper types, so we don't need to do silly conversions from float64 to int64.

Do you have other suggestions?

Alex

[mqsoh]

I have the conversion in place in my code, but I have to do it often and thought I would try and track down the root cause to potentially help other people. After my previous comment, I ended up poking around the unixodbc headers and, well...you'd be surprised how little I understand what I'm looking at. 😏 I'm fairly ignorant.

I think I was confused that they were all returning api.SQLSMALLINT but now that you've explained the distinctions I see that it's definitely a vendor problem.

Thank you for your help! Thank you also for this project; it's enabled me to do some good work.

[alexbrainman]

I have the conversion in place in my code, but I have to do it often and thought I would try and track down the root cause to potentially help other people.

Fair enough. I also had a new idea I did not have yesterday.

The way we read SQL results (I will be talking about single column) is this:

1. discover data type by calling SQLDescribeCol;
2. tell ODBC that type and where to put column data (as we are reading it) by calling SQLBindCol;
3. read next SQL row by calling SQLFetch
4. convert data stored in memory (specified at step 2) of type (discovered at step 1) to whatever user specified in conn.QueryRow("select ...").Scan(&param) - this is done by database/sql package;
5. if not at the end of file, then go to step 3;

The problem you are having is because at step 1 we were told the data will be float64, but at step 4 you requested data to be int64. And generally it is impossible to convert float64 to int64 without loosing some info.

Perhaps we could use the data type you provided (int64) and get ODBC do all conversion itself. Here is approximate plan:

1. read next SQL row by calling SQLFetch
2. discover type of conn.QueryRow("select ...").Scan(&param) parameter - let's say it is int64;
3. call SQLGetData to read column data (but pass int64 into it to have it all converted properly inside that call);
4. if not at the end of file, then go to step 1;

This approach is, probably, less efficient then original, but it might work around your issue.

Would you like to try and implement this? Just to see if it solves your problem. If not, I can try it myself when I have some free time. I won't e able to test it properly, because I don't have database like yours. Also, if works, I am not sure how to accommodate multiple code paths for reading table data. Do you have any suggestions?

Let me know. Thank you.

Alex

[mqsoh]

That sounds like a good idea. I think the vendor in both mine and nicksaroki's cases should do the right thing. If I can't get my vendor to fix it, I'll try and implement what you've described.

[alexbrainman]

Sure thing, @mqsoh . Ping me, if you want me to do something.

Alex

[Wang-Kai]

@mqsoh Can you show me your code of access postgres use ODBC ?

[mqsoh]

I don't have that code anymore, @Wang-Kai. I'm really sorry about that. If you're wondering about what the DSN for the connection should look like, I share your frustration about the state of documentation. I think this will help from the driver's official site.

[Wang-Kai]

@mqsoh Thank you very much !