I have a program that makes profuse usage of SQL prepared statements for selecting, updating, deleting, etc. to save/restore text to various controls without any problems. Now, I have the need to save something besides regular old text strings … namely XML. I’m using the BKS FTC control to allow the user the enter formatted text and save it to the database as XML (using the getDoc.getXML to read and set XML methods to save). Also, I’m using the MBS MSSQL plugin instead of the Xojo native SQL plugin.
My problem (which I have a sneaking suspicion has something to do with encodings) is that when I try to save the following, it doesn’t save anything nor throw any db errors (I’m checking both after the prepare statement and the SQLExecute):
Anti-condensate heater data for unlighted rear sill. For lighted rear sill, double the values.
BUT, if I take the bullet away from the start of the line, as in below, it works fine and saves the text itself.
Anti-condensate heater data for unlighted rear sill. For lighted rear sill, double the values.
I have tried different data types for the MSSQL table column where the data is saved … TEXT, NTEXT, NVARCHAR(MAX) and XML … all to no avail. The actual code involved in the save routine is as follows:
[code] dim ss as string
// save the NOTES section
for iloop As Integer = 0 to Ubound(oContainers)
if oContainerIndex(iloop) = "nc" then
ss = cntNotes(oContainers(iloop)).FTNotes.getDoc.getXML
Exit For
end if
next
//MsgBox ss
// get the caseID of the current record
Dim rs As RecordSet = db.SQLSelect(“SELECT CaseID FROM caseModel WHERE (CaseName=’” + caseName + “’) AND (Revision=’” + caseRevision + “’)”)
Dim cIDnum As Integer = rs.Field(“CaseID”).IntegerValue
rs.close
// save the Notes
Dim sqln As String = _
"UPDATE Notes " + _
" SET NotesData = :nts " + _
" WHERE CaseID = :cID; "
dim vn as Variant = db.Prepare(sqln)
If db.Error Then
// open Modal MsgBox window
taMessage = "UPDATE Model General Record Set PREPARE NOTES Data DB Error: " + db.ErrorMessage
wndModalMsgBox.ShowModal
End If
dim pn as SQLPreparedStatementMBS = vn
pn.BindType(“nts”, SQLPreparedStatementMBS.kTypeString)
pn.BindType(“cID”, SQLPreparedStatementMBS.kTypeLong)
MsgBox ss
pn.Bind(“nts”, ss)
pn.Bind(“cID”, cIDnum)
pn.SQLExecute
If db.Error Then
// open Modal MsgBox window
taMessage = "UPDATE Model General Record Set SAVE NOTES Data DB Error: " + db.ErrorMessage
wndModalMsgBox.ShowModal
else
db.Commit // Commit the recordset to the database
End If[/code]
Checking the contents of the string attempting to be saved shows what looks like proper XML data: