FAQ

[echo17]

Q: How do I use "LIKE" with parameters?

You can use the LIKE keyword in your query along with parameter in a manner similar to this:



SimpleSQL.SimpleSQLManager dbManager;

string query = "SELECT * FROM Weapon WHERE WeaponName LIKE ?";
                       
List<Weapon> weapons = dbManager.Query<Weapon>(query, "%ring%");



The key here being that you just swap out the entire literal string with a parameter question mark (?). Then in the parameter value, you place the percent (%) characters around your string.

Of course, you can always just include the LIKE keyword directly in your sql string:


string query = "SELECT * FROM Weapon WHERE WeaponName LIKE '%ring%'";
                       
List<Weapon> weapons = dbManager.Query<Weapon>(query);


Warning: Parameters may be able to be used for sql injection attacks, so if your query will be running based on user input, then I would recommend building your strings explicitly. Please see this link for more information: link

[echo17]

Q: Is WP8 supported?

Not currently. This is due to a limitation in Microsoft's .NET implementation on the Windows Phone 8. This implementation does not allow linking to static libraries like the sqlite3.dll code that SimpleSQL is based on.

We are currently looking into alternatives to the static library, but have no eta on when or if this will be implemented. In the meantime, SimpleSQL comes with the full source, so you may be able to modify the plugin to your project's needs.

[echo17]

Q: Why am I getting the error: "Attempting to JIT compile method '(wrapper delegate-invoke) System.Reflection.MonoProperty/ ..."?

iOS JIT errors occur when using reflection and the full .NET api. To get rid of these errors, set your project's api compatibility to .NET subset.

[echo17]

Q: How do you get column names from an empty query or empty table?

You could use the SimpleDataTable structure and browse through the SimpleDataColumn fields in it. Something like:



    public SimpleSQLManager dbManager;

    void Start()
    {
        SimpleDataTable table = dbManager.QueryGeneric("SELECT * FROM Weapon");

        Debug.Log("Row Count = " + table.rows.Count);

        foreach (SimpleDataColumn column in table.columns)
        {
            Debug.Log(column.name);
        }
    }



[echo17]

Q: How do I delete the database at the working path?

You can delete the database files by using System.IO function calls. Before you can delete, you will need to close and dispose of the SimpleSQLManager object first. Here is some sample code:


using UnityEngine;
using System.Collections;
using SimpleSQL;
using System.IO;

public class DeleteDB : MonoBehaviour
{

    public SimpleSQLManager dbManager;

    void Update ()
    {

        if (Input.GetKeyDown(KeyCode.D))
        {
            // first close and dispose of the manager
            dbManager.Close();
            dbManager.Dispose();

            // now we can delete the file. Note that if we are overwriting the working name
            // then we will use that instead of the default name
            File.Delete(Path.Combine(Application.persistentDataPath, (dbManager.changeWorkingName ? dbManager.workingName.Trim() : dbManager.databaseFile.name + ".bytes")));
        }

    }
}


[echo17]

Q: Why does indexing not work on Android?

SimpleSQL uses the sqlite3.dll library under the hood. This library has a limitation on Android that it cannot index with a LIKE operator. Instead, you can use BETWEEN statements to get better results. Something like:

SELECT field FROM table WHERE field BETWEEN ? AND ?

[echo17]

Q: How do I modify the source code?

The source code for SimpleSQL can be found at the path Assets/SimpleSQL/Source/SimpleSQL Library x.x.zip.

You will want to copy this file outside of your project before extracting to avoid errors. Once extracted, you can make changes to the library in your solution editor of your choice and the rebuild the solution.

SimpleSQL compiles several libraries, but it embeds all of the runtimes into the editor library located at (relative to the source directory) SimpleSQL_Editor/bin/Release/SimpleSQL_Editor.dll. Any time you make changes to the source and rebuild, you will want to copy this file back into your project at: Assets/SimpleSQL/Editor.

Once you have copied the editor library, you will then need to open the SimpleSQL options window in the Unity menu Tools > SimpleSQL > Options. Select a different platform than what you are using (it doesn't matter which one -- this example assumes you are using the Mac / iOS / Win platform). Switch to another platform (wait for Unity to finish compiling and updating. The spinning wheel will go away when it is done). Then, select the platform you were originally using (wait for Unity to finish compiling again)

What this does is force the plugin to extract the appropriate runtime dll from the newly compiled editor library.

[echo17]

Q: Why do I get the error "Failed to open database at the working path"?

You can put in an OnDestroy method in your scripts to close and dispose of the database. This will prevent locks when the editor goes into a paused state:


public override void OnDestroy()
{
dbManager.Close();
dbManage.Dispose();

base.OnDestroy();
}


If that does not solve the problem, there is a known issue when using the Insert or InsertAll helper functions that can sometimes put a lock on your database file that is not released until the Unity editor is closed.

To get around the problem, you can use SQL commands, instead of using the helper functions. Something like:



public SimpleSQL.SimpleSQLManager dbManager;

private void SavePlayerStats_Query(string playerName, int totalKills, int points)
{
// Call our SQL statement using ? to bind our variables
dbManager.Execute("INSERT INTO PlayerStats (PlayerName, TotalKills, Points) VALUES (?, ?, ?)", playerName, totalKills, points);
}


Please see the demo scripts included with SimpleSQL for more examples of how to use SQL queries.

[echo17]

Q: How do I get just a single value from a database?

There are two ways this can be accomplished:

1) Setting up an ORM class to store the data, calling a query to gather the data, then reference the stored result.

or

2) Using a generic table structure to gather the data then reference the appropriate table cell.

Here are some examples of how you would do either method. These examples use the databases found with the demo included with SimpleSQL:

1) ORM

First, you need to set up an ORM class to store the value. Something like:


public class WeaponNameRecord
{
public string WeaponName { get; set; }
}

Then, you can gather the data and store in this class object. There are two methods: Query (which returns a list of records) or QueryFirstRecord (which returns the first record that is found). This example uses the QueryFirstRecord method to get only the first record.

        bool recordExists;

        WeaponNameRecord record = dbManager.QueryFirstRecord<WeaponNameRecord>(out recordExists, "SELECT WeaponName FROM Weapon WHERE WeaponID = 4");

        if (recordExists)
            Debug.Log("Weapon Found! name = " + record.WeaponName);
        else
            Debug.Log("No Weapon found");

2) Generic Table

If you don't want to go to the trouble of setting up an ORM class for each query you need to do, you can just use a generic table structure and reference the resulting cells. This method is quick to code, but it can lead to mistakes if you reference the wrong cell or row.

     
        SimpleSQL.SimpleDataTable dt = dbManager.QueryGeneric("SELECT WeaponName FROM Weapon WHERE WeaponID = 4");

        if (dt.rows.Count > 0)
        {
            if (dt.columns.Count > 0)
            {
                Debug.Log("Weapon Name found! name = " + dt.rows[0][0].ToString());
            }
            else
            {
                Debug.Log("No columns found");
            }
        }
        else
        {
            Debug.Log("No record found");
        }

Please see the included demos that come with SimpleSQL for a complete set of examples on how to use the plugin

[echo17]

Q: Is SimpleSQL supported on iOS il2cpp?

Yes, but you need to use Unity 4.6.3 or greater for it to work. There is a bug in Unity in 4.6.2 that will not build with named libraries (SimpleSQL uses the sqlite3.dll named library).

There are still a lot of other bugs in il2cpp that are being addressed, so the il2cpp code backend is still unstable in Unity. Please see this post for more information: forum.unity3d.com/threads/4-6-ios-64-bit-beta.290551/

[echo17]

Q: Why am I getting the error that sqlite3.dll is missing when using Unity 5?

Please upgrade to the latest version of SimpleSQL (1.6.1+) to eliminate this error in Unity 5.

After you download the new version, go to the Unity menu "Tools > SimpleSQL > Options". Select a different platform than the one you current have set (it doesn't matter which one). Wait for the spinning wheel in Unity to stop, then switch back to your original platform. Wait for the spinning wheel to stop again. This will extract the new runtime that fixes the issue.

If you are still getting an error stating that the sqlite3.dll is missing. Please download the sqlite dll from link

Once you extract the file, copy it to your project's root folder (NOT THE ASSETS FOLDER). One level above your assets folder:



If you still get errors after all these steps, try running Unity as administrator.

[echo17]

Q: Why is my data showing empty values?

There are a couple of causes for this:

1) Be sure you have set data types on your fields inside the database. SimpleSQL uses the data types in order to properly map the data to the ORM classes.

2) Be sure your ORM classes are using properties and not members. SimpleSQL uses reflection to map properties to your database fields. A property will have a getter and setter, something like:


public int SomeValue { get; set; }


[echo17]

Q: Why am I getting an error saying "No such table"?

This can be caused by many things, but if you are using the ORM helper function CreateTable, then it may be caused by attribute settings on your ORM class fields.

Be sure any default values you have set for string fields are surrounded by single quotation marks. Something like:


[Default("'No description'")]
public string ItemDescription { get; set; }


[echo17]

Q: How do I get the last inserted row of a table?

The ORM Insert and InsertAll commands optionally send back the last rowID for you. If you prefer to use SQL syntax, you can get the last row by calling the LastInsertRowid function of the static Sqlite3 class. However, you will have to pass the handle of the sql connection to that function. The sql connection is a protected member of the SimpleSQLManager class, so you will need to use reflection to get access to it.

The following example shows how this can be accomplished:



using UnityEngine;
using SimpleSQL;
using System.Reflection;

public class GetLastRow : MonoBehaviour
{
    private static FieldInfo _connectionFieldInfo;

    public SimpleSQLManager dbManager;

void Start () {

        var sql = "INSERT INTO Weapon (WeaponName, Damage, Cost, Weight, WeaponTypeID) VALUES ('New Weapon', 1, 1, 1, 1)";
        dbManager.Execute(sql);

        var connection = GetConnection(dbManager);
        var lastRow = SQLite3.LastInsertRowid(connection.Handle);

        Debug.Log("Last row: " + lastRow);

    }

    private SQLiteConnection GetConnection(SimpleSQLManager dbManager)
    {
        if (_connectionFieldInfo == null)
        {
            _connectionFieldInfo = typeof(SimpleSQLManager).GetField("_db", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.GetField);
        }

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




If you are just interested in the last identity field value, then you could use the MAX sqlite aggregator function. Something like:



using UnityEngine;
using SimpleSQL;

public class GetLastID : MonoBehaviour
{
    public SimpleSQLManager dbManager;

void Start ()
    {
        var sql = "INSERT INTO Weapon (WeaponName, Damage, Cost, Weight, WeaponTypeID) VALUES ('New Weapon', 1, 1, 1, 1)";
        dbManager.Execute(sql);

        sql = "SELECT MAX(WeaponID) WeaponID FROM Weapon";
        bool recordExists;
        var record = dbManager.QueryFirstRecord<Weapon>(out recordExists, sql);
        if (recordExists)
        {
            Debug.Log("Last WeaponID: " + record.WeaponID);
        }
        else
        {
            Debug.Log("No record exists");
        }
    }
}




[echo17]

Q: How can I store my database in a path other than the default persistent data path?

By default, the database is stored in the persistent data path (Application.persistentDataPath: see this FAQ). This is so that all devices can have universal access to the database without any code modifications. There may be times when you wish to work with the database in a different location, however. To do this, you can set the overrideBasePath member of the SimpleSQLManager class. There is also an inspector field to allow changing this at design time.


Example override path: C:\testing\SimpleSQLTest\DB

Setting this value will use the path in place of Application.persistentDataPath.

If you wish to use a path relative to the application's persistentDataPath, you can set the override mode to RelativeToPersistentData. Then you can pass a path like "SubDirectoryA/SubSubDirectoryB"

Note: using the override mode of Absolute will not work on mobile devices since they do not allow access outside of their respective sandbox environments.

Available in version 1.8.0+