ansaurus

Question

Inserting SQL Data into Linked (Foreign Key) Tables in SQLite

Answer 1

A:

SELECT last_insert_rowid()

cement 2010-10-22 14:04:49

Nice, that does work. However, I now have another problem. Using last_insert_rowid() in an INSERT statement works fine, but then the next INSERT statement fails. I need to somehow "store" that temporary value and use it in multiple INSERT statements into the second table. Any thoughts on how to do this?

jfm429 2010-10-23 00:53:46

So what stops you from saving value of last_insert_rowid() to a variable and providing it within other parameters (as selectionArgs) for further insert statements?

cement 2010-10-23 14:00:37

The issue is that if I execute a SQLite statement and grab the value, then execute another SQLite statement using the value, it uses two statements. I'm not sure if this is an issue for locking, since technically a single transaction should be a single SQLite statement because in between the two inserts, the database is in an inconsistent state. If I break up the statement (SQL statement -> C variable -> SQL Statement) it breaks up the transaction. I'd like a way to keep it all within a single string of SQL... or is that not a problem to split it up like that?

jfm429 2010-10-25 04:18:28

It will not be a problem if you will use db.beginTransaction(), then do all your insert operations and call db.setTransactionSuccessful(), db.endTransaction().

cement 2010-10-25 07:13:48

SQLite doesn't have a beginTransaction() function; transactions are handled in the SQL code; however, all statements from the BEGIN TRANSACTION and END TRANSACTION statements must be contained in a single block of SQL. That's my problem; I can't store intermediate values within that SQL block in SQLite.

jfm429 2010-10-25 19:35:08

SQLite doesn't have beginTransaction(). But SQLiteDatabase class does have all methods listed in my comment above.

cement 2010-10-26 06:29:30

There is no such thing as SQLiteDatabase in SQLite. SQLite is C; it's not object-oriented. There also is no sqlite3_*() function that seems to work in that manner. If there is I'd like to know about it.

jfm429 2010-10-26 19:07:15

Sorry. Don't know why, but I thought you are using android. Have you tried to declare sqlite variable and select identity in it? Then you can use variable value for your insert operations.

cement 2010-10-26 19:13:53

SQLite doesn't support variables... at least I can't figure out how they would work. It looks like using a temporary table is the only way to do it.

jfm429 2010-10-27 02:18:08

In any case, it looks like the temporary table will work. It would be nice to have variables in SQLite, but oh well. Thanks for your help!

jfm429 2010-10-27 02:19:13

ansaurus

tags:

views:

answers:

Inserting SQL Data into Linked (Foreign Key) Tables in SQLite

related questions