Xcode compare numbers from FMDB

Question

I want to SELECT a record FROM database WHERE the 'balance' is bigger than the number you type.

The regular sql is : SELECT cardNum FROM cardInfo WHERE balance > money ORDER BY withdrawals DESC, discount DESC.

But I have no idea how to do it in objective-C (to compare an integer with NSString in the 'WHERE' clause). The problem is that no NSInteger can be saved to FMDB tables.

here is part of my code:

NSString * moneyText = moneyField.text;
double money = [moneyText doubleValue];

NSString *selectedRecord = [db stringForQuery:@"SELECT cardInfo.cardNum FROM cardInfo WHERE (cardInfo.currentBalance ... I don't know what here is)ORDER BY withdrawals DESC];  (I want to get the first record only).

Is anybody who can help me?~ thank you!~

score 1 · Answer 1 · edited Dec 16 '13 at 13:55

1

NSArray *dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docDir = [dirPaths objectAtIndex:0];
NSString *databasePath = [docDir stringByAppendingPathComponent:@"samcontact.sqlite"];
NSString *name;
NSString *phone;
FMDatabase *database = [FMDatabase databaseWithPath:databasePath];

[database open];
FMResultSet *results = [database executeQuery:@"select * from phone where Name = ?",txt_Name.text];
while ([results next])
{
    name = [results stringForColumn:@"Name"];
    phone = [results stringForColumn:@"phoneno"];
}

txt_Name.text = name;
txt_Contact.text = phone;
[database close];

edited Dec 16 '13 at 13:55

bummi

27,123
14
62
101

answered Dec 16 '13 at 13:48

To improve your answer, consider writing a short explanation to accompany your code. – adamdunson Dec 16 '13 at 14:09
Am Using FMDB Database – Dec 17 '13 at 12:12

score 0 · Answer 2 · answered Mar 23 '13 at 10:12

0

You're close:

NSString *selectedRecord = [db stringForQuery: @"SELECT cardNum FROM cardInfo WHERE balance > ? ORDER BY withdrawals DESC, discount DESC", @(money)];

This basically binds the NSNumber which is created by the @(money) in the statement, which causes it to be replaced when SQL is called. stringForQuery: always returns the first value in the first recordset (or nil if there are no matches).

With older versions of the compiler, use [NSNumber numberWithDouble: money] instead of @(money).

answered Mar 23 '13 at 10:12

gaige

17,263
6
57
68

the problem is: balance in the database is a string, not a number. When I add the content of balance into database, number does not work. – user1132443 Mar 24 '13 at 13:03
If the number in the database is a string, then you're going to get a string comparison, not a number comparison, so your original comment on "the regular sql" would seem to be in error. To compare to a number encoded as a string, you're going to have to have the database perform the conversion, so that you can get a numeric comparison, so use "convert(decimal(28,8), isnull(balance,'0')" instead of "balance" above. – gaige Mar 24 '13 at 17:41
thank you. Do you have any idea how to save the content as a number in the database? I am using FMDB frame. When i try to put the number into it. it shows nothing in the table. – user1132443 Mar 26 '13 at 01:48

Asif Mujteba · Answer 3 · 2013-03-24T13:15:11.817

0

Just Pass it as NSNumber, like this:

NSString * moneyText = moneyField.text;
double money = [moneyText doubleValue];
NSString *selectedRecord = [db stringForQuery:@"SELECT cardNum FROM cardInfo WHERE balance > ? ORDER BY withdrawals DESC, discount DESC", [NSNumber numberWithDouble:money]];

EDIT

If Balance is a string then simply do this!

NSString * moneyText = moneyField.text;
NSString *selectedRecord = [db stringForQuery:@"SELECT cardNum FROM cardInfo WHERE balance > ? ORDER BY withdrawals DESC, discount DESC", moneyText];

edited Mar 24 '13 at 13:15

answered Mar 23 '13 at 10:12

Asif Mujteba

4,596
2
22
38

the problem is, balance in database is a string, not a number. I cannot add the content as a number into the table. – user1132443 Mar 24 '13 at 13:05

Xcode compare numbers from FMDB

3 Answers3