Recent

Author Topic: Storing richmemo text on a blob field in sqlite database  (Read 4080 times)

calebs

  • Full Member
  • ***
  • Posts: 190
Storing richmemo text on a blob field in sqlite database
« on: July 16, 2018, 10:52:34 pm »
Hello all, i think the subject resumes all.
I want to store a formatted text from a richedit on a (blob) field in a sqlite database and restore it later.
I've succesfully stored an image on a blob in a mysql database but i can't make work with text.
I've tried many suggestions and tried to make modifications but dosesn't seem to work.
Something is stored but when i retrieve it the richmemo is blank.
If i retrieve as text it's incomplete i guess.
here is the code for the insertion

Code: Pascal  [Select][+][-]
  1. ...
  2.  try
  3.               strStream := TMemoryStream.Create();
  4.               strstream2:= TMemoryStream.Create();
  5.               RichMemo1.SaveRichText(strStream);
  6.               RichMemo2.SaveRichText(strstream2);
  7.               ibase.fbase.queryle.ParamByName('param1').DataType:=ftblob;
  8.               ibase.fbase.queryle.ParamByName('param1').ParamType:=ptinput;
  9.               ibase.fbase.queryle.ParamByName('param1').LoadFromStream(strStream, ftBlob);
  10.               ibase.fbase.queryle.ParamByName('param2').DataType:=ftblob;
  11.               ibase.fbase.queryle.ParamByName('param2').ParamType:=ptinput;
  12.               ibase.fbase.queryle.ParamByName('param2').LoadFromStream(strStream2, ftBlob);
  13.               cadw:='INSERT INTO presupuestos (idcliente, fecha, publico, privado) VALUES ('+label2.Caption+', "';
  14.               cadw:=cadw+devolverfecha(DateEdit5.Date)+'", :param1, :param2)';
  15.               ibase.fbase.queryle.SQL.Clear;
  16.               ibase.fbase.queryle.SQL.Add(cadw);
  17.               ibase.fbase.queryle.ExecSQL;
  18.               ibase.fbase.queryle.Close;
  19.               ShowMessage('El presupuesto fue guardado');
  20.               strStream.Free;
  21.               strStream2.Free;
  22.             except
  23.               showmessage('No se pudo guardar el presupuesto');
  24.               strStream.Free;
  25.               strStream2.Free;
  26.             end;                    
  27. ...
  28.  
  29.  

And this to retrieve it

Code: Pascal  [Select][+][-]
  1. procedure tform4.cargarpresu;
  2. var
  3.   cad: TCaption;
  4.   strStream ,strStream2: TMemoryStream;
  5. Begin
  6.   cad:='SELECT * FROM presupuestos WHERE idpresupuesto = '+label37.Caption;
  7.   ibase.fbase.querysl.SQL.Clear;
  8.   ibase.fbase.querysl.SQL.Text:=cad;
  9.   ibase.fbase.querysl.Open;
  10.   strStream := TMemoryStream.Create();
  11.   strStream2:= TMemoryStream.Create();
  12.   if not ibase.fbase.querysl.EOF then begin
  13.     DateEdit5.Date:=devolverfechan(ibase.fbase.querysl.FieldByName('fecha').AsString);
  14.     TBlobField(ibase.fbase.querysl.FieldByName('publico')).SaveToStream(strStream);
  15.     RichMemo1.LoadRichText(strStream);
  16.     TBlobField(ibase.fbase.querysl.FieldByName('privado')).SaveToStream(strStream2);
  17.     RichMemo2.LoadRichText(strStream2);
  18.   end;
  19.   ibase.fbase.querysl.Close;
  20.   PageControl1.PageIndex:=2;
  21.   RichMemo1.SetFocus;
  22.   strStream.Free;
  23.   strStream2.Free;
  24. end;
  25.  
  26.  

Thanks!

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Storing richmemo text on a blob field in sqlite database
« Reply #1 on: July 16, 2018, 11:45:08 pm »
Hello all, i think the subject resumes all.
I want to store a formatted text from a richedit on a (blob) field in a sqlite database and restore it later.
I've succesfully stored an image on a blob in a mysql database but i can't make work with text.
I've tried many suggestions and tried to make modifications but dosesn't seem to work.
Something is stored but when i retrieve it the richmemo is blank.
If i retrieve as text it's incomplete i guess.
here is the code for the insertion

Code: Pascal  [Select][+][-]
  1. ...
  2.  try
  3.               strStream := TMemoryStream.Create();
  4.               strstream2:= TMemoryStream.Create();
  5.               RichMemo1.SaveRichText(strStream);
  6.               RichMemo2.SaveRichText(strstream2);              ibase.fbase.queryle.ParamByName('param1').DataType:=ftblob;
  7.               ibase.fbase.queryle.ParamByName('param1').ParamType:=ptinput;
  8.               ibase.fbase.queryle.ParamByName('param1').LoadFromStream(strStream, ftBlob);
  9.               ibase.fbase.queryle.ParamByName('param2').DataType:=ftblob;
  10.               ibase.fbase.queryle.ParamByName('param2').ParamType:=ptinput;
  11.               ibase.fbase.queryle.ParamByName('param2').LoadFromStream(strStream2, ftBlob);
  12.               cadw:='INSERT INTO presupuestos (idcliente, fecha, publico, privado) VALUES ('+label2.Caption+', "';
  13.               cadw:=cadw+devolverfecha(DateEdit5.Date)+'", :param1, :param2)';
  14.               ibase.fbase.queryle.SQL.Clear;
  15.               ibase.fbase.queryle.SQL.Add(cadw);
  16.               ibase.fbase.queryle.ExecSQL;
  17.               ibase.fbase.queryle.Close;
  18.               ShowMessage('El presupuesto fue guardado');
  19.               strStream.Free;
  20.               strStream2.Free;
  21.             except
  22.               showmessage('No se pudo guardar el presupuesto');
  23.               strStream.Free;
  24.               strStream2.Free;
  25.             end;                    
  26. ...
  27.  
  28.  

And this to retrieve it

Code: Pascal  [Select][+][-]
  1. procedure tform4.cargarpresu;
  2. var
  3.   cad: TCaption;
  4.   strStream ,strStream2: TMemoryStream;
  5. Begin
  6.   cad:='SELECT * FROM presupuestos WHERE idpresupuesto = '+label37.Caption;
  7.   ibase.fbase.querysl.SQL.Clear;
  8.   ibase.fbase.querysl.SQL.Text:=cad;
  9.   ibase.fbase.querysl.Open;
  10.   strStream := TMemoryStream.Create();
  11.   strStream2:= TMemoryStream.Create();
  12.   if not ibase.fbase.querysl.EOF then begin
  13.     DateEdit5.Date:=devolverfechan(ibase.fbase.querysl.FieldByName('fecha').AsString);
  14.     TBlobField(ibase.fbase.querysl.FieldByName('publico')).SaveToStream(strStream);
  15.     RichMemo1.LoadRichText(strStream);
  16.     TBlobField(ibase.fbase.querysl.FieldByName('privado')).SaveToStream(strStream2);
  17.     RichMemo2.LoadRichText(strStream2);
  18.   end;
  19.   ibase.fbase.querysl.Close;
  20.   PageControl1.PageIndex:=2;
  21.   RichMemo1.SetFocus;
  22.   strStream.Free;
  23.   strStream2.Free;
  24. end;
  25.  
  26.  

Thanks!
you can't set parameters before they are created. They are created after you set the correct sql command, eg.
Code: Pascal  [Select][+][-]
  1. ...
  2.  try
  3.               strStream := TMemoryStream.Create();
  4.               strstream2:= TMemoryStream.Create();
  5.               RichMemo1.SaveRichText(strStream);
  6.               RichMemo2.SaveRichText(strstream2);
  7.  
  8.               cadw:='INSERT INTO presupuestos (idcliente, fecha, publico, privado) VALUES ('+label2.Caption+', "';
  9.               cadw:=cadw+devolverfecha(DateEdit5.Date)+'", :param1, :param2');
  10.               ibase.fbase.queryle.SQL.Clear;
  11.               ibase.fbase.queryle.SQL.Add(cadw);//after setting the sql command parameters can be set.
  12.  
  13.               ibase.fbase.queryle.ParamByName('param1').DataType:=ftblob;
  14.               ibase.fbase.queryle.ParamByName('param1').ParamType:=ptinput;
  15.               ibase.fbase.queryle.ParamByName('param1').LoadFromStream(strStream, ftBlob);
  16.               ibase.fbase.queryle.ParamByName('param2').DataType:=ftblob;
  17.               ibase.fbase.queryle.ParamByName('param2').ParamType:=ptinput;
  18.               ibase.fbase.queryle.ParamByName('param2').LoadFromStream(strStream2, ftBlob);
  19.               ibase.fbase.queryle.ExecSQL;
  20.               ibase.fbase.queryle.Close;
  21.               ShowMessage('El presupuesto fue guardado');
  22.               strStream.Free;
  23.               strStream2.Free;
  24.             except
  25.               showmessage('No se pudo guardar el presupuesto');
  26.               strStream.Free;
  27.               strStream2.Free;
  28.             end;                    
  29. ...
  30.  
  31.  
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

calebs

  • Full Member
  • ***
  • Posts: 190
Re: Storing richmemo text on a blob field in sqlite database
« Reply #2 on: July 17, 2018, 04:07:33 pm »
Yes i thougth so, but ive tried before and doesn't work and reading in a forum someone said that i should try to put the parameters before. I'll try again with your suggestion tazz

calebs

  • Full Member
  • ***
  • Posts: 190
Re: Storing richmemo text on a blob field in sqlite database
« Reply #3 on: July 17, 2018, 04:41:46 pm »
Changed the code, tried and the same results. Apparently stores the content (i can't really check it out, i dont know how) but when i try to load the richedit with the db it results blank.
here is the code as you suggested

Code: Pascal  [Select][+][-]
  1. try
  2.               strStream := TMemoryStream.Create();
  3.               strstream2:= TMemoryStream.Create();
  4.               RichMemo1.SaveRichText(strStream);
  5.               RichMemo2.SaveRichText(strstream2);
  6.               cadw:='INSERT INTO presupuestos (idcliente, fecha, publico, privado) VALUES ('+label2.Caption+', "';
  7.               cadw:=cadw+devolverfecha(DateEdit5.Date)+'", :param1, :param2)';
  8.               ibase.fbase.queryle.SQL.Clear;
  9.               ibase.fbase.queryle.SQL.Add(cadw);
  10.               ibase.fbase.queryle.ParamByName('param1').DataType:=ftblob;
  11.               ibase.fbase.queryle.ParamByName('param1').ParamType:=ptinput;
  12.               ibase.fbase.queryle.ParamByName('param1').LoadFromStream(strStream, ftBlob);
  13.               ibase.fbase.queryle.ParamByName('param2').DataType:=ftblob;
  14.               ibase.fbase.queryle.ParamByName('param2').ParamType:=ptinput;
  15.               ibase.fbase.queryle.ParamByName('param2').LoadFromStream(strStream2, ftBlob);
  16.               ibase.fbase.queryle.ExecSQL;
  17.               ibase.fbase.queryle.Close;
  18.               ShowMessage('El presupuesto fue guardado');
  19.               strStream.Free;
  20.               strStream2.Free;
  21.               accion:=0;
  22.             except                

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Storing richmemo text on a blob field in sqlite database
« Reply #4 on: July 17, 2018, 04:44:25 pm »
upload a small application that shows your problem and some one will fix it for you. As things are now we have no idea if it fails to save or it fails to load.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

calebs

  • Full Member
  • ***
  • Posts: 190
Re: Storing richmemo text on a blob field in sqlite database
« Reply #5 on: July 17, 2018, 07:12:44 pm »
I have done a little project for this, it repeats the problem, retrieving the blob from database results blank.
I was thinking to try with a memo component for discover if the problem is the richedit.
If anyone can help, thanks!

calebs

  • Full Member
  • ***
  • Posts: 190
Re: Storing richmemo text on a blob field in sqlite database
« Reply #6 on: July 17, 2018, 07:44:52 pm »
With the memo component also don't work. Could be the way i'm reading from the database and assigning to the memo/richmemo?

 

TinyPortal © 2005-2018