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 Wednesday at 04:16 PM by Squall_FF8

[Remy Lebeau 1606]

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 Wednesday 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 1606]

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.

 

[Squall_FF8 1]

5 hours ago, Remy Lebeau said:

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

TBH, I dont know how to escape for MS SQL query. However as you said control codes are not needed for RTF standard, so I just made a routine that skips them. And all works fine now!!!
BTW this might be interesting for you - Delphi and WordPad put exact same control codes. In my observation - 13, 10 after \par and 0 at the end.  

 

5 hours ago, Remy Lebeau said:

This is why I suggested using varchar instead of nvarchar in the DB

Good point - great suggestion!

Thank you very much for taking time to write detail answers!!!

[rvk 44]

11 hours ago, Squall_FF8 said:

BTW this might be interesting for you - Delphi and WordPad put exact same control codes. In my observation - 13, 10 after \par and 0 at the end.  

Correct. That's why I use BLOB. (in Firebird BLOB SUB_TYPE 0 SEGMENT SIZE 80)

(In Firebird you could also use SUB_TYPE 1 to let the DB treat it as text blob, but then you would need to make sure the char set is correct.)

 

Then... I don't use the TBlobField(TField).LoadFromStream etc. for just RTF text (where I do for dropping random attachment and files in the DB).

I use my own GetRTF(RichEdit1) and GetRTFSelection which retrieve the raw RTF and then use the normal TField.asString via parameterized update/insert queries.

 

But... there is also a TDBRichEdit which should handle this automatically (I create my own TMyDBRichEdit for also handling some extras).

So... when you have a visual richedit on screen, use the TDBRichEdit. When you need to store and read without visual richedit, use a GetRTF/SetRTF function.

 

Don't show RTF in a grid as column (when you would use varchar). Because of the encoding it's not for reading directly anyways.

 

[Squall_FF8 1]

35 minutes ago, rvk said:

I use my own GetRTF(RichEdit1)

It will be interesting to peek in a snippet of it ^_^
 

37 minutes ago, rvk said:

there is also a TDBRichEdit

How that works in conjunction with TQuery?
I mean, when I call ExecSQL with Insert/Update, TDBRichEdit automatically provides the context for my BLOB field (no extra code)? Or I need to provide some code before/after ExecSQL?

[mjustin 28]

40 minutes ago, rvk said:

Don't show RTF in a grid as column (when you would use varchar). Because of the encoding it's not for reading directly anyways.

While the encoding issue can be solved by transforming the RTF to plain text (iirc TRichEdit can help with this), the bigger obstacle is the amount of data which will be read if the RTF is 'unlimited' in length.

A workaround would be to process and show only a part of the RTF as plain text. 

[Squall_FF8 1]

21 hours ago, Tom Chamberlain said:

Select statement for the record that you want to update/create

How I can do that for create (Insert)? My Query returns only 1 record, from Where clause (usually ID of a table). But if it is a new record ...?

BTW Using Select to Insert/Update - that will never come to my mind 🙂 I understand that actual operations come from Edit/Append. Thank you!

[rvk 44]

13 minutes ago, Squall_FF8 said:

It will be interesting to peek in a snippet of it ^_^

function EditStreamInCallback(dwCookie: Longint; pbBuff: PByte;
  cb: Longint; var pcb: Longint): dword; stdcall;
const
  E_FAIL = dword($80004005);
var
  theStream: TStream;
  dataAvail: Longint;
begin
  theStream := TStream(dwCookie);
  with theStream do
  begin
    dataAvail := Size - Position;
    Result := 0; { assume everything is ok }
    if dataAvail <= cb then
    begin
      pcb := read(pbBuff^, dataAvail);
      if pcb <> dataAvail then { couldnt read req. amount of bytes }
          Result := E_FAIL;
    end
    else
    begin
      pcb := read(pbBuff^, cb);
      if pcb <> cb then
          Result := E_FAIL;
    end;
  end;
end;

function EditStreamOutCallback(dwCookie: Longint; pbBuff: PByte;
  cb: Longint; var pcb: Longint): dword; stdcall;
var
  theStream: TStream;
begin
  theStream := TStream(dwCookie);

  with theStream do
  begin
    if cb > 0 then
        pcb := write(pbBuff^, cb);
    Result := 0;
  end;
end;

procedure GetRTFSelection(aRichEdit: TJvRichEdit; intoStream: TStream);
var
  editstream: TEditStream;
begin
  with editstream do
  begin
    dwCookie := Longint(intoStream);
    dwError := 0;
    pfnCallback := @EditStreamOutCallback;
  end;
  aRichEdit.Perform(EM_STREAMOUT, SF_RTF or SFF_SELECTION, LParam(@editstream));
end;

procedure PutRTFSelection(aRichEdit: TJvRichEdit; sourceStream: TStream);
var
  editstream: TEditStream;
begin
  with editstream do
  begin
    dwCookie := Longint(sourceStream);
    dwError := 0;
    pfnCallback := @EditStreamInCallback;
  end;
  aRichEdit.Perform(EM_STREAMIN, SF_RTF or SFF_SELECTION, LParam(@editstream));
end;

function GetRTF(RE: TJvRichEdit; Selection: Boolean = false): string;
var
  strStream: TStringStream;
begin
  strStream := TStringStream.Create('');
  try
    if Selection then
    begin
      GetRTFSelection(RE, strStream);
      Result := strStream.DataString;
    end
    else
    begin
      RE.PlainText := false;
      RE.Lines.SaveToStream(strStream);
      Result := strStream.DataString;
    end;
  finally
      strStream.Free
  end;
end;

procedure SetRTF(var RE: TJvRichEdit; S: string);
var
  strStream: TStringStream;
begin
  strStream := TStringStream.Create('');
  try
    strStream.WriteString(S);
    strStream.Position := 0;
    RE.PlainText := false;
    RE.Lines.LoadFromStream(strStream);
  finally
      strStream.Free
  end;
end;

GetRTF/SetRTF for the complete content of TRichEdit (I use TJvRichEdit but you can change that to TRichEdit).

GetRTFSelection/SetRTFSelection for only the selected content.

 

13 minutes ago, Squall_FF8 said:

How that works in conjunction with TQuery?
I mean, when I call ExecSQL with Insert/Update, TDBRichEdit automatically provides the context for my BLOB field (no extra code)? Or I need to provide some code before/after ExecSQL?

With ExecSQL you don't work with dataaware components. The you can just use TField.AsString for the parameterized select/update/insert. TRichEdit is not dataaware.

When you want to work with data-aware components like TDBRichEdit, you don't need to use ExecSQL. You can just do SELECT and fill in the UpdateSQL Object with INSERT/UPDATE/DELETE statements.

Then in code you only have to do TQuery.Open (for the SELECT). Your TDBRichEdit will be automatically filled.

When you do TQuery.Edit you enter edit mode (or you can do TQuery.Insert to insert a new record).

After you are ready, you do TQuery.Post and the records (including the content of TDBRichEdit) is saved. (don't forget to Commit the transaction)

 

[Tom Chamberlain 50]

No dataaware, it is a kind of cheat/lazy way to do it

'Select * From TableWithBlod Where ID = -1', Open this to get an empty dataset (assuming you do not have a ID = -1), and that dataset 'knows' about the fields, when you do the append you can then get the stream to the blob field and fill it with raw data from the rich edit stream, set your ID and any other fields and call post then close.