[SOLVED] Error saving image (bytea) to a PostgreSQL database
lazarus / 16-Databases

----------------------------------------------------------------------
Sakkie · 2023-10-26 10:33

I get the following error when I save an image to the PostgreSQL
database:

'Execution of query failed  (PostgreSQL: ERROR:  invalid byte sequence
for encoding "UTF8": 0xff'#$0A#$0D#$0A' Severity: ERROR'#$0D#$0A'SQL
State: 22021'#$0D#$0A 'Primary Error: invalid byte sequence for
encoding "UTF8": 0xff)'

I have been battling with this for 2 days now. If I exclude the image
everything saves successfully on the database. The data type of the
image column on the database is bytea.

I am using Lazarus 2.2.6 (FPC 3.2.2) on Windows 10 with a local
database (PostgreSQL 14). I have also tried with a database (also
PostgreSQL 14) on a remote server (Ubuntu 22.04) and the result is the
same.

I have displayed various images (with different formats) on the form
as a test to see if they load properly into the memory stream and all
is well. The only issue is when I save to the database.

I have also read various threads on this forum about saving images to
a PostgreSQL database and used some pointers there but nothing has
worked. Maybe it could be my code but I can't seem to find the
problem.

Below is my FP code:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TfrmCompanyDetails.btnCompanyLogoAttach(Sender: TObject);var  booFlag:
boolean = True;  strmLogo : TMemoryStream;  strLogoFile, strLogoType,
strError : string;  strList : tstringlist;begin 
diaCompanyLogoFile.Title:= 'Select the company logo file'; 
diaCompanyLogoFile.Filter:= 'Images |*.jpg;*.png;*.jpeg'; 
diaCompanyLogoFile.FileName:= '';  if diaCompanyLogoFile.Execute then
begin    strmLogo := TMemoryStream.Create;    try     
strmLogo.LoadFromFile(diaCompanyLogoFile.FileName);    except      on
E : Exception do begin         booFlag := False;       
Application.MessageBox(PChar(E.Message), 'Error!!!');      end;   
end;    if booFlag then begin      try        conQuant.Connected :=
True;      except        on E : Exception do begin          booFlag :=
False;          Application.MessageBox(PChar(E.Message),'Error!!!'); 
     end;      end;    end;    if booFlag then begin      strList :=
tstringlist.create;      Assert(Assigned(strList)) ;     
strList.Clear;      strList.StrictDelimiter := true;     
strList.Delimiter := '.';      strList.DelimitedText :=
diaCompanyLogoFile.Filename;      strLogoType :=
LowerCase(strList[strList.Count-1]);      strList.Clear;     
strList.StrictDelimiter := true;      strList.Delimiter := '\';     
strList.DelimitedText := diaCompanyLogoFile.Filename;      strLogoFile
:= strList[strList.Count-1];      strList.Free;         
strmLogo.Seek(0,soFromBeginning);      qryRecord.Clear;     
qryRecord.SQL.Text := 'call
pro_company_ins_image(:company_id,:logo,:logo_name,:logo_type)';     
qryRecord.Params.ParamByName('company_id').AsInteger := intCompanyID; 
   
qryRecord.Params.ParamByName('logo').LoadFromStream(strmLogo,ftBlob); 
    // *** I have tried the line below      // ==> qryRecord.Params.Pa
ramByName('logo').LoadFromFile(diaCompanyLogoFile.FileName,ftBlob);  
  // *** I have even tried this one below      // ==>
qryRecord.Params.ParamByName('logo').AsBlob := strmLogo.Memory;      
qryRecord.Params.ParamByName('logo_name').AsString := strLogoFile;   
 qryRecord.Params.ParamByName('logo_type').AsString := strLogoType;  
  tsnLine.Active := True;      try        qryRecord.ExecSQL;       
tsnLine.Commit;      except        on E : Exception do begin         
strError := E.Message;          booFlag := false;         
tsnLine.Rollback;        end;      end;      tsnLine.Active := False; 
    if booFlag then        Application.MessageBox('The logo was
successfully saved.','Confirmation!!!')       else       
Application.MessageBox(PChar(strError),'Error!!!');         end;     
conQuant.Connected := False;      strmLogo.Free;  end;end;           
****

PostgreSQL procedure code:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE OR
REPLACE PROCEDURE public.pro_company_ins_image(        IN comp_id
integer,        IN logo_file bytea,        IN logo_name text,       
IN logo_type text)LANGUAGE 'plpgsql'AS $BODY$BEGIN   delete from logo
where cmpid=comp_id;  insert into logo(cmpid,logox,lognm,lotyp)
values(comp_id,logo_file,logo_name,logo_type);END 
I have even replaced "logo_file" with "encode(logo_file,'base64')" in
the values but it didn't help.

----------------------------------------------------------------------
Sakkie · 2023-11-02 06:53

I solved the problem by inserting the image directly instead of using
the procedure. Retrieving it using a function also failed but a direct
select works. This means there is an issue with how PostgreSQL
procedures and functions handle bytea encoding as this problem also
arose when I tried to save text, Microsoft Word/Excel, and pdf files
as a test.