Problem with saving/loading RTF text to/from DB

[saeid2016]

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]

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]

Thank you for answer.

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.

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.

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]

I used RVGetTextRange to get text, Problem still exists.

[Sergey Tkachenko]

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

[saeid2016]

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.

[Sergey Tkachenko]

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]

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.

[Sergey Tkachenko]

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]

Hello, Is there a way to solve the problem?

[Sergey Tkachenko]

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

[Sergey Tkachenko]

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]

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.

[Sergey Tkachenko]

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

[saeid2016]

Thank you for very useful help.