[Mono-list] SQLLite problem

[andy at brdstudio.net]

My guess, there is an escape character or SQLite syntax issues in the 
previous field being supplied. The Siblings value may be the issue, but 
that is just a quick guess.

----------------------------------------
 From: "Paul Johnson" <paul at all-the-johnsons.co.uk>
Sent: Thursday, March 14, 2013 10:49 AM
To: mono-list at lists.ximian.com
Subject: [Mono-list] SQLLite problem

Hi,

I seem to be having a problem with inserting into my SQLLite database. 
It is set up like this

I have the SQLLite.cs file referenced in a helper class that provides 
the interface to the app (just to make my life simple really). This 
helper class creates the table, deletes the table and does everything 
else.

I'm having an issue with two methods (both oddly at the same point). The 
method looks like this

public void addBoyRecords(List<BoyRecords> br)
{
if (br.Count == 0)
return;
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new 
SQLiteConnection(this.DBPath))
{
sqlCon.Execute(Constants.DBClauseSyncOff);
for (int m = 0; m < br.Count; ++m)
try
{
if (sqlCon.Execute("UPDATE BoyRecords SET " +
"ReplicationGuid=?, LkwdID=?, 
MoneyID=?, AffiliationID=?, Affiliation1ID=?, DescriptionID=?, 
Description1ID=?, TypeID=?, " +
"Type1ID=?, LastName=?, FirstName=?, 
HebrewName=?, DateOfBirth=?, Calculated=?, Height=?, Address1=?, 
Address2=?, " +
"Address3=?, City=?, State=?, 
Country=?, Zip=?, Phone1=?, Phone2=?, FatherCell=?, MotherCell=?, 
FatherEmailAddress=?, " +
"MotherEmailAddress=?, OwnAddress1=?, 
OwnAddress2=?, OwnCityID=?, OwnState=?, OwnCountry=?, OwnHomePhone1=?, " +
"OwnCellPhone=?, OwnEmailAddress=?, 
DateCameToYeshiva=?, HighSchool=?, Yeshiva1=?, Yeshiva2=?, Yeshiva3=?, 
Working=?",
br [m].ReplicationGuid, br 
[m].LkwdID, br [m].MoneyID, br [m].AffiliationID, br [m].Affiliation1ID, 
br [m].DescriptionID,
br [m].Description1ID, br 
[m].TypeID, br [m].Type1ID, br [m].LastName, br [m].FirstName, br 
[m].HebrewName, br [m].DateOfBirth,
br [m].Calculated, br 
[m].Height, br [m].Address1, br [m].Address2, br [m].Address3, br 
[m].City, br [m].State, br [m].Country,
br [m].Zip, br [m].Phone1, 
br [m].Phone2, br [m].FatherCell, br [m].MotherCell, br 
[m].FatherEmailAddress,
br [m].MotherEmailAddress, 
br [m].OwnAddress1, br [m].OwnAddress2, br [m].OwnCityID, br 
[m].OwnState, br [m].OwnCountry,
br [m].OwnHomePhone1, br 
[m].OwnCellPhone, br [m].OwnEmailAddress, br [m].DateCameToYeshiva, br 
[m].HighSchool,
br [m].Yeshiva1, br 
[m].Yeshiva2, br [m].Yeshiva3, br [m].Working) == 0)
sqlCon.Insert(br [m], typeof(BoyRecords));

if (sqlCon.Execute("UPDATE BoyRecords SET " +
"Job=?, College=?, ParentsLastName=?, 
ParentsMaritalStatus=?, FathersName=?, FathersHebrewName=?, 
FathersTitle=?, " +
"FathersOrigin=?, FathersJob=?, 
FathersSchull=?, MothersName=?, MothersHebrewName=?, MothersTitle=?, 
MothersMaiden=?, " +
"MothersOrigin=?, MothersJob=?, 
OrderInFamily=?, FamilySize=?, Mechutanim=?, Siblings=?, References=?, 
Notes=?, " +
"RoomMate1=?, RoomMate2=?, RoomMate3=?, 
RoomMate4=?, NamePartner1=?, NamePartner2=?, RoshChaburah1=?, 
RoshChaburah2=?, " +
"Seat1=?, Seat2=?, DateAdded=?, 
Engaged=?, UpdateNo=? WHERE ReplicationGuid=?", br [m].Job, br 
[m].College, br [m].ParentsLastName,
br [m].ParentsMaritalStatus, 
br [m].FathersName, br [m].FathersHebrewName, br [m].FathersTitle, br 
[m].FathersOrigin,
br [m].FathersJob, br 
[m].FathersSchull, br [m].MothersName, br [m].MothersHebrewName, br 
[m].MothersTitle, br [m].MothersMaiden,
br [m].MothersOrigin, br 
[m].MothersJob, br [m].OrderInFamily, br [m].FamilySize, br 
[m].Mechutanim, br [m].Siblings,
br [m].References, br 
[m].Notes, br [m].RoomMate1, br [m].RoomMate2, br [m].RoomMate3, br 
[m].RoomMate4, br [m].NamePartner1,
br [m].NamePartner2, br 
[m].RoshChaburah1, br [m].RoshChaburah2, br [m].Seat1, br [m].Seat2, br 
[m].DateAdded,
br [m].Engaged, br 
[m].UpdateNo, br [m].ReplicationGuid) == 0)
sqlCon.Insert(br [m], typeof(BoyRecords));
} catch (Exception ex)
{
#if DEBUG
Console.WriteLine("Error in addBoyRecords : 
{0}-{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}
}
}
}

The list is provided from an XML file (I managed to sort that one from 
yesterday by using reader.Depth - quick and easy!).

No matter what I do, I always get a syntax error near "References". I've 
checked all of the inputs and they are the correct type and non-null. My 
thinking was that SQLLite doesn't like more than so many parameters 
going in at once (which is why I've split the input). That made no 
difference. Next was that the XML string being passed in was causing an 
issue. In the XML for <References> is R', so I've replaced that 
with ' using reader.Value.Replace("'", "'"); - still I get the same 
error.

The XML file is using UTF-8. Do I need to do anything within my code to 
ensure the strings being fed into the database are also UTF-8?

Any clues here would be useful!

Paul
-- 
"Space," it says, "is big. Really big. You just won't believe how 
vastly, hugely, mindbogglingly big it is. I mean, you may think it's a 
long way down the road to the chemist's, but that's just peanuts to 
space, listen..."
Hitch Hikers Guide to the Galaxy, a truly remarkable book!

_______________________________________________
Mono-list maillist  -  Mono-list at lists.ximian.com
http://lists.ximian.com/mailman/listinfo/mono-list

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ximian.com/pipermail/mono-list/attachments/20130314/367dbeba/attachment.html>