RTF control, MS SQL Server

[pdi]

I have some troubles with the new RTF control. I hope you could help me.
I wanted to store the content of a RTF control (the text including the formatting) in a database field (Microsoft SQL Server 2008 R2). I tried different approaches and didn’t find a good solution. So I made a simple app.
It creates a table RtfTest in a database TEST. The table consists of five columns with different types (VARBINARY, VARCHAR, NVARCHAR, TEXT, IMAGE). You can save and load to each of the five fields.
First I tried it with a simple string “Test”. This works for all columns except for VARBINARY. It doesn’t load the content from the database although the field contains a value. This test was done with Team Developer 6.0 SP5 and ODBC driver “SQL Server”. Then I changed to the new ODBC Driver “SQL Server Native Client 10.0”. This time only TEXT and IMAGE worked fine. All other didn’t load, although a value is stored in the corresponding database field. Then I tried the same with Team Developer 6.1 and got the same result.
My second try was a somehow larger RTF document. I inserted a picture. So the RTF document was about 2.5 MB. Now none of the five possibilities worked. The document TestRtf.pdf shows a table with the result of my tries. The number in the row "Save" shows the size of the corresponding field after saving the data (SELECT DATALENGTH(..) ..).
Is there any bug or do I miss something?
Thanks,
Peter.

[Nils Jänicke]

You should use VARBINARY(MAX), then you can write binary data into the MS SQL server, but you should look on the maximum buffer length in sql.ini.
But to save big RTF contents you should not set the longbuffer value in thr sql.ini to a very large value, that will not work. Also you should tell TD to write binary data (SqlSetLongBindDatatype with second parameter = 23, see the TD help).
To store big data you should cut the data into smaller packages and use database commands to concatenate them.
Here are two functions fcWriteBigBinary and fcReadBigBinary to write and read big binary data to a MS Sql Server database using varbinary(max) columns (min. SqlServer 2005 required).
bOk = fcWriteBigBinary(hSql, 'MYRTFFIELD','MYTABLE',SqlDatabase,'MYTABLE.PrimaryKey = 1',mlRTF)
stores the long string value mlRTF regardless of the size
mlRTF = fcReadBigBinary(hSql,'MYRTFFIELD','MYTABLE',SqlDatabase,'MYTABLE.PrimaryKey = 1') gives the value back.

You can make your data smaller using SalCompress and SalUncompress, if no other software should read the data...

@Unify: You can move this example to 'Samples'

[pdi]

Hi Nils!

Thank you for quick response and your source code.

Also you should tell TD to write binary data (SqlSetLongBindDatatype with second parameter = 23, see the TD help).

I know as you can see in my example.

To store big data you should cut the data into smaller packages and use database commands to concatenate them.

Sorry, this doesn't work for me. I need the whole data as one piece for other applications.

Thank you,
Peter.

[Jeff Luther]

I am not sure what Nils meany by "To store big data..,." because with varbinary(max) you certainly shouldn't have to. Not according to the size of 'max' described here:
http://msdn.microsoft.com/en-us/library/ms188362.aspx

Maybe he can clarify that.

Otherwise, Nils is correct: RTF is considered 'binary' data in TD parlance because images, for example, can be embedded. I looked at your test case, tried As Binary and see some wrong coding. I'll attach my mod. to your test (which assigns RTC on create) and the changes I made:

JL_correctionsToTestCase_AsBinary.png

I found 2 issues:
** option '1' was wrong for which column was selected. Should be the AsBinary column, that's where your Save As Binary is inserting the RTC text

** Note I've commented out your SalStrTo calls. Why are those there? They're not necessary and are wrong, *unless* your DB does not support Unicode. Our MS SS 2005 I tested with does, so any DML will insert/update Unicode and when fetched is already Unicode so no conversion is necessary.

I used your original msg. as the RTC text, and before the fetch/assign clear RTC just to be sure That we're not fooling ourselves as to what's getting fetched. I didn't test any of your test case's other options (nice test!!) but for RTC and AsBinary and Unicode-enabled DB the mod. test works for me, as shown.

[Nils Jänicke]

Sorry, I've sent the wrong file.
Here are the functions to store big binary data using ODBC connect to MS SqlServer

[pdi]

Hi Jeff!

Thank you very much for finding my typing error. Saving the RTF content as binary data works fine now.

However, it doesn't work with the "SQL Server Native Client 10.0". Does TD 6.0 support the SQL Server Native Client ODBC driver?

Otherwise, Nils is correct: RTF is considered 'binary' data in TD parlance because images, for example, can be embedded.

In my opinion there is no need for considering RTF as binary data. I didn't go through the specification, so I am not absolutely sure about that. However, Wikipedia says RTF is encoded in 7-Bit ASCII. If you insert a picture in the RTF control and save it to a RTF file you can open the RTF file with a normal text editor. There is no binary coding at all. The picture data is coded in ASCII digits. That's even the same for Word Documents. You can save them as RTF documents and edit them using a text editor. Saving RTF data as text is necessary if you want to produce documents with other tools, which interpret RTF Text as ASCII text and cannot handle binary data.

That's why I tried different data types to save the RTF Text. And it works for small text (except with the mentioned Native Client Driver). However, for larger text it seems that the text is truncated at 32767 / 65534. Consequently loading that text shows only part of the previous text. Again the native ODBC driver doesn't work and produces errors like "String data, length mismatch" or "String data, right truncation". So I tried SqlSetParameterAll(lhSql, DBP_LONGBUFFER, SalStrLength(lsText) * 2) and got the same result.

Thanks,
Peter.