ora
ora copied to clipboard
rana
converting OCINum to bool
Hi. I used
drvCfg.Env.StmtCfg.Rset.SetNumberBigInt(ora.I64)
and after restoring dump I have
sql: Scan error on column index 2: converting driver.Value type float64 (\"1e+06\") to a int: invalid syntax
which is linked to https://github.com/rana/ora/issues/57
The reason, why I used SetNumberBigInt(ora.I64) - to have auto scanning int to bool.
- Is it possible to have auto scanning OciNum(1) to true and 0 to false? I know you supporting booleans with Char (byte 1), but our scheme is different.
- or other way, maybe better - could we in
oralook at number precision, if it's 0 - we can suppose that it's int. And if dest if float - golang sql package will make it float. This way we can avoid losing big integers precision and work with build-in golang types. Thanks.
ok, I found
func (c *RsetCfg) numericColumnType(precision, scale int) (gct GoColumnType) {
function which implements nicely what I need.
Problem was that my column was just Number, which by default is prec=0, scale=-127
Thats why ora thought that this is float64
Yes, NUMBER column is with unknown scale, which does not fit in an int64. If you're interested, we can walk around a number<->bool conversion just like the current char<->bool does.
I have no idea how it could be done.
Because by time you are running
func (def *defOCINum) value(offset int) (value interface{}, err error) {
we don't know what destination is.
We know this only when executing Scan function.
To make it work we should implement our own convertAssign function, with case OciNum.
Then we should modify our code to use this forked function. And fork other libs, like sqlx.
We can allow ses.Sel to get ora.B/ora.OraB for numeric columns, and have 0 be false, everything else true. We could do the same for then config (SetNumberBigInt), but that would take effect for every number, not just that spec. column.
Maybe the easiest is to prepare the query with "num_bool" function, which may be
CREATE OR REPLACE TYPE typ_c1 IS VARCHAR2(1);
CREATE OR REPLACE FUNCTION num_bool(p_num IN NUMBER) RETURN typ_c1 DETERMINISTIC IS
BEGIN
IF p_num IS NULL THEN RETURN(NULL);
ELSIF p_num <= 0 THEN RETURN('F');
ELSE RETURN('T');
END IF;
END num_bool;
But even this simple function shows how non-trivial is this conversion... (The specific type is required to force the query column's type be CHAR(1), to allow ora to detect it's a Char1).
Idk, I'm already using
drvCfg.Env.StmtCfg.Rset.SetNumberBigInt(ora.I64), migrated all numbers to (19,0)
and using defaults go conversions. I guess this is the simplest way.
Other simple way - make DbBool which conforms Scannable protocol and support conversion from OraNumber.
And if possible to make type DbBool = bool it would be perfect.
Please help me here a little bit - I'm always lost with the database.Scanner / driver.Valuer / driver.ValueConverter What shall the driver return for a NUMBER column, and how to configure it? We can make it work as SetChar1(ora.B), but really, for all numeric columns? At least with go1.8, we'll have context.Context in Query, so we'll be able to push such instructions specifically for that specific statement.
But I've found the following statement in the documentation of https://golang.org/pkg/database/sql/#Rows.Scan
"For scanning into *bool, the source may be true, false, 1, 0, or string inputs parseable by strconv.ParseBool."
Would you try with scanning into a *bool?
really, I'm not good at it too. I can help how to use Scaner protocol. So you are making your own structure like type IntsArray = []int
then conform it to Scanner protocol.
Sql lib checking if destination supports Scanner - running this function.
e.g.
type DbIntsArray []int
func (self *DbIntsArray) Scan(src interface{}) error {
s := src.(string)
if len(s) == 0 {
*self = []int{}
return nil
}
arr := strings.Split(s, ",")
ids := make([]int, len(arr))
for i, id := range arr {
idInt, err := strconv.Atoi(id)
if err != nil {
return fmt.Errorf("received not int, v=%s", id)
}
ids[i] = idInt
}
*self = ids
return nil
}
type AuthUser struct {
Id int
AuthType int
Roles DbIntsArray
}
after scanning you have filled array there
(sql there is select joined(roles.id))
in convert.go on 192 line we have
case *bool:
bv, err := driver.Bool.ConvertValue(src)
if err == nil {
*d = bv.(bool)
}
return err
which executing on bool too. I guess you could inject your convertValue into your driver. default implementation is
func (boolType) ConvertValue(src interface{}) (Value, error) {
switch s := src.(type) {
case bool:
return s, nil
case string:
b, err := strconv.ParseBool(s)
if err != nil {
return nil, fmt.Errorf("sql/driver: couldn't convert %q into type bool", s)
}
return b, nil
case []byte:
b, err := strconv.ParseBool(string(s))
if err != nil {
return nil, fmt.Errorf("sql/driver: couldn't convert %q into type bool", s)
}
return b, nil
}
sv := reflect.ValueOf(src)
switch sv.Kind() {
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
iv := sv.Int()
if iv == 1 || iv == 0 {
return iv == 1, nil
}
return nil, fmt.Errorf("sql/driver: couldn't convert %d into type bool", iv)
case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
uv := sv.Uint()
if uv == 1 || uv == 0 {
return uv == 1, nil
}
return nil, fmt.Errorf("sql/driver: couldn't convert %d into type bool", uv)
}
return nil, fmt.Errorf("sql/driver: couldn't convert %v (%T) into type bool", src, src)
}
Should add case for OraNum
My main problem with all ConvertValue/Value methods is that they receive only one value, and must convert from that to a driver.Value type - which can be int, float and bool, too.
Only Scan knows what type the caller wants - neither OCINum.value, nor a general driver.ColumnConverter can decide what that value should be converted: int/float or bool. They decide they don't want to loose information, so use int/float.
Scan uses convertAssign, which knows the destination type, and calls driver.Bool.ConvertValue. driver.Bool converts integers, bool and strings, but does not know about floats.
But driver.Bool is a variable, so it can be overridden! Sth like
type oraBoolType struct {
driver.ValueConverter
}
func (o oraBoolType) ConvertValue(src interface{}) (driver.Value, error) {
switch x := src.(type) {
case float32:
return x == 1, nil
case float64:
return x == 1, nil
default:
// use the original implementation
return o.ValueConverter.ConvertValue(src)
}
}
func init() {
driver.Bool = oraBoolType{ValueConverter: driver.Bool}
}
If this works, we can raise it into the ora driver.
driver.Bool = oraBoolType{}
db/connection.go:198: cannot use oraBoolType literal (type oraBoolType) as type driver.boolType in assignment
exit status 2
// Value is a value that drivers must be able to handle.
// It is either nil or an instance of one of these types:
//
// int64
// float64
// bool
// []byte
// string
// time.Time
type Value interface{}
// ColumnConverter may be optionally implemented by Stmt if the
// statement is aware of its own columns' types and can convert from
// any type to a driver Value.
type ColumnConverter interface {
// ColumnConverter returns a ValueConverter for the provided
// column index. If the type of a specific column isn't known
// or shouldn't be handled specially, DefaultValueConverter
// can be returned.
ColumnConverter(idx int) ValueConverter
}
As I understood, stdlib is not flexible enough to work with custom types, make conversions. The only way is to return one of types above.