IBM i DB2 Latin-1 /ISO-8859-1 columns

[sportfloh]

Hi,
I have a IBM i DB2 database with Latin-1 /ISO-8859-1 encoded CHAR columns and numeric single digit colums for bool values.
Until now I used odbc-api rather manually/ugly like this:

trait FromBoolIntStr {
    type Err;

    // Required method
    fn from_boolintstr(s: &str) -> Result<Self, Self::Err>
    where
        Self: Sized;
}

#[derive(Error, Debug, PartialEq, Eq)]
enum ParseFromBoolIntStrError {
    #[error("Parsing bool from int string failed")]
    ParseFromBoolIntStr,
}

impl FromBoolIntStr for bool {
    type Err = ParseFromBoolIntStrError;

    fn from_boolintstr(s: &str) -> Result<Self, Self::Err> {
        match s {
            "0" => Ok(false),
            "1" => Ok(true),
            _ => Err(ParseFromBoolIntStrError::ParseFromBoolIntStr),
        }
    }
}

fn string_from_latin1(bytes: &[u8]) -> String {
    let (decoded, _, _) = encoding_rs::WINDOWS_1252.decode(bytes);
    let value_str = String::from(decoded);
    value_str
        .parse()
        .expect("Encoding error, check the database!")
}

...
    match db2_connection.execute(sql_query, ())? {
        Some(mut cursor) => {
            let mut buffers = TextRowSet::for_cursor(BATCH_SIZE, &mut cursor, Some(4096))?;
            let mut row_set_cursor = cursor.bind_buffer(&mut buffers)?;

            while let Some(batch) = row_set_cursor.fetch()? {
                for row_index in 0..batch.num_rows() {
                    some_vec.push(SomeStruct {
                        string_field: string_from_latin1(batch.at(1, row_index).unwrap_or(&[])).parse()?,
                        bool_field: bool::from_boolintstr(&string_from_latin1(
                            db2_batch.at(2, db2_row_index).unwrap_or(&[]),
                        ))?,
                        i64_field: string_from_latin1(batch.at(3, row_index).unwrap_or(&[])).parse()?,
                    });
                }
            }
        }
        None => {
            eprintln!("Query came back empty. No output has been created.");
        }
    }
...

it works but is far from ideal.

I tried to use arrow-odbc but so far I haven't figured out if and how I can decode Latin-1 to UTF-8 while reading tinto a arrow batch.
I get the table schema and rewrite the Utf8 datatype to Binary, but then I need to add another column and encode the binary columns as utf-8 string. Hmm

    let mut schema = arrow_schema_from(&mut cursor)?;
    schema.fields = schema
        .fields
        .iter()
        .map(|f| {
            if f.data_type() == &DataType::Utf8 {
                (**f).clone().with_data_type(DataType::Binary)
            } else {
                (**f).clone()
            }
        })
        .collect();

    let arrow_record_batches = OdbcReaderBuilder::new()
        .with_schema(schema.into())
        .build(cursor)?;

Is it somehow possible to give arrow-odbc a conversion function/closure for such cases?
Idealy I'd like to read data from odbc source, define a mapping betwen data types, put the data into a data frame, calculate suff and write the data back to odbc source.
Any idears or pointers?
Thanks and cheers!

[pacman82]

Hi @sportfloh ,

ODBC offers to ways to fetch string data. Narrow and Wide. Wide strings are always guaranteed to be UTF-16, but Narrow Strings depend on the (Clients) system encoding according to ODBC standard, or whatever your driver thinks is appropriate.

So you could try out a bunch of things.

1. Change the locale of your system to UTF-8 and check if you now receive UTF-8 encoded data. Some drivers allow for specifying the desired encoding in the Connection String.
2. Fetch text as UTF-16. This is the default for arrow-odbc on Windows, since Windows does as of now usually not have an UTF-8 system encoding by default. Using odbc-api you can e.g. use ColumnarBuffer<TextColumn<u16>>. TextRowSet is just an alias for its narrow counterpart. See: <ColumnarBuffer<TextColumn<u8>>>
3. Fetch everything as binary data instead of text and decode it in your application logic. arrow-odbc also allows you to specify and map schema.

Best, Markus

[sportfloh]

Thanks for your quick response!
Turns out there is some broken whatever encoding in the table -.-
I'm on macOS and my locale is "en_US.UTF-8". So should be good.
My current solution looks like this:

    let mut cursor = db2_connection
        .execute(sql_statement, ())?
        .expect("SELECT statement must produce a cursor");

    let mut schema = arrow_schema_from(&mut cursor)?;
    schema.fields = schema
        .fields
        .iter()
        .map(|f| match *f.data_type() {
            // DataType::Utf8 => (**f).clone().with_data_type(DataType::Binary),
            DataType::Decimal128(1, 0) => (**f).clone().with_data_type(DataType::Boolean),
            DataType::Decimal128(_, 0) => (**f).clone().with_data_type(DataType::Int64),
            DataType::Decimal128(_, s) if 0 < s => (**f).clone().with_data_type(DataType::Float64),
            _ => (**f).clone(),
        })
        .collect();
    // dbg!(&schema);

    let arrow_record_batches = OdbcReaderBuilder::new()
        .with_schema(schema.into())
        .build(cursor)?;

    fn record_batch_to_dataframe(batch: &RecordBatch) -> Result<DataFrame, PolarsError> {
        let schema = batch.schema();
        let mut columns = Vec::with_capacity(batch.num_columns());

        for (i, column) in batch.columns().iter().enumerate() {
            let arrow = Box::<dyn polars_arrow::array::Array>::from(&**column);
            columns.push(Series::from_arrow(
                schema.fields().get(i).unwrap().name(),
                arrow,
            )?);
        }

        Ok(DataFrame::from_iter(columns))
    }

    for batch in arrow_record_batches {
        dbg!(record_batch_to_dataframe(&batch?)?);
    }

I found the conversion from RecordBatch to polars::Dataframe here: https://stackoverflow.com/questions/78084066/arrow-recordbatch-as-polars-dataframe

Thanks and cheers!

[pacman82]

Happy you found a solution