How To Insert All New Values To The Same Row For Each Session In Sqlite (for Ios)

i can't seem to understand this database issue completely. First of all, should i just open my db connection from the beginning and keep it open until the application enters the ba

Solution 1:

A couple of thoughts:

1. Why do you have two methods to insert the first and last name? Do you need that? Any reason you don't do that in a single SQL statement?

2. Assuming for a second that you must do it in separate statements like this, the first one should be INSERT and the second SQL statement should be UPDATE.

3. If you want, SQLite can keep track of the unique identifier for the row for you. So add a column called user_info_id integer primary key autoincrement. Then, immediately after you insert your data, look at the [database lastInsertRowId], and that will return the value auto-generated for your user_info_id column.

   So, going back to prior point, you would insert a row with the first name, immediately retrieve the lastInsertRowId, and then use that as a parameter to the WHERE clause in your UPDATE statement where you set the last name, e.g.,

   success = [database executeUpdate:@"UPDATE userInfo SET last_name = ? WHERE user_info_id = ?", lastName, @(rowIdValue)];

   Please note that that value for the user_info_id is presumably stored in a sqlite_int64 variable (e.g., the value returned by the lastInsertRowId method), so when you use that in a executeUpdate call, make it a NSNumber by wrapping it with a @(rowIdValue).

4. While FMDB saves you a lot of headaches with binding values to columns and the like, you still should be checking the return values (generally a BOOL value in FMDB). If it failed, log [database lastErrorMessage].

5. Do not open and close the database for each SQL statement. Open the database once and leave it open. It automatically commits the INSERT/UPDATE statements as you perform them, so it's very robust.

Solution 2:

If I understood your problem right, you want to create a new entry in your database with every new session. So just give your session an ID (i.e. a random Number) and keep it stored somewhere in your App. If the ID already exists in your Database, chose a new one. If not, insert a new row. Your ID should be the primary key.

So now all values are null, except the ID. If you want to add/change values for this specific ID, just execute something like this:

NSString*sql = [NSString stringWithFormat:@"UPDATE userInfo SET last_name = \"%@\" WHERE id = %@", lastname, uniqueSessionID];

PS: Yes, you should open and close the connection every time you use it, and not keep it open all the time. An open connection takes up resources which could be used somewhere else.