ios - 从 sqlite3 表中提取数据
<p><p>当用户尝试在同一日期(日期是主键)输入两个或多个条目时，我试图从特定行中提取数据。 </p>
<p>在我的 .h 文件中</p>
<pre><code>@interface PunchClock : UIViewController{
sqlite3 *db;
}
-(NSString *) filePath;
-(void)openDB;
-(void) createTable: (NSString *) tableName
withField1:(NSString *) field1
withField2:(NSString *) field2
withField3:(NSString *) field3;
</code></pre>
<p>在我的 .m 文件中(不确定是否需要以下任何代码来破译我的问题，但无论如何都会发布它)</p>
<pre><code>-(void) createTable: (NSString *) tableName
withField1:(NSString *) field1
withField2:(NSString *) field2
withField3:(NSString *) field3;
{
char *err;
NSString *sql=;
if(sqlite3_exec(db, , NULL, NULL, &err) != SQLITE_OK){
sqlite3_close(db);
NSAssert(0, @"Could not create table");
}else{
NSLog(@"table created");
}
}
-(NSString *) filePath{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = ;
NSString *path = ;
return path;
}
-(void)openDB{
if(sqlite3_open([ UTF8String], &db) != SQLITE_OK){
sqlite3_close(db);
NSAssert(0, @"Database failed to open");
}else{
NSLog(@"database opened");
}
}
</code></pre>
<p>现在这是我的问题所在。我可以成功插入数据，但是当用户在同一日期输入数据(字段 1 是 date ，我的主键)我想拉出该数据更新它并更新表。我停留在我从行中提取数据的部分(检查评论以进行澄清):</p>
<pre><code>- (IBAction)save:(id)sender {
{
NSString *HoursWorked = ;
NSString *TotalEarned = ;
NSString *sql = ;
char *err;
if(sqlite3_exec(db, , NULL, NULL, &err) != SQLITE_OK){
sqlite3_close(db);
// NSAssert(0,@"Could not update table");
//primary key already exists
;
NSString *currentHoursWorked = HoursWorked;
NSString *currentTotalEarned = TotalEarned;
NSString *previousHoursWorked;
NSString *previousTotalEarned;
NSString *sql = ;
sqlite3_stmt *statement;
// the if statement below is not being executed for some reason? Can someone see why?
if(sqlite3_prepare_v2(db, , -1, &statement, nil)==SQLITE_OK){
char *field2 = (char *) sqlite3_column_text(statement, 1);
NSString *field2Str = [initWithUTF8String:field2];
previousHoursWorked = field2Str;
char *field3 = (char *) sqlite3_column_text(statement, 2);
NSString *field3Str = [initWithUTF8String:field3];
previousTotalEarned = field3Str;
}
sqlite3_finalize(statement);
sqlite3_close(db);
NSLog(@"%@", previousHoursWorked);
NSLog(@"%@", previousTotalEarned);
}else{
NSLog(@"table updated");
}
}
}
</code></pre></p>
<br><hr><h1><strong>Best Answer-推荐答案</ strong></h1><br>
<p><p>一些想法:</p>
<ol>
<li><p>如果您的 <code>sqlite3_prepare_v2</code> 失败，您没有记录 <code>sqlite3_errmsg</code>，我原以为会，因为您没有指定要从哪些字段返回你的 <code>SELECT</code> 语句。</p>
<p>当你遇到 SQLite 错误时，你应该确保你正在记录 <code>sqlite3_errmsg</code>。</p>
</li>
<li><p>您也永远不会调用 <code>sqlite3_step</code> 来实际执行 <code>SELECT</code> 语句。</p>
</li>
<li><p>最后，<code>sqlite3_column_text</code>返回的是从0开始的索引，而不是从1开始的索引，所以第一列是<code>0</code>，第二列是<code>1 </code>。如<a href="http://www.sqlite.org/c3ref/column_blob.html" rel="noreferrer noopener nofollow">the docs</a>比如说，“结果集最左边的列的索引为 0。”</p>
</li>
</ol>
<p>所以，我原以为你会想要这样的东西:</p>
<pre><code>// replace column_a and column_b with your column names
NSString *sql = ;
sqlite3_stmt *statement;
if(sqlite3_prepare_v2(db, , -1, &statement, nil) == SQLITE_OK){
int rc;
if ((rc = sqlite3_step(statement)) == SQLITE_ROW) {
char *field2 = (char *) sqlite3_column_text(statement, 0);
NSString *field2Str = [initWithUTF8String:field2];
previousHoursWorked = field2Str;
char *field3 = (char *) sqlite3_column_text(statement, 1);
NSString *field3Str = [initWithUTF8String:field3];
previousTotalEarned = field3Str;
} else if (rc != SQLITE_DONE) {
NSLog("%s: sqlite3_step failed: %s", __FUNCTION__, sqlite3_errmsg(db));
}
} else {
NSLog("%s: sqlite3_prepare_v2 failed: %s", __FUNCTION__, sqlite3_errmsg(db));
}
</code></pre>
<hr/>
<p>顺便说一句，虽然您可以先执行此 <code>SELECT</code> 以查看是否有匹配项，如果找到匹配项执行 <code>UPDATE</code>，然后执行 <code>INSERT </code> 如果没有，使用 SQLite <code>INSERT OR REPLACE</code> 语法可能会更容易。见 <code>INSERT</code> <a href="http://www.sqlite.org/lang_insert.html" rel="noreferrer noopener nofollow">documentation</a> .</p></p>
<p style="font-size: 20px;">关于ios - 从 sqlite3 表中提取数据，我们在Stack Overflow上找到一个类似的问题：
<a href="https://stackoverflow.com/questions/19827046/" rel="noreferrer noopener nofollow" style="color: red;">
https://stackoverflow.com/questions/19827046/
</a>
</p>
页:
[1]