• Registered: 2015-10-12
  • Posts: 84

Topic: Storing a jpeg image Blob in the database

I would appreciate it if someone could help me solve the following problem:

I have a table with 2 fields. One is an image field and the other is it's name.
In the first field I want to store the image in blob format and in the second it's name.

How can I do it with a script? I cannot find out how to store the image as blob in the database.

Thank you
George

  • From: Нефтеюганск
  • Registered: 2018-04-11
  • Posts: 1,775

Re: Storing a jpeg image Blob in the database

вырезал из проекта, надеюсь разберетесь

// кнопка загрузки изображения
procedure frmQuestion_bEditImageQuestion_OnClick (Sender: TObject; var Cancel: boolean);
var
  sSQL,loadIDImage: string;
  sFileName: string;
  sFieldName: string;
  oDlg:TOpenDialog;
  Params: TParams;
  MemoryStream: TMemoryStream;
  Param: TParam;
begin
if (frmQuestionList.tgQuestionList.SelectedRow<>-1) then begin
  oDlg:=TOpenDialog.Create(frmMain);
  oDlg.DefaultExt := '.jpg';
  oDlg.Filter := 'Image files|*.jpg|*.png';
  if (oDlg.Execute) then begin
      sFileName := oDlg.FileName;
      sFieldName := 'image';

      if (frmQuestion.TagString='questionEdit') then sSQL := 'UPDATE Question SET ' +sFieldName+'= :'+sFieldName+', '+sFieldName+'_filename="'+ExtractFileName(sFileName)+'" WHERE id='+idQ;
      if (frmQuestion.TagString='descriptQuestionEdit') then sSQL := 'UPDATE questionDescript SET ' +sFieldName+'= :'+sFieldName+', '+sFieldName+'_filename="'+ExtractFileName(sFileName)+'" WHERE id_Question='+idQ;
      if (frmQuestion.TagString='answerEdit') then sSQL := 'UPDATE resultSetting SET ' +sFieldName+'= :'+sFieldName+', '+sFieldName+'_filename="'+ExtractFileName(sFileName)+'" WHERE id=(SELECT numberResult FROM otherSetting WHERE id=1)';

      Params := TParams.Create(nil);

      MemoryStream := TMemoryStream.Create;
      MemoryStream.LoadFromFile(sFileName);
      MemoryStream.Position := 0;
      Params.CreateParam(ftBlob, sFieldName, ptInput).LoadFromStream(MemoryStream, 15);

      frmStartProject.SQLConnection.Execute(sSQL, Params);
  end;
  MemoryStream.Free;
  Params.Free;
  oDlg.Free;
  end;
  questionLoad;
end;
  • Registered: 2015-10-12
  • Posts: 84

Re: Storing a jpeg image Blob in the database

Thank you for your reply.

I will try it over the next days and report back.

Regards
George

  • Registered: 2015-10-12
  • Posts: 84

Re: Storing a jpeg image Blob in the database

Unfortunately I did not manage to make it work.

It should be simple but I am not a professional programmer.

I looked at your code and searched online also but I could not make it work.

The question is simple. I have a table with 4 fields. One of them is for storing an image in BLOB format.

I have all the nessacery data to insert a new table entry but I do not know how to formulate the sql command.

In the attached image you can see the 4 fields.

id                             >>> id of record
Picture                     >>> this is where the BLOB picture must be stored
Picture_filename     >>> the path of the stored picture
id_Vehicles              >>> the id of a connected record in another table

Can anyone help me?

Thank you
George

Post's attachments

Attachment icon database.png 22.06 kb, 127 downloads since 2021-01-01 

  • Registered: 2015-10-12
  • Posts: 84

Re: Storing a jpeg image Blob in the database

Back again.

I managed to do it based on Drivesofts previous post on another thread. It is the same as the one proposed by sibprogsistem but it is formulated as a stand alone procedure.

procedure SaveFileToDatabase (Tablename, Fieldname, Filename: string; id: integer);
var
    sSQL: string;
    sFileName: string;
    sFieldName: string;

    Params: TParams;
    MemoryStream: TMemoryStream;
    Param: TParam;
begin
    sSQL := 'UPDATE '+Tablename+' SET ' +Fieldname+'= :'+Fieldname+', '+Fieldname+'_filename="'+ExtractFileName(Filename)+'" WHERE id='+IntToStr(id);
    Params := TParams.Create(nil);


    // BLOB
    MemoryStream := TMemoryStream.Create;
    MemoryStream.LoadFromFile(Filename); // load file to memory
    MemoryStream.Position := 0;
    Params.CreateParam(ftBlob, Fieldname, ptInput).LoadFromStream(MemoryStream, 15);  // 15 ftBlob (TBlobType)

    Form1.SQLConnection.Execute(sSQL, Params);

    MemoryStream.Free;
    Params.Free;
end;

Thank you for your help.
Regrads
George