I have a string field that contains Mi’kmaq and I encountered this error today:
"SQLiteException: unrecognized token: "'Miꞌkma"
I have confirmed there is no typo in the table or the code and no data field called Mi’kma. Is it possible the apostrophe is causing the issue?
Update: I have had another field with an apostrophe throw an exception.
If would recommend using parameters to insert data as it will handle the string building for you. Something like:
var sql = "INSERT INTO Weapon (WeaponID, WeaponName) VALUES (?, ?)";
dbManager.Execute(sql, 1000, "Gandalf's Staff");
If you want to build the string yourself, you’ll need to escape the single quote with a double instance of the single quote. This is a requirement built into the sqlite language itself. You can find out more here: https://stackoverflow.com/questions/603572/escape-single-quote-character-for-use-in-an-sqlite-query
Here is an example:
var sql = "INSERT INTO Weapon (WeaponID, WeaponName) VALUES (999, 'Gandalf''s staff')";
dbManager.Execute(sql);
Note: if you are using an external program to view data, like DB Browser for SQLite, then it will display the value without the single quote, so you might think it didn’t work. This is just a display issue in that particular program. If you copy and paste that value into a text editor you will see it still has the single quote.
I’m creating the tables in Google Sheets and exporting csv to DBeaver. No issues with that and the fields show up properly.
Maybe a better question: Do these statements follow standard sqlite convention? If so, then I can look for guides and docs online.
Yes, they follow the standard conventions. The link I posted was to StackOverflow, which is a generic help source. The examples I provided were based on the information in that thread using the standard sqlite method of escaping quotation marks and parameter usage.
Is there a way to SELECT query a single row and not have to pass it into a class list and instead just a single class instance?
Check out this FAQ: https://echo17.proboards.com/thread/2/faq?page=2&scrollTo=25
Hi,
I am trying to create a table with a combined primary key
public class Test
{
[PrimaryKey] // would like to add [ForeignKey], but does not exist
public int SaveGameID { get; set; } // corresponds to SaveGame.ID for handling multiple save games
[PrimaryKey]
public int ID { get; set; } // ID of asset
public int Data { get; set; } = 0;
}
If I try with DatabaseManager.CreateTable<Test>(); I get an error “SQLiteException: no such table”.
I can use a command as a work around
DatabaseManager.Execute("CREATE TABLE Test(" +
"SaveGameID INTEGER NOT NULL, " +
"ID INTEGER NOT NULL, " +
"Data INTEGER NOT NULL, " +
"PRIMARY KEY (SaveGameID, ID))");
But I would like to do it with an ORM class. Is this possible?
On a general node, I am unsure about my design using a combined primary key of save-game ID and asset ID. How do you recommend handling multiple save games?
SaveGameID in this design would be a typical example for a foreign key. Is there any news on that feature?
Thank you for your support!
I pulled the ORM to SQL generation code from sqlite.net a few years back. I’m not sure if that code has been updated to include multiple primary keys, I’ll have to investigate.
I generally try to keep a single primary key for a table, usually naming it UniqueID or something like that. It simplifies the coding, especially since I don’t have to remember what I’ve named my primary key when creating queries. So, something like this for your ORM would be how I’d structure it:
public class Test
{
[PrimaryKey, AutoIncrement]
public int UniqueID { get; set; }
public int SaveGameID { get; set; }
public int ID { get; set; }
public int Data { get; set; } = 0;
}
That would be another feature I’d have to investigate in sqlite.net.
Hi. Why I can’t read a long type value from CREATED_AT and UPDATED_AT fields? SimpleDataRow returns value type of Int32 witch equals -1.
I am using the QueryGeneric method. Data in table present and has valid values.
Does this work on Linux and Web player builds??
SimpleDataRow pulls data as raw objects. If you want to have the full effect of type casting, you’ll need to use an ORM. You can define your data fields in the ORM with the appropriate object type, something like:
public class User
{
public string ID { get; set; }
public string NAME { get; set; }
public string AVATAR { get; set; }
public string FLAG { get; set; }
public long CREATED_AT { get; set; }
public long UPDATED_At { get; set; }
}
Then you can pull the data like:
var results = dbManager.Query<User>("SELECT * FROM USERS");
It will work on Linux, but not web player. The reason is that the sqlite database file is a physical file that has to reside on a file system to be accessed. Web players don’t allow file access for security reasons.
You sure its not possible now…
?
Looks like that is using a 3rd party library to somehow get the webplayer to persist data. Unfortunately, I cannot include 3rd party libraries due to the licensing restrictions. The source code is included with SimpleSQL if you want to make modifications, however.
So is there no way around it… like being able to use simplesql during runtime and save load handled outside of it?
None that I am aware of.
Damn that is a bugger… would have prefer to stay using sql lite been considering this BG Database (inMemory database | Excel/Google Sheets syncing | CodeGen | Save/Load support) has some webgl limitations aswel…
(╯°□°）╯︵ ┻━┻ webgl
Hey @echo17 ,
I changed your source in the following ways,
Instead of using a MonoBehaviour for the SimpleSQLManager I have used ScriptableObject. This helps in having to configure just one Asset that everyone can reference and doesn’t stay dependent on Scenes and GameObjects. Refactored the Editor Scripts for the same.
Had to create a separate class to handle the OnApplicationQuit to handle Closing and Disposing of the SimpleSQLManagers.
Ping me if you want to take a look at it.
My plugin works https://u3d.as/3ka 
last updated in 2016?