Board index » delphi » Storing formatted RichEdit text in a SQL field

Storing formatted RichEdit text in a SQL field

Suppose you have a RichEdit and want to store formatted text in a SQL db
field.  Should one use a TEXT or a BLOB field to store the formatted text?
 

Re:Storing formatted RichEdit text in a SQL field


Quote
"Bob Marietta" <igdsoftw...@yahoo.com> wrote in message

news:3d2e161b$1_1@dnews...

Quote
> Suppose you have a RichEdit and want to store formatted text in a SQL db
> field.  Should one use a TEXT or a BLOB field to store the formatted text?

I would always use a blob. Even if its just plain text, if its potentially
enough to need a memo control rather than a simple edit then it belongs in a
blob.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Those who disdain wealth as a worthy goal for an individual or a society
seem not to realize that wealth is the only thing that can prevent
poverty. - Thomas Sowell

Re:Storing formatted RichEdit text in a SQL field


Would you mind posting some sample code how to do this?  I've been using a
SQLQuery to manipulate my table.  This is what I am doing now:

To insert:

with SQLQuery do
begin
   SQL.Clear;
   sql.Add('insert into mytable (textfield) values
('''+TemplateRichEdit.Lines.Text+''')' );
   ExecSql;
end;

To retrieve:

with SQLQuery do
begin
    SQL.Clear;
    SQL.Add('select * from mytable where
uniquetemplateid='+IntToStr(TemplateID) );
    Open;
    Richedit.Text:=FieldByName('textfield').AsString;
    Close;
end;

The above works fine except that all the formatted text is lost.

"Wayne Niddery [TeamB]" <wnidd...@chaffaci.on.ca.com> wrote in message
news:3d2e20e5$1_2@dnews...

Quote
> "Bob Marietta" <igdsoftw...@yahoo.com> wrote in message
> news:3d2e161b$1_1@dnews...
> > Suppose you have a RichEdit and want to store formatted text in a SQL db
> > field.  Should one use a TEXT or a BLOB field to store the formatted
text?

> I would always use a blob. Even if its just plain text, if its potentially
> enough to need a memo control rather than a simple edit then it belongs in
a
> blob.

Re:Storing formatted RichEdit text in a SQL field


Quote
"Bob Marietta" <igdsoftw...@yahoo.com> wrote in message

news:3d2e37c6_2@dnews...

Quote
> Would you mind posting some sample code how to do this?  I've been using a
> SQLQuery to manipulate my table.  This is what I am doing now:

> To insert:

> with SQLQuery do
> begin
>    SQL.Clear;
>    sql.Add('insert into mytable (textfield) values
> ('''+TemplateRichEdit.Lines.Text+''')' );
>    ExecSql;
> end;

For inserting I would stringly recommend using a parameter, if the text gets
very large (remembering that it also includes the formatting), you could
exceed the max length of an SQL statement, however most servers accept more
through parameters:

    sql.Text := 'insert into mytable (textfield) values (:textfield)' ;
    Params[0].AsString := TemplateRichEdit.Lines.Text;
    ExecSql;

Now about losing the formatting. The Lines.Text property does indeed only
give you the text, but saving to a stream or file also saves the formatting.
Try the following:

var ms: TMemoryStream;
begin
  ms := TMemoryStream.Create;
  try
    TemplateRichEdit.SaveToStream(ms);
    ms.Position := 0;
    sql.Text := 'insert into mytable (textfield) values (:textfield)' ;
    Params[0].LoadFromStream(ms);
    ExecSql;
  finally
    ms.Free;
  end;

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Those who disdain wealth as a worthy goal for an individual or a society
seem not to realize that wealth is the only thing that can prevent
poverty. - Thomas Sowell

Re:Storing formatted RichEdit text in a SQL field


I tried exactly what you said below except I had used TStringStream.  Would
that make a difference compared to using TMemoryStream?
Apparently the formatted text contains control characters that mess up the
SQL query.

Quote
> For inserting I would stringly recommend using a parameter, if the text
gets
> very large (remembering that it also includes the formatting), you could
> exceed the max length of an SQL statement, however most servers accept
more
> through parameters:

>     sql.Text := 'insert into mytable (textfield) values (:textfield)' ;
>     Params[0].AsString := TemplateRichEdit.Lines.Text;
>     ExecSql;

> Now about losing the formatting. The Lines.Text property does indeed only
> give you the text, but saving to a stream or file also saves the
formatting.
> Try the following:

> var ms: TMemoryStream;
> begin
>   ms := TMemoryStream.Create;
>   try
>     TemplateRichEdit.SaveToStream(ms);
>     ms.Position := 0;
>     sql.Text := 'insert into mytable (textfield) values (:textfield)' ;
>     Params[0].LoadFromStream(ms);
>     ExecSql;
>   finally
>     ms.Free;
>   end;

> --
> Wayne Niddery (Logic Fundamentals, Inc.)
> RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
> Those who disdain wealth as a worthy goal for an individual or a society
> seem not to realize that wealth is the only thing that can prevent
> poverty. - Thomas Sowell

Re:Storing formatted RichEdit text in a SQL field


Quote
"Bob Marietta" <igdsoftw...@yahoo.com> wrote in message

news:3d2f86ea$1_1@dnews...

Quote
> I tried exactly what you said below except I had used TStringStream.
Would
> that make a difference compared to using TMemoryStream?

Don't think so, but try the memory stream just in case.

Quote
> Apparently the formatted text contains control characters that mess up the
> SQL query.

This shouldn't happen if you are using parameters, but can certainly happen
if you are not.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Those who disdain wealth as a worthy goal for an individual or a society
seem not to realize that wealth is the only thing that can prevent
poverty. - Thomas Sowell

Re:Storing formatted RichEdit text in a SQL field


I think I got it working as far as storing...

One point, you have to specify a TBlobType here Params[0].LoadFromStream(ms,
BLOBTYPE);

Hey how about retrieving?  There doesn't seem to be an .AsBlob for
FieldByName.

with SQLQuery do
begin
    SQL.Clear;
    SQL.Add('select * from mytable where
uniquetemplateid='+IntToStr(TemplateID) );
    Open;
    MemoryStream.LoadfromStream(FieldByName('formattedtextfield')...);
    Close;
end;

"Wayne Niddery [TeamB]" <wnidd...@chaffaci.on.ca.com> wrote in message
news:3d2f5ad8_1@dnews...

Quote
> "Bob Marietta" <igdsoftw...@yahoo.com> wrote in message
> news:3d2e37c6_2@dnews...
> > Would you mind posting some sample code how to do this?  I've been using
a
> > SQLQuery to manipulate my table.  This is what I am doing now:

> > To insert:

> > with SQLQuery do
> > begin
> >    SQL.Clear;
> >    sql.Add('insert into mytable (textfield) values
> > ('''+TemplateRichEdit.Lines.Text+''')' );
> >    ExecSql;
> > end;

> For inserting I would stringly recommend using a parameter, if the text
gets
> very large (remembering that it also includes the formatting), you could
> exceed the max length of an SQL statement, however most servers accept
more
> through parameters:

>     sql.Text := 'insert into mytable (textfield) values (:textfield)' ;
>     Params[0].AsString := TemplateRichEdit.Lines.Text;
>     ExecSql;

> Now about losing the formatting. The Lines.Text property does indeed only
> give you the text, but saving to a stream or file also saves the
formatting.
> Try the following:

> var ms: TMemoryStream;
> begin
>   ms := TMemoryStream.Create;
>   try
>     TemplateRichEdit.SaveToStream(ms);
>     ms.Position := 0;
>     sql.Text := 'insert into mytable (textfield) values (:textfield)' ;
>     Params[0].LoadFromStream(ms);
>     ExecSql;
>   finally
>     ms.Free;
>   end;

> --
> Wayne Niddery (Logic Fundamentals, Inc.)
> RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
> Those who disdain wealth as a worthy goal for an individual or a society
> seem not to realize that wealth is the only thing that can prevent
> poverty. - Thomas Sowell

Other Threads