long as PrimaryKey is problem in transactions(SQL logic error)?

[xlazom00]

When I use long for primary key I have this really wired issue and I don't think it is sqlite problem
on second sql update I get
"SQL logic error"

I don't have this problem when I use int as primary key
Any idea?

public class Stock
{
    [PrimaryKey]
    public long Id { get; set; }
    [MaxLength(8)]
    public string Symbol { get; set; }

    public bool IsDownloaded { get; set; }
}

SQLiteConnection _db;
string DBconnectionString = String.Format("{0}\db.sqlite", Windows.Storage.ApplicationData.Current.LocalFolder.Path);

        //Sqlite
        _db = new SQLiteConnection(new SQLitePlatformWP8(), DBconnectionString, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite, true);
        _db.TraceListener = new DebugTraceListener2();

        _db.DropTable<Stock>();
        _db.CreateTable<Stock>();

        _db.BeginTransaction();
        List<Stock> stocks = new List<Stock>();
        stocks.Add(new Stock { Symbol = "cccccccccc", Id=12312 });
        stocks.Add(new Stock { Symbol = "aaaaaaa", Id = 1231212 });
        stocks.Add(new Stock { Symbol = "bbbbbbbbb", Id = 12312222 });

        _db.Insert(stocks[0]);
        _db.Insert(stocks[1]);
        _db.Insert(stocks[2]);
        _db.Commit();


        _db.BeginTransaction();

        stocks[0].Symbol = "asdfasdfasdfa";
        stocks[0].IsDownloaded = true;

        stocks[1].Symbol = "asdfasdfasdfa34r5432";
        stocks[1].IsDownloaded = true;

        stocks[2].Symbol = "asdfasdfasdfa345r3242";
        stocks[2].IsDownloaded = true;

        _db.Execute("update \"Stock\" set \"IsDownloaded\"=? ,\"Symbol\"=? where (\"Id\" = ?)", stocks[0].IsDownloaded, stocks[0].Symbol, stocks[0].Id);
        _db.Execute("update \"Stock\" set \"IsDownloaded\"=? ,\"Symbol\"=? where (\"Id\" = ?)", stocks[1].IsDownloaded, stocks[1].Symbol, stocks[1].Id);
        _db.Execute("update \"Stock\" set \"IsDownloaded\"=? ,\"Symbol\"=? where (\"Id\" = ?)", stocks[2].IsDownloaded, stocks[2].Symbol, stocks[2].Id);

        _db.Commit();

[oysteinkrog]

Hmm, I don't know too much about why this could fail, but I am curious why you think it is not a sqlite error?
You can enable the trace debugging to see the SQL statements that are done, this could help narrow down the problem.
Can you reproduce this problem with direct SQL statements?

[oysteinkrog]

I just saw this:
praeclarum@eea78a7
It could be that this is the problem.

[oysteinkrog]

I've cherry-picked that commit now, please test with HEAD.

[xlazom00]

why do you think I can't do updates in transaction ?

If I use GUID => string It also don't work. :(

I tested sqlite in qt 5.4.1 and all works fine but they have 6-8 months older sqlite
I am going to test it with latest C api directly

[xlazom00]

I made wp silverlight c# version that call sqlite methods directly
http://pastebin.com/dtR4eKYr
And it don't work.

this is C version
http://pastebin.com/vNQTra7Q
that work fine

it is all again same version of sqlite 3.8.8.3

so only different is wrapper to silverlight or sqlite compiled with different parameters

I am going to check for .net to native wrapper

[xlazom00]

so
problem was in transaction log (DB journal)
it looks like transaction log need separate file that you need to set up on wp/winrt
https://www.sqlite.org/c3ref/temp_directory.html

I don't know if sqlite use WINDOWS TEMP folder by default or not

but with simple PRAGMA temp_store=2; you keep your log in memmory
http://www.sqlite.org/pragma.html

I don't know why
Sqlite3.set_temp_directory(Windows.Storage.ApplicationData.Current.LocalFolder.Path);
https://github.com/poumason/sqlite-net-wp8
don't work as it really keep temp folder in global property
https://github.com/peterhuene/sqlite-net-wp8/blob/master/SqliteWP8.cpp#L286

[oysteinkrog]

Hmm, ok, so this is a limitation in sqlite on that platform.
Ideally we should detect these kinds of things..

[oysteinkrog]

Ok, I merged #157 which sets temp dir for WinRT platform.
What platform are you using? WP8?

[oysteinkrog]

@xlazom00 Have you had a chance to test the current prerelase nuget?