locked
How to permanently drop or delete SQLite-net-plc table in Xamarin.Forms? RRS feed

  • Question

  • User43400 posted

    Hi friends, I created a simple application to load data from an API that will populate a local SQLite database. All is well for the first time synchronization, but when the user try to resync the data the SQLite.SQLiteException: 'Constraint' is displaying.

    I cannot understand why, I'm dropping the table befor resync. Can some of you help me to figure it out? That's my Class:

    [Table("tabpropriedade")]
        public class Propriedade
        {
            [PrimaryKey, Column("idPropriedade")]
            public int idPropriedade { get; set; }
            public string numPropriedade { get; set; }
        public string nomePropriedade { get; set; }
    }
    

    That's my Sync routine:

    public async void Sincroniza()
            {
                try
                {
                    lblSincTitle.Text = "Deletando os dados existentes...";
                    aiProgresso.IsVisible = true;
                    aiProgresso.IsEnabled = true;
                    aiProgresso.IsRunning = true;
                    dataBase.DropTable<Propriedade>();
    
                    lblSincTitle.Text = "Obtendo os dados do servidor...";
                    //Conectando à API
                    var httpClient = new HttpClient();
    
                    //Definindo a API a ser chamada
                    apiName = "propriedades";
                    var response = await httpClient.GetStringAsync(apiPath + apiName);
    
                    //Recuperando os dados da API e deserializando
                    var propriedade = JsonConvert.DeserializeObject<List<Propriedade>>(response);
                    totalProp = propriedade.Count;
    
                    lblSincTitle.Text = "Recriando a tabela de prorpriedades";
                    dataBase.CreateTable<Propriedade>();
    
                    //Insere os dados retornados da API
                    lblSincTitle.Text = "Sincronizando a base de dados";
                    dataBase.InsertAll(propriedade);
    
                    aiProgresso.IsVisible = false;
                    aiProgresso.IsEnabled = false;
                    aiProgresso.IsVisible = false;
                    lblSincTitle.Text = "Banco de dados sincronizado com sucesso...\n" + totalProp + " propriedades foram baixadas do servidor.";
                }
                catch (SQLiteException ex)
                {
                    await DisplayAlert("Erro na requisição", "O seguinte erro ocorreu: " + ex.Message + ".\nTente novamente mais tarde", "OK");
                }
            }
    

    Any help will b apreciated.

    Thursday, February 27, 2020 11:17 PM

Answers

  • User382871 posted

    but when the user try to resync the data the SQLite.SQLiteException: 'Constraint' is displaying In most SQL databases, if you have a UNIQUE, NOT NULL, or CHECK constraint on a table and you try to do an UPDATE or INSERT that violates the constraint, the database will abort the operation in progress, back out any prior changes associated with the same UPDATE or INSERT statement, and return an error.

    In your code, the Propriedade table has a UNIQUE constraint - PrimaryKey and the corresponding value should be unique.

    Please check if the return list data from json correct. var propriedade = JsonConvert.DeserializeObject<List<Propriedade>>(response);

    Refer to: https://stackoverflow.com/questions/41793700/xamarin-sqlite-sqliteexception-constraint-using-sqlite-net-pcl https://stackoverflow.com/questions/37079931/why-insertall-in-sqlite-crashes

    • Marked as answer by Anonymous Thursday, June 3, 2021 12:00 AM
    Friday, February 28, 2020 8:25 AM
  • User43400 posted

    I found the issue. No problem with the application code.

    The issue was in the API, it was duplicating the data each request instead of insert only the current data. I clear the API list before insert and all is well now.

    Thanks all for the fast response.

    • Marked as answer by Anonymous Thursday, June 3, 2021 12:00 AM
    Friday, February 28, 2020 1:51 PM

All replies

  • User382871 posted

    but when the user try to resync the data the SQLite.SQLiteException: 'Constraint' is displaying In most SQL databases, if you have a UNIQUE, NOT NULL, or CHECK constraint on a table and you try to do an UPDATE or INSERT that violates the constraint, the database will abort the operation in progress, back out any prior changes associated with the same UPDATE or INSERT statement, and return an error.

    In your code, the Propriedade table has a UNIQUE constraint - PrimaryKey and the corresponding value should be unique.

    Please check if the return list data from json correct. var propriedade = JsonConvert.DeserializeObject<List<Propriedade>>(response);

    Refer to: https://stackoverflow.com/questions/41793700/xamarin-sqlite-sqliteexception-constraint-using-sqlite-net-pcl https://stackoverflow.com/questions/37079931/why-insertall-in-sqlite-crashes

    • Marked as answer by Anonymous Thursday, June 3, 2021 12:00 AM
    Friday, February 28, 2020 8:25 AM
  • User43400 posted

    So, the original data from API has not duplicated or null value in this column.

    The error is caused because the local table has not been dropped. The first time works as a sharm because there is no data.

    I don't understand it if I used the drop table function before inserting the data again.

    Do you have some example of code that I can use to accomplish that task (if table doesn't exist, create otherwise, drop and recreate it)?

    Friday, February 28, 2020 10:31 AM
  • User3516 posted

    Try adding database.Commit(); after you drop your table.

    As an alternative you can also try to delete everything from your table: database.DeleteAll<Propriedade>();

    If it is ok to just insert new data and update existing ones you can also use InsertOrReplace .

    Friday, February 28, 2020 10:47 AM
  • User43400 posted

    I found the issue. No problem with the application code.

    The issue was in the API, it was duplicating the data each request instead of insert only the current data. I clear the API list before insert and all is well now.

    Thanks all for the fast response.

    • Marked as answer by Anonymous Thursday, June 3, 2021 12:00 AM
    Friday, February 28, 2020 1:51 PM