Search Unity

  1. All Pro and Enterprise subscribers: find helpful & inspiring creative, tech, and business know-how in the new Unity Success Hub. Sign in to stay up to date.
    Dismiss Notice
  2. Dismiss Notice

Help Wanted SQLite SQL help

Discussion in 'Scripting' started by pKallv, Apr 29, 2021.

  1. pKallv

    pKallv

    Joined:
    Mar 2, 2014
    Posts:
    915
    I am very new to Sqlite and need help to recount the id row. This would be from 0 to end.

    Id: 1, 2, 3, 8, 9
    to
    id: 1, 2, 3, 4, 5

    I have the following sql code to do this:

    Code (CSharp):
    1. void RecountDB_id()
    2.     {
    3.         connection = "URI=file:" + Application.dataPath + "/Database/" + "FamilyQuiz.db";
    4.         dbconn = (IDbConnection)new SqliteConnection(connection);                            //creates database connection
    5.         dbconn.Open();
    6.  
    7.         dbcmd = dbconn.CreateCommand();
    8.  
    9.         sqlQuery = "SET (@id = 0);" +
    10.             "UPDATE questions " +
    11.             "SET id = (@id := @id + 1);";
    12.  
    13.          print(sqlQuery);
    14.  
    15.         int _sql = 0;
    16.  
    17.         try
    18.         {
    19.             dbcmd.CommandText = sqlQuery;
    20.             _sql = dbcmd.ExecuteNonQuery();
    21.         }
    22.         catch (Exception e)
    23.         {
    24.             print("ERROR: " + e);
    25.             print("sql: " + _sql);
    26.         }
    27.  
    28.        
    29.  
    30.         CloseDB();
    31.  
    32.         print("DONE");
    33.     }
    I get the following error message:

    Code (CSharp):
    1. ERROR: Mono.Data.Sqlite.SqliteException (0x80004005): SQLite error
    2. near "SET": syntax error
    I have tested all kinds of SQL-UPDATEs but found this that suppose to work.

    Anyone that can help me to do this?
     
  2. Kurt-Dekker

    Kurt-Dekker

    Joined:
    Mar 16, 2013
    Posts:
    15,794
    This sounds more like a SQL syntax problem than a Unity problem.

    I don't know what SQL syntax is acceptable for any given installation of SQL/SQLite but it seems you have something wrong with your expression.

    Print out the expression before you execute it, and then see if it looks reasonable once it is all printed out as a single string.
     
    pKallv likes this.
  3. JeffDUnity3D

    JeffDUnity3D

    Unity Technologies

    Joined:
    May 2, 2017
    Posts:
    10,452
    It looks like you may be trying to update a primary key? Typically that is prohibited to maintain referential integrity. And you logic seems that would only update each ID to ID + 1 (1,2,3 becomes 2,3,4). You might want to craft your query first and test it https://sqlitebrowser.org/
     
    pKallv likes this.
unityunity