Problem with saving/loading RTF text to/from DB

General TRichView support forum. Please post your questions here
Post Reply
saeid2016
Posts: 44
Joined: Wed Mar 16, 2016 11:56 am

Problem with saving/loading RTF text to/from DB

Post by saeid2016 » Tue Aug 01, 2017 9:30 am

Hello support,
I have 50000 RTF files which I want to save them to DB. Because of large size of Blob fields I save them as plain text in DB and save their text styles (only TextColor and BackColor) in another table. My tables is:

Books: BookID, BookName.

BookPages: PageID, BookID, PageNumber, PageText.

Styles: StyleID, StyleName, R_Color, G_Color, B_Color, Back_R_Color, Back_G_Color, Back_B_Color.

PageStyles: PageStyleID, PageID, StyleID, FromPosition, ToPosition.

I load the RTF file to TSRichViewEdit by this code:

Code: Select all

    rveMain.Clear;
    rveMain.Format;
    rveMain.Update;
    rveMain.RichViewEdit.LoadRTF(dlgOpen.FileName);
    rveMain.Format;
Then I save each page of RTF file as a record in BookPages table.

Code: Select all

  i := 1;
  while i <= rveMain.PageCount do
  begin
    MyText := '';

    rveMain.GetPageStartItemNo(i, FirstItemNo, FirstItemOffset);
    rveMain.GetPageLastItemNo(i, LastItemNo, LastItemOffset);
    rveMain.RichViewEdit.Deselect;
    rveMain.RichViewEdit.SetSelectionBounds(FirstItemNo,FirstItemOffset,LastItemNo, LastItemOffset);
    Sleep(50);
    rveMain.RichViewEdit.Copy;
    Sleep(50);
    rveTemp.Clear;
    rveTemp.Format;
    rveTemp.Update;
    rveTemp.RichViewEdit.Paste;
    rveTemp.Format;

    MyText := rveMain.RichViewEdit.GetSelText;
    InsertPages (MyText); // This is a function that inserts page text and other fields in table.

    Application.ProcessMessages;

    ///////////////////////////////////////////////////////
    qry2.SQL.Text := 'SELECT SCOPE_IDENTITY() AS MyID';
    qry2.Open;

    InsertedPageID := qry2.FieldByName('MyID').AsString;
 
    SavePageStylesToDB(InsertedPageID, rveTemp); // A procedure that saves text styles;
    ////////////////////////////////////////////////////////

    i := i + 1;
  end;

The SavePageStylesToDB procedure code:

Code: Select all

procedure SavePageStylesToDB(PageID: string; rveObject : TSRichViewEdit);
var i : Integer;
  qryStyles : TADOQuery;
  RGB, R, G, B, Back_R, Back_G, Back_B : Integer;
  StyleID : string;
  FromPos ,tmpLength , ToPos : Integer;
begin
  qryStyles := TADOQuery.Create(Self);
  qryStyles.Connection := ADOConnection1;

  for I := 0 to rveObject.RichViewEdit.ItemCount - 1 do
    if rveObject.RichViewEdit.GetItem(i) is TRVTextItemInfo then

    begin
      RGB := ColorToRGB(rveObject.ExternalRVStyle.TextStyles[rveObject.RichViewEdit.GetItemStyle(i)].Color);
      R := GetRValue(RGB);
      G := GetGValue(RGB);
      B := GetBValue(RGB);

      RGB := ColorToRGB(rveObject.ExternalRVStyle.TextStyles[rveObject.RichViewEdit.GetItemStyle(i)].BackColor);
      Back_R := GetRValue(RGB);
      Back_G := GetGValue(RGB);
      Back_B := GetBValue(RGB);

      qryStyles.SQL.Text := 'SELECT * FROM Styles ' +
      ' WHERE RColor = ' + IntToStr(R) +
      ' and GColor = ' + IntToStr(G) +
      ' and BColor = ' + IntToStr(B) +
      ' and Back_RColor = ' + IntToStr(Back_R)+
      ' and Back_GColor = ' + IntToStr(Back_G)+
      ' and Back_BColor = ' + IntToStr(Back_B);
      qryStyles.Open;
      if qryStyles.RecordCount > 0 then
      begin
        StyleID := qryStyles.FieldByName('StyleID').AsString;
        rveObject.RichViewEdit.SetSelectionBounds(i,rveObject.RichViewEdit.GetOffsBeforeItem(i),i,rveObject.RichViewEdit.GetOffsAfterItem(i));
        RVGetSelection(rveObject.RichViewEdit,FromPos, tmpLength);
        ToPos := FromPos + tmpLength;

        qryStyles.SQL.Text := 'INSERT INTO PageStyles (PageID, StyleID, FromPosition, ToPosition) ' +
        'Values (' + PageID + ',' + StyleID + ',' + IntToStr(FromPos) + ',' + IntToStr(ToPos) + ')';
        qryStyles.ExecSQL;
      end;

    end;
end;

And I load the page text with styles in TSRichViewEdit by this code:

Code: Select all

   AdoQuery1.SQL.Text := 'SELECT PageText FROM BookPages WHERE PageID = ' + MyPageID;
   AdoQuery1.Open;

   rve.Clear;
   rve.RichViewEdit.AddText(AdoQuery1.FieldByName('PageText').AsString,0);
   rve.Format;

   GetStyles(rve, MyPageID); // A procedure that get and apply styles to page text.
The GetStyles procedure code:

Code: Select all

procedure GetStyles(rveObject : TSRichViewEdit; PageID : string);
begin
   AdoQuery2.SQL.Text := 'SELECT PageStyles.PageID, PageStyles.FromPosition, PageStyles.ToPosition , Styles.* ' +
   'FROM PageStyles INNER JOIN Styles On PageStyles.StyleID = Styles.StyleID ' +
   'WHERE PageID = '+ PageID;
   AdoQuery2.Open;
   if AdoQuery2.RecordCount > 0 then
   begin

     while not AdoQuery2.Eof do
     begin
       RVSetSelection(rveObject.RichViewEdit,AdoQuery2.FieldByName('FromPosition').AsInteger,AdoQuery2.FieldByName('ToPosition').AsInteger-AdoQuery2.FieldByName('FromPosition').AsInteger);
       
       TextColor := RGB(AdoQuery2.FieldByName('R_Color').AsInteger,AdoQuery2.FieldByName('G_Color').AsInteger,AdoQuery2.FieldByName('B_Color').AsInteger);
       
       BackColor := RGB(AdoQuery2.FieldByName('Back_R_Color').AsInteger, AdoQuery2.FieldByName('Back_G_Color').AsInteger, AdoQuery2.FieldByName('Back_B_Color').AsInteger);
       rveObject.RichViewEdit.ApplyStyleConversion(TEXT_COLOR);
        rveObject.RichViewEdit.ApplyStyleConversion(TEXT_BACKCOLOR);

       AdoQuery2.Next;
     end;

   end;
end;

This codes work correctly with some RTF files and incorrectly with some other. I mean that in some RTF texts The text styles applied to wrong positions.

Now my questions is that:
1. why this code does not work correctly to all of my RTF files?
2. Is this a right idea to save text styles in another table?

My DB is Sql Server and my RTF files content in Arabic Texts.

Sergey Tkachenko
Site Admin
Posts: 13584
Joined: Sat Aug 27, 2005 10:28 am
Contact:

Re: Problem with saving/loading RTF text to/from DB

Post by Sergey Tkachenko » Tue Aug 01, 2017 12:24 pm

It's very difficult to find bugs in code in browsers.

Some notes:

1) Do you documents have tables? This code does not work correctly for pages started and/or ended in the middle of a table.
In this case, the results of GetPageStartItemNo and GetPageLastItemNo cannot be used for SetSelectionBounds.
Actually, in the current version, it is not possible to make selection started and/or ended in the middle of table, so, if you use tables, you need to store page in up to 3 documents: rows of a table starting the page (if exists), main content (between tables), rows of a table ending the page (if exists).
Is there a real necessity to process each page separately? I believe it causes the most of problems.
2) I cannot see how PageText is filled. If you need one-to-one correspondence to the original document and text, use RVGetTextRange to get text.

saeid2016
Posts: 44
Joined: Wed Mar 16, 2016 11:56 am

Re: Problem with saving/loading RTF text to/from DB

Post by saeid2016 » Tue Aug 01, 2017 2:21 pm

Thank you for answer.
Sergey Tkachenko wrote:
Tue Aug 01, 2017 12:24 pm
1) Do you documents have tables? This code does not work correctly for pages started and/or ended in the middle of a table.
In this case, the results of GetPageStartItemNo and GetPageLastItemNo cannot be used for SetSelectionBounds.
Actually, in the current version, it is not possible to make selection started and/or ended in the middle of table, so, if you use tables, you need to store page in up to 3 documents: rows of a table starting the page (if exists), main content (between tables), rows of a table ending the page (if exists).
No, My documents don't have tables. their contents only is text.
Sergey Tkachenko wrote:
Tue Aug 01, 2017 12:24 pm
Is there a real necessity to process each page separately? I believe it causes the most of problems.
I develop two applications. one for filling DB and another for showing texts. in second application I use TWebBrowser to show texts, My documents have more pages, for example 500 pages. Loading 500 pages in WebBrowser takes a lot of time, so I save each page separately in DB to show them one after another.
Sergey Tkachenko wrote:
Tue Aug 01, 2017 12:24 pm
2) I cannot see how PageText is filled. If you need one-to-one correspondence to the original document and text, use RVGetTextRange to get text.
The type of PageText is NVARCHAR(MAX). I will test RVGetTextRange to get text and give you the result.

Thanks again for help.

saeid2016
Posts: 44
Joined: Wed Mar 16, 2016 11:56 am

Re: Problem with saving/loading RTF text to/from DB

Post by saeid2016 » Wed Aug 02, 2017 6:48 am

I used RVGetTextRange to get text, Problem still exists.

Sergey Tkachenko
Site Admin
Posts: 13584
Joined: Sat Aug 27, 2005 10:28 am
Contact:

Re: Problem with saving/loading RTF text to/from DB

Post by Sergey Tkachenko » Wed Aug 02, 2017 3:42 pm

Can you reproduce the problem in a simple project, using files instead of DB?
If yes, please send it to me

saeid2016
Posts: 44
Joined: Wed Mar 16, 2016 11:56 am

Re: Problem with saving/loading RTF text to/from DB

Post by saeid2016 » Mon Aug 07, 2017 11:54 am

Sergey Tkachenko wrote:
Wed Aug 02, 2017 3:42 pm
Can you reproduce the problem in a simple project, using files instead of DB?
If yes, please send it to me
I made a sample with mdb database.
my sample docs are in Docs folder.
install fonts in Fonts folder before you test the project.

Reminder: The problem is that in some Docs, when I save their text page by page in DB as plain text and save their styles in another table, the styled items position saves incorrect. In some other it is correct.
Attachments
Fonts.rar
(101.73 KiB) Downloaded 22 times
Docs.rar
(86.02 KiB) Downloaded 21 times
TestForRichView.rar
(82.44 KiB) Downloaded 23 times

Sergey Tkachenko
Site Admin
Posts: 13584
Joined: Sat Aug 27, 2005 10:28 am
Contact:

Re: Problem with saving/loading RTF text to/from DB

Post by Sergey Tkachenko » Mon Aug 07, 2017 5:37 pm

Please give me step-by-step instructions how to reproduce the problem.
Which doc? Which page? Where I can see what the result is wrong?

saeid2016
Posts: 44
Joined: Wed Mar 16, 2016 11:56 am

Re: Problem with saving/loading RTF text to/from DB

Post by saeid2016 » Tue Aug 08, 2017 4:46 am

Sergey Tkachenko wrote:
Mon Aug 07, 2017 5:37 pm
Please give me step-by-step instructions how to reproduce the problem.
Which doc? Which page? Where I can see what the result is wrong?
For example when we load Doc3.rtf with Load RTF button, It loads successfully. In page 3 of this doc there is an item from position 3489 to 3623 with text color: RGB(0,176,80), But when we save it to DB, this item position does not save in DB to apply text color on loading. Other items position saves and loads correctly. Please see my screen shot in Doc3Page3.rar attached file.

Other example: In Doc2.rtf page 2 from page start to 2124 the text color is black, but after saving, when we load this page from DB, the text color of all items of page is RGB(0,112,192). see screen shot in Doc2Page2.rar.

Other example: In Doc2.rtf page 3 there is an item from position 3206 To 3342, It's color is: RGB(148,54,52). but after saving, its color is black.see screen shot in Doc2Page3.rar.

The text color of All items of Doc2 applies incorrectly on loading from DB except the first page.
Attachments
Doc2Page3.rar
(187.82 KiB) Downloaded 21 times
Doc2Page2.rar
(190.09 KiB) Downloaded 22 times
Doc3Page3.rar
(155.49 KiB) Downloaded 22 times

Sergey Tkachenko
Site Admin
Posts: 13584
Joined: Sat Aug 27, 2005 10:28 am
Contact:

Re: Problem with saving/loading RTF text to/from DB

Post by Sergey Tkachenko » Thu Aug 10, 2017 9:06 pm

I wanted to solve this problem before leaving for vacation, but I couldn't, sorry.
I'll check your files when I return from vacation, after August, 21.

saeid2016
Posts: 44
Joined: Wed Mar 16, 2016 11:56 am

Re: Problem with saving/loading RTF text to/from DB

Post by saeid2016 » Wed Aug 23, 2017 4:13 pm

Hello, Is there a way to solve the problem?

Sergey Tkachenko
Site Admin
Posts: 13584
Joined: Sat Aug 27, 2005 10:28 am
Contact:

Re: Problem with saving/loading RTF text to/from DB

Post by Sergey Tkachenko » Thu Aug 24, 2017 10:10 am

I need some time to find the problem. I'll try to solve it till the end of this week.

Sergey Tkachenko
Site Admin
Posts: 13584
Joined: Sat Aug 27, 2005 10:28 am
Contact:

Re: Problem with saving/loading RTF text to/from DB

Post by Sergey Tkachenko » Tue Aug 29, 2017 1:13 pm

So far I found the following bugs in the code.

1) rveNew.RichViewEdit and rveTemp.RichViewEdit are linked to the same RVStyle (using ExternalRVStyle property, and direct assignment in code:

Code: Select all

rveNew.RichViewEdit.Style := rvsNewPages;
)

TSRichViewEdit.Clear not only clears document, but deletes unused styles. Because of this, when you call rveTemp.Clear, styles are removed from rveNew as well, and you can see white-on-red text marking places with incorrect style indexes.
But I think this problem must not affect results, because rveNew is not reformatted while saving, so formatting calculated with older state of styles still used.

Solution: avoid using ExternalRVStyle property. It is useful only when you need define styles at design time. Otherwise, you can always access RVStyle as SRichViewEdit.RichViewEdit.Style.

2) The real problem affecting results is here:

Code: Select all

rveNew.RichViewEdit.SetSelectionBounds(FirstItemNo,rveNew.RichViewEdit.GetOffsBeforeItem(FirstItemNo),LastItemNo, rveNew.RichViewEdit.GetOffsAfterItem(LastItemNo));
This code must be:

Code: Select all

    rveNew.RichViewEdit.SetSelectionBounds(FirstItemNo,
      FirstItemOffset,LastItemNo, LastItemOffset);
Does it fix the problem? Sorry, it's very hard for me to test this application, because I do not know Arabic.
At least, after that, it looks like pages stored in DB have the same text as in the original document.

saeid2016
Posts: 44
Joined: Wed Mar 16, 2016 11:56 am

Re: Problem with saving/loading RTF text to/from DB

Post by saeid2016 » Mon Sep 04, 2017 6:44 pm

Sergey Tkachenko wrote:
Tue Aug 29, 2017 1:13 pm
Does it fix the problem? Sorry, it's very hard for me to test this application, because I do not know Arabic.
At least, after that, it looks like pages stored in DB have the same text as in the original document.
I tested a lot of my docs, The main problem has been solved. thank you very much.

But another problem already exists. When I load the file I set all ParaStyles alignment to rvaJustify. After saving doc pages to DB, When I load each page from DB, I set all ParaStyles alignment to rvaJustify again but the last line of page doesn't align correctly. see my screen shot.
Attachments
richview2.jpg
richview2.jpg (213.34 KiB) Viewed 1064 times

Sergey Tkachenko
Site Admin
Posts: 13584
Joined: Sat Aug 27, 2005 10:28 am
Contact:

Re: Problem with saving/loading RTF text to/from DB

Post by Sergey Tkachenko » Tue Sep 05, 2017 10:13 am

The last line in justified and distributed paragraphs is aligned according to RVStyle.ParaStyles[].LastLineAlignment property.

saeid2016
Posts: 44
Joined: Wed Mar 16, 2016 11:56 am

Re: Problem with saving/loading RTF text to/from DB

Post by saeid2016 » Tue Sep 05, 2017 4:54 pm

Thank you for very useful help.

Post Reply