in iphone how to get Sqlite last insert row id?

Question

I am making an application where i want to use update query?

In my application i have two stages to complete the registration process

in page1 i have registration form and one submit button?

on click of submit button all the details should get insert into my sqlite table.

in page2 i have confirmation page and one edit and continue button?

all the value saved in a table should be view by the user on this page and if he want's to make any change he should be able to do that. once user had edited some value and he press continue button all the value insert should get updated?

but i have try doing this but i am not able to update the last enter value?

following is my code page1 insert code:

-(void)submit
{

    if( ([UserName.text isEqualToString:@""]) || ([Password.text isEqualToString:@""]) || 
        ([ConfirmPassword.text isEqualToString:@""]) || ([Name.text isEqualToString:@""]) || 
        ([Email.text isEqualToString:@""]) || ([ContactNO.text isEqualToString:@""]) || 
        ([MobileNo.text isEqualToString:@""]) || ([Address.text isEqualToString:@""]) )

    {

        UIAlertView *ErrorAlert = [[UIAlertView alloc] initWithTitle:@"Error!!" 
                                                                 message:@"Please fill in the details." delegate:nil
                                                       cancelButtonTitle:@"OK"
                                                       otherButtonTitles:nil, nil];
            [ErrorAlert show];
            [ErrorAlert release];
        }
        else 
        {
            Confirmation_form *conForm = [[Confirmation_form alloc] initWithNibName:@"Confirmation_form" bundle:nil];
            conForm.data = UserName.text;
            conForm.data1 = Password.text;
            conForm.data2 = ConfirmPassword.text;
            conForm.data3 = Name.text;
            conForm.data4 = Email.text;
            conForm.data5 = ContactNO.text;
            conForm.data6 = MobileNo.text;
            conForm.data7 = Address.text;


            sqlite3_stmt  *statement;
            const char *dbpath = [databasePath UTF8String];
            if (sqlite3_open(dbpath, &test1DB) == SQLITE_OK)
            {



                NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO test(UserName, Password, ConfirmPassword, Name, Email, ContactNO, MobileNo, Address) VALUES (\"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\", \"%@\")",UserName.text, Password.text, ConfirmPassword.text, Name.text, Email.text, ContactNO.text, MobileNo.text, Address.text];
                const char *insert_stmt = [insertSQL UTF8String];
                sqlite3_prepare_v2(test1DB, insert_stmt, -1, &statement, NULL);
                if(sqlite3_step(statement) == SQLITE_DONE)
                {
                    //status.text = @"Contact added";
                    UserName.text = @"";
                    Password.text = @"";
                    ConfirmPassword.text = @"";
                    Name.text = @"";
                    Email.text = @"";
                    ContactNO.text = @"";
                    MobileNo.text = @"";
                    Address.text = @"";


                    sqlite3_last_insert_rowid;
                    rowID = sqlite3_last_insert_rowid(test1DB);
                    NSLog(@"last inserted rowId = %d",rowID);

                    sqlite3_reset(statement);
                    sqlite3_finalize(statement);
                    sqlite3_close(test1DB);


                    [self.navigationController pushViewController:conForm animated:YES];

                }                   
            }


        }
    }

from the above code i am able to get the row id but i am not able to use that rowid in update query

following is my code page2 Update code:

-(IBAction)Update;
{

sqlite3_stmt  *statement;
const char *dbpath = [databasePath UTF8String];
if (sqlite3_open(dbpath, &test1DB) == SQLITE_OK)
{
    NSString *insertSQL = [NSString stringWithFormat:@"UPDATE test SET UserName='%@',Password='%@',ConfirmPassword='%@',Name='%@',Email='%@',ContactNO='%@',MobileNO='%@',Address='%@'WHERE ID='%@'",UserName.text,Password.text,ConfirmPassword.text, Name.text, Email.text, ContactNO.text, MobileNo.text, Address.text, sqlite3_last_insert_rowid(test1DB)];

    const char *insert_stmt = [insertSQL UTF8String];
    sqlite3_prepare_v2(test1DB, insert_stmt, -1, &statement, NULL);
    if(sqlite3_step(statement) == SQLITE_DONE)
    {
        sqlite3_step(statement);
                                UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"UIAlertView" message:@"Record added" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
                                    [alert show];
                                    [alert release];
                                    alert = nil;    
        sqlite3_finalize(statement);
        sqlite3_close(test1DB);
    }

    else {
        sqlite3_step(statement);
                        UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"UIAlertView" message:@"Record notadded" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];
                        [alert show];
                        [alert release];
                        alert = nil;    
         }
}

}

Solution 1

NSInteger lastRowId = sqlite3_last_insert_rowid(yourdatabasename);

Solution 2

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);

http://www.sqlite.org/c3ref/last_insert_rowid.html

EDIT: call that function after you call the insert function, it will return you the id.

Also:

You're preparing your statement (which compiles the sql) but you're not saving it off. save a ref to that statement and call reset on it before using it again. It saves the compile.

EDIT:

sqlite3_prepare_v2 returns a reference (&) to a statement. Save that off to a member variable and don't call prepare again. Since you're stepping through the results, if you re-use the statement, you need to call sqlite3_reset() on the statement, then call step and it will execute it again without re-compiling (parsing) the sql statement. This is an optimization.

You're not checking return codes from you're sqlite calls. Always check and at a minimum log - but should really happen. Otherwise you're silently moving pass a problem and error later where it's harder to discover.

EDIT:

each sqlite call can fail or have issues. See the close function I reference below. almost every function gives you back a return code. Check them, log them etc...

Finally, you're close needs to be more robust. See the close function in this SO post:

Sqlite in iOS memory issues

Comments

[StuStirling]

I have also written a brief tutorial that includes this implementation.

[bryanmac]

You read last id immediately after the insert (as you have done). At that point, you should pass | share that id with the update page. Do not have the update page read the last inserted id because that creates a very weird coupling. For example, you may want to use that update page to update after the fact and that id won't be the last row id changed in sql necessarily. Pass it.

[bryanmac]

Also, in your update statement, your formatting the int64 with %@. That's not the right format statement - log our your statements.

[Harish]

Hello nilesh check this link it will help you iphonedevsdk.com/forum/iphone-sdk-development/…