how to create table in sqlite data base programmatically
i am new to sqlite on iphone.
i am to creating a data base using terminal.Now i need to create a table through code in my application.
my code for that is
开发者_StackOverflow中文版-(BOOL)createtable:(NSString *)tableName {
BOOL ret;
int rc;
// SQL to create new table
NSString *sql_str = [NSString stringWithFormat:@"CREATE TABLE %@ (pk INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , ItemName VARCHAR(100), Quantity INTEGER DEFAULT 0, Status BOOLEAN,Type VARCHAR(100))", tableName];
const char *sqlStatement = (char *)[sql_str UTF8String];
NSLog(@"query %s",sqlStatement);
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(database, sqlStatement, -1, &stmt, NULL);
ret = (rc == SQLITE_OK);
if (ret)
{ // statement built, execute
rc = sqlite3_step(stmt);
ret = (rc == SQLITE_DONE);
}
sqlite3_finalize(stmt); // free statement
NSLog(@"creating table");
return ret;
}
but table is not created, i am checking that database in mozilla sqlite manager.
can any one please help me.
Thank u in advance.
You can use sqlite3_exec() method instead of sqlite3_step().
sqlite3_exec() will execute whatever the query you have given.
I am sure, It will definitely help you.
-(BOOL)createNewTable
{
NSArray *array=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *filePath=[array objectAtIndex:0];
filePath =[filePath stringByAppendingPathComponent:@"yourdatabase.db"];
NSFileManager *manager=[NSFileManager defaultManager];
BOOL success = NO;
if ([manager fileExistsAtPath:filePath])
{
success =YES;
}
if (!success)
{
NSString *path2=[[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"yourdatabase.db"];
success =[manager copyItemAtPath:path2 toPath:filePath error:nil];
}
createStmt = nil;
NSString *tableName=@"SecondTable";
if (sqlite3_open([filePath UTF8String], &database) == SQLITE_OK) {
if (createStmt == nil) {
NSString *query=[NSString stringWithFormat:@"create table %@(rollNo integer, name text)",tableName];
if (sqlite3_prepare_v2(database, [query UTF8String], -1, &createStmt, NULL) != SQLITE_OK) {
return NO;
}
sqlite3_exec(database, [query UTF8String], NULL, NULL, NULL);
return YES;
}
}
return YES;
}
Im posting an answer as well because there is no error handling in your example and that seems valuable
INIT DB:
self.databasePath = [[NSString alloc]init];
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"myDB.db"];
success = [fileManager fileExistsAtPath:writableDBPath];
self.databasePath = writableDBPath;
NSFileManager *filemgr = [NSFileManager defaultManager];
if ([filemgr fileExistsAtPath: self.databasePath ] == NO)
{
const char *dbpath = [self.databasePath UTF8String];
if (sqlite3_open(dbpath, &myDB) == SQLITE_OK)
{
NSLog(@"Database Opened");
sqlite3_close(myDB);
} else {
NSLog(@"Failed to open/create database");
}
}else {
// NSLog(@"File exist");
}
MAKE CALL TO CREATE TABLES:
const char *dbpath = [self.databasePath UTF8String];
if (sqlite3_open(dbpath, &myDB) == SQLITE_OK) {
NSString *charsTableNameQuery = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(id INTEGER PRIMARY KEY, charName TEXT)",charsTableName];
int results = 0;
//create all chars tables
const char *charsTableNameQuerySQL = [charsTableNameQuery UTF8String];
sqlite3_stmt * charsTableNameStatment = nil;
results = sqlite3_exec(myDB, charsTableNameQuerySQL, NULL, NULL, NULL);
if (results != SQLITE_DONE) {
const char *err = sqlite3_errmsg(tekkenDB);
NSString *errMsg = [NSString stringWithFormat:@"%s",err];
if (![errMsg isEqualToString:@"not an error"]) {
NSLog(@"createTables-chartables error: %@",errMsg);
return FALSE;
}
}
sqlite3_finalize(charsTableNameStatment);
sqlite3_close(myDB);
return TRUE;
}else{
NSLog(@"database not opened");
}
return FALSE;
}
- create objc file with bridging header (YOURFILENAME-Bridging-Header.h)
paste this code in your CustomDB.m file. ("CustomDB.m" <- this is my file name)
#import "CustomDB.h" @implementation CustomDB - (void)createWith:(NSString *)strFileName; { NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *docPath = [path objectAtIndex:0]; dbPathString = [docPath stringByAppendingPathComponent:strFileName]; NSLog(@"%@",dbPathString); char *error; NSFileManager *fileManager = [NSFileManager defaultManager]; if (![fileManager fileExistsAtPath:dbPathString]) { const char *dbPath = [dbPathString UTF8String]; //creat db here if (sqlite3_open(dbPath, &personDB)==SQLITE_OK) { const char *sql_stmt = "CREATE TABLE IF NOT EXISTS PERSONS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, AGE TEXT)"; sqlite3_exec(personDB, sql_stmt, NULL, NULL, &error); sqlite3_close(personDB); } } } -(void)insertInToDB:(NSString *)strQuery{ char *error; if (sqlite3_open([dbPathString UTF8String], &personDB)==SQLITE_OK) { NSString *inserStmt = [NSString stringWithFormat:@"%@", strQuery]; const char *insert_stmt = [inserStmt UTF8String]; if (sqlite3_exec(personDB, insert_stmt, NULL, NULL, &error)==SQLITE_OK) { NSLog(@"Insert successfully"); } sqlite3_close(personDB); } } -(NSArray *)selectFromDB:(NSString *)strQuery{ sqlite3_stmt *statement; id result; NSMutableArray *thisArray = [[NSMutableArray alloc]init]; if (sqlite3_open([dbPathString UTF8String], &personDB)==SQLITE_OK) { NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM PERSONS"]; const char* query_sql = [querySql UTF8String]; if (sqlite3_prepare(personDB, query_sql, -1, &statement, NULL)==SQLITE_OK) { while (sqlite3_step(statement)==SQLITE_ROW) { NSMutableDictionary *thisDict = [[NSMutableDictionary alloc]init]; for (int i = 0 ; i < sqlite3_column_count(statement) ; i++) { if(sqlite3_column_type(statement,i) == SQLITE_NULL){ continue; } if (sqlite3_column_decltype(statement,i) != NULL && strcasecmp(sqlite3_column_decltype(statement,i),"Boolean") == 0) { result = [NSNumber numberWithBool:(BOOL)sqlite3_column_int(statement,i)]; } else if (sqlite3_column_type(statement,i) == SQLITE_INTEGER) { result = [NSNumber numberWithInt:(int)sqlite3_column_int(statement,i)]; } else if (sqlite3_column_type(statement,i) == SQLITE_FLOAT) { result = [NSNumber numberWithFloat:(float)sqlite3_column_double(statement,i)]; } else { if((char *)sqlite3_column_text(statement,i) != NULL){ result = [[NSString alloc] initWithUTF8String:(char *)sqlite3_column_text(statement,i)]; [thisDict setObject:result forKey:[NSString stringWithUTF8String:sqlite3_column_name(statement,i)]]; result = nil; } } if (result) { [thisDict setObject:result forKey:[NSString stringWithUTF8String:sqlite3_column_name(statement,i)]]; } } [thisArray addObject:[NSDictionary dictionaryWithDictionary:thisDict]]; } } } return thisArray; } -(void)deleteFromDB:(NSString *)strQuery{ char *error; if (sqlite3_exec(personDB, [strQuery UTF8String], NULL, NULL, &error)==SQLITE_OK) { NSLog(@"Person deleted"); } } @end // paste this code in your CustomDB.h file. ("CustomDB.h" <- this is my file name) #import <Foundation/Foundation.h> #import <sqlite3.h> @interface CustomDB : NSObject { sqlite3 *personDB; NSString *dbPathString; } -(void)createWith:(NSString *)strFileName; -(void)insertInToDB:(NSString*)strQuery; -(NSArray *)selectFromDB:(NSString *)strQuery; -(void)deleteFromDB:(NSString*)strQuery; @end
Don't forget to add this line in your bridging header -> #import "CustomDB.h" and adding sqlite framework in your project... :)
Enjoy these create,insert,delete and select queries in sqlite database :)
call in receive memmory warning
(IBAction)findContact:(UIButton *)sender {
const char *dbPath = [databasePath UTF8String];
sqlite3_stmt *statement;
if (sqlite3_open(dbPath, &contactDB)== SQLITE_OK) {
NSString *querySQL = [NSString stringWithFormat:@"SELECT ADDRESS,PHONE FROM CONTACTS WHERE NAME = \"%@\"",_name.text];
const char *query_stmt = [querySQL UTF8String];
if(sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL)== SQLITE_OK)
{
if (sqlite3_step(statement)== SQLITE_ROW) {
NSString *addressField = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
_address.text = addressField;
NSString *phoneField = [[NSString alloc]initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
_phoneNo.text = phoneField;
_status.text = @"Match Found";
}else{
_status.text = @"Match not found";
_address.text = @"";
_phoneNo.text = @"";
}
sqlite3_finalize(statement);
}
sqlite3_close(contactDB);
}
}
NSString *docsDict; NSArray *dirPath;
//Get the Dictionary
dirPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES);
docsDict = dirPath[0];
NSLog(@"Path : %@",docsDict);
//Build the path to keep the database
_database = [[NSString alloc] initWithString:[docsDict stringByAppendingPathComponent:@"myUser.sql"]];
NSFileManager *fileMngr= [NSFileManager defaultManager];
if([fileMngr fileExistsAtPath:_database] == NO)
{
const char *dbpath = [_database UTF8String];
if(sqlite3_open(dbpath, &_DB) == SQLITE_OK)
{
char *errorMassage;
const char *sql_statement = "CREATE TABLE IF NOT EXISTS users (ID INTEGER PRIMARY KEY AUTOINCREMENT, FIRSTNAME TEXT, LASTNAME TEXT , MOBILENO TEXT)";
if (sqlite3_exec(_DB,sql_statement,NULL,NULL,&errorMassage)!= SQLITE_OK)
{
[self showUIAlertWithMessage:@"Failed to create the table" andTitle:@"error"];
}
sqlite3_close(_DB);
}
else
{
[self showUIAlertWithMessage:@"Failed to open/create the table" andTitle:@"Error"];
}
}
加载中,请稍侯......
精彩评论