SQL Update Statement issue

[EagleSunRey]

I might be missing something obvious, but i'm seeing an error when trying to run an update statement in the SQLite DB. I am currently testing on windows 10. I am able to read rows from the DB and i'm able to insert new rows.

NotSupportedException: Cannot update c__Iterator1D`1: it has no PK SqlCipher4Unity3D.SQLiteConnection.Update (System.Object obj, System.Type objType) (at Assets/SqlCipher4Unity3D/Sqlite3Connection.cs:1251) SqlCipher4Unity3D.SQLiteConnection.Update (System.Object obj) (at Assets/SqlCipher4Unity3D/Sqlite3Connection.cs:1224)

I do have a Primary key field in the DB, and there is a Primary key defined in the [table_name].cs file. Would you be able to provide a bit of example code for the example project?

[netpyoung]

there is example. SqlCipher4Unity3D/Assets/example/Scripts If you make some sample project for that and share by github or something. i can check.

[EagleSunRey]

I saw those examples. I was looking for where we might be changing someone's Name, or resetting everyone's Age. Seems like we're only adding rows and retrieving persons.

After some tinkering, I did find a workaround. _connection.Update() will update one row at a time. Somehow _connection.UpdateAll() doesn't seem to do anything for me. Which is fine for me, i'm only grabbing 2-3 rows from my table. My DB is not very big in general, so i'll be good for my project. Thanks

[netpyoung]

I don't know the reason of that problem. but I think it is better open for other peoples. actually in UpdateAll function it use Update function.

Could I recived some example code?

[EagleSunRey]

Oh hey, just saw this. Let me re-write the non-working code. This runs without error now, but didn't see any updates in the DB. (i wrote this quick, please judge lightly)

//Code i got to work private static SQLiteConnection dbconn1; //this was initialized elsewhere

public void set_active_player(string player_name) {

var all_active_players = dbconn1.Table() .Where(x => x.active_player > 0 || x.save_name == player_name) ;

dbconn1.BeginTransaction(); if (all_active_players != null) { foreach (var player in all_active_players) { if (player_name.Equals(player.save_name)) { Debug.Log("Attempting to set the active_player field to: " + player.save_name); player.active_player = 1; } else { Debug.Log("Attempting to clear the active_player field for: " + player.save_name); player.active_player = 0; } dbconn1.Update(player); } } dbconn1.Commit(); }

//------------------------------------------------------------ //player_profile.cs //omitted unrelated stuff here

using SQLite.Attribute; using UnityEngine.Scripting;

[Preserve] public class player_profile { [PrimaryKey] public string save_name { get; set; } public int active_player { get; set; }

public override string ToString()
{
    return string.Format("[level_story: save_name={0}, active_player={1}]"
        , save_name, active_player);
}

}

//------------------------------------------------------------ //Example code i couldn't get to work.

public void wannabe_set_active_player(string player_name) { var active_players = dbconn1.Table() .Where(x => x.active_player > 0 || x.save_name == player_name) ;

dbconn1.BeginTransaction(); if (active_players != null) foreach (var player in active_players) { if (player_name.Equals(player)) { Debug.Log("Attempting to set the active_player field to: " + player.save_name); player.active_player = 1; } else { Debug.Log("Attempting to clear the active_player field for: " + player.save_name); player.active_player = 0; } } dbconn1.UpdateAll(active_players); dbconn1.Commit(); }

[netpyoung]

I forget this issue. I will test on next month.. it take a time

[netpyoung]

@EagleSunRey i'm sorry, it takes longtime. I doing something other task, So I just postpone this issue. today I checked this issue.

you can check this commit - https://github.com/netpyoung/SqlCipher4Unity3D/blob/master/SqlCipher4Unity3D/Assets/test/test_update/test_update.cs

because, SqlCipher4Unity's Linq's where result, IEnumerable is lazy so, it's real result will be delayed until be called tolist.

// it's work!
public void wannabe_set_active_player(string player_name)
        {
            //IEnumerable<player_profile> active_players = this.dbconn1.Table<player_profile>()
            //        .Where<player_profile>(x => x.active_player > 0 || x.save_name == player_name);
            // because, SqlCipher4Unity's Linq's where result's will be delayed until tolist.

            List<player_profile> active_players = this.dbconn1.Table<player_profile>()
                    .Where<player_profile>(x => x.active_player > 0 || x.save_name == player_name).ToList();

            // UpdateAll runs Update within RunInTransaction, so it is okay to skip begintransaction/commit.
            //this.dbconn1.BeginTransaction();

            if (active_players != null)
                foreach (player_profile player in active_players)

                    //if (player_name.Equals(player))
                    if (player_name.Equals(player.save_name))
                    {
                        Debug.LogWarning($"Attempting to set the active_player field to: {player_name} -> {player}");
                        player.active_player = 1;
                    }
                    else
                    {
                        Debug.LogWarning($"Attempting to clear the active_player field for: {player_name} -> {player}");
                        player.active_player = 0;
                    }

            this.dbconn1.UpdateAll(active_players);
            //this.dbconn1.Commit();

        }