将文本字段中的数据插入 SQLite 数据库

Question

我正在尝试将数据插入到我的 SQLite 数据库中。我已经导入了我的项目所需的所有必要框架和数据库文件。在我的控制器类 xib 中，有两个文本字段和一个按钮。我希望当我单击按钮时，输入到两个文本字段中的数据都保存在我的数据库中。

在我的应用程序 delagate 中，我创建了两个函数，一个函数用于追加数据库的路径，另一个函数用于将数据插入数据库。在插入函数中，我检查某些条件，即，如果添加了数据，则应显示一个警报视图，显示添加的记录，但是当我添加新记录时，它总是进入 else 块，这是一个错误。

-(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);
    }
}

这是我的登录控制器类。在这里，我声明了一个函数，通过它我可以访问我的应用程序委托的函数。

-(IBAction)buttonPressed:(id)sender
{

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


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

}

请解决这个问题。

Answer 1

插入的另一种方法是

// Create insert statement for the address

NSString  *insertStatement = [NSString stringWithFormat:@"INSERT INTO ADDRESS (STREETNAME, STREETNUMBER, PERSONID) VALUES (\"%@\", \"%@\", \"%d\")", person.address.streetName, person.address.streetNumber, personID];

 if ( sqlite3_exec(databaseHandle, [insertStatement UTF8String], NULL, NULL, &error) == SQLITE_OK)
    {
        NSLog(@"Person inserted.");
    }
    else
    {
        NSLog(@"Error: %s", error);
    }

Answer 2

试试这个代码：

-(void)addItem{

//for saving the data into database

    if(addStmt == nil) {
        const char *sql = "insert into employee(name, address) Values(?, ?)";
        if(sqlite3_prepare_v2(database, sql, -1, &addStmt, NULL) != SQLITE_OK)
            NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
    }

    sqlite3_bind_text(addStmt, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(addStmt, 2, [address UTF8String], -1, SQLITE_TRANSIENT);
    NSLog(@"%@",name);
    NSLog(@"%@",address);
    if(SQLITE_DONE != sqlite3_step(addStmt))
        NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
    else
        //SQLite provides a method to get the last primary key inserted by using sqlite3_last_insert_rowid
        rowID = sqlite3_last_insert_rowid(database);

    //Reset the add statement.
    sqlite3_reset(addStmt);
}


+ (void) getInitialDataToDisplay:(NSString *)dbPath {

//for retrieving data from database

    InsertDataAppDelegate *appDelegate = (InsertDataAppDelegate *)[[UIApplication sharedApplication] delegate];

    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

        const char *sql = "select  name, address from employee";
        sqlite3_stmt *selectstmt;
        if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {

            while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                NSInteger primaryKey = sqlite3_column_int(selectstmt, 0);

                //NSInteger primaryKey = sqlite3_column_int(selectstmt, 0);
                Item *Obj = [[Item alloc]initWithPrimaryKey:primaryKey];
                Obj.name = [NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 0)];
                Obj.address=[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)];
                Obj.isDirty = NO;
                [appDelegate.array addObject:Obj];
                [Obj release];
            }
        }
    }
    else
        sqlite3_close(database); //Even though the open call failed, close the database connection to release all the memory.
}

Answer 3

- (IBAction)sbumitbuttoncliked:(id)sender 
{

    sqlite3 *database;

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"somefile1.sqlite"];

    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) 
    {

        sqlite3_stmt *statment = nil;

        NSString *queryString = [NSString stringWithFormat:@"Insert into form1 (name,password,phoneno)values(\'%@\',\'%@\',\'%@\')",textobj1.text, textobj2.text,textobj3.text];

        NSLog(@"%@",queryString);
        const char *sql = [queryString UTF8String];
        sqlite3_prepare_v2(database, sql, -1, &statment, NULL);

        if (textobj1.text.length==0||textobj2.text.length==0||textobj3.text.length==0) {
            UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"Success" message:@"Not Registered " delegate:nil cancelButtonTitle:@"OK" otherButtonTitles: nil ];
            [alert show];
        }

        else if (sqlite3_step(statment) == SQLITE_DONE)
        {
            UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"Success" message:@"you have successfully registered" delegate:nil cancelButtonTitle:@"OK" otherButtonTitles: nil ];
            [alert show];
            [self dismissModalViewControllerAnimated:YES];

        }

        sqlite3_finalize(statment);
    }

Answer 4

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;           

在上面你可以看到。如果您要绑定数据，则无需使用 stringWithFormat。只需放置问号并绑定文本即可。

希望有帮助。

Answer 5

这段代码可以运行

-(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];

    }