How to Insert records into a table in Unity using SQLite?

Question

I'm trying to add a record to my table using string variables for VarChar columns. I want to insert the values I give it under the next incremented ID. However, I keep getting the SQL error that "no such column exists" when I try and run the code. There is no elaboration on where the error occurs, so I'm sorta stuck. So how can I insert records into a table?

Here's my code

using UnityEngine;
using Mono.Data.Sqlite;
using System.Data;
using UnityEngine.UI;
using System.Collections;

public class AddQuery : MonoBehaviour {

    public Text cipher;
    public Text initialMessage;
    public Text encryptedMessage;

    private string _constr = "URI=file:previousMessages.db";
    private IDbConnection _dbc;
    private IDbCommand _dbcm;
    private IDataReader _dbr;


public void AddSQL() {
    string _cipher = cipher.text;
    string _initialMessage = initialMessage.text;
    string _encryptedMessage = encryptedMessage.text;

    _dbc = new SqliteConnection(_constr);
    _dbc.Open();

    _dbcm = _dbc.CreateCommand();
    _dbcm.CommandText = "CREATE TABLE IF NOT EXISTS previousMessages (ID    INTEGER NOT NULL PRIMARY KEY , Cipher   VARCHAR(5000) NOT NULL, InitialMessage  VARCHAR(5000) NOT NULL,EncryptedMessage TEXT NOT NULL)";
    _dbr = _dbcm.ExecuteReader();
    _dbr.Close();

    _dbcm.CommandText = "INSERT INTO previousMessages ( Cipher, InitialMessage, EncryptedMessage) VALUES ( "+_cipher+", "+_initialMessage+", "+_encryptedMessage+")";
    _dbr = _dbcm.ExecuteReader();
    _dbr.Close();

    _dbcm.CommandText = "SELECT FROM previousMessages (Cipher, InitialMessage, EncryptedMessage) VALUES ( " + _cipher + ", " + _initialMessage + ", " + _encryptedMessage + ")";
    _dbr = _dbcm.ExecuteReader();

    while (_dbr.Read())
    {
        Debug.Log("Cipher: " + _dbr["Cipher"] + "\t Initial: " + _dbr["InitialMessage"]);
    }

}

}

Nika Kasradze · Accepted Answer · 2017-10-17 15:30:48Z

2

If you follow OOP you're life will be simpler. I mean you'd rather let a method do only one thing; or if it HAS TO do many things than make it a list of calls to other methods which do only 1 thing each. That being said your AddSQL() breaks down to:

public void AddSQL() {
    string _cipher = cipher.text;
    string _initialMessage = initialMessage.text;
    string _encryptedMessage = encryptedMessage.text;

    OpenConnection();
    ExecuteCreateCommand();
    InsertCommand(_cipher, _initialMessage, _encryptedMessage);
    ReadStuff(_cipher, _initialMessage, _encryptedMessage);
    DebugRead();

}

void OpenConnection(){
    _dbc = new SqliteConnection(_constr);
    _dbc.Open();
}

void ExecuteCreateCommand(){
    _dbcm = _dbc.CreateCommand();
    _dbcm.CommandText = "CREATE TABLE IF NOT EXISTS previousMessages (ID    INTEGER NOT NULL PRIMARY KEY , Cipher   VARCHAR(5000) NOT NULL, InitialMessage  VARCHAR(5000) NOT NULL,EncryptedMessage TEXT NOT NULL)";
    _dbr = _dbcm.ExecuteReader();
    _dbr.Close();
}

void InsertCommand(string _cipher, string _initialMessage, string _encryptedMessage){
    _dbcm.CommandText = "INSERT INTO previousMessages ( Cipher, InitialMessage, EncryptedMessage) VALUES ( "+_cipher+", "+_initialMessage+", "+_encryptedMessage+")";
    _dbr = _dbcm.ExecuteReader();
    _dbr.Close();
}

void ReadStuff(string _cipher, string _initialMessage, string _encryptedMessage){
    _dbcm.CommandText = "SELECT FROM previousMessages (Cipher, InitialMessage, EncryptedMessage) VALUES ( " + _cipher + ", " + _initialMessage + ", " + _encryptedMessage + ")";
    _dbr = _dbcm.ExecuteReader();
}

void DebugRead(){
    while (_dbr.Read())
    {
        Debug.Log("Cipher: " + _dbr["Cipher"] + "\t Initial: " + _dbr["InitialMessage"]);
    }
}

Now in AddSQL() comment all the method calls and uncomment one by one to see which query throws an exception.

edited Oct 17, 2017 at 15:30

answered Apr 8, 2016 at 13:41

Nika Kasradze

3,0263 gold badges28 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adithya Fathom Over a year ago

Thanks for the help; I can be a bit messy at times with my coding. I still keep getting the error that apparently the string "Ceaser" cannot go into my table as "no such column exists". Do you know why this is happening?

Nika Kasradze Over a year ago

The point was to let you know which line gives you an error. But well, I assume in ExecuteCreateCommand() and in InsertCommand() you need to use _dbcm.ExecuteNonQuery() instead of _dbcm.ExecuteReader(). Not sure though. ExecuteNonQuery docs say you can use this for creating database objects such as tables

Collectives™ on Stack Overflow

How to Insert records into a table in Unity using SQLite?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related