Page 1 of 1

Crach inserting same objects in wxSqlite3

Posted: Thu Jun 11, 2020 4:39 pm
by dkaip
Hello. Trying to insert the cl object that already exists in db, i have no error but program crashes ..
The table has many other columns but the inserting object is the client, that is unique, so for the already exists must not insert a new row. Is that correct?

Code: Select all

db->ExecuteUpdate(wxS("CREATE TABLE clients (id int unique, client blob unique, miniseis blob, daneia blob , kartes blob , logariasmoi blob , diafora blob )"));
Here the code that crashes the program.

Code: Select all

        wxString sql=wxT("insert into clients ( client ) values ('");
        sql+=cl.serialize();
        sql+=wxT("')");
        db->ExecuteUpdate(sql);
How to prevent crash? Why is crashed if client as unique must not make a new row?
Thank you.
Jim

Re: Crach inserting same objects in wxSqlite3

Posted: Thu Jun 11, 2020 4:49 pm
by doublemax
The "crash" is most likely an exception thrown by wxSQLite3. Wrap the code in try/catch and print the exception message. It should tell you what's wrong. (if you don't know how to do that, search the wxSQLite3 sample for "catch (wxSQLite3Exception& e)")

Re: Crach inserting same objects in wxSqlite3

Posted: Thu Jun 11, 2020 8:08 pm
by dkaip
Thank you.
Error code is 19 and message "constraint failed[2067]: UNIQUE constraint failed: clients.client"

Code: Select all

 catch (wxSQLite3Exception& e)
  {
    wxMessageBox(wxString::Format(wxT("%d"),e.GetErrorCode()));
    wxMessageBox(e.GetMessage());
  }
If client is unique it must not try to make a row. So why make the exception? How change the code to prevent exception?
Than you.
Jim

Re: Crach inserting same objects in wxSqlite3

Posted: Thu Jun 11, 2020 8:10 pm
by doublemax
If an entry with that id already exists, you need to either DELETE it first, or use UPDATE instead of INSERT.

Re: Crach inserting same objects in wxSqlite3

Posted: Fri Jun 12, 2020 7:33 am
by utelle
doublemax wrote:
Thu Jun 11, 2020 4:49 pm
The "crash" is most likely an exception thrown by wxSQLite3. Wrap the code in try/catch and print the exception message. It should tell you what's wrong. (if you don't know how to do that, search the wxSQLite3 sample for "catch (wxSQLite3Exception& e)")
As the OP confirmed an exception was thrown due to a constraint violation. It is strongly recommended to use try-catch blocks to handle exceptions. Otherwise the application is usually aborted.

However, there are additional options how to handle insert conflicts within SQL: see ON CONFLICT clause or UPSERT clause (an extension to the INSERT statement).

Re: Crach inserting same objects in wxSqlite3

Posted: Fri Jun 12, 2020 10:46 am
by dkaip
Thank you.
Now runs ok with
select exists(select * from table where condition=value limit 1)
where *=client, condition=client and value is previews client.

Code: Select all

            wxString sql1=wxT("select exists(select client from clients where client='");
            sql1+=cl.serialize();
            sql1+=wxT("' limit 1)");

            wxSQLite3ResultSet set = db->ExecuteQuery(sql1);

            {
                int count = 0;
                wxString s;
                set.NextRow();
                s = set.GetAsString(0);
                set.Finalize();
                if(!s.IsSameAs(wxT("1")))
                {
                    wxString sql=wxT("insert into clients ( client ) values ('");
                    sql+=cl.serialize();
                    sql+=wxT("')");
                    db->ExecuteUpdate(sql);
                }
            }
Thank you
Jim