Using UpdateWithChildren in a transaction results in error: 'SQL logic error or missing database'

[Stefan Stumpfl]

I'm trying to establish a circular many to many relationship similar to the one in the example / tests.

As long as I'm doing it as its done in the TestManyToManyCircular() it does work and the intermediate table gets populated as expected. 1. Inserting relevant objects into db 2. Establishing relationships through 'Parent' property 3. Calling conn.UpdateWithChildren() for each object This flow works.

Since I've a few hundred objects this takes quite some time and I would like to do this in a transaction. But as soon as I try to call conn.UpdateWithChildren() in a conn.RunInTransaction() block I'll get a 'SQL logic error or missing database' error. (In fact writing the first entry to this table does work all the time, the error will always occur for the second entry)

Should it be possible at all to call UpdateWithChildren() in a transaction? Is there anything else I could try?

Thanks so much for every help!

I'm using SQLiteNetExtensions 1.2.2 for winRT (PCL)

[Guillermo Gutiérrez]

The only issue that I've faced when using transactions is that SQLite-Net didn't support nested transactions and some SQLite-Net methods already created a transaction, so there were some methods that you couldn't wrap in another transaction. But that doesn't sound to be the same error.

I've performed some tests and I'm unable to reproduce the issue. Can you attach your code?

[Stefan Stumpfl]

Thanks for your fast response.

I've created a sample: ManyToManyTest

The key where it differs from TestManyToManyCircularReadOnly():

    // Updating objects using transaction
            conn.RunInTransaction(() =>
            {
                foreach (var dbObject in objects)
                {
                    conn.UpdateWithChildren(dbObject);
                    // --> Error: 'SQL logic error or missing database'
                }
            });

[Guillermo Gutiérrez]

That works fine for MonoTouch. Probably it's related with this SQLite-Net issue when using transactions in WinRT environment: https://github.com/praeclarum/sqlite-net/issues/78

Can you try the solution that they propose and tell me if it works for you?

[Stefan Stumpfl]

Awesome, it does work. You can't image how much you just helped me :) Thank you so much!

So I've just added two lines of code (immediately after opening the db):

var TempPath = Windows.Storage.ApplicationData.Current.TemporaryFolder.Path;
await conn.Execute(string.Format("PRAGMA temp_store_directory = '{0}';", TempPath));

[Guillermo Gutiérrez]

Glad it worked ;)