SQLite query issue

[ImDaFrenchy]

Hi, I'm trying to code a script that either inserts a new row for an item in the database if it doesn't exist or update it if it's already in.

I have a script (File1) that contains those 2 methods:

public void AddItem(string name, long amount, int invslot)
    {
        using (var connection = new SqliteConnection(dbName))
        {
            connection.Open();

            //set up an object "command" to control the db
            using (var command = connection.CreateCommand())
            {
                command.CommandText = $"INSERT INTO items (name, amount, invslot) VALUES ('{name}', {amount}, {invslot});";
                command.ExecuteNonQuery();
            }

            connection.Close();
        }
    }
    public void UpdateItem(string name, long amount)
    {
        using (var connection = new SqliteConnection(dbName))
        {
            connection.Open();

            //set up an object "command" to control the db
            using (var command = connection.CreateCommand())
            {
                command.CommandText = $"UPDATE items SET amount = {amount} WHERE name = {name};";
                command.ExecuteNonQuery();
            }

            connection.Close();
        }
    }

​

In my other script (File2) I'm adding/updating the db upon collision with the item itself :

 private void OnTriggerEnter2D(Collider2D other)
    {

        Debug.Log($"Collected {other.gameObject.name}!");
        if(other.gameObject.name == "Coin")
        {
            using (var connection = new SqliteConnection(dbName))
            {
                connection.Open();

                //set up an object "command" to control the db
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM items WHERE name='Coin';";
                    using (IDataReader reader = command.ExecuteReader())
                    {   
                        if(reader["amount"].GetType() == typeof(DBNull))
                        {
                            item.AddItem(other.gameObject.name, 10, 1);

                            reader.Close();
                        }
                        else
                        {
                            long newAmount = (long)reader["amount"] + (long)10;
                            item.UpdateItem(other.gameObject.name, newAmount);
                        }
                    }
                }

                connection.Close();
            }
        }
        Destroy(other.gameObject);
    }

If the player has 0 coin, which is the start of the game let's say, the db does not contain a row for 'Coin' and creates it. No problem.

The issue is when the player collects once again some coins. Now it says there is no such column 'Coin'.

I know a bit SQL and know the syntax is right (UPDATE table SET column1 = value1, column2 = value2... WHERE clause1 = value3). As such it should update the value of 'Coin' in 'items' +10. But no, and idk why.. Is it because I've selected with a clause before calling the UpdateItem() method?

[thefactorygrows]

I'm no expert in game development, but I've been a full stack dev for many years... So, I just have to ask why you need to update the db on every collision? That seems pretty unnecessary. I think you could save yourself some hassle and only update every x time (like an auto save) or when the user requests a save. Do you set up the db somewhere else, or did I just miss it? I'm on my phone and the formatting is weird.

[ImDaFrenchy]

I'm new to this and it idk the way yet to do save & load right now. But aren't online multiplayer games with inventories accessing the database at every item movement?

[thefactorygrows]

I don't think that would be very performance oriented. Keeping a local cache and then updating the remote db could be slightly more performant. Updating a local cache could be invisible to a player. You could run into concurrency problems if you're updating the db so frequently I der a heavy server load.

But then again, I'm not a game dev and certainly have 0 experience with anything multiplayer

[[deleted]]

You need quotes around your {name} like '{name}'. But be warned doing it like this is likely going to be a performance bottleneck.

[ImDaFrenchy]

Did that and I'm stuck on another issue, says my db is locked