How do I bind database Text type data in buffers::RowVec?

[doveq]

Hi there, how can I bind database Text type data in buffers::RowVec? Is setting a sufficiently large VarCharArray the only solution, or are there other approaches?
Thank you.

[pacman82]

Hello @doveq ,

Short answer: Yes.

It is a strict requirement of bulk fetching with ODBC that the memory layout must be a contiguous buffer. So there is currently now way to have heap allocated dynamically sized string.

Best, Markus

[doveq]

Thank you for the quick reply Markus.

[pacman82]

You are welcome. I missed an opportunity though to ask you about your use-case and what it is about VarCharArray which does not work for you? That the size needs to be known at compile time? Would a columnar buffer fit your needs?

Best, Markus

[doveq]

My use case involves reading table data similar to the example below, where the content field is of text type with variable length. Currently, I'm considering using VarCharArray<65535> to store the read data, but allocating a fixed buffer of 65,535 bytes would waste significant memory. Alternatively, using Cursor::next_row is discouraged by the documentation due to performance concerns.
Thank you Markus.

CREATE TABLEuser_comments(  idbigint NOT NULL AUTO_INCREMENT,  user_idbigint NOT NULL,  contenttext NOT NULL,  parent_idbigint DEFAULT NULL,  reply_tobigint DEFAULT NULL,  entity_typevarchar(32) NOT NULL,  entity_idbigint NOT NULL,  statustinyint NOT NULL DEFAULT '1',  like_countint NOT NULL DEFAULT '0',  reply_countint NOT NULL DEFAULT '0',  created_atdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  updated_atdatetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, )

[pacman82]

As an alternative to RowVec you can also use a ColumnarAnyBuffer. Which as the name indicates stores the content 'column by column' rather than row by row. It further allows you to set the size of the text columns at runtime rather than compile time, but you still need to decide on the size, before fetching the values.

See: https://docs.rs/odbc-api/latest/odbc_api/buffers/struct.ColumnarBuffer.html

but allocating a fixed buffer of 65,535 bytes would waste significant memory

Bulk fetching in ODBC requires a big enough allocation, to hold NUMBER_OF_FIELDS_FETCHED * LARGEST_EXPECTED_FIELD. The question is: Are the contents in the database really 65,535 bytes long? If yes, the memory is likely a good investment, because bulk fetching is a lot faster if you fetch a lot of rows.

Best, Markus

[doveq]

Thank you for your help.