[Interest] Storing complex SQL relations with Qt

Knut Krause knut.krause at lagom.de
Thu Dec 12 20:00:42 CET 2013


Thanks for your opinion. I thought I'd be at least this far right now but it 
looks like I cannot insert new rows. I already opened a new thread

[Interest] How to insert a new record with generated primary key into a 
QSqlTableModel

for that. But I've been readin all day long and I can't find a solution on how 
I can initialize a new record with my DataMapper form …


Knut

Am Donnerstag, 12. Dezember 2013, 21:24:09 schrieb Constantin Makshin:
> Usually if you don't explicitly ask for a transaction, RDBMS creates an
> implicit one for each statement. AFAIK, SQLite uses this idea too.
> 
> And the next to last paragraph at http://www.sqlite.org/autoinc.html
> gives an impression that uniqueness of values in AUTOINCREMENT columns
> isn't affected by transactions, i.e. automatically generated customer
> IDs will be unique no matter what happens to your application after step 1.
> 
> SQLite driver's lastInsertId() returns the result of
> sqlite3_last_insert_rowid(), so step 2 looks safe enough (according to
> SQLite documentation, INTEGER PRIMARY KEY [AUTOINCREMENT] column is just
> an alias to row ID). Just remember to get it from the same query object
> you use to insert the customer. ;)
> 
> Conclusion — your idea looks good to me. Not very portable (different
> RDBMSes [may] have different notation of "last insert ID"), but should
> work with SQLite.
> 
> On 12/12/2013 10:57 AM, Knut Krause wrote:
> > Hi,
> > 
> > my application uses sqlite for its data and I modeled some complex
> > relations for customers, appointments and so on.
> > 
> > Now I want to edit such a relation using a custom dialog where the
> > appointments go to a list and some other stuff to a table (all are
> > QSqlTableModels).
> > 
> > The problem is: How do I insert the data depending on other things? Let's
> > say an appointment needs a customer. I insert the new customer data and
> > the appointments and when I click on submit I'd have to do something like
> > 
> > 1. insert customer (I think with primary key autoincrement I can simply do
> > that)
> > 
> > 2. retrieve the inserted customers ID. Can I use the models query() to get
> > lastInsertId()? Is this save?
> > 
> > 3. Set this ID to all models that depends on customer (by hand?) and
> > submit
> > them as well
> > 
> > Is this approach safe? I mean AFAIK there are no transactions involved and
> > what happens for example if I insert the customer and then my application
> > crashes leaving my database in an inconsistent state?
> > 
> > Would be nice if someone could explain how I'm supposed to do this.
> > 
> > regards
> > 
> > 
> > Knut



More information about the Interest mailing list