Inserting NULL values with ColumnarBulkInserter

[jamesetaylor-tudor]

I would like to insert nulls with a ColumnarBulkInserter. Is that possible? I see that ColumnarAnyBuffer has a nullable slice view but I don't see anything similar for the bulk inserter.

[pacman82]

Short answer: yes you can. Adapting the code example from the documentation to show how it is done:

use odbc_api::{Connection, Error, IntoParameter, buffers::BufferDesc};

fn insert_birth_years(conn: &Connection, names: &[&str], years: &[Option<i16>]) -> Result<(), Error> {

    // All columns must have equal length.
    assert_eq!(names.len(), years.len());

    let prepared = conn.prepare("INSERT INTO Birthdays (name, year) VALUES (?, ?)")?;

    // Create a columnar buffer which fits the input parameters.
    let buffer_description = [
        BufferDesc::Text { max_str_len: 255 }, // <- variadic sized types are always nullable
        BufferDesc::I16 { nullable: true}, // <- Important to specify nullable in the buffer description of fixed size type
    ];
    // The capacity must be able to hold at least the largest batch. We do everything in one go, so
    // we set it to the length of the input parameters.
    let capacity = names.len();
    // Allocate memory for the array column parameters and bind it to the statement.
    let mut prebound = prepared.into_column_inserter(capacity, buffer_description)?;
    // Length of this batch
    prebound.set_num_rows(capacity);


    // Fill the buffer with values column by column
    let mut col = prebound
        .column_mut(0)
        .as_text_view()
        .expect("We know the name column to hold text.");

    for (index, name) in names.iter().enumerate() {
        col.set_cell(index, Some(name.as_bytes()));
    }

    let col = prebound
        .column_mut(1)
        .as_nullable_slice::<i16>() // Ask for a nullable slice
        .expect("We know the year column to hold nullable i16.");
    for (index, maybe_year) in years.enumerate() {
        col.set_cell(index, *maybe_year);
    }

    prebound.execute()?;
    Ok(())
}

I just adapted the sample without compiling it, so some errors are possible.

Longer answer:

The ColumnarBulkInserter is a very generic type. Wether or not it supports NULL or which types of values, is all defined by the column types bound to it. Using this crate you are most likely to bind an AnyBuffer to it which decides at runtime precisely what is supported for each column based on the buffer description.

If this does not help please do not be afraid to ask again.

Best, Markus

[jamesetaylor-tudor]

thanks, that's really helpful; I hadn't seen that as_nullable_slice was implemented there.

[pacman82]

Yeah it is not implemented on ColumnarBulkInserter itself. Yet the view on the bound column buffer allows it if the bound buffer is AnyBuffer which is actually the same buffer one would typically bind to a ColumnarBuffer. So it is the same implementation. Generic programming is really powerful, but it is helpful to see an example how things fit together. Rust Analyzer also is a big help here.

[pacman82]

For really elaborate generic implementations you can check out the code of the odbc2parquet command line tool, or the arrow-odbc bindings.