Insert with custom sql and get rowID back?

[kurrypuffs]

Hi,

I'm trying to do the following:

CREATE TABLE IF NOT EXISTS tbl_unit (

  instanceId INTEGER PRIMARY KEY,

  data TEXT

);

public class Unit {
    public ulong instanceId { get; set; }

    public int hp {get; set;}

    public int atk {get; set;}


    public void Insert(){
        // This doesn't work
        long rowID = simpleSQLManager.Execute("INSERT INTO tbl_unit (data) VALUES (?)", JSON.Serialize(this));
        this.instanceId = rowID;

    }
}


Basically I'm trying to a custom insert where the insert sql does not conform with the class definition and retrieving the rowID after the sql execution. Is this possible?

[echo17]

Unity's JsonUtility cannot serialize properties, instead you need to provide variables. If you really need properties in your Unit class (if you are going to be using it in the ORM, for example), then take a look at this post: link. Also, your Unit class needs access to the SimpleSQLManager, so passing it via the Insert method is one way to accomplish this.

For getting the last row, please see this FAQ: link

One way to do this is to get the connection then get the last insert row id:


using UnityEngine;
using UnityEngine.UI;
using System.Collections.Generic;
using SimpleSQL;
using System.Reflection;


public class Unit
{
public ulong instanceId;

public int hp;

public int atk;

public void Insert(SimpleSQLManager dbManager, SQLiteConnection connection)
{
dbManager.Execute("INSERT INTO tbl_unit (data) VALUES (?)", JsonUtility.ToJson(this));

this.instanceId = (ulong)SQLite3.LastInsertRowid(connection.Handle);

Debug.Log(this.instanceId);
}


}

public class MainClass : MonoBehaviour
{
public SimpleSQL.SimpleSQLManager dbManager;

void Start()
{
var connection = GetConnection();

var sql = "CREATE TABLE IF NOT EXISTS tbl_unit (instanceId INTEGER PRIMARY KEY, data TEXT);";
dbManager.Execute(sql);

var unit = new Unit()
{
hp = 100,
atk = 52
};

unit.Insert(dbManager, connection);

unit = new Unit()
{
hp = 32,
atk = 99
};

unit.Insert(dbManager, connection);
}

private SQLiteConnection GetConnection()
{
FieldInfo connectionFieldInfo = typeof(SimpleSQLManager).GetField("_db", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.GetField);

return (SQLiteConnection)connectionFieldInfo.GetValue(dbManager);
}
}




A far simpler way to get the last row is to just call a MAX query on the tbl_unit instanceId field, like:


using UnityEngine;
using UnityEngine.UI;
using System.Collections.Generic;

public class RowNumber
   {
public int rowNumber { get; set; }
   }

public class Unit
{
public ulong instanceId;

public int hp;

public int atk;

public void Insert(SimpleSQLManager dbManager)
{
dbManager.Execute("INSERT INTO tbl_unit (data) VALUES (?)", JsonUtility.ToJson(this));

bool recordExists;
var result = dbManager.QueryFirstRecord<RowNumber>(out recordExists, "SELECT MAX(instanceId) as rowNumber FROM tbl_unit");
if (recordExists)
           {
this.instanceId = (ulong)result.rowNumber;
           }

Debug.Log(this.instanceId);
}


}

public class MainClass : MonoBehaviour
{
public SimpleSQL.SimpleSQLManager dbManager;

void Start()
{
var sql = "CREATE TABLE IF NOT EXISTS tbl_unit (instanceId INTEGER PRIMARY KEY, data TEXT);";
dbManager.Execute(sql);

var unit = new Unit()
{
hp = 100,
atk = 52
};

unit.Insert(dbManager);

unit = new Unit()
{
hp = 32,
atk = 99
};

unit.Insert(dbManager);
}
}