Greetings:
I recently upgraded to a MacBook Pro with an i7 processor. The current version
of sqlite on this machine is 3.6.12. For the last 4+ years, I have been using
a MacBook Intel Core Duo.
I am not a trained professional programmer, I do it for fun and to have some
personal applications for things that I do.
I do all my programming in Cocoa & Objective-C.
One thing that I do is maintain a database for specific purposes for my church
congregation. The data is emailed to me in a CSV file, and my app parses the
desired fields and then updates the sqlite database. I have been using the
application (I wrote the entire thing) for almost 5 years with no problems, but
am now seeing something that I do not know how to approach. I will try to be
clear yet brief.
When I read the CSV file as an NSString object, I set the encoding to UTF8.
Each member of the congregation is on one line of the CSV, and each person's
information is formed into an NSDIctionary. An NSArray of NSDictionaries of
new members is sent to the class that handles the communication with the sqlite
database. At that point, this is the code that writes each new member to the
database:
sql = [@"INSERT into members (cong_id, Full_Name, birth, moved) VALUES
(?,?,?,?);" UTF8String];
sqlite3_prepare(wardDB, sql, strlen(sql), &stmt, NULL);
NSEnumerator *enumerator = [ward objectEnumerator];
id element;
while(element = [enumerator nextObject])
{
NSLog(@"Processing Person %@ with Birth:%@ and ID: %d",
[element objectForKey:@"FullName"],[element objectForKey:@"Birth"], [[element
objectForKey:@"Congregantid"] intValue]);
fn = [[element objectForKey:@"FullName"] UTF8String];
//full_name
bi = [[element objectForKey:@"Birth"] UTF8String]; //birth
NSLog(@"Trying to bind");
sqlite3_bind_int (stmt, 1, [[element
objectForKey:@"Congregantid"] intValue]);
sqlite3_bind_text(stmt, 2, fn ,strlen(fn), SQLITE_TRANSIENT);
sqlite3_bind_text(stmt, 3, bi, strlen(bi), SQLITE_TRANSIENT);
sqlite3_bind_int (stmt, 4, 0);
sqlite3_step(stmt);
sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
The pertinent schema for the members table is:
(db_id INTEGER PRIMARY KEY AUTOINCREMENT, cong_id INT, Full_name TEXT, birth
DATE, ......)
The problem lies with two, and consistently two persons. Every time I process
the update, the data I then try to SELECT from the database is garbled:
sqlite> select * from members where db_id =1127;
1127|2202|?????? ||||||||?????D??||||||0|
sqlite> select * from members where db_id =1177;
1177|2015|????h?? ||||||||?f??????||||||0|
I have installed NSLog statements to try to hunt down the problem (those NSLogs
are in the method shown above), and every time I execute the code, the NSLog
seems to show the correct information, compared to the CSV:
2011-06-19 22:50:24.534 iClerk[4292:a0f] Processing Person Moody, Isaiah
Terrell with Birth:1991-09-28 and ID: 2202
2011-06-19 22:50:24.534 iClerk[4292:a0f] Trying to bind
2011-06-19 22:50:24.591 iClerk[4292:a0f] Processing Person White, Lonnie Jay
with Birth:1973-12-12 and ID: 2015
2011-06-19 22:50:24.591 iClerk[4292:a0f] Trying to bind
This particular update contains an array of 105 NSDictionaries to be added, and
every time, it is only these two that create problems. Everyone else's data
writes perfectly to the table, and I can SELECT them all I want, no hiccups.
I should also mention that there were originally about 5 or 6 other persons
creating difficulty, but when I narrowed down the desired data to just Full
Name and Birthdate, it is only these two. (The original routine collects about
a dozen pieces of information for each person, but my list only needs name and
birthdate, so I tried to just make it work by only dealing with those.)
My main question is how do I ascertain what is actually being written to sqlite
so that I can try to discover where the garbled text is coming from? This is
all black magic to me, I wrote the routine nearly 5 years ago, and I fully
admit that although I spent a lot of time with The Definitive Guide to SQLite
(I still have the book, but can find nothing there to help), my lack of
understanding of C hinders my ability to actually know what I am doing.
I am grateful for any advice or instruction.
Thank you very much,
Craig Smith
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
