Search Unity

  1. Unity 2019.2 is now released.
    Dismiss Notice

C# SQLITE Problems with INSERT, UPDATE and DELETE.

Discussion in 'Android' started by Ryanc268, Apr 9, 2018.

  1. Ryanc268

    Ryanc268

    Joined:
    Apr 9, 2018
    Posts:
    4
    Before going into detail, all of these functions WORK in the unity editor on my PC but as soon as I go to test on my mobile device, INSERT, UPDATE and DELETE do not work while SELECT does. I've been googling around for about 7h today as well as testing different methods to no avail. Below I will post my code, any help will be super appreciated.

    Note: my update and delete are pretty identical to my insert, just obviously different SQL syntax

    Create User:
    Code (CSharp):
    1.     public void CreateNewUser(string email, string nickName)
    2.     {
    3.         ConnectDB();
    4.         dbcmd = new SqliteCommand("INSERT INTO PlayerInfo (GPlayEmail, UserName, Experience, Level, Stamina, Gold) VALUES ('" + email + "', '" + nickName + "', 0, 1, 0, 1000)", dbconn);
    5.         dbcmd.ExecuteNonQuery();
    6.         DisconnectDB();
    7.     }
    ConnectDB Method:
    Code (CSharp):
    1.     private void ConnectDB()
    2.     {
    3.  
    4.         string filepath = Application.streamingAssetsPath + "/PlayerDB.db";
    5.  
    6.         if (!File.Exists(filepath))
    7.         {
    8.             //Default android path
    9.             WWW loadDB = new WWW("jar:file://" + Application.dataPath + "!/assets/PlayerDB.db");
    10.  
    11.             while (!loadDB.isDone) { }
    12.             if (!string.IsNullOrEmpty(loadDB.error))
    13.             {
    14.                 //IOS db load
    15.                 string iosPath = Application.dataPath + "/Raw/PlayerDB.db";
    16.                 filepath = iosPath;
    17.             }
    18.             else
    19.             {
    20.                 //Android db load
    21.                 string androidPath = Application.persistentDataPath + "/PlayerDB.db";
    22.                 File.WriteAllBytes(androidPath, loadDB.bytes);
    23.                 filepath = androidPath;
    24.             }
    25.  
    26.         }
    27.         connection = "URI=file:" + filepath;
    28.         dbconn = new SqliteConnection(connection);
    29.         dbconn.Open(); //Open connection to the database.
    30.     }
     
  2. JeffDUnity3D

    JeffDUnity3D

    Unity Technologies

    Joined:
    May 2, 2017
    Posts:
    5,451
    Do you receive an error? Do you have WRITE permissions for your app?
     
  3. Ryanc268

    Ryanc268

    Joined:
    Apr 9, 2018
    Posts:
    4
    Is there somewhere I can check an error log on an android device? The fact it works on PC should mean I have WRITE permissions, not too sure how to check. Is there a special thing I have to do to give mobile devices WRITE permissions?
     
  4. JeffDUnity3D

    JeffDUnity3D

    Unity Technologies

    Joined:
    May 2, 2017
    Posts:
    5,451
    I generally use "adb logcat | grep -i unity" to view the logs. Also, I have concerns with while (!loadDB.isDone). You first assume an error with iOS, then assume Android. I would suggest that you instead use a compiler directive #if statement instead. And use Debug.Log to debug your code.
     
  5. Ryanc268

    Ryanc268

    Joined:
    Apr 9, 2018
    Posts:
    4
    I just set up the debug logs and I'm running into no errors so I am totally lost. I used to have a compiler directive #if but I didn't see any upsides of it so I switched
     
  6. walidabazo

    walidabazo

    Joined:
    Jul 8, 2017
    Posts:
    16
    SQLite Unity3d ( Android , Windows Phone , Windows , IOS, WINRT )
    Connection Database Sqlite how to Create Table , Select , Insert , Update , Delete , Search:
    https://github.com/walidabazo/SQLiteUnity3d_Android

    Video To Create it


    **Application database Path**
    string filepath = Application.dataPath + "/Plugins/" + DatabaseName;

    **Application database Path android**
    string filepath = Application.persistentDataPath + "/" + DatabaseName;

    // If not found on android will create Tables and database

    Debug.LogWarning("File \"" + filepath + "\" does not exist. Attempting to create from \"" + Application.dataPath + "!/assets/Employers");



    // #UNITY_ANDROID
    WWW loadDB = new WWW("jar:file://" + Application.dataPath + "!/assets/Employers.s3db");
    while (!loadDB.isDone) { }
    // then save to Application.persistentDataPath
    File.WriteAllBytes(filepath, loadDB.bytes);

    using (dbconn = new SqliteConnection(conn))
    {
    dbconn.Open();
    //Open connection to the database.
    dbcmd = dbconn.CreateCommand();

    sqlQuery = string.Format("insert into Staff (name, Address) values (\"{0}\",\"{1}\")", name, Address);// table name

    dbcmd.CommandText = sqlQuery;

    dbcmd.ExecuteScalar();

    dbconn.Close();
    }

    Debug.Log("Insert Done ");