Re: [sqlite] C API - Parameterized Atomic Transactions
Thank you very much, it worked! On Tue, Aug 9, 2016 at 11:49 PM, Richard Hipp <d...@sqlite.org> wrote: > On 8/9/16, Paulo Roberto <betobran...@gmail.com> wrote: > > > > I found your solution pretty elegant and I tried to implement it. > > But after solving a lot of building issues with the sqlite3ext header > > It does not have to be implemented as a loadable extension. Just copy > the lines https://www.sqlite.org/src/artifact/8440f8d0b4?ln=41-53 into > your application, then invoke > https://www.sqlite.org/src/artifact/8440f8d0b4?ln=65-66 on the > database connection right after you get it back from sqlite3_open(). > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C API - Parameterized Atomic Transactions
Thank you for all the answers. Clemens, The counterid in my case is a text field and not an integer. That's why I need to sanitize. Clemens and Keith, As each of my process has its own connection to the database, I tried your solution using BEGIN IMMEDIATE and it worked successfully. Thank you. Richard, I found your solution pretty elegant and I tried to implement it. But after solving a lot of building issues with the sqlite3ext header I was not able to figure out how to use the function sqlite3_remember_init even reading the MACROS definition. My problem is with the parameter : const sqlite3_api_routines *pApi No matter what value I pass I keep receiving the signal SIGSEV i.e. Segmentation fault. Do you have another reference to give to me? Thanks for your help. Regards. Paulo Roberto. On Tue, Aug 9, 2016 at 9:07 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > > "BEGIN EXCLUSIVE TRANSACTION;" > > "SELECT counter FROM mytable WHERE counterid = ?;" > > "UPDATE mytable SET counter=? WHERE counterid = ?;" > > "COMMIT TRANSACTION;" > > > I have a counter that I need to increment and get its previous value in > one > > operation. > > To access this counter I must pass as a parameter to the query a specific > > WHERE condition. > > To paraphrase: > > I want to return the current value of a counter identified by an id and > then increment it. This operation must be atomic (have repeatable read > isolation). > > BEGIN IMMEDIATE; > SELECT counter FROM mytable WHERE counterid = ?; > UPDATE mytable SET counter = counter + 1 WHERE counterid = ?; > COMMIT; > > Of course, the 1 in the increment does not need to be a constant but can > be a ? if you are incrementing by some arbitrary value. > > You need to prepare and execute the statements one after each. > > And yes, the select and update, performed inside the same transaction, on > a connection not being simultaneously used for "other purposes" is executed > with repeatable read isolation. > > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] C API - Parameterized Atomic Transactions
Hello, I need some help to do a simple operation, increment a counter and get its former value. I could have some race condition, so the transaction must be atomic. I also would like to use prepared statements to accomplish that, so I have less effort sanitizing inputs. My problem: I have a counter that I need to increment and get its previous value in one operation. To access this counter I must pass as a parameter to the query a specific WHERE condition. I would like something like this: "BEGIN EXCLUSIVE TRANSACTION;" "SELECT counter FROM mytable WHERE counterid = ?;" "UPDATE mytable SET counter=? WHERE counterid = ?;" "COMMIT TRANSACTION;" The *sqlite3_exec* doesn't allow me to pass parameters. And the documentation of *sqlite3_prepare_v2 *says: "*These routines only compile the first statement in zSql, so *pzTail is left pointing to what remains uncompiled*" My question is: Preparing 4 statements, binding then and calling *sqlite3_step *for each one of then in order, would have the expected atomic operation behavior or not? If not, how could I achieve this? Thank you in advance. Regards. Paulo Roberto. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
