在SQLite的同一数据库中插入多个表 [英] Insert multiple tables in same database in sqlite

问题描述

创建Iphone应用程序之前，我曾在不同的数据库上执行INSERT QUERY.但是，当我尝试将数据插入同一数据库时，它使我断言失败.我正在读取两个不同的XML URL，并尝试创建数据，然后将其插入表中，这是通过创建DB读取的.有谁知道为什么这会告诉我错误?我有以下使用SQLITE插入数据库的代码:

Creating an Iphone application, I used to perform INSERT QUERY on different databases. But when I tried inserting the data in the same database, it gives me assertion failure. I am reading two different XML URL and trying to create and then insert the data in the table as it is read by creating DB. Does anyone have idea why does this show me error ? I have the following code which does insert on DB using SQLITE:

  -(void) insertProductTable:(NSMutableArray *)Dict{
[Dict retain];
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:@"product.sql"];
NSInteger i;

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

    const char * insert_product_sql = "INSERT OR REPLACE INTO test(ID,KEY ) VALUES (?,?)" ;
    if(sqlite3_prepare_v2(database, insert_product_sql, -1, &product_statement, nil) != SQLITE_OK){
        NSAssert1(0, @"Error: failed to prepare insert_product_sql: '%s'.", sqlite3_errmsg(database));
    }

    for (i = 0; i < [self.array count]; i++) {
     sqlite3_bind_text(product_statement, 1, [[Dict valueForKey:@"ID"] UTF8String] ,-1, SQLITE_TRANSIENT);
     sqlite3_bind_text(product_statement, 2, [[Dict valueForKey:@"KEY"] UTF8String] ,-1, SQLITE_TRANSIENT);
    }

    if (sqlite3_step(product_statement) != SQLITE_DONE) {
        NSAssert(0,@"Error updating table");
    }

    [Dict release]; Dict = 0;
    sqlite3_finalize(product_statement);
    sqlite3_close(database);

}
}

我具有以下方法，该方法执行不同的插入功能，但执行不同的表，但是在此阶段，它不允许我插入，导致我断言失败.两种方法都由不同的类调用.

I have the following method which does the different insert function but different table but at this stage it doesnot allow me to insert throwing me Assertion Failure. Both the methods are called by different class.

    -(void) insertTable:(NSMutableArray *)Dict{
[Dict retain];
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:@"product.sql"];
NSInteger i;

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

    const char * insert_sql = "INSERT OR REPLACE INTO test1(Term,Value ) VALUES (?,?)" ;
    if(sqlite3_prepare_v2(database, insert_sql, -1, &product_statement, nil) != SQLITE_OK){
        NSAssert1(0, @"Error: failed to prepare insert_sql: '%s'.", sqlite3_errmsg(database));
    }

    for (i = 0; i < [self.arayList count]; i++) {
     sqlite3_bind_text(product_statement, 1, [[Dict valueForKey:@"TERM"] UTF8String] ,-1, SQLITE_TRANSIENT);
     sqlite3_bind_text(product_statement, 2, [[Dict valueForKey:@"VALUE"] UTF8String] ,-1, SQLITE_TRANSIENT);
    }

    if (sqlite3_step(product_statement) != SQLITE_DONE) {
        NSAssert(0,@"Error updating table");
    }

    [Dict release]; Dict = 0;

    sqlite3_finalize(product_statement);
    sqlite3_close(database);

}
}

推荐答案

@lifemoveson

@lifemoveson

我曾经对您的代码进行调试，发现该函数

I used to your code to debug and found that the function

sqlite3_prepare_v2(database, insert_product_sql, -1, &product_statement, nil)

每次返回1，它对应于您在所需位置缺少的数据库.

returns 1 everytime and it corresponds to your missing database at the desired location.

我会建议您做一个这样的功能

What I will suggest to you is make a function such as this

-(void)checkAndCreateDatabase{
    databaseName = @"MasterDB.sqlite";
    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    databasePath = [documentsDir stringByAppendingPathComponent:databaseName];
    NSLog(@"database path %@",databasePath);
    BOOL success;

    // Create a FileManager object, we will use this to check the status
    // of the database and to copy it over if required
    NSFileManager *fileManager = [NSFileManager defaultManager];

    // Check if the database has already been created in the users filesystem
    success = [fileManager fileExistsAtPath:databasePath];

    // If the database already exists then return without doing anything
    if(success) return;

    // If not then proceed to copy the database from the application to the users filesystem

    // Get the path to the database in the application package
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

    // Copy the database from the package to the users filesystem
    [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
    //[fileManager release];

    // Now we can add new table

    sqlite3 *database;
    sqlite3_stmt *statement; 
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
    {

        NSString *sqlQuery = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS tbl_Login (userId integer PRIMARY KEY,userName text,password text);"] ;
        const char *create_stmt = [sqlQuery UTF8String];
        sqlite3_prepare_v2(database, create_stmt, -1, &statement, NULL);

        if(sqlite3_step(statement)==SQLITE_DONE) 
        {
            NSLog(@"Table created");            
        }
        else 
        {
            NSLog(@"Table could not be created");
        }   
        // Release the compiled statement from memory
        sqlite3_finalize(statement);    
        sqlite3_close(database);
    }
}

设置类变量databasePath并确保在运行任何数据库查询之前，该数据库实际上存在于应用程序的文档目录中.您可以在应用程序委托

Which sets your class variable databasePath and ensure that before you run any database query the database is actually present in the applcation's document directory. You can call this function in application delegate

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions{
//Other code 
[self checkAndCreateDatabase];

}

当您进行任何数据库调用时，请使用此

When you make any database call use this

const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath , &database) == SQLITE_OK) {
//Code to perform insert operations
 }

我建议您为执行的所有数据库操作编写一个单独的类，而不是调用该类函数以实现所需的功能.这将节省您重写代码的工作.

i would suggest you to write a separate class for all the database operation that you perform and instead call the class function to achieve the desired functionality. This will save your efforts from rewriting the code.

您可以为此使用FMDB包装器，该包装器可以找到

You can make use of FMDB wrapper for this which you can find here

希望这会有所帮助:-)

Hope this helps :-)

这篇关于在SQLite的同一数据库中插入多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！