Getting all changes within a begin; end; transaction

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Getting all changes within a begin; end; transaction

Jose Isaias Cabrera-4

Greetings.

Imagine this SQL,

BEGIN TRANSACTION;
...
changes to records
...
END;

When I execute "int result = sqlite3_changes(database);" after that SQL execution, I always get 1.  I think that it is because it is only providing the result of the last statement that was successful within the BEGIN-END transaction.  I know that in all cases there should be many more than 1. Is there a way to know all the changes that may have happened within the full BEGIN and END?  Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting all changes within a begin; end; transaction

Simon Slavin-3
On 4 Mar 2020, at 2:37pm, Jose Isaias Cabrera <[hidden email]> wrote:

> Is there a way to know all the changes that may have happened within the full BEGIN and END?  Thanks.

Use this function

<https://sqlite.org/c3ref/total_changes.html>

before and after your block, and subtract one from another.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting all changes within a begin; end; transaction

Jose Isaias Cabrera-4

Simon Slavin, on Wednesday, March 4, 2020 09:42 AM, wrote...

>
> On 4 Mar 2020, at 2:37pm, Jose Isaias Cabrera <[hidden email]> wrote:
>
> > Is there a way to know all the changes that may have happened within
> the full BEGIN and END? Thanks.
>
> Use this function
>
> <https://sqlite.org/c3ref/total_changes.html>
>
> before and after your block, and subtract one from another.

Thanks, this is exactly what I needed.  However, I have found that it does not actually provide the **ACTUAL** changes, but a count of the possible changes.  The reason why I know is that if I have 238 INSERTS, but I have a constraint , there should be 238 INSERTs the first time I run a set of SQL, but if I run the same SQL again, there should not be any records INSERTED, and thus, the amount should be 0, correct?  So, is there one that actually provides the actual number of changes?  Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting all changes within a begin; end; transaction

Simon Slavin-3
On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera <[hidden email]> wrote:

> However, I have found that it does not actually provide the **ACTUAL** changes, but a count of the possible changes.

Hmm.  I understand you.  Does this do something more like what you need ?

<https://sqlite.org/pragma.html#pragma_data_version>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting all changes within a begin; end; transaction

Simon Slavin-3
In reply to this post by Jose Isaias Cabrera-4
On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera <[hidden email]> wrote:

> The reason why I know is that if I have 238 INSERTS, but I have a constraint , there should be 238 INSERTs the first time I run a set of SQL, but if I run the same SQL again, there should not be any records INSERTED, and thus, the amount should be 0, correct?

Can you try the same thing but instead of INSERT use INSERT OR IGNORE ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Getting all changes within a begin; end; transaction

Jose Isaias Cabrera-4

Simon Slavin, on Wednesday, March 4, 2020 10:47 AM, wrote...
>
> On 4 Mar 2020, at 3:28pm, Jose Isaias Cabrera <[hidden email]> wrote:
>
> > The reason why I know is that if I have 238 INSERTS, but I have a
> constraint , there should be 238 INSERTs the first time I run a set of SQL,
> but if I run the same SQL again, there should not be any records INSERTED,
> and thus, the amount should be 0, correct?
>
> Can you try the same thing but instead of INSERT use INSERT OR IGNORE ?

Simon Slavin, you are a genius!  Well, you're pretty smart. ;-)  Yes, indeed, I was using
INSERT OR REPLACE, which will always work. ;-)  INSERT OR IGNORE is now providing
the result I am looking for.  As we say in Spanish, muchas gracias.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users