Insert image on blobfiled mysql

Question

0.00/5 (No votes)

See more:

MySQL 8
FireDac Components (FDQuery + FDPhyMySqlLink)

Table with two field a BLOB and a TINYBLOB

The image is 7384 byte

When execute the query the error is :

[Firedac][Phys][MySql] data too long for column immagine at row 1

from the MySqlWordBench the operation on Field Blob /TinyBlob the Image is Inserted on DataBase

What I have tried:

 MemStream: TMemoryStream;

begin
  Image1.Picture.LoadFromFile(sFile);

  MemStream := TMemoryStream.Create;
  try
    Image1.Picture.SaveToStream(MemStream);
    MemStream.Seek(0,0);
    FDQuery1.SQL.Text := 'INSERT INTO tb_test (immagine1) VALUES (:img)';
    FDQuery1.ParamByName('img').LoadFromStream(MemStream, ftBlob);
    FDQuery1.ExecSQL;

  finally
    MemStream.Free;
  end;

Posted 22-Jul-19 2:37am

Member 13174280

Updated 22-Jul-19 7:50am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 13174280 · Answer 1 · 2019-07-22T07:51:00

Fixed (almost, still a problem with 5 Mb files - eg: 4.987.904 should go smoothly on MEDIUMBLOB instead
Lost Connection during query... to understand, already with 2.7 Mb files you
has the exception).


in MySql
{
TINYBLOB   :     maximum length of 255 bytes
BLOB       :     maximum length of 65,535 bytes
MEDIUMBLOB :     maximum length of 16,777,215 bytes
LONGBLOB   :     maximum length of 4,294,967,295 bytes
}

procedure Tfrm_Articoli.InserisceImmagine(sFile: string);
var jp:TJpegImage;
    MemStream: TMemoryStream;
begin
  sFile := '<foto da inserire>';

  FDQuery1.Open;
  FDTransaction1.StartTransaction;

  //Medium Blob
  sFile := '<foto da inserire>';

  FDTransaction1.StartTransaction;

  try
    jp := TJpegImage.Create;
    jp.LoadFromFile(sFile);
    FDQuery1.Active := true;
    FDQuery1.Insert;
    FDQuery1.FieldByName('immagine1').Assign(jp);
    FDQuery1.Post;
    FreeAndNil(jp);
    FDTransaction1.Commit;
  except
    FDTransaction1.RollBack;
  end;

//versione SQL

  MemStream := TMemoryStream.Create;

  sFile := '<foto da inserire>';
  Image1.Picture.LoadFromFile(sFile);

  Image1.Picture.SaveToStream(MemStream);
  MemStream.Seek(0,0);
  FDQuery1.SQL.Text := 'INSERT INTO tb_test (immagine1) VALUES (:img)';
  FDQuery1.ParamByName('img').LoadFromStream(MemStream, ftBlob);
  FDQuery1.ExecSQL;

..//
end;