Problem with Saving XML to MSSQL Server Database

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:

A few more tidbits of info:

  • If I enter the line of formatted text by copying and pasting it from Wordpad (which is what I’ve been trying to do all along), it doesn’t work.
  • If I “manually” enter the exact same string of text where I create the bullet at the beginning of the sentence (using ALT 0149) to generate the character (in place of using the one Wordpad created), everything DOES work right.

Is it a matter of not properly ESCAPING the characters within the XML?

Thanks for your response, Dave. If you mean “escaping characters” in the same sense as replacing literals (single and double quotes) in SQL strings, it should not be necessary since I’m using prepared statements (and don’t do that anywhere else because of that usage). Also, one of the reasons I went with the MBS SQL plugin was to get away from the Xojo native SQL plugin problems with prepared statements, so that I didn’t have to escape those characters anymore.

Now, if you’re referring to “escaping” in another sense, then I’m not aware of what that would be … so, if I’m missing your point, could you help me by elaborating, please.

Oh, I also tired wrapping the data string in EncodeBase64 and DecodeBase64 on retrieval … no difference … still won’t save the Wordpad generated line of formatted text as copied to the clipboard and pasted in the control

Maybe you send me example so I can reproduce?

Wow, “tired” more so than I thought! ^^

Just about lunch time here, so I’ll use that time to strip out the relevant code and create a sample app to send you … as always, your help is much appreciated, Christian!

… Don