Testing Oracle

[jhowa1]

I am testing arrow-odbc with Oracle, was a test plan used for SQL Server? I would follow that plan as closely as possible and share results.

[pacman82]

Hello @jhowa1 ,

thanks for reaching out. Sorry it takes me some time to answer. My professional career leaves me at the moment little time for my Open Source Pet projects.

To answer your actual question. As you can see this repository has an automated test suite in the tests . These are all the tests I needed to drive the development of the Rust to C to Python bindings you see in this crate. Exchanging the MSSQL connection string with one which would reach an Oracle instance may prove enlightening.

The actual functionallity of Apache Arrow and ODBC interaction is part of a Rust crate also called arrow-odbc. Its repository can be found at https://github.com/pacman82/arrow-odbc. It also features a test suite. Here too switching the connecting string might enable you to run the tests against oracle in principle. Since this artefact is written in Rust, the tests can be executed using cargo test.

arrow-odbc is in turn build on top of the arrow and the odbc crates. For Databases the odbc crate is more interessting. This crate already features a test setup against different database systems, besides MSSQL. I once spend two Saturdays trying to figure out a maintainbale, legal way to add Oracle to this test setup. Ultimatly I decided I have better things to do in my spare time. If Oracle should provide a database docker image on a pubilc registry and a driver which is neatly packaged, both with a permissive license which would at least allow me to use them in a test setup I would be very open to include them in an automated CI setup.

From my manual tests I learned that the ODBC diagnostics of Oracle are horrible and could the cases I stumbled upon not be interpreted without looking into C headers and reverse engineering the value of constants. I also learned that they still do not support 64Bit Intergers. Inserting 64Bit integers into an Oracle is one of the tests I would expect to fail if running the arrow-odbc (Rust crate) tests against that database. I do not know what else you might find.

This being said, overall I should work just nicely. Anecdotical "evidence" shows people using this and the similarly structured odbc2parquet CLI tool with Oracle without too much hassle.

If you tell me your motivation for why you want to test with this Oracle I might be able to help even better. So far I would just suggest as a first step running the tests in this artefact against a different ODBC connection string. See what (if anything) yields an error. Happy to investigate together. Even better if we can reproduce the setup so we can include it in CI.

Best, Markus

[jhowa1]

Hello!
I appreciate your response. Oracle has a long history (always been?) of being sloppy about the adherence of its ODBC drivers to standards and its database. 64bit ints have been handled incorrectly in their ODBC drivers since their introduction.

The dilemma of open source - love it, will help with it, but I need to pay bills.

I'm utilizing Oracle 23c via Virtual Box now and everything is wonderful. My reading of the corresponding license agreements concludes it is ok to test code against this stack. I'll run the tests artifact as you suggested.

I'd be happy to discus more details with my goals with you directly.

Cheers,
Jack

[pacman82]

Hello @jhowa1 , added you as collaborater to a contact repository. So you can contact me directly without sharing information in a public forum.

Best, Markus

[jayctran]

Hi, just wondering if anything was progressed further with Oracle testing here? I did test out arrow-odbc-py with Oracle but seeing some issues around timestamps and precision - it's losing the fractional seconds, not sure if this is due to Oracle's sloppy adherence for ODBC standards or a bug.

pyodbc is working correctly when comparing results when working with Polars.

[pacman82]

@jayctran I am unable to tell you that either. I would recommend open a separate issue for that. Please include the values of the database. The relational type of the column and include logs with debug level activated (See: https://arrow-odbc.readthedocs.io/en/latest/arrow_odbc.html#arrow_odbc.log_to_stderr)