[CONC-620] Please add support for accessing st_mysql_stmt.bind field Created: 2022-12-24  Updated: 2023-12-06

Status: Open
Project: MariaDB Connector/C
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Trivial
Reporter: Ivan Assignee: Georg Richter
Resolution: Unresolved Votes: 0
Labels: Performance

Issue Links:
Relates
relates to CONC-348 Add callback support for prepared sta... Open

 Description   

Hi!
First of all, an excellent library, a pleasure to work with.

I'm fetching a statement execution result into unrelated C++ types, and i do it row by row via `mysql_stmt_bind_result`
call for each new row like this:

  1. Create an instance of my C++ type
  2. set up an array of `MYSQL_BIND` structures to point into that instance
  3. call `mysql_stmt_bind_result` with that array
  4. call `mysql_stmt_fetch` and somehow resolve potential truncations with `mysql_stmt_fetch_column` calls

and it works wonders.

Doing some benchmarks i noticed that `mysql_stmt_bind_result` call seems to be very costly, because it copies the whole array into `st_mysql_stmt.bind` field and that `memcpy` shows.
However in my cases this copy is actually redundant and instead of doing 2. and 3. in aforementioned process i can just update the result bind array stored in `st_mysql_stmt` in place, and not only it works just fine, but also allows one to avoid copying binds array for every new row.

Right now i'm accessing that `st_mysql_stmt.bind` field directly, but it would be much more convenient to access it via an API call instead, something in the lines of `mysql_stmt_get_result_bind` or like that.

I do know that `STMT_ATTR_CB_RESULT` exists, however it's not documented and i'm perfectly fine with mariadb-connector-c taking the burden of fetching/parsing data into `MYSQL_BIND` for me.

 Comments   
Comment by Georg Richter [ 2022-12-25 ]

Hi Ivan,

why do you need to rebind for every new row?

Usually you bind your result array via mysql_stmt_bind_result() directly after execute, then you fetch row by row without rebinding.

Comment by Ivan [ 2022-12-25 ]

Hi Georg!

I provide an API that allows users to fetch results into not necessary contiguous in memory containers of their choice, and although i could fetch into an array first and proceed with it, that would require not only an additional potentially heavy allocation, but also an additional copy of all the data.

Or is there something i'm missing in how mysql_stmt_bind_result works?

Comment by Ivan [ 2022-12-25 ]

To add to that, i'm limited to row-wise binding, and even if mysql_stmt_fetch would follow the same logic mysql_stmt_execute does with STMT_ATTR_ROW_SIZE, that would still be off the table for me, because i'm operating on C++ types and pointers arithmetic just doesn't work - there is no portable way to get an address of a pointer of std::string, for instance.

Comment by Georg Richter [ 2022-12-25 ]

In this case, it might be better to use a callback function which can directly process raw data.
For an example check the MariaDB Connector/Python implementation: https://github.com/mariadb-corporation/mariadb-connector-python/blob/1.1/mariadb/mariadb_codecs.c#L578

Comment by Ivan [ 2022-12-25 ]

I see; will save this link for future references, thank you.

That definitely could be done, but as i said mariadb-connector-c doing all the parsing for me is way too handy to throw away)

Basically what i'm implementing is almost exactly how STMT_ATTR_CB_PARAM works (and i use that to do batch inserts from those user-provided containers), but in reverse. The notably difference being is that CB_PARAM operates on binds array, and allows me to update the binds in place, when all the result callbacks i've seen so far expect me to do parsing myself, and the idea of doing it makes me uneasy.

With this feature request i'm trying to get the best of both worlds - leave parsing to mariadb-connector-c via MYSQL_BIND, but still have a way to update binds of that bind array mariadb-connector-c uses internally, be that way a callback-based approach or a direct access to the st_mysql_stmt.bind field.

Do you find this idea reasonable, or am i going the wrong direction?

Generated at Thu Feb 08 03:06:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.