how can I insert value into my sqlite3 database table?

Question

I am writing a simple query as below

const char *sql = "insert into abc(name) values ('Royal')";

and this will insert each time 'Royal' into my 'name', so now I want to take input from user each time as names of hotel and wants to save them instead of 'Royal', so what should I do?

If you are not clear to my question, you may as me again,,,,,

score 2 · Answer 1 · answered Aug 28 '12 at 06:14

this code is very simple for insert the value in sqlite3 table

 -(void)writeValueInSettings:(NSMutableArray *)arrayvalue

   {   


  if(sqlite3_open([databasePath UTF8String],&myDatabase)==SQLITE_OK)

     {
     database *objectDatabase=[[database alloc]init];

     NSString *stringvalue2=[objectDatabase countValue];

    [objectDatabase release];

     NSLog(@"opened");
     NSString *sql1;

sql1=[[NSString alloc] initWithFormat:@"insert into setting values('%i','%i','%i','%@','%i','%i','%@','%i','%i','%i','%i','%i','%i','%@');",intvalue1,
        [[arrayvalue objectAtIndex:0] intValue],[[arrayvalue objectAtIndex:1] intValue],[arrayvalue objectAtIndex:2],[[arrayvalue objectAtIndex:3] intValue],[[arrayvalue objectAtIndex:4]intValue ],[arrayvalue objectAtIndex:5],[[arrayvalue objectAtIndex:6]intValue],[[arrayvalue objectAtIndex:7]intValue ],[[arrayvalue objectAtIndex:8] intValue],[[arrayvalue objectAtIndex:9] intValue],[[arrayvalue objectAtIndex:10]intValue ],[[arrayvalue objectAtIndex:11]intValue],[arrayvalue objectAtIndex:12]];
    char *err1; 
    if (sqlite3_exec(myDatabase,[sql1 UTF8String],NULL,NULL,&err1)==SQLITE_OK)
    {
        NSLog(@"value inserted:");

         UIAlertView *alert=[[UIAlertView alloc]initWithTitle:@"Attention" message:@"You  inserted successfully" delegate:self cancelButtonTitle:nil otherButtonTitles:@"Ok", nil];
        [alert show];
       [alert release];
    }
    [sql1 release];
    sqlite3_close(myDatabase);
  }

  }

Vasu Ashok · Answer 2 · 2011-02-11T10:57:49.600

0

Hi if Your getting user inputs in userinput textfield

NSString *qry=[NSString stringwithformat:@"insert into abc(name) values (\"%@\")",userinput.text];
const char *sql = [qry UTF8string];
sqlite3 *contactDB; 

const char *dbpath = [databasePath UTF8String]; // Convert NSString to UTF-8

if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) == SQLITE_OK)
 { // SQL statement execution succeeded
 }
} else {
    //Failed to open database
}

edited Feb 11 '11 at 10:57

answered Feb 10 '11 at 11:18

Vasu Ashok

1,413
3
17
37

@Astaln enopi, but I have saved my input string as NSString name as "restaurant_name_save", so now what should I do when I have only NSString, and wants to insert? – Chatar Veer Suthar Feb 10 '11 at 11:40
It is crashed with this console error,,,, [NSString stringwithformat:]: unrecognized selector sent to class 0x1b3800' – Chatar Veer Suthar Feb 10 '11 at 11:42
you must use proper capitalization for method names ` [NSString stringWithFormat:] ` – Rich Feb 10 '11 at 11:43
my database is abc.sql, and 'abc' is table name, so how ur code look like if these are names? – Chatar Veer Suthar Feb 10 '11 at 11:50

score 0 · Accepted Answer · answered Feb 10 '11 at 11:19

0

you need to connect to a database

- (sqlite3 *)database {
    if (nil == db) {
        NSString *path = <path to your database>;
        int res = sqlite3_open([path UTF8String], &db);
        if (res != SQLITE_OK){
            // handle the error.
            db = nil;
            return nil;
        }
    }
    return db;
}

then you can call this with the query

    -(void)executeQuery:(NSString *)query{


        sqlite3_stmt *statement;

        if (sqlite3_prepare_v2([self database], [query UTF8String], -1, &statement, NULL) == SQLITE_OK) {

            sqlite3_step(statement);

        }else{
            // handle the error.
        }

        sqlite3_finalize(statement);
    }

-(void)dealloc {
    //close database connection
    sqlite3_close(db);
    db = nil;
    [super dealloc];
}

answered Feb 10 '11 at 11:19

Rich

1,082
7
11

db is an instance variable ` sqlite3 db; ` – Rich Feb 10 '11 at 11:20
where does it give you that error. I'm not sure, but I think that it means that it tried to do a binary modulus "%" somewhere. – Rich Feb 10 '11 at 11:50
heres a sample query ` [sqlContext executeQuery:[NSString stringWithFormat:@"Insert into abc (name) values('%@');", hotel]]; ` – Rich Feb 10 '11 at 11:52
my sqlite3 is database as "sqlite3 *database" – Chatar Veer Suthar Feb 10 '11 at 12:02

score 0 · Answer 4 · answered Feb 10 '11 at 11:39

0

-(NSArray *) executeSelect:(NSString *)query {
//Data search block****************************************

char *zErrMsg;
char **result;
int nrow, ncol;



sqlite3_get_table(
                  [self database],              /* An open database */

                  [query UTF8String],       /* SQL to be executed */
                  &result,       /* Result written to a char *[]  that this points to */
                  &nrow,             /* Number of result rows written here */
                  &ncol,          /* Number of result columns written here */
                  &zErrMsg          /* Error msg written here */
                  );


NSMutableArray *returnArray = [NSMutableArray arrayWithCapacity:3];



for (int i=0; i<nrow; i++){
    [returnArray addObject:[NSString stringWithUTF8String:result[ncol + i]]];

}

sqlite3_free_table(result);

return returnArray;

}

answered Feb 10 '11 at 11:39

Rich

1,082
7
11

@Rich, my database is "abc.sql", my table name is abc, and "name" is name where I am inserting data in format of NSString named as restaurant_name_save, now tell me exact coding according to them – Chatar Veer Suthar Feb 10 '11 at 11:56
do you want a way to find the path? – Rich Feb 10 '11 at 11:58
@Rich no, I want to take save just NSString into my table, I told U everything (regarding names of tables and other things ) in previous comment, – Chatar Veer Suthar Feb 10 '11 at 11:59
in ` -(sqlite3)database ` if abc.sql is in your resources folder you can get the path with ` [[NSBundle mainBundle] pathForResource:@"abc" ofType:@"sql"]; ` if you make a class to contain the methods I've provided and make an instance of it called sqlContext, in the class that you declared ` restaurant_name_save ` you can do this to save the resturant name ` [sqlContext executeQuery:[NSString stringWithFormat:restaurant_name_save, nameToSave]]; ` – Rich Feb 10 '11 at 12:11
assuming restaurant_name_save looks like this ` NSString *const restaurant_name_save = @"Insert into abc (name) values('%@');"; ` – Rich Feb 10 '11 at 12:18
@Rich, you have given me two coding, which one you are talking about? – Chatar Veer Suthar Feb 10 '11 at 12:21
I have connected with the database, just want to insert values of "restaurant_name_save", nothing else,,,, – Chatar Veer Suthar Feb 10 '11 at 12:22
` executeQuery: ` will simply do the query, if it is an insert query it will insert if it's something else it will do that instead ` executeSelect: ` will search the database and return what it finds and should be used with select statements. So I'm talking about ` executeQuery ` – Rich Feb 10 '11 at 12:24
@Rich, ok dear, I got what you means, now tell me where should I put this method and how can I call this method with insert query? – Chatar Veer Suthar Feb 10 '11 at 12:27
@Rich, I put your method in AppDelegate and make an object, now how can I sent query by using this object and insert data(restaurant_name_save)? – Chatar Veer Suthar Feb 10 '11 at 12:32
what i do is I make a class called SQLContext which stores the database as an instance variable. just put the four method implementations that I've provided into SQLContext. If you only need to use the database in one view controller you can make an SQLContext instance variable in that view controller. otherwise you need to find some more central point of access (possibly your appDelegate). – Rich Feb 10 '11 at 12:34
There will be some point where the user commits the text they have entered, so at this point you want to call a method that makes a string from the text and the insert format, and calls ` executeQuery: `. I've already told you how to call executeQuery: from an object that has an sqlContext variable so I don't know what else to tell you. – Rich Feb 10 '11 at 12:35
@Rich, I agree, I have implemented the database methods in AppDelegate - (NSString *) getDBPath, and - (void) copyDatabaseIfNeeded, now I have put your method in delegate, just want a passing way to access that method and pass my value. – Chatar Veer Suthar Feb 10 '11 at 12:43
` [[[UIApplication sharedApplication] delegate] executeQuery:[NSString stringWithFormat:restaurant_name_save, nameToSave]]; ` – Rich Feb 10 '11 at 12:44
@Rich, what is nameToSave, you havn't declared it anywhere? – Chatar Veer Suthar Feb 10 '11 at 12:55
nameToSave is the text the user has entered. restaurant_name_save is the string format for inserting names into abc. If I've misunderstood you and restaurant_name_save is the data to save then you would make restaurant_name_save the second argument to ` stringWithFormat: ` and the first argument would be the string format: ` @"Insert into abc (name) values('%@');" `. – Rich Feb 10 '11 at 13:02
@Rich, I have only one data and that is 'restaurant_name_save', but what is nameToSave that you have given? – Chatar Veer Suthar Feb 10 '11 at 13:05
[[[UIApplication sharedApplication] delegate] executeQuery:[NSString stringWithFormat:restaurant_name_save, nameToSave]]; what is nameToSave and also what is executeQuery, because your method name is executeSelect, then what is executeQuery? – Chatar Veer Suthar Feb 10 '11 at 13:09
What I would do is write this in the appDelegate's header file (outside the ` @interface `) ` extern NSString *const restaurantInsertFormat; `. Then in the .m file (outside the ` @implementation) ` write ` NSString *const restaurantInsertFormat = @"Insert into abc (name) values('%@');"; `. Then, if you ` #import ` the appdelegate's .h file you can pass the constant to ` stringWithFormat: ` as the first argument and your user input as the second argument in the view controller's ` @implementation `. – Rich Feb 10 '11 at 13:17
Terminating app due to uncaught exception 'NSInvalidArgumentException', reason: '*** -[NSPlaceholderString initWithFormat:locale:arguments:]: nil argument' This is error, which you have said coding – Chatar Veer Suthar Feb 10 '11 at 13:25
- (void)executeQuery:(NSString *)query{ sqlite3_stmt *statement; if (sqlite3_prepare_v2([self database], [query UTF8String], -1, &statement, NULL) == SQLITE_OK) { sqlite3_step(statement); }else{ // handle the error. } sqlite3_finalize(statement); } this prepares a sql statement and executes it once. if you repeatedly call ` sqlite3_step ` it will execute the statement multiple times. you can use ` sqlite3_exec ` without a call to ` sqlite3_prepare_v2 ` if you just want to do it once. – Rich Feb 10 '11 at 13:30
[[[UIApplication sharedApplication] delegate] executeQuery:[NSString stringWithFormat:restaurant_name_save]]; – Chatar Veer Suthar Feb 10 '11 at 13:45
@Veer you only passed one argument to ` stringWithFormat: ` – Rich Feb 10 '11 at 13:57

how can I insert value into my sqlite3 database table?

4 Answers4