[SQLite] Should running a parameterized update statement with execute be possible?

[margaretselzer]

I have the following update statement:

auto u =
    sqlpp::update(t)
        .set(t.checksum = sqlpp::parameter(t.checksum))
        .where(sqlpp::verbatim<sqlpp::unsigned_integral>("ROWID")
               == sqlpp::parameter(sqlpp::unsigned_integral(), sqlpp::alias::i));

auto u_stmnt = db.prepare(u);

Running this query like this: db(u_stmnt); works perfect.

However, using db.execute(u_stmnt);
results in compile time error: no matching function for call to ‘serialize(const sqlpp::prepared_update_t<sqlpp::sqlite3::connection_base, sqlpp::statement_t<void, sqlpp::update_t, sqlpp::single_table_t<void, MyTable>, sqlpp::update_list_t<void, sqlpp::assignment_t<sqlpp::column_t<MyTable, MyTable_::field_checksum>, sqlpp::parameter_t<sqlpp::unsigned_integral, sqlpp::column_t<MyTable, MyTable_::field_checksum> > > >, sqlpp::where_t<void, sqlpp::binary_expression_t<sqlpp::verbatim_t<sqlpp::unsigned_integral>, sqlpp::op::equal_to, sqlpp::parameter_t<sqlpp::unsigned_integral, sqlpp::alias::i_t> > > > >&, sqlpp::sqlite3::connection_base::_context_t&)’

Now, as I am running an update I really do not need any kind of result back so my gut tells me that this should work with execute(), but it does not.

Is my gut talking nonsense or - against all odds - it does have a point?

[rbock]

Thanks for the question. As of today, the db(u_stmnt); is the correct way.

That said, your gut has a point: It should probably be allowed to do db.execute(u_stmnt);. Do you want to try preparing a PR for that?

[margaretselzer]

I'd be glad to help out with that, but then you need to tell me where I should intervene. Based on the error message I guess that a new overload of serialize() is required, but then the same query can be serialized via the "non-execute" way, so it might be execute () that needs tweaking?

If you think that even after revealing my ignorance I can be useful, let me know how and I will put that together. Then hopefully the next time I will need less hand-holding... ;)

[rbock]

You pretty much answered your questions yourself :-)

I would try to add another overload for sqlite3::connection::execute for anything that fulfills is_prepared_statement_t<T>::value == true.

[margaretselzer]

Will give it a try sir! :)

[margaretselzer]

I have the patch ready, but I seem to be unable to push my changes: do not have permissions. How is one supposed to submit a PR here?

git push -u --recurse-submodules=check --progress "origin" refs/heads/issue_596:refs/heads/issue_596
remote: Permission to rbock/sqlpp11.git denied to margaretselzer.
fatal: unable to access 'https://github.com/rbock/sqlpp11.git/': The requested URL returned error: 403
Done

Update
Tried with SSH as well, can clone the repo, but have the same permission denied issue when trying to push a new branch.

git push -u --progress "origin" refs/heads/issue_596:refs/heads/issue_596
Enter passphrase for key 'xxx':
ERROR: Permission to rbock/sqlpp11.git denied to margaretselzer.
fatal: Could not read from remote repository.

Please make sure you have the correct access rights
and the repository exists.
Done

[MeanSquaredError]

@margaretselzer

You need to :

• Fork the sqlpp11 project on GitHub, using the fork button in the upper right part of the screen.
• Setup the git credentials for your forked copy of sqlpp11. See below.
• git clone https://your-forked-sqlpp11-repository
• Make the changes to your local (cloned) copy of sqlpp11
• git push your local copy of sqlpp11. This will update your forked copy of sqlpp11 on GitHub.
• Go to the GitHub page for your forked copy of sqlpp11. Then click on the [Contribute] button to make a Pull Request.

The location of git credentials varies on different system, but on Fedora Linux they are in

In ~/.config/git/config there are a couple of relevant sections

...
[user]
	name = Your_Name
	email = The_NoReply_email_provided_by_github
...
[credential]
	helper = store
...

In ~/.config/git/credentials there should be one line:

https://Your_Name:[email protected]

Your_Name and Your_Auth_Token are the credentials provided by GitHub.

[margaretselzer]

@MeanSquaredError thanks for the detailed guide, really appreciated.

I am used to the [dev branch from main] --> [do your changes in dev branch] --> [create PR from dev branch] --> [merge dev branch back to main branch] approach, but one learns something new every day. Now I do not have to wonder why are there so many forks of this project. :-)

[MeanSquaredError]

@margaretselzer

Indeed the way most people commit changes to the upstream/original project is by making changes to a separate local branch, then pushing that branch to their own fork and then creating a PR to merge their branch to the upstream. This approach would work in your case too, but for brevity I omitted the step where you create a separate branch for your changes.

The critical thing which caused the push error in your case is that you are only allowed to push to github repositories for which you have the necessary permissions. In most cases these are your own projects (either the ones that you created or your forks of 3rd party projects) for which you have the corresponding permissions and your have configured the auth credentials in your git client.

That is why you can push to your forks of 3rd party projects (in this case sqlpp11) as long as you get your auth credentials and configure them in your git client. However you cannot push to the 3rd party projct (in this case sqlpp11), because your auth credentials don't have the permissions for that.

[margaretselzer]

@MeanSquaredError

I get the concept, it is the "own fork" part that was new to me as it feels a bit unnecessary. Limiting the main branch to not accept direct commits (to outsiders at least) should be enough because that way the only way in to main is via a PR. But I see now that this is the GitHub way of doing things so learning and adapting... After you spelled it out for me it was a rather straightforward process. I hope the PR looks ok. :)