FR: Table-valued parameter

[sphawk]

Table-valued parameters are useful for handling multiple rows in stored procedure. but mysql, postgres, sqlite doesn't support it. (only mssql support it. maybe oracle? I don't know) other databases can minic this feature using json (but less performance..)

If any maintainers interested in this feature, I'll make pull request within a few weeks.

Table-Valued Parameters (ODBC) https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-table-valued-parameters/table-valued-parameters-odbc?view=sql-server-ver15

Use Table-Valued Parameters (ODBC) : example https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-how-to/use-table-valued-parameters-odbc?view=sql-server-ver15

[mloskot]

Have you reviewed nanodbc for what it actually is missing in order to support TVP?

[sphawk]

describe how to bind TVP parameters.

• call SQLDescParam with param index, get type SQL_SS_TABLE and TVP name
• call SQLSetStmtAttr with SQL_SOPT_SS_NAME_SCOPE, SQL_SS_NAME_SCOPE_TABLE_TYPE, SQLColumns, SQLGetData, get TVP column data types.
• call SQLBindParameter with SQL_SS_TABLE
• call SQLSetStmtAttr with SQL_SOPT_SS_PARAM_FOCUS, param index
• repeat call SQLBindParameter with each TVP columns
• call SQLSetStmtAttr with SQL_SOPT_SS_PARAM_FOCUS, param index 0

hmm. I think nanodbc actually missing all. we need make it from scratch

[mloskot]

Fine, then how those would integrate into the abstraction/facade offered by the nanodbc? i.e. what kind of modifications to the existing classes like statement or what new classes we need to wrap the TVP functionality

It may be easier to discuss a living code, so feel free to start a work-in-progress pull request as early as you wish, even if it does not compile cleanly on CI. I just suspect it may take some more iterations of review/changes of the PR before we all agree on the shape of that feature.

I'd suggest to start the PR with syntax-first approach by writing tests (even if they don't compile or pass) as examples presenting how that feature would be used from perspective of nanodbc calls.

[sphawk]

ok. I'll try it within a few weeks.

[sphawk]

@mloskot Is this feature needs configuration like NANODBC_ENABLE_TVP? pros: if you don't use mssql, you can remove useless feature easily. cons: too complex build configuration, test sets..

[mloskot]

It either needs NANODBC_MSSQL_ENABLE_TVP or NANODBC_MSSQL_DISABLE_TVP :-)

The former if the feature is too 'intrusive' to have it enabled by default. The latter if the feature can be enabled by default and it won't affect users, that is, users can safely ignore it.

What if the feature is enabled and a user runs nanodbc client against e.g. MySQL or PostgreSQL and issues any of TVP calls?

[sphawk]

@mloskot If the feature is enabled and a user runs nanodbc client against e.g. MySQL or PostgreSQL and issues any of TVP calls, auto tvp = nanodbc::tvp(stmt, 1, num_rows); will throw programming_error("invalid tvp param type"); because other odbc drivers do not use sql param type SQL_SS_TABLE (-153) see #259, nanodbc/nanodbc.cpp line 1463

[mloskot]

Good. Let's move the discussion about the TVP to the PR #259 completely then

[sphawk]

#259 merged