Insert data from text fields into SQLite database

Question

I am trying to insert data into my SQLite database. I have imported all the neccassry frameworks and database files nedded for my project. In my controller class xib there are two textfields and a button. I want the data entered into both of the text fields saved in my database when I click on the button.

In my app delagate I have created two functions, one function to append the path of the database, and the other to insert data into the database. In the insert function, I check for certain conditions, i.e., if data gets added, an alert view should be displayed showing the record added, but when I add a new record it always goes into the else block, which is an error.

-(void)checkAndCreateDB {
    NSString* databaseName = @"login.sqlite";
    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    NSString *databasePath=[documentsDir stringByAppendingPathComponent:@"login.sqlite"];
    databasePath = [documentsDir stringByAppendingPathComponent:databaseName];
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    success = [fileManager fileExistsAtPath:databasePath];
    if(success) return;
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];
    [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
}

-(void)insertData{

    sqlite3 *database;
    sqlite3_stmt *statement;
     NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
     NSString *documentsDir = [documentPaths objectAtIndex:0];
    NSString *databasePath=[documentsDir stringByAppendingPathComponent:@"login.sqlite"];
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
    {

        NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO Loginchk (uname,password) VALUES ('%@',' %@');",Gunameq,Gpassq];
        const char *insert_stmt = [insertSQL UTF8String];
        if(sqlite3_prepare_v2(database, insert_stmt, -1, &statement, nil)== SQLITE_OK)
        {
            sqlite3_bind_text(statement, 1, [Gunameq UTF8String], -1, NULL);
            sqlite3_bind_text(statement, 2, [Gpassq UTF8String], -1, NULL);
        }

        if(sqlite3_step(statement)==SQLITE_DONE) 
        {
            UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Add Record" message:@"Contact Added" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];    
            [alert show];
            [alert release];
            alert=nil;

        }
        else 
        {
            UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"record" message:@"record not created" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];   
            [alert show];
            [alert release];
            alert=nil;
        }   
        // Release the compiled statement from memory
        sqlite3_finalize(statement);    
        sqlite3_close(database);
    }
}

This is my login controller class. Here I have declared a function through which I access my app delegate's functions.

-(IBAction)buttonPressed:(id)sender
{

    Gpassq=Password.text;
    Gunameq=Uname.text;


    NSLog(@"%@%@",Gunameq,Gpassq);
    AddListAppDelegate *appDelegate =(AddListAppDelegate *)[[UIApplication sharedApplication]delegate];
    [appDelegate insertData];

}

Please solve this problem.

Solution 1

static sqlite3_stmt *insertStmt = nil;

if(insertStmt == nil) 
{
    insertSql = "INSERT INTO Loginchk (uname,password) VALUES(?,?)";
    if(sqlite3_prepare_v2(database, insertSql, -1, &insertStmt, NULL) != SQLITE_OK)
        NSAssert1(0, @"Error while creating insert statement. '%s'", sqlite3_errmsg(database));
}

sqlite3_bind_text(insertStmt, 1, [Gunameq UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(insertStmt, 2, [Gpassq UTF8String], -1, SQLITE_TRANSIENT);
if(SQLITE_DONE != sqlite3_step(insertStmt))
    NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
else
    NSLog("Inserted");
//Reset the add statement.
sqlite3_reset(insertStmt);
insertStmt = nil;           

In above you can see. If you are binding data no need to have stringWithFormat. Just put question marks and than bind text.

Hope it helps.

Solution 2

this code may be run

-(void) insert:key1:key2:key3
    {
        databaseName= @"db3.sqlite";
        NSArray *documentPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
        NSString *documentsDir=[documentPaths objectAtIndex:0];
        databasePath=[documentsDir stringByAppendingPathComponent:databaseName];
        sqlite3 *database;


        if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
            NSString *stmnt=[[[NSString alloc]initWithFormat:@"insert into login values(\"%@\",\"%@\",\"%@\")", key1,key2,key3] autorelease];
            const char *sqlStatement = [stmnt UTF8String];
            sqlite3_stmt *compiledStatement;    
            if (sqlite3_prepare_v2(database, sqlStatement,-1, &compiledStatement,NULL) == SQLITE_OK)
            {
                if (SQLITE_DONE!=sqlite3_step(compiledStatement))
                {
                    UIAlertView *al=[[UIAlertView alloc] initWithTitle:@"INFO" 
                                                        message:@"Registration Fail"
                                                        delegate:self
                                                         cancelButtonTitle:@"ok"
                                                          otherButtonTitles:nil];
                    [al show];
                    [al release];
                }

                else {
                    UIAlertView *al=[[UIAlertView alloc] initWithTitle:@"INFO" 
                                                               message:@"Registerd"
                                                              delegate:self
                                                     cancelButtonTitle:@"ok"
                                                     otherButtonTitles:nil];
                    [al show];
                    [al release];
                }


            }

            sqlite3_reset(compiledStatement);
        }
        sqlite3_close(database);
    }
    -(IBAction)clicked:(id)sender
    {

        NSString *t1=[[NSString alloc]initWithFormat:@"%@",txt1.text];

        NSString *t5=[[NSString alloc]initWithFormat:@"%@",txt2.text];

        NSString *t3=[[NSString alloc]initWithFormat:@"%@",txt3.text];

        //NSString *t2=[[NSString alloc]init];
        //NSInteger t3;
        //NSString *t3=[[NSString alloc]init];

        //t1=txt1.text;
        //t2=txt2.text;
        //t3=txt3.text;

        [self insert:t1:t5:t3];

    }

Comments

[Jhaliya - Praveen Sharma]

........ possible duplicate of stackoverflow.com/questions/5699025/…