DecFP type conversion to Integer

question

#1

Hello, I’m a newbie and I’m using the ODBC package to query data and it’s returning integers as type Nullable{DecFP.Dec64}. I need to pass that as a string in a second query but it won’t let me convert the variable from Dec64 to Int or String.

I’ve tried both convert(Int,var) and Int(var) and they both return this error

ERROR: MethodError: Cannot convert an object of type Nullable{DecFP.Dec64} to an object of type Int64

The Nullable type is something I’m having a lot of trouble grasping how to handle. Any help?


#2

try convert(Int, get(x)) where x is your Nullable variable.
can use isnull(x) where appropriate.

see https://docs.julialang.org/en/stable/stdlib/base/#Nullables-1


#3

Thanks for the help, that worked, but is it just me, or is that just not unintuitive. Why do we have to “get” a value. Is there a case where convert(Int, x) is somehow ambiguous in what the end user wants to achieve?


#4

Well I suppose you could define something like this:

Base.convert(::Type{T}, x::Nullable) where {T} = convert(T, get(x))

#5

Thanks for the suggestion. But I’m just asking because of my inexperience with Julia and the nullable type. This isn’t intended as a confrontational question, but really for better understanding. Is there some use case where you wouldn’t always want to do this? What would break if the default convert used this logic?


#6

Missing data handling is undergoing a large change before 1.0 because nullables are just unintuitive to work with. This should essentially fix itself soon.


#7

Are you sure? DECIMAL in SQL isn’t just for integers, so first if you’re using that type, even just to store integers, consider using an SQL integer-only type (I see in ODBC.jl SQLDECIMAL and SQLINTEGER).

Also consider using a NOT NULL constraint (defined for a column with e.g. CREATE TABLE) as much as possible, at least for any kind of numbers.

If not, note that get "throws a NullException" at runtime if somehow a NULL gets into your database, and convert will throw an InexactError() in case a fractional value is in the database, as allowed for non-integer SQL types.

Converting to a string (for querying again) will not get you to query for the NULL automatically (see below), but will at least break less helping for fractional values.

I can’t see clearly from the code that Nullable is only used if you fail to use that constraint. It might be for either case to simplify.

Allowing NULLs is a bitch in SQL because when it happens you usually have to consider a) what to put in its place, say the DEFAULT 0 (you can specify that at least in PostgreSQL database). And b) you might either want to query all the time with (just an example) … WHERE value >= 0 OR value IS NULL


#8

I’m extracting from Oracle. Oracle uses number(38) for integers and this field has a not null constraint on it. Is it possible that there is some inefficiency in the Julia odbc libraries that isn’t interpreting this correctly?


#9

I’ve never used Oracle but it seems number(38) is ok, meaning will only return integers since you do not use “scale”.

It’s very possible that (at least) ODBC.jl (or interfaces it calls) treats NOT NULL and NULL columns the same. You of course get the type back, but this distinction may not be thought of as part of the type; possibly database interfaces will usually not give you that information, and just not provide NULL values where they can’t happen (you could also at any time relax the constraint, so that might be a reason, to not force you to change your code).

There seemed to be no other type for integers: https://docs.oracle.com/cd/B19306_01/olap.102/b14346/dml_datatypes002.htm

I however see e.g. INTEGER and LONGINTEGER here: https://docs.oracle.com/cd/B19306_01/olap.102/b14346/dml_datatypes002.htm

It might be new, and at least possible to check if it behaves differently.

Not sure how other databases or db interface-specific such as PostgreSQL.jl or SQLite.jl handles the situation, maybe somebody can check. I see:


“Integer and Bigint perform better than Number() when used for joins of large tables”

There’s also even JDBC.jl that can work with Oracle… not sure if better or only different…

I do also see:


"All values of type T that could be null should be returned as Nullable{T}."