Really appreciate your continuing effort to suggest things to try here, Jym. I modified the read/write routines to convert the string holding the RTF text data to HEX as you suggested, but unfortunately get the same result … when the string goes over 2000 characters, it acts as if it saved it, but in reality did not (doesn’t even write over anything that was previously there in that database table cell).
It keeps coming down to a byte count related thing that seemingly is created by executing the MBS Prepared Statements to save the data to the db. Everything works perfectly if the string size is less than 2000, but exhibits the problem once it crosses the 2000 character threshold (and believe me, with all the RTF overhead garbage in there, that doesn’t allow for much “real” text to be saved!!!) … the users of this program would constantly be exceeding the 2000 character “line in the sand”.
This project I’m working on has reached the point of “the quicker I try to go, the ‘behinder’ I get!” ^^ (of course, my customer’s face would be somewhat upside down from that one right now). Experiences like this sequence of issues I’ve had to overcome are what I refer to as the “Layer of Poop Theory” … you dig and dig and dig to make your way through the layer of poop you find yourself in … only to reach another layer of poop.
You wouldn’t believe just how short once you take out the RTF overhead characters … you get about one good size paragraph of text … which is nearly useless for the application I have. The users will not be writing books with this here, but there will be two or three paragraphs at times that need saved (it’s only a “comments” section for extraneous notes).
Thanks for the reference, Jeff … one of the very few I think I didn’t see. However, what they are suggesting there (usage of MAX with VARCHAR or NVARCHAR), I’m already using. And the fact that I can manually stick a very large RTF text string (>5000 characters at least) in the database table using SSMS and then successfully read it back tells me the problem is NOT on the database side, but rather pointing towards the execution of the MBS prepared statements … the data is correct going into those statements, but the save does not occur properly (if the string exceeds 2000 characters … anything under that works just fine). Very perplexing!!!
Thanks again, Jym. Not quite sure I understand what you’re suggesting here. The ‘finished’ SQL statement is in a prepared statement form (see towards the bottom of the code block) with the bindings and all in MBS syntax. How would I do what you suggest? Recreate the prepared statement in standard SQL syntax form and execute it from the Query Engine in SSMS? Or attempt a direct UPDATE from the Query Engine using fixed values instead of bound parameters?
[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)).TANotes.WinRTFDataMBS // get the rtf text from the TextArea
Exit For
end if
next
//MsgBox (str(ss.Len) + EndOfLine + ss) // here’s what I use to verify the data (and number of characters)
// 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
Dim cID As String = Str(cIDnum)
// 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
[code] // build big text
dim lines() as string
for i as integer = 1 to 20000
lines.Append str(i)
next
dim data as string = Join(lines)
dim l as integer = len(data)
MsgBox str(l)+" characters"
dim v as Variant = db.Prepare(“INSERT INTO TestTable (vorname, TextField) VALUES (‘Test’, :test)”)
dim p as SQLPreparedStatementMBS = v
p.Bind(“test”, data)
p.BindType(“test”, p.kTypeString)
p.SQLExecute
MsgBox “OK”[/code]
For my Microsoft SQL Server, the limit is the nvarchar or varchar data type which is limited to 4000 characters (nvarchar) or 8000 characters (varchar).
If I use text or ntext as column type, it works and I can easily save 100000 characters.
PS: It doesn’t matter for the MBS Plugin in this case if text is passed as kTypeString or kTypeCLob.