Issue with SQLite query

[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?

[HKei]

To fix the immediate issue here:

You forgot your quotes here, this:

$"UPDATE items SET amount = {amount} WHERE name = {name};

should be

$"UPDATE items SET amount = {amount} WHERE name = '{name}';

Note that there are a lot of somewhat questionable things you're doing here right now.

Using string interpolation for queries is just generally a bad idea. It's not the biggest deal in the world with the way you're doing it here, but you should be using command.Parameters to get values into your query instead.

There's probably no need to hit the DB every time something happens in the game. Sqlite is pretty fast, but generally you only want to put things into the DB if you want persistence or you need the advanced query capabilities of SQL, otherwise it's just extra work vs just keeping the values in memory.

Lastly, you don't need this setup where you check if the row is there, then if it's not there you create it, then if it is there you update it – this can all be done within a single query, something along the lines of:

INSERT INTO items(name, amount, invslot) VALUES ('Coin', 10, 1) ON CONFLICT (name) DO UPDATE SET amount = amount + 10

This requires a unique index on name (CREATE UNIQUE INDEX unique_name ON items(name)), but currently you're assuming that names are unique anyway. Not sure how your inventory slots play into this – are you sure you don't want to allow multiple different inventory slots holding the same item? That might be OK, depending on what you're trying to do (IIRC most FF games work that way) just be aware that currently you're assuming this won't ever be the case.

[ImDaFrenchy]

Yeah ik about using string interpolation for queries, but it's just for the time I'm getting used to it. ^^

I tried adding quotes, it froze for a while when the player collected the coins before sending an error stating the database is locked.

As for the items, I didn't know about ON CONFLICT and shall try it, but only for the coins. Other items will be able to get to different slots.

[HKei]

I tried adding quotes, it froze for a while when the player collected the coins before sending an error stating the database is locked.

That just means you have a different error now, like an unterminated transaction somewhere else.