Database Connectivity Toolkit : How do I store RTF( Rich text files) into a database?

[OpenAI]

I am new to database concepts. I am developing a simple LabVIEW application that stores the user data inside the MySQL database. I have an activeX container that acts as a Rich Text Box. The RTBox allows user to paste contents into it. I understand that the DB should have a column with the datatype 'varbinary(MAX)' to store the RTF file. But how can I send the contents from the RichTextBox to the database directly?

[aputman]

I don't think varbinary is necessary.  You should be able to store it as a text string.

[OpenAI]

But the text string has a character limit and it might truncate the string after the limit. wouldn't it?

[aputman · Accepted solution]

I'm not sure what text string you are talking about that has a limit. MSSQL has a varchar(MAX) that used to be limited to 2GB in older versions, higher values in newer versions.  There is also a TEXT and a BLOB column type that overcomes row size limitations.  How big a' file are ya lookin' at?  (For some reason, I just thought of Roy D. Mercer and had to write it that way.)  

[OpenAI]

The RTF text from the textbox has a lot of rtf characters and it doesn't get stored as text in the SQL DB

Here's the rtf text:

{\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fprq2\fcharset0 Calibri;}{\f1\fnil\fcharset0 Microsoft Sans Serif;}}
{\colortbl ;\red128\green128\blue128;}
\viewkind4\uc1\trowd\trgaph108\trleft-108\trbrdrt\brdrs\brdrw10 \trbrdrl\brdrs\brdrw10 \trbrdrb\brdrs\brdrw10 \trbrdrr\brdrs\brdrw10 \clbrdrt\brdrw15\brdrs\clbrdrl\brdrw15\brdrs\clbrdrb\brdrw15\brdrs\clbrdrr\brdrw15\brdrs \cellx5310\clbrdrt\brdrw15\brdrs\clbrdrl\brdrw15\brdrs\clbrdrb\brdrw15\brdrs\clbrdrr\brdrw15\brdrs \cellx10890\pard\intbl\ltrpar\sl276\slmult1\b\f0\fs22 Test Requestor\b0\cell Luke Skywalker\cell\row\b\trowd\trgaph108\trleft-108\trbrdrt\brdrs\brdrw10 \trbrdrl\brdrs\brdrw10 \trbrdrb\brdrs\brdrw10 \trbrdrr\brdrs\brdrw10 \clbrdrt\brdrw15\brdrs\clbrdrl\brdrw15\brdrs\clbrdrb\brdrw15\brdrs\clbrdrr\brdrw15\brdrs \cellx5310\clbrdrt\brdrw15\brdrs\clbrdrl\brdrw15\brdrs\clbrdrb\brdrw15\brdrs\clbrdrr\brdrw15\brdrs \cellx10890\intbl Test Owner\b0  \cf1 (if different than Requestor)\cf0\cell\cell\row
\b\trowd\trgaph108\trleft-108\trbrdrt\brdrs\brdrw10 \trbrdrl\brdrs\brdrw10 \trbrdrb\brdrs\brdrw10 \trbrdrr\brdrs\brdrw10 \clbrdrt\brdrw15\brdrs\clbrdrl\brdrw15\brdrs\clbrdrb\brdrw15\brdrs\clbrdrr\brdrw15\brdrs \cellx5310\clbrdrt\brdrw15\brdrs\clbrdrl\brdrw15\brdrs\clbrdrb\brdrw15\brdrs\clbrdrr\brdrw15\brdrs \cellx10890\intbl Department\b0\cell Process Development\cell\row
\b\trowd\trgaph108\trleft-108\trbrdrt\brdrs\brdrw10 \trbrdrl\brdrs\brdrw10 \trbrdrb\brdrs\brdrw10 \trbrdrr\brdrs\brdrw10 \clbrdrt\brdrw15\brdrs\clbrdrl\brdrw15\brdrs\clbrdrb\brdrw15\brdrs\clbrdrr\brdrw15\brdrs \cellx5310\clbrdrt\brdrw15\brdrs\clbrdrl\brdrw15\brdrs\clbrdrb\brdrw15\brdrs\clbrdrr\brdrw15\brdrs \cellx10890\intbl Test Request Submission Date\b0\cell May 18, 2017\cell\row
\b\trowd\trgaph108\trleft-108\trbrdrt\brdrs\brdrw10 \trbrdrl\brdrs\brdrw10 \trbrdrb\brdrs\brdrw10 \trbrdrr\brdrs\brdrw10 \clbrdrt\brdrw15\brdrs\clbrdrl\brdrw15\brdrs\clbrdrb\brdrw15\brdrs\clbrdrr\brdrw15\brdrs \cellx5310\clbrdrt\brdrw15\brdrs\clbrdrl\brdrw15\brdrs\clbrdrb\brdrw15\brdrs\clbrdrr\brdrw15\brdrs \cellx10890\intbl Preferred Start Date\b0\cell May 19, 2017\cell\row
\pard\ltrpar\f1\fs17\par
}

[aputman]

You may have to escape all of those backslashes before writing it to the dB. There is a Hidden Gem for escaping characters.

[OpenAI]

But without those backslash characters, I cannot recreate the RTF file back again right? The data is a table containing the rows and columns. So when I retrieve it back from the DB, I need to be able to produce the same data in the tabular format.

[aputman]

So when you query the data out, unescape the backslashes.  What data type are you using in the database?

[aputman]

Just to be clear, escaping the \ character doesn't take it out. It replaces it with a \\ so that, for instance, a \n is not replaced with a new line. 

Please post your code and I can take a look at it tomorrow.

[OpenAI]

---

@aputman wrote:

So when you query the data out, unescape the backslashes.  What data type are you using in the database?

---

I use varchar(MAX) datatype. I used the Hidden Gems library to escape and unescape the backslashes and it works perfect. Thanks. 🙂