RichEdit with MSSQL

[Squall_FF8 1]

Hi guys,
I have a TFDQuerry connected to MSSQL that returns a record with field Test - nvarchar(max).
I would like to be able to Paste a text from Word in a RichEdit and be able to save it (formatted as RTF) in Test and latter on load it from there.
How I can do that? (code snippet)

P.S. If possible I try to use Insert/Update statements, but if I have to - I will use BLOBs. I just dont understand how blobs work in conjunction with normal Insert/Update.

Edited yesterday at 04:16 PM by Squall_FF8

[Remy Lebeau 1604]

RTF is encoded in plain ASCII text, where non-ASCII characters are escaped. So, you should not need a blob for this, a simple text field will suffice (although, you might consider a blob if you want to store the RTF using 8bit characters but don't want to use varchar instead of nvarchar).

 

Have you tried simply saving the RTF from the RichEdit into a UnicodeString (ie nvarchar) or AnsiString (ie varchar) variable and then load it back in, without involving a DB at all?  Set the RichEdit.PlainText property to false to tell the RichEdit to act on RTF, and then use the RichEdit.SaveToStream() and RichEdit.LoadFromStream() methods to get and load the RTF.  You can then expand on that logic to introduce text handling with a DB.

Edited yesterday at 06:34 PM by Remy Lebeau

[Squall_FF8 1]

13 hours ago, Remy Lebeau said:

and then use the RichEdit.SaveToStream() and RichEdit.LoadFromStream() methods to get and load the RTF.  You can then expand on that logic to introduce text handling with a DB.

OK, I did:

// To get RTF
  var Stream := tStringStream.Create;
  Rich.Lines.SaveToStream(Stream);
  Result := QuotedStr(Stream.DataString); // for Insert/Update, the text needs quotes

However when I use the result in Query, I get error - it messes up the syntax of the SQL.
So I inspected the returned DataString ... It seems it uses not just plain text, but control codes: 13, 10, 0. That creates 2 problems:
1. QuotedStr - formats it in Delphi way: 'text'#13#10'text'.... SQL doesn't like that!
2. #0 is considered end of a string (most of the times) and Delphi cuts what follows.

Questions:
- Since when RTF includes control codes (ASCII < 32)? If that is the case then things like `\par` are nonsense.
- The RTF text states version 2.0. Do you know what version of RichEdit Delphi 12+ uses?

 

Quote

RTF is encoded in plain ASCII 

Because of: "{\rtf1\ansi". I'm not familiar with "RTF using 8bit characters". Does Delpji / Windows RichEdit supports that? Do you know of a program that supports that?

[Tom Chamberlain 50]

I use a TRichViewEdit which has other methods but this should work for a regular TRichEdit as well, do not treat it like a string stream, it's a blob stream.

 

To save the RichEdit
 

var
  BlobStream: TFDBlobStream;
...
... dbQuery = Select statement for the record that you want to update/create
...  
  dbQuery.Open;
  try
    ...
    ... dbQuery.Append; if you are creating a new record
    ...
    ... dbQuery.Edit; if you are editing one
    ...
    BlobStream := TFDBlobStream.Create(dbQuery.FieldByName('BlobFieldName') as TBlobField, bmWrite);
    try
      YourRichEdit.Lines.SaveToStream(BlobStream);
    finally
      BlobStream.Free;
    end;
    dbQuery.Post;
  except
    on e: exception do
      MessageDlg(e.Message, mtError, [mbOK], 0);
  end;
  dbQuery.Close;

To load the RichEdit

 

var
  BlobStream: TFDBlobStream;
...
... dbQuery = Select statement for the record that you want to read/load
...  
    dbQuery.Open;
    BlobStream := TFDBlobStream.Create(dbQuery.FieldByName('BlobFieldName') as TBlobField, bmRead);
    try
      if not dbQuery.FieldByName('BlobFieldName').IsNull then
        begin
          YourRichEdit.Lines.LoadFromStream(BlobStream)
        ...
        ... Not sure if there is anything RichEdit needs to update/format the display
        ...
        end;
    finally
      BlobStream.Free;
    end;
    dbQuery.Close;

 

[Squall_FF8 1]

Thank you @Tom Chamberlain for the code and comments!!! I'm sure it will be very helpful. Later today I will examine it very carefully.

[Remy Lebeau 1604]

5 hours ago, Squall_FF8 said:

However when I use the result in Query, I get error - it messes up the syntax of the SQL.

Then you will have to escape control characters manually, which QuotedStr() doesn't do for you.

 

But, you really shouldn't be concatenating strings to create SQL queries to begin with. Use a parameterized query instead, letting the DB enging handle all of the escaping and quoting for you. Or, use a blob stream (though, I would use TFQQuery.CreateBlobStream() instead of creating TFDBlobStream directly, but either way works).

6 hours ago, Squall_FF8 said:

Since when RTF includes control codes (ASCII < 32)? If that is the case then things like `\par` are nonsense.

AFAIK, RTF does not use ASCII control characters. Line breaks are handled by \line and \par control words. And there should be no #0 characters at all. That being said, if there are raw line breaks in the RTF, it might be for human reading purposes, but not machine reading.

6 hours ago, Squall_FF8 said:

The RTF text states version 2.0. Do you know what version of RichEdit Delphi 12+ uses?

Delphi 12 uses RichEdit 4.1 (since D11) , which is the current version available from Microsoft at this time. I don't know offhand what version of RTF it supports.

6 hours ago, Squall_FF8 said:

I'm not familiar with "RTF using 8bit characters". Does Delpji / Windows RichEdit supports that? Do you know of a program that supports that?

RTF is encoded in ASCII, which fits in 8bit (AnsiChar) characters in memory, so you don't need to waste memory using 16bit (WideChar) characters to hold it. A Delphi String is 16bit since D2009. This is why I suggested using varchar instead of nvarchar in the DB. Or, a blob field will suffice, too.