腾讯云

文章/答案/技术大牛

发布

社区首页 >问答首页 >插入后Sqlite select返回1行

问插入后Sqlite select返回1行
EN

Stack Overflow用户

提问于 2016-01-11 11:36:39

回答 1查看 192关注 0票数 0

我在select from sql之后遇到了一个问题。我向updateRow发送了一个包含5个对象的NSArray。在更新之后(我做了5次addRow)，我在getGroupsByUserId上只得到了其中的一个。

下面是我的代码：

+(BOOL)createUserTable:(sqlite3*)database{
    char* errormsg;

NSString* sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (%@ TEXT PRIMARY KEY, %@ TEXT)",USER_GROUPS_TABLE,USER_ID,GROUP_ID];
int res = sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errormsg);
if(res != SQLITE_OK){
    NSLog(@"ERROR: failed creating USERS-GROUPS table");
    return NO;
}
return YES;
}

+(void)updateRow:(sqlite3*)database rowToUpdate:(NSArray*)rowToUpdate{
    for (UserGroup* ug in rowToUpdate) {
        [UsersGroupsRelationShipSql addRow:database userId:ug.userId groupId:ug.groupId ];
        NSLog(@"update Row %@ %@", ug.userId, ug.groupId);
    }
}

+(void)addRow:(sqlite3*)database userId:(NSString*)userId groupId:(NSString*)groupId{
    NSLog(@"addRow: '%@', '%@'", userId, groupId);
    sqlite3_stmt *statment;
    NSString* query = [NSString stringWithFormat:@"INSERT OR REPLACE INTO %@ (%@,%@) values (?,?);",USER_GROUPS_TABLE,USER_ID,GROUP_ID];

    if (sqlite3_prepare_v2(database,[query UTF8String],-1,&statment,nil) == SQLITE_OK){
        sqlite3_bind_text(statment, 1, [userId UTF8String],-1,NULL);
        sqlite3_bind_text(statment, 2, [groupId UTF8String],-1,NULL);
        if(sqlite3_step(statment) == SQLITE_DONE){
            sqlite3_finalize(statment);
            return;
        }
    }
    sqlite3_finalize(statment);
    NSLog(@"ERROR: addRowToTable failed %s",sqlite3_errmsg(database));
}



+(NSMutableArray*)getGroupsByUserId:(sqlite3*)database userId:(NSString*)userId{
    NSLog(@"getGroupsByUserId: '%@'", userId);
    NSMutableArray* data = [[NSMutableArray alloc] init];
    sqlite3_stmt *statment;
    NSString* query = [NSString stringWithFormat:@"SELECT * from USERS_GROUPS WHERE %@ = %@;", USER_ID,userId];

    if (sqlite3_prepare_v2(database,[query UTF8String],-1,&statment,nil) == SQLITE_OK){
        while(sqlite3_step(statment) == SQLITE_ROW){
            NSString* usId = [NSString stringWithFormat:@"%s",sqlite3_column_text(statment,0)];
            NSString* grId = [NSString stringWithFormat:@"%s",sqlite3_column_text(statment,1)];
            NSLog(@" row: '%@', '%@'", usId, grId);
            UserGroup* ug = [[UserGroup alloc] init:usId groupId:grId];
            [data addObject:ug];
        }
    }
    else{
        NSLog(@"ERROR: getGroupsByUserId failed %s",sqlite3_errmsg(database));
        sqlite3_finalize(statment);
        return nil;
    }
    sqlite3_finalize(statment);
    return data;
}

调试结果：

2016-01-13 11:30:11.901 MaverickApp[7196:142637] userId Logged = 1
2016-01-13 11:30:11.919 MaverickApp[7196:142637] Groups tab was loaded
2016-01-13 11:30:11.920 MaverickApp[7196:142675] getGroupsByUserId: userId = 1 from Sql
2016-01-13 11:30:11.922 MaverickApp[7196:142675]  get row: userId = 1, groupId = 2  from Sql
2016-01-13 11:30:11.927 MaverickApp[7196:142675] Returned groupsByUser from parse from date 2016-01-13 09:24:11 +0000
2016-01-13 11:30:11.931 MaverickApp[7196:142637] Groups tab was loaded
2016-01-13 11:30:11.932 MaverickApp[7196:142684] getGroupsByUserId: userId = 1 from Sql
2016-01-13 11:30:11.935 MaverickApp[7196:142684]  get row: userId = 1, groupId = 2  from Sql
2016-01-13 11:30:11.937 MaverickApp[7196:142684] Returned groupsByUser from parse from date 2016-01-13 09:24:11 +0000
2016-01-13 11:30:12.121 MaverickApp[7196:142675] Returned row: groupId 1 userId 6
2016-01-13 11:30:12.121 MaverickApp[7196:142675] Returned 1 groups for userId = 1 from parse
2016-01-13 11:30:12.122 MaverickApp[7196:142675] data 1
2016-01-13 11:30:12.122 MaverickApp[7196:142675] update Row: userId = 1 groupId = 6 in Sql
2016-01-13 11:30:12.123 MaverickApp[7196:142675] add Row: userId = 1 groupId = 6 in Sql
2016-01-13 11:30:12.127 MaverickApp[7196:142675] addRow done
2016-01-13 11:30:12.128 MaverickApp[7196:142675] getGroupsByUserId: userId = 1 from Sql
2016-01-13 11:30:12.129 MaverickApp[7196:142675]  get row: userId = 1, groupId = 6  from Sql
2016-01-13 11:30:12.129 MaverickApp[7196:142675] Group id number 6 for userId 1
2016-01-13 11:30:12.142 MaverickApp[7196:142675] getGroup: groupId = 6 from Sql
2016-01-13 11:30:12.143 MaverickApp[7196:142675] getGroup: groupId = 6  from Sql
2016-01-13 11:30:12.145 MaverickApp[7196:142675] Returned groups from parse from date 2016-01-13 09:24:14 +0000
2016-01-13 11:30:12.504 MaverickApp[7196:142684] Returned row: groupId 1 userId 6
2016-01-13 11:30:12.505 MaverickApp[7196:142684] Returned 1 groups for userId = 1 from parse
2016-01-13 11:30:12.505 MaverickApp[7196:142684] data 1
2016-01-13 11:30:12.506 MaverickApp[7196:142684] update Row: userId = 1 groupId = 6 in Sql
2016-01-13 11:30:12.506 MaverickApp[7196:142684] add Row: userId = 1 groupId = 6 in Sql
2016-01-13 11:30:12.509 MaverickApp[7196:142684] addRow done
2016-01-13 11:30:12.511 MaverickApp[7196:142684] getGroupsByUserId: userId = 1 from Sql
2016-01-13 11:30:12.511 MaverickApp[7196:142684]  get row: userId = 1, groupId = 6  from Sql
2016-01-13 11:30:12.512 MaverickApp[7196:142684] Group id number 6 for userId 1
2016-01-13 11:30:12.512 MaverickApp[7196:142684] getGroup: groupId = 6 from Sql
2016-01-13 11:30:12.512 MaverickApp[7196:142684] getGroup: groupId = 6  from Sql
2016-01-13 11:30:12.513 MaverickApp[7196:142684] Returned groups from parse from date 2016-01-13 09:24:14 +0000
2016-01-13 11:30:12.824 MaverickApp[7196:142675] Returned 3 groups from parse
2016-01-13 11:30:12.825 MaverickApp[7196:142675] addGroup: groupId = 2 in Sql
2016-01-13 11:30:12.828 MaverickApp[7196:142675] addGroup done
2016-01-13 11:30:12.828 MaverickApp[7196:142675] addGroup: groupId = 6 in Sql
2016-01-13 11:30:12.830 MaverickApp[7196:142675] addGroup done
2016-01-13 11:30:12.830 MaverickApp[7196:142675] addGroup: groupId = 8 in Sql
2016-01-13 11:30:12.832 MaverickApp[7196:142675] addGroup done
2016-01-13 11:30:12.833 MaverickApp[7196:142675] getGroup: groupId = 6 from Sql
2016-01-13 11:30:12.834 MaverickApp[7196:142675] getGroup: groupId = 6  from Sql
2016-01-13 11:30:13.138 MaverickApp[7196:142684] Returned 3 groups from parse
2016-01-13 11:30:13.138 MaverickApp[7196:142684] addGroup: groupId = 2 in Sql
2016-01-13 11:30:13.142 MaverickApp[7196:142684] addGroup done
2016-01-13 11:30:13.142 MaverickApp[7196:142684] addGroup: groupId = 6 in Sql
2016-01-13 11:30:13.144 MaverickApp[7196:142684] addGroup done
2016-01-13 11:30:13.144 MaverickApp[7196:142684] addGroup: groupId = 8 in Sql
2016-01-13 11:30:13.145 MaverickApp[7196:142684] addGroup done
2016-01-13 11:30:13.146 MaverickApp[7196:142684] getGroup: groupId = 6 from Sql
2016-01-13 11:30:13.147 MaverickApp[7196:142684] getGroup: groupId = 6  from Sql

sqlite

objective-c

回答 1

Stack Overflow用户

发布于 2016-01-11 13:20:04

有几个问题：

您正在使用INSERT OR REPLACE。如果您的表的主键只是userid字段，那么您将看到所描述的行为。仔细检查此表的主键。

确保数据库不在捆绑包中，而是位于某个可更新的位置，如Documents文件夹等。

在你的原始getGroupsByUserId中，我根本看不出这是如何工作的，因为你没有使用?占位符，也没有在字符串文字两边使用引号。我本以为这会产生一个错误。

我建议对userId使用?占位符，然后在WHERE子句中对该值使用sqlite3_bind_text。但您现在更改了答案中的代码(没有告诉我们行为是否更改)。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/34713837

复制

相似问题

问插入后Sqlite select返回1行
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问插入后Sqlite select返回1行EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问插入后Sqlite select返回1行
EN