[sqlite] Garbled data after binding

[CodeBoy DVM]

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

Re: [sqlite] Garbled data after binding

[Jean-Denis Muys]

On 20 juin 2011, at 08:42, CodeBoy DVM wrote:

   NSEnumerator *enumerator = [ward objectEnumerator];
   id element;
   
   while(element = [enumerator nextObject])
   {

You might want to modernize that code a bit (though this is likely not your 
problem here):

for (NSDictionary *element in ward) {

}

Also try to avoid id as a type for variables you work with as id will 
prevent the compiler from doing any static type checking. Better to use the 
real class name, with perhaps one type cast.


 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|
 

Select * is not very informative, though we can try to guess. Please select 
only the mentioned columns. Also I would inspect your database with a GUI tool 
(eg MesaSQLite) to double check those columns.


 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:

I would put a breakpoint in the code just before your bind statement, to check 
the data you are going to send them is indeed what you expect.

 
 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

Perhaps set the values to bind in their own variables, and NSLog those 
variables right there. I would even log them *after* the call to sqlite3_step, 
just to make sure it's not a weird memory allocation issue.

 
 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.)

But did you find out *why* those 5 or 6 persons had problems? If not, by 
discarding those 5 or 6 persons, you also discarded information that may have 
lead to the reason for the problem.

I am sorry I don't have a better suggestion, I'm pretty much a SQLite newbie 
too.

Jean-Denis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Garbled data after binding

[Simon Slavin]

On 20 Jun 2011, at 9:23am, Jean-Denis Muys wrote:

 Select * is not very informative, though we can try to guess. Please select 
 only the mentioned columns.

Check to see they don't have any unexpected 'invisible' characters in, by doing 
something like

SELECT FullName,LENGTH(FullName) FROM MEMBERS where db_id =1127;

and see if the lengths are what you expect.  Also check to see that the 
database doesn't have some lower-level corruption using PRAGMA integrity_check

http://www.sqlite.org/pragma.html#pragma_integrity_check

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users