[FIXED AS OF U3B5] U3B4 SQLLite?

News & General Discussion
1

Any idea if the SQLLite will be fixed by B5? I can create databases but can’t do any commands like add, insert, update, delete for any tables.

2

The bug case you submitted hasn’t been marked as fixed in the database, so I can’t confirm it will be fixed by B5 (but that doesn’t mean it definitely won’t be).

3

I am really hoping that it gets fixed for the next beta build. I use it heavily in Unity 2.6 Pro, so if Unity 3 goes live without it working, that would hurt bad for upgrading my projects.

4

Just did a test with U3B5 on the PC only, don’t have my Mac with me at the moment, but using sqlite3, I was able to do a new database, new table, add fields, add data to those fields, use a datareader to read those values, use an update command, execute statements both using executenonquery and executescalar, and also use the delete command, the database opens and is readable using the sqlite tiny binary client. The only thing that doesn’t work is LINQ, but who cares about LINQ on sqlite3, it still has the same invalid parameters issues but that is not my concern.

Thanks for getting this fixed!

5

Code used for test, in case anyone needs it. This is purely the method for the test in C#, of course you have to include the sqlite3 library, etc. Not optimized, was down and dirty just to do the test.

 void dbSqlite()
    {
  string constr = "URI=file:gamedata.db,Version=3";
        // game table
        string sql = "CREATE TABLE IF NOT EXISTS gamedata (guid varchar(64),lastIP varchar(32)); ";
        string sqlInsert = "INSERT INTO gamedata (guid, lastip) Values (?, ?); ";

        SqliteConnection con = new SqliteConnection(constr);
        SqliteCommand cmd = new SqliteCommand(sql, con);
        SqliteCommand cmdInsert = new SqliteCommand(sqlInsert, con);

        SqliteParameter p0 = new SqliteParameter();
        p0.ParameterName = "@pguid";
        p0.DbType = DbType.String;
        p0.Value = p.guid;

        SqliteParameter p1 = new SqliteParameter();
        p1.ParameterName = "@plastip";
        p1.DbType = DbType.String;
        p1.Value = p.ipAddress;

        cmdInsert.Parameters.Add(p0);
        cmdInsert.Parameters.Add(p1);

        try
        {
            Debug.Log("opening con");
            con.Open();
            Debug.Log("executing create using executenonquery");
            cmd.ExecuteNonQuery();
            Debug.Log("executing insert using executenonquery");
            cmdInsert.ExecuteNonQuery();

            Debug.Log("executing reader for select query");
            sql = "SELECT * FROM gamedata;";
            cmd = new SqliteCommand(sql, con);
            SqliteDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Debug.Log("GUID: " + rdr["guid"].ToString());
                Debug.Log("IP: " + rdr["lastip"].ToString());
                Debug.Log("----------------------------------");
            }
            rdr.Close();

            Debug.Log("updating the record with some junk data for test UPDATE");
            sql = "UPDATE gamedata SET GUID='1234567890';";
            cmd = new SqliteCommand(sql, con);
            cmd.ExecuteNonQuery();

            Debug.Log("showing new value from change");
            sql = "SELECT * FROM gamedata;";
            cmd = new SqliteCommand(sql, con);
            SqliteDataReader rdr1 = cmd.ExecuteReader();
            while (rdr1.Read())
            {
                Debug.Log("GUID: " + rdr1["guid"].ToString());
                Debug.Log("IP: " + rdr1["lastip"].ToString());
                Debug.Log("----------------------------------");
            }
            rdr1.Close();

            Debug.Log("Purging all records");
            sql = "DELETE FROM gamedata";
            cmd = new SqliteCommand(sql, con);
            cmd.ExecuteNonQuery();

            Debug.Log("Checking record count, should be 0, using executescalar");
            sql = "SELECT COUNT(*) AS COUNT FROM gamedata;";
            cmd = new SqliteCommand(sql, con);
            object results = cmd.ExecuteScalar();
            Debug.Log("Record Count: " + results.ToString());

            Debug.Log("closing connection");
            con.Close();
            Debug.Log("Table created, records inserted, then read back, altered and verified then purged");
        }
        catch (Exception ex)
        {
            if (con != null)
                con.Close();

            Debug.Log(ex.ToString());
        }
        finally
        {
            try
            {
                con.Close();
                con.Dispose();
            }
            catch (Exception ex)
            {
                Debug.Log(ex.ToString());
            }
        }
    }