Support for more ODBC C Type(e.g. Numeric) buffers in AnyBuffer or alternative extension mechanism.

[topblast]

Hello,

I'm working with odbc-api (via arrow-odbc) for bulk inserts and ran into a limitation: AnyBuffer is great for common types, but it doesn't cover some cases like DECIMAL/NUMERIC (especially when using Always Encrypted, as it is very strict with operand type clashes). Right now, the only way to handle this is to drop down to odbc-sys to call SQLBindParameter with a custom implementation of AnyBuffer, which feels a bit clunky & redundant alongside the nice high-level API.

Suggestions:

• Either a way to extend AnyBuffer with custom variants (e.g. Decimal) like AnyBuffer::Timestamp, or
• A trait-based approach so downstream crates can implement their own buffer types and still use ColumnarBulkInserter.

Why this matters:

• Always Encrypted requires exact type binding, so text-based buffers won't work.
• Arrow integration often brings in Decimal128 columns, and mapping those cleanly into bulk inserts would be awesome without resorting to FFI.

Possible directions:

• Add Decimal variant to AnyBuffer.
• Expose a trait like BindableBuffer that ColumnarBulkInserter canwork with.
• Or even document a recommended pattern for mixing AnyBuffer with custom FFI bindings.

Side note?:
As an application depending on arrow-odbc which depends on odbc-api, I'm having to circumvent of both crates due to this limitation.

Happy to help with a PR if this sounds reasonable!

[pacman82]

Hello @topblast,

thanks for reporting the issue. I need to look into this. Having a numeric variant in AnyBuffer is the way to go, but it won't suffice. Numeric is different from the other types, that additional properties need to be set in the ADR record.

See here:

odbc-api/odbc-api/tests/integration.rs

Line 6045 in a413baf

fn fetch_decimal_as_numeric_struct_using_bind_col(profile: &Profile) {

Finding a safe abstraction will require careful reading of the ODBC standard and accounting for all the situations any buffer can be used in.

Very open to tackling this. Encryption is a valid enough reason to not just use string, but also to manage expectations: Do not expect a quick fix.

Best, Markus

[pacman82]

@topblast What Database do you use?

If it is e.g. PostgreSQL or MariaDB this might work easier than expected. Microsoft SQL Server is a bit harder.

Best, Markus

[pacman82]

@topblast odbc-api 20 is released. It features a Numeric variant of AnyBuffer. This might already enable you, if your Database respects precision and scale without manipulating APD or ARD.

I would be interested whether this is already helpful for you, or additional effort is needed.

Best, Markus

[topblast]

@topblast What Database do you use?

If it is e.g. PostgreSQL or MariaDB this might work easier than expected. Microsoft SQL Server is a bit harder.

Best, Markus

Hi @pacman82,

Thanks for the quick response and the update. I see that application_row_descriptor() is exposed, which lets me experiment with setting precision/scale on the ARD.

For Microsoft SQL Server with Always Encrypted, I’ll likely need APD access as well, since AE validates parameter precision/scale on both sides. I can retrieve those via the raw handle using SQLGetStmtAttr.

I’ll test this in v0.20 and report back on whether APD-only is sufficient for AE numeric parameters.
Really appreciate your time and support on this.

Best,
Rashawn

[pacman82]

Microsoft SQL Server

Microsoft SQL Server will assume scale 0. So you can test with DECIMAL and NUMERICS as long as the scale is 0.

You will need both APD and ARD.

• ARD for fetching
• APD for inserting

It may not be enough to set scale and precision, but you may need to also set the data-ptr and C-TYPE. In essence redo, the work of SQLBindParam.

Let me know how it works.

Best, Markus

[pacman82]

Apart from safe abstractions. I would know how to set ARD for fetching values with Microsoft SQL Server.

So far my experiments with manipulating the APD were not successful. Do you have a piece of unsafe code which does work?

Apart from this. Maybe we should verify that using a buffer with Numeric actually does solve your underlying problem. I would suggest an experiment using a Numeric Column with Scale 0. These would work right now out of the Box even with Microsoft SQL Server. This would allow us to verify that indeed using a Numeric buffer would not clash with encryption.

Best, Markus