Expose OCINumber (dpiNumber)

[felipenoris]

See commit in branch https://github.com/felipenoris/odpi/tree/fn/numeric.

The following changes allow odpi users to bind and read dpiNumber from a variable

• native type DPI_NATIVE_TYPE_NUMBER

• dpiNumber struct with 22 bytes

• asNumber variant for dpiDataBuffer. It does not increase sizeof(dpiDataBuffer).

• dpiData_getNumber and dpiData_setNumber

• dpiDataBuffer__fromOracleNumberAsNumber

• dpiDataBuffer__toOracleNumberFromNumber

• dpiOci__numberAssign wraps OCINumberAssign

• dpiVar now reads and writes data of native type DPI_NATIVE_TYPE_NUMBER.

Ref #4 .

[anthony-tuininga]

I will take a look later when I have a bit more time available.

[felipenoris]

If it would make things easier, I can isolate the arithmetic issue in a separate commit, since this is the only non-working portion of this development.

[tgulacsi]

I'm a C noob, but can you use a pointer to some value-received structs? In "dpiNumber_add(const dpiNumber number1, const dpiNumber number2, dpiNumber *result)" but use it as "dpiOci__numberAdd(&error, &number1, &number2, result)".

Why not *dpiNumber all the way up?

[felipenoris]

Yes, that was my first attempt btw. I'll just revert to that and separate the commits.

To answer your question, I did this because I can't make this function call to work, so I tried an alternative. This is the non-working part of the commit.

[felipenoris]

So, for the sake of simplicity, I removed all non-working parts of the commit at https://github.com/felipenoris/odpi/tree/fn/numeric, which are related to dpiNumber arithmetic.

With this, the patch will only expose a dpiNumber. This is already useful because users can convert dpiNumber to other formats directly from the binary format, instead of having to rely on an intermediate string format.

I also updated the first post of this issue.

[felipenoris]

@anthony-tuininga , please let me know if you need anything else on this.

[anthony-tuininga]

@felipenoris, I don't think I need anything else. Thanks. I'm not sure when this will be implemented. As you're likely aware there are other priorities!

[acautin]

@anthony-tuininga this would be helpful as for me as I am trying to work with a database explorer which uses erlang/odpi as the driver in erlang. Precision is lost on the way out and I am trying to understand if this is a limitation of odpi or the erlang driver on top of it.

Do you have an example reading numeric values similar to SELECT 2.700000000000005 FROM dual with full precision ?. (text or otherwise).

[anthony-tuininga]

@acautin, you can tell ODPI-C to fetch the data as a string representation of the OCI number instead of a double precision floating point number (the default). You can see this happening in the sample. If you create variables instead (by calling dpiConn_newVar()), then make sure the nativeTypeNum parameter is set to DPI_NATIVE_TYPE_BYTES. Either of these methods will return a string representation of the OCI decimal number without any loss of precision.

[felipenoris]

Hi @anthony-tuininga ! Long time! I would like to ask if it is possible for you to take a look at this. See commit https://github.com/felipenoris/odpi/commit/3204a058ab5416b776d936c2a87d24a9d99f905b . The idea is to just expose a dpiNumber struct.

[anthony-tuininga]

Yes, it has been a while! The commit you reference seems to be simply exposing the 22 bytes of data -- which are in a format that is not easily consumed. How do you intend to consume and generate those 22 bytes of data?

[felipenoris]

@anthony-tuininga thanks for the reply!

The idea is to let driver developers to access the raw 22 bytes of OraNumber, instead of having to rely on the string representation to get the exact fixed decimal representation.

Obviously the driver developer has to parse these 22 bytes.

I'm already doing this in Oracle.jl. These bytes are loaded into this 22-bytes struct:

struct OraNumber <: Real
    len::UInt8
    ex::UInt8
    mantissa::NTuple{MAX_SIZEOF_MANTISSA, UInt8}
end

From that, I can either implement an API around this struct, or translate to other fixed decimal format.

Hopefully, using these 22-bytes directly is faster than converting from a string representation.

[anthony-tuininga]

One possibility that might make sense: instead of targeting OCINumber directly, I could add a new "native type" called DPI_NATIVE_TYPE_OCI which simply provides access to the OCI value directly. You can use it for OCINumber but it could be used for other types as well in the future, if that proves useful. I might also expose methods to convert OCINumber to double and text, simply for testing purposes. I believe that should work for you but comments are welcome!

[felipenoris]

@anthony-tuininga, yes, I think that works, as long as it returns a copy of the data and not a volatile reference in memory. The dpiNumber struct I created was just an auxiliary struct for transferring OCINumber to/from ODPI-C.

[anthony-tuininga]

I'm not sure what you mean by "volatile reference in memory". The 22 bytes will be stored in the variable buffers and dpiDataBuffer.asOci will be a pointer to that buffer. I believe that's what you meant but can you confirm?

[felipenoris]

@anthony-tuininga , yes I think that works. I'm sure you understand the internals of OCI better than I do. I had to rely on OCINumberAssign to copy the data in a dpiData field so that I could access the data (also setting isNull information on dpiData). I understand that what you're suggesting is a more general approach.

If I select the native type DPI_NATIVE_TYPE_OCI how will I know that it will return a OCINumber and not other data structure? (possible answer: by combining with the information on the Oracle Type + Native Type) ?

[anthony-tuininga]

If I select the native type DPI_NATIVE_TYPE_OCI how will I know that it will return a OCINumber and not other data structure? (possible answer: by combining with the information on the Oracle Type + Native Type) ?

Yes, you would look at the Oracle Type to determine what actual value you are examining.

[felipenoris]

@anthony-tuininga that would be awesome!

[tgulacsi]

Hi,

Go's database/sql seems to get support for decimal handling: https://github.com/golang/go/issues/30870#issuecomment-717358078

Which means I'm interested in getting/setting OCINumbers directly.

[cjbj]

Go's database/sql seems to get support for decimal handling: golang/go#30870 (comment)

Neat!