Search Unity

  1. Welcome to the Unity Forums! Please take the time to read our Code of Conduct to familiarize yourself with the forum rules and how to post constructively.

SimpleSQL - SQLite integration with Unity3D

Discussion in 'Assets and Asset Store' started by echo17, Jul 12, 2012.

  1. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Yes.

    Yes. After downloading the database to your local application's path, you can set the local database. Please see this FAQ: https://echo17.proboards.com/thread/2/faq?page=1&scrollTo=14
     
  2. doogiegonegood

    doogiegonegood

    Joined:
    Oct 1, 2013
    Posts:
    3
    (RESOLVED)

    Hi! Sorry if this is an obvious question but I have my database in my assets folder as GameWorld.bytes
    upload_2021-5-24_18-38-25.png
    I can read and write to my db with no problems with SimpleSQL. However, I noticed that when I open up the db using "DB Browser for SQLite" the data is different to the data unity sees during runtime.

    I tried dropping the db again on the unity editor to make sure it was the same file, and it is, but there seems to be a different copy of data for runtime compared to when I open the file in DB Browser.
    upload_2021-5-24_18-41-44.png
    Was the db copied by unity to some other folder? I would like to edit that version of the db outside runtime.

    Thank you so much! :)

    UPDATE:
    I found the answer on the other simplesql forums:
    https://echo17.proboards.com/thread/2/faq?page=1&scrollTo=9

    The paths for Unity's working directory per platform are:

    • Windows Editor and App (XP): C:\Documents and Settings\<user name>\Local Settings\Application Data\<company name>\<application name>
    • Windows Editor and App (7): C:\Users\<user name>\AppData\LocalLow\<company name>\<application name>
    • Mac Editor and App: /Users/<user name>/library/caches/<company name>.<application name>
    • iOS: the documents directory for the device (i don't believe the simulator is used)
    These paths can also be found at runtime using Unity's member:

    Application.persistentDatapath
     
    Last edited: May 25, 2021
  3. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Thanks for letting us know. I was going to link that FAQ :)
     
  4. pjaimin

    pjaimin

    Joined:
    Nov 3, 2018
    Posts:
    13
    Hiii

    When I am creating table in db on android it gives me error

    SQLiteException: IOError

    but it working fine in unity editor

    what i am doing wrong here ???
     
  5. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    I'm not sure. I would need to see your project to give you a diagnosis. You can create a simplified version of the project, just enough to demonstrate the issue and send it to the email address in my signature. Please be sure to include your invoice number and a list of steps to recreate. Please see this FAQ for more information: https://echo17.proboards.com/thread/2/faq?page=1&scrollTo=3
     
  6. unity_56399AB65F28AE01D1DA

    unity_56399AB65F28AE01D1DA

    Joined:
    Dec 18, 2021
    Posts:
    7
    Hi team,

    I haven't purchased your asset yet, but I would want to know if your Unity asset can help me with my project.

    I have used SQLite for the functioning of a game made with Unity 2020.3.8f1 and it worked fine in both: Windows and Android OS (IL2CPP and Mono build). However, I am getting the below issue when trying to run the same app on iOS platform. I believe, I have set everything correctly for the app to work.

    MissingMethodException: System.Runtime.InteropServices.Marshal::SetLastWin32Error(System.Int32)
    at System.Runtime.InteropServices.CriticalHandle.Cleanup () [0x00000] in <00000000000000000000000000000000>:0

    at System.Runtime.InteropServices.CriticalHandle.Dispose (System.Boolean disposing) [0x00000] in <00000000000000000000000000000000>:0

    at System.Runtime.InteropServices.CriticalHandle.Dispose () [0x00000] in <00000000000000000000000000000000>:0

    at Mono.Data.Sqlite.SQLite3.Close () [0x00000] in <00000000000000000000000000000000>:0

    at Mono.Data.Sqlite.SqliteConnection.Close () [0x00000] in <00000000000000000000000000000000>:0

    at Mono.Data.Sqlite.SqliteConnection.Dispose (System.Boolean disposing) [0x00000] in <00000000000000000000000000000000>:0

    at System.ComponentModel.Component.Dispose () [0x00000] in <00000000000000000000000000000000>:0

    Can you let me know if your asset be used to make apps for iOS platform as well without getting any issues on all the architectures (arm64 , arm7 , x86)?
     
  7. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    Hi there. Yes, SimpleSQL works on Android, Windows, and iOS. I haven't had any issues compiling across these platforms. If you do come across something, you take a look at these FAQs: https://echo17.proboards.com/thread/2/faq or email me at my address in the signature.
     
  8. JohnnyFactor

    JohnnyFactor

    Joined:
    May 18, 2018
    Posts:
    343
    I would like to initialize a single variable with one query line. Is this possible?
    EDIT: I'm trying to avoid
    foreach
    loops to keep the code manageable.

    int defense = dbManager.Query<Buildings>("SELECT defense FROM buildings WHERE name = '" + type + "'");
     
  9. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    You can technically do this with something like:

    Code (csharp):
    1.  
    2. int defense = dbManager.Query<Buildings>("SELECT defense FROM buildings WHERE name = '" + type + "'")[0].defense;
    3.  
    since the Query result is just a list, so returning the first element is as simple as referencing the 0th position.

    This will crash if there is no value returned, however, so that is why I would not recommend a single line. Instead I would use the QueryFirstRecord method and check if there is a value.

    Code (csharp):
    1.  
    2.             bool recordExists;
    3.             var record = dbManager.QueryFirstRecord<Weapon>(out recordExists, "SELECT Damage FROM Weapon WHERE WeaponID = 1");
    4.             if (recordExists)
    5.             {
    6.                 var damage = record.Damage;
    7.             }
    8.  
     
    JohnnyFactor likes this.
  10. JohnnyFactor

    JohnnyFactor

    Joined:
    May 18, 2018
    Posts:
    343
    Is this statement valid? It returns the total row count including nulls.

    names = dbManager.Query<Names>("SELECT English FROM names WHERE English IS NOT NULL");
    Debug.Log(names.Count);


    EDIT: This is working now. My database software wasn't writing NULL fields properly.
     
    Last edited: Jan 10, 2022
  11. JohnnyFactor

    JohnnyFactor

    Joined:
    May 18, 2018
    Posts:
    343
    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.
     
  12. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    If would recommend using parameters to insert data as it will handle the string building for you. Something like:

    Code (csharp):
    1.  
    2. var sql = "INSERT INTO Weapon (WeaponID, WeaponName) VALUES (?, ?)";
    3. dbManager.Execute(sql, 1000, "Gandalf's Staff");
    4.  
    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:

    Code (csharp):
    1.  
    2. var sql = "INSERT INTO Weapon (WeaponID, WeaponName) VALUES (999, 'Gandalf''s staff')";
    3. dbManager.Execute(sql);
    4.  
    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.
     
  13. JohnnyFactor

    JohnnyFactor

    Joined:
    May 18, 2018
    Posts:
    343
    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.
     
  14. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    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.
     
    JohnnyFactor likes this.
  15. ZolnierGames

    ZolnierGames

    Joined:
    Feb 19, 2018
    Posts:
    84
    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?
     
  16. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
  17. olejuer

    olejuer

    Joined:
    Dec 1, 2014
    Posts:
    204
    Hi,

    I am trying to create a table with a combined primary key
    Code (CSharp):
    1. public class Test
    2. {
    3.     [PrimaryKey] // would like to add [ForeignKey], but does not exist
    4.     public int SaveGameID { get; set; } // corresponds to SaveGame.ID for handling multiple save games
    5.  
    6.     [PrimaryKey]
    7.     public int ID { get; set; } // ID of asset
    8.  
    9.     public int Data { get; set; } = 0;
    10. }
    If I try with
    DatabaseManager.CreateTable<Test>();
    I get an error "SQLiteException: no such table".

    I can use a command as a work around

    Code (CSharp):
    1. DatabaseManager.Execute("CREATE TABLE Test(" +
    2.     "SaveGameID INTEGER NOT NULL, " +
    3.     "ID INTEGER NOT NULL, " +
    4.     "Data INTEGER NOT NULL, " +
    5.     "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!
     
  18. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    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:

    Code (CSharp):
    1. public class Test
    2. {
    3.     [PrimaryKey, AutoIncrement]
    4.     public int UniqueID { get; set; }
    5.  
    6.     public int SaveGameID { get; set; }
    7.     public int ID { get; set; }
    8.     public int Data { get; set; } = 0;
    9. }
    That would be another feature I'd have to investigate in sqlite.net.
     
    olejuer likes this.
  19. ynavrotskiy

    ynavrotskiy

    Joined:
    Mar 8, 2015
    Posts:
    1
    upload_2022-10-22_19-26-4.png
    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.
    upload_2022-10-22_19-30-55.png
     
  20. Slashbot64

    Slashbot64

    Joined:
    Jun 15, 2020
    Posts:
    183
    Does this work on Linux and Web player builds??
     
  21. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    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:

    Code (CSharp):
    1. public class User
    2. {
    3.     public string ID { get; set; }
    4.     public string NAME { get; set; }
    5.     public string AVATAR { get; set; }
    6.     public string FLAG { get; set; }
    7.     public long CREATED_AT { get; set; }
    8.     public long UPDATED_At { get; set; }
    9. }
    Then you can pull the data like:

    Code (CSharp):
    1. var results = dbManager.Query<User>("SELECT * FROM USERS");
     
  22. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    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.
     
  23. Slashbot64

    Slashbot64

    Joined:
    Jun 15, 2020
    Posts:
    183
    You sure its not possible now..
    ?
     
  24. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    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.
     
  25. Slashbot64

    Slashbot64

    Joined:
    Jun 15, 2020
    Posts:
    183
    So is there no way around it.. like being able to use simplesql during runtime and save load handled outside of it?
     
  26. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    None that I am aware of.
     
  27. Slashbot64

    Slashbot64

    Joined:
    Jun 15, 2020
    Posts:
    183
  28. hitarthdoc1994

    hitarthdoc1994

    Joined:
    Dec 17, 2015
    Posts:
    15
    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.
     
    echo17 likes this.
  29. Oksana-Iashchuk

    Oksana-Iashchuk

    Joined:
    Sep 10, 2012
    Posts:
    125
    My plugin works https://u3d.as/3ka :)
     
  30. Slashbot64

    Slashbot64

    Joined:
    Jun 15, 2020
    Posts:
    183
    last updated in 2016?
     
  31. Oksana-Iashchuk

    Oksana-Iashchuk

    Joined:
    Sep 10, 2012
    Posts:
    125
    exactly, stable :). It's pure C# (no dll) and it works even on WebGL
     
    Last edited: Dec 14, 2022
  32. Oksana-Iashchuk

    Oksana-Iashchuk

    Joined:
    Sep 10, 2012
    Posts:
    125
    actually - you right, I'll update it, there is non critical issue which I promise to update, but mean while there is nothing to change to do empty update.
     
  33. Kayumus

    Kayumus

    Joined:
    Jul 21, 2020
    Posts:
    14
    upload_2023-1-8_19-40-14.png

    You drag and drop the Fantasy database file. What is the file format of the Fantasy file?
    Can I use .CSV file?
     
  34. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,233
    SimpleSQL is a wrapper for SQLite, so the database files are sqlite formatted databases. CSV files will not work. You will need to rename your files with a .bytes extension so that Unity can use it in the inspector for the component. Please see the user manual for more information: https://support9569.wixsite.com/echo17/simplesql
     
    Kayumus likes this.