-
Notifications
You must be signed in to change notification settings - Fork 40
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Iterative bind (for an insert with dynamic number of columns) #71
Comments
This is not supported currently, as the number of columns for each data type needs to be fixed. That said, what would the statement itself look like? Or do you generate a new statement each time? Can you provide a more specific example? |
Thanks a lot @d-frey for your consideration, its appreciated!! A pseudo-code example would be something along those lines: std::vector<std::vector<std::variant<uint64_t, double, std::string>>> vDataTable;
std::vector<std::string> vDataTableHeader;
const size_t vDataTableRows = vDataTable.size();
const size_t vDataTableCols = vDataTable.front().size();
const std::string vInsertStatement =
"INSERT INTO TestTable (" +
mystring::join(vDataTableRows, ", ") +
") VALUES(" +
mystring::generateIntegerSequence(vDataTableCols) +
");";
const auto conn = tao::pq::connection::create( "dbname=template1" );
const auto statement = tao::pq::prepare( conn, vInsertStatement );
for(size_t vRowIdx = 0; vRowIdx < vDataTableRows; ++vRowIdx) {
for(size_t vColIdx = 0; vColIdx < vDataTableCols; ++vColIdx) {
// Iterative binding of the n-th parameter:
statement.bind(vColIdx, vDataTable[vRowIdx][vColIdx]);
}
statement.execute();
statement.clear_binding();
} I know that for example SQLiteCpp supports iterative binding in their Admittedly I'm not sure about performance or safety considerations, though! However I think performance is not the critical aspect. It would be just awesome if runtime-sized statements could be supported. PS: My pseudo-code is missing a number of aspects like setting the data in |
The main issue I see is lifetime and temporaries, there's a much larger potential for errors. Or you need to copy everything which makes things inefficient, up to a point where I'm no longer comfortable with it. Also, the libpq API requires arrays, and I'd like to avoid dynamic allocations of those (or fixed limits). Don't get me wrong, I'll look into it and see what I can do, but I see quite a few challenges... |
I added some experimental support for |
Awesome work @d-frey , and thanks a lot for this quick consideration! Due to the holidays I may not have time to look into it this week, but I'll give it a try before the new year. Looking forward! |
I put some more work into it, temporaries are now moved into a holder if necessary and there are some optimizations to skip the temporaries if the underlying data does not reference the original value. Anyway, I'll continue to improve it and then add some documentation when I get to the end. |
Awesome! |
I have a use case where I'd like to store tables with a dynamic number of columns. The use case is from analysis measurements, where users can configure the actually computed measurements with a simple on/off selection. I receive the measurements as a vector of vectors which represent the columns.
It seems not directly possible to use the parameter binding with such dynamic data, or is it? In my naiive understanding it seems I could loop over the columns and bind value by value, iteratively. But taopq seems not to support this.
Am I even on the right track? How to work with a dynamic set of columns?
The text was updated successfully, but these errors were encountered: