Recent

Author Topic: SQLdb with PostgreSQL blob  (Read 6029 times)

goodname

  • Sr. Member
  • ****
  • Posts: 297
SQLdb with PostgreSQL blob
« on: March 04, 2012, 10:23:05 pm »
It took me some time to figure the following out so though I would post the information here as it may help someone else. Would put it into the wiki but not sure where.

Lazarus Data Controls see varchar(256) or longer and text fields as blobs. To use text fields cast them as varchar(255) or shorter and Lazarus data controls will see strings instead of blobs. Using text fields is recommended since varchar and char fields are just text fields with extra checks and limits. This is not the case for most other database engines.

Code: [Select]
SELECT CAST(textfield AS varchar(255)) AS stringName FROM tbl;
The PostgreSQL text and bytea (byte array) data types are similar. Text does not allow "non-printable" octets (usually, octets outside the range 32 to 126) while bytea does. PostgreSQL has built in functions to encode binary to text and decode from text to binary. Storing binary as encoded text requires more processing and storage space so storing in bytea is recommended.

Since FPC 2.6.0 SQLdb has linked ftBlob with the bytea data type. The ftBlob data is sent and received in binary so there is no need to worry about the PostgreSQL bytea escape or hex formats.

Save an image from a TImage(imgSrc) control to a bytea or text field.
Code: [Select]
procedure TFrmMain.BtnSaveImgClick(Sender: TObject);
var strm: TmemoryStream;
begin
  strm:=TMemoryStream.Create;
  imgSrc.Picture.SaveToStream(strm);
  //Use the following for bytea fields FPC 2.6.0 and up.
  SQLImageSave.SQL.Text:='UPDATE tbl SET img=:i WHERE id=1;';
  //Use the following for text fields. base64, hex, or escape
  SQLImageSave.SQL.Text:='UPDATE tbl SET img=encode(:i,'base64') WHERE id=1;';
  SQLImageSave.Params[0].LoadFromStream(strm,ftBlob);
  try
   SQLImageSave.ExecSQL;
   SQLTranImg.Commit;
  except
   on E : Exception do begin
    SQLTranImg.Rollback;
    ShowMessage('Save image error:'+E.Message);
   end;
  end;
  strm.Free;
end;
Read an image from a bytea or text field and display it in a TImage(imgOut) control.
Code: [Select]
procedure TFrmMain.BtnLoadImgClick(Sender: TObject);
var strm: Tstream;
begin
  //Use the following for bytea fields.  FPC 2.6.0 and up
 SQLImgLoad.SQL.Text:='SELECT img FROM tbl WHERE id=1;' ;
  //Use the following for text fields. base64, hex, or escape
 SQLImgLoad.SQL.Text:='SELECT decode(img,'base64') FROM tbl WHERE id=1;' ;
 try
   SQLImgLoad.Open;
   strm:=SQLImgLoad.CreateBlobStream(SQLImgLoad.Fields[0],bmRead);
   imgOut.Picture.LoadFromStream(strm);
   strm.Free;
 except
  on E : Exception do begin
   ShowMessage('Read image error:'+E.Message);
  end;
 end;
end;