How to store PDF files in MySQL ?

[axilus]

Hello,
How to store and retrieve(download) PDF files in MySQL using uniQuery ?
and what's the field type is needed?
Thanks

[FCS]

Hello,

For PosgreSQL:
filed type = BYTEA

TBlobField(UQ_Blob.FieldByName('xxxxx')).SaveToFile( );
TBlobField(UQ_Blob.FieldByName('xxxxx')).LoadFromFile( );

For MySQL try:
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, Spatial Data Types
https://www.devart.com/unidac/docs/?data_types.htm

You probably should to set the size of blob data field in MySQL server.
File: my.ini
[mysqld]
max_allowed_packet=33M

Regards
Michal

[ViktorV]

To have an ability to save PDF in MySQL, you should declare a LONGBLOB field in your table. Then for saving PDF in the database, you will be able to use the following code:

Code: Select all

var
  BlobField: TBlobField;
...
  BlobField := MyQuery.FieldByName('PDFField') as TBlobField;
  BlobField.LoadFromFile(FileName);

And for retrieving PDF from the database, you will be able to use the following code:

Code: Select all

  TBlobField(MyQuery.FieldByName('PDFField')).SaveToFile(FileName);

[axilus]

Hello,
it dosn't work !
can you write a complete code ?
Thanks

[FCS]

Hello,

What error message do you get and where ?

Did you try to add this PDF outside UniDac project ? Maybe the definition of the table or database is wrong ? Did you check the parameter of MySQL database ? Maybe the field size is too small to store the PDF file ?

For PostgreSQL I write blobs like this

Code: Select all

function Write_BLOB(Plik:string): LongInt;
var
  UQ_Blob : TUniQuery;
begin
  Result:=0;
  if (Plik='') or (not FileExists(Plik)) then EXIT;

  try
    UQ_Blob:=TUniQuery.Create(nil);
    UQ_Blob.Connection:=UniConnection1;
    UQ_Blob.SQL.Clear;
    UQ_Blob.SQL.Add('SELECT * FROM t_blob  WHERE id  = 0' );
    UQ_Blob.Options.DefaultValues:=true;
    UQ_Blob.Open;
    UQ_Blob.Append;
    
    TBlobField(UQ_Blob.FieldByName('P_Blob_Data')).LoadFromFile(Plik);

    UQ_Blob.Post;
    // UQ_Blob.RefreshRecord;  //the id field is serial type (autoinc), in PostrgeSQL it is updated after post  
    Result:=UQ_Blob.FieldByName('id').AsInteger;

    UQ_Blob.Close;
    UQ_Blob.Free;

  except
    msgBox('Problem ....');
  end;
end;

Regards
Michal

[axilus]

hi,
did not work !
can you make sample and send it here?

[ViktorV]

Here is an example of console application that saves a pdf file in the MySQL database and then extracts the file from it:

Code: Select all

program Test_PDF;
{$APPTYPE CONSOLE}

uses
  System.SysUtils,
  Uni,
  MySQLUniProvider;

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;

begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ProviderName := 'MySQL';
    UniConnection.Server := Server;
    UniConnection.Port := Port;
    UniConnection.Username := UserName;
    UniConnection.Password := Password;
    UniConnection.Database := Database;
    UniConnection.Connect;
    UniConnection.ExecSQL('CREATE TABLE IF NOT EXISTS TEST_PDF (ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, PDF_FIELD LONGBLOB);');
    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.SQL.Text := 'SELECT * FROM TEST_PDF';
      UniQuery.Open;
      UniQuery.Append;
      TBlobField(UniQuery.FieldByName('PDF_FIELD')).LoadFromFile(FileName);
      UniQuery.Post;
      WriteLn('Append Ok');
      TBlobField(UniQuery.FieldByName('PDF_FIELD')).SaveToFile(FileName);
      WriteLn('Save Ok');
    finally
      UniQuery.Free;
    end;
  finally
    UniConnection.Free;
  end;
end.

[axilus]

Hello,
it works fine !
thanks a lot

[ViktorV]

It is good to see that the issue has been solved. Feel free to contact us if you have any further questions about UniDAC.