0

I have an iOS app using FMDB library.

There is a 7 field SQLite DB (1 autoincrement, 6 regular text fields).

I am trying to execute 

 [db executeUpdate:@"INSERT INTO messages  VALUES (:field1,:field2,:field3,:field4,:field5,:field6)" withParameterDictionary:message];

However because there are only 6 fields I get an error. I cannot insert the autoincrement value, since I do not know what it is (I guess I could put a separate query for that...). And I am trying to avoid injection-susceptible syntax

NSString* sql = [NSString stringWithFormat:@"insert into messages (%@) values (%@)", [newCols componentsJoinedByString:@", "], [newVals componentsJoinedByString:@", "]];

Thank you for advice!

selytch
  • 535
  • 2
  • 9
  • 24
  • I suppose you have an ID column that is auto incremented. You don't have to provide anything for this column as it is automatically inserted by the DB. – Christian Rapp Feb 23 '13 at 18:13
  • If I omit :primarykey in the values part, I get error: table messages has 7 columns but 6 values were supplied; If I send a nil object for pk I get error Error: the bind count (6) is not correct for the # of variables in the query (7); If I use @"" for primary key I get "datatype mismatch" since primary key in integer. – selytch Feb 23 '13 at 19:08
  • Did you see my answer and does it help you? – Christian Rapp Feb 24 '13 at 13:48

3 Answers3

3

You didn't specify which column was the autoincrement column so I'll assume it's simply "field1". When you build your dictionary, add this:

[message setObject:[NSNull null] forKey:@"field1"];

then

[db executeUpdate:@"INSERT INTO messages  VALUES (:field1,:field2,:field3,:field4,:field5,:field6)" withParameterDictionary:message];

Be sure to change "field1" to whatever field you are using for autoincrement.

Yogesh Suthar
  • 30,424
  • 18
  • 72
  • 100
user3246173
  • 488
  • 6
  • 18
1

I just had a look in my code where I also used FMDB. First of all you also have to provide the columns your insert statement. This is what I do:

NSString *sqlSL = @"INSERT INTO SmartLibrary (TITEL, SUBTITEL, PICTURE, BLURB, MARK) VALUES (?, ? ,? ,? ,?)";
[db beginTransaction];
[db executeUpdate:sqlSL, book.title, book.subtitle, book.picture, book.summary, [NSNumber numberWithInteger:book.mark]];
[db commit];

I have a structure Book in this case. So what you need to do is provide the columns affected by this insert in parentheses. Also you can see how to make a prepared statement and you should use transactions. Hopefully this helps!

Christian Rapp
  • 1,853
  • 24
  • 37
0

I got this error because of a typo I oversaw looking at the code a hundred times: one of the dictionary keys was wrong. In this case, FMDB prints the following error to the debug console:

Could not find index for <wrong_dict_key_name>

The next error FMDB prints to the console is a little misleading:

Error: the bind count (6) is not correct for the # of variables in the query (7) (INSERT INTO ...

The root cause is of course the wrong dictionary key.

Markus
  • 2,412
  • 29
  • 28