2.8.10 is returning int column as string

[elgs]

I have the following data in the db (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)

create TABLE TEST_TABLE (
    ID INTEGER NOT NULL PRIMARY KEY,
    NAME VARCHAR(50)
);
insert INTO TEST_TABLE (ID, NAME) VALUES (1, 'Alpha');

With 2.8.9, the driver returns ID as go type int64, but with 2.8.10, it returns ID as go type string.

Here is the test code:

package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"
    "reflect"
    "strings"

    _ "github.com/sijms/go-ora/v2"
)

// table and data in oracle db
// create TABLE TEST_TABLE (
//  ID INTEGER NOT NULL PRIMARY KEY,
//  NAME VARCHAR(50)
// );
// insert INTO TEST_TABLE (ID, NAME) VALUES (1, 'Alpha');

func main() {

    dbUrl := "******"

    conn, err := sql.Open("oracle", dbUrl)
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close()

    QueryToMaps(conn, "SELECT * FROM TEST_TABLE WHERE ID=1")

}

func QueryToMaps(conn *sql.DB, sqlStatement string, sqlParams ...any) ([]map[string]any, error) {
    results := []map[string]any{}
    rows, err := conn.Query(sqlStatement, sqlParams...)
    if err != nil {
        if os.Getenv("env") == "dev" {
            fmt.Println("Error executing: ", sqlStatement)
        }
        return results, err
    }
    cols, err := rows.Columns()
    if err != nil {
        return results, err
    }
    lenCols := len(cols)

    for i, v := range cols {
        cols[i] = strings.ToLower(v)
    }

    rawResult := make([]any, lenCols)
    colTypes, err := rows.ColumnTypes()
    if err != nil {
        return results, err
    }
    dest := make([]any, lenCols) // A temporary any slice
    for i := range rawResult {
        dest[i] = &rawResult[i] // Put pointers to each string in the interface slice
    }
    for rows.Next() {
        result := make(map[string]any, lenCols)
        rows.Scan(dest...)
        for i, raw := range rawResult {
            // in v2.8.9, the raw type is int64;
            // in v2.8.10, the raw type is string;
            fmt.Println(sqlStatement, " ", colTypes[i].DatabaseTypeName(), " ", raw, reflect.TypeOf(raw))
        }
        results = append(results, result)
    }
    return results, nil
}

[elgs]

2.8.9 output:

SELECT * FROM TEST_TABLE WHERE ID=1   NUMBER   1 int64
SELECT * FROM TEST_TABLE WHERE ID=1   NCHAR   Alpha string

2.8.10 output:

SELECT * FROM TEST_TABLE WHERE ID=1   NUMBER   1 string
SELECT * FROM TEST_TABLE WHERE ID=1   NCHAR   Alpha string

[robstradling]

I believe this is due to https://github.com/sijms/go-ora/commit/b04862ca3078dc46907e4e8a566507bd505af880, which fixed issues #515 and #516.

[elgs]

Thanks @robstradling. I hope this will get a fix. This is breaking my client code.

[sijms]

Hi @elgs number return from oracle database can be in64 or float64 values or value beyond their limit. beside I can't anticipate what data type you will pass to sql.Scan function so the best data type that fit for all is string

if you are using interface{} type so you should convert string to its appropriate value either int64, float64 or custom number type

[elgs]

Thanks for the quick reply @sijms.

Interesting it worked well in 2.8.9. It's only breaking in 2.8.10.

I wonder if the driver can do some type check using colTypes, err := rows.ColumnTypes()?

I do it for MySQL driver. I think it's a matter the driver checks for it or the client checks for it. In order for the driver to be useful, the check has to be performed somewhere. The db has provided enough information from rows.ColumnTypes(). Here rows are of type*sql.Rows.

result[cols[i]] = faultyMysqlDriverPatch(raw, colTypes[i].DatabaseTypeName())

func faultyMysqlDriverPatch(raw any, colType string) any {
    if v, ok := raw.([]byte); ok {
        value := string(v)
        switch colType {
        case "SMALLINT", "MEDIUMINT", "INT", "INTEGER", "BIGINT", "YEAR":
            raw, _ = strconv.Atoi(value)
        case "TINYINT", "BOOL", "BOOLEAN", "BIT":
            raw, _ = strconv.ParseBool(value)
        case "FLOAT", "DOUBLE", "DECIMAL":
            raw, _ = strconv.ParseFloat(value, 64)
        case "DATETIME", "TIMESTAMP":
            raw, _ = time.Parse("2006-01-02 15:04:05", value)
        case "DATE":
            raw, _ = time.Parse("2006-01-02", value)
        case "TIME":
            raw, _ = time.Parse("15:04:05", value)
        case "NULL":
            raw = nil
        default:
            raw = value
        }
    }
    return raw
}

[sijms]

I will explain the issue for you see this code:

db, err := sql.Open("oracle", os.Getenv("DSN"))
if err != nil {
    panic(err)
}
defer func() {
    err = db.Close()
    if err != nil {
        fmt.Println("can't close db: ", err)
    }
}()
var (
    x int64
    y float64
    z string
)
err = db.QueryRow("SELECT 123456789, 1234.56789, 123456789123456789123456789  FROM DUAL").Scan(&x, &y, &z)
if err != nil {
    panic(err)
}
fmt.Println(x)
fmt.Println(y)
fmt.Println(z)

3 numbers int64, float64 and large integer will not fit in both so I use string all of them has same properties

column type	NUMBER
SCALE	255
precision	38

[elgs]

Eventually someone has to do the conversion, be it the driver, or the client. I understand it's tedious, but it's worth the work. If the driver could at least cover the cases for float64, int64 and string, that would cover most of cases.

This is my workaround now:

// faulty mysql driver workaround https://github.com/go-sql-driver/mysql/issues/1401
// faulty oracle driver workaround https://github.com/sijms/go-ora/issues/533
func faultyDriverPatches(raw any, colType string) any {
    switch v := raw.(type) {
    // for mysql
    case []byte:
        value := string(v)
        switch colType {
        case "SMALLINT", "MEDIUMINT", "INT", "INTEGER", "BIGINT", "YEAR":
            raw, _ = strconv.Atoi(value)
        case "TINYINT", "BOOL", "BOOLEAN", "BIT":
            raw, _ = strconv.ParseBool(value)
        case "FLOAT", "DOUBLE", "DECIMAL":
            raw, _ = strconv.ParseFloat(value, 64)
        case "DATETIME", "TIMESTAMP":
            raw, _ = time.Parse("2006-01-02 15:04:05", value)
        case "DATE":
            raw, _ = time.Parse("2006-01-02", value)
        case "TIME":
            raw, _ = time.Parse("15:04:05", value)
        case "NULL":
            raw = nil
        default:
            raw = value
        }
    // for oracle
    case string:
        // fmt.Println(colType, reflect.TypeOf(raw), raw)
        switch colType {
        case "NUMBER":
            raw, _ = strconv.ParseFloat(v, 64)
        case "DATE", "TIMESTAMP":
            raw, _ = time.Parse("2006-01-02 15:04:05", v)
        default:
            raw = v
        }
    }
    return raw
}

[sijms]

you convert number to float64 this will return back to issue #516 also using string with sql package is ok as it will convert to it requested data type automatically

[sijms]

if I return 3 types int64, float64 and string still client should do some work to deal with string which means issue still present

[elgs]

Well actually only string and float64 will be enough. This is the convention for other db drivers except MySQL. In MySQL's case, they scan everything into byte array. For most other drivers, like SQLite, Postgres, MSSQL and maybe a few others, they at least return float64 and string. But it's ok for this Oracle driver to return everything as string consistently. I will do the conversion on my end. Thanks for the explanation.