JWan Wiki

a progressbar can be created and use filepos as its position Create mysql connection prior to reading the file

Getting Size of BLOB (ie: file size):

SELECT OCTET_LENGTH(filecontent) AS filesize FROM picfile;

Getting substring of a blob (or any string):

SELECT SUBSTRING("SQL Tutorial", 5, 5) AS ExtractString;  <<= Tutor
SELECT SUBSTRING("SQL Tutorial", -5, 5) AS ExtractString;  <<= orial
SELECT SUBSTRING("SQL Tutorial", -8, 2) AS ExtractString;  <<= Tu

If extracting binary file, need to do conversion to binary format rather than hex (0x) format. Do note that the chunk size should be x2 since the result in hexadecimal format wherein each character is in 2 character hexa code:

SELECT UNHEX(SUBSTRING(HEX(filecontent), 0, 16000*2)) AS partialchunk FROM picfile;

Assign fstream as a global variable

var
  Fstream: TFileStream;

To get the Blob in chunks:

function Tformsbsystemloader.downloadsystem:boolean;
var
  filepos   : Int64;
  fullsize  : Int64;
  partsize  : Int64;
  s : string;
  filename  : string;
begin  
  mysqld.Active  := false;
  mysqld.SQL.text:= 'SELECT filename,OCTET_LENGTH(filebinary) AS blobsize FROM file';
  mysqld.Active  := True;
  fullsize       := StrToInt(mysqld.FieldValueByFieldName('blobsize'));
  filename       := mysqld.fieldvaluesbyfieldname('filename');
  filepos        := 1;
  partsize       := 0;
  DeleteFile(filename);  // just making sure
  if FileExists(FileName) then Fstream := TFileStream.Create(FileName, fmOpenReadWrite or fmShareDenyWrite) 
  else FStream := TFileStream.Create(FileName, fmcreate);
  FStream.Seek(0, soEnd); // Move to the end of the file
  repeat
     filepos := filepos + partsize;
     try
        mysqld.active   := false;
        // specify the chunk size, better divisible by 1K (1024) //
        mysqld.SQL.Text := 'SELECT (SUBSTRING(hex(filebinary),'+inttostr(filepos)+',1024)) as partials FROM file';
        mysqld.Active   := true;
     finally
     end;
     s := mysqld.FieldValueByFieldName('partials');
     partsize := length(s);
     AppendToFile(s);
  until partsize =0;
  Fstream.free;
  result := GetFileSize(filename)=fullsize;
end;  

procedure appendtoFile(const HexStr: string);
const
  HexDigits = '0123456789abcdef';
var
  I, Hi, Lo: Integer;
  ByteValue: Byte;
  Buffer: array of Byte;
begin
  if Length(HexStr) mod 2 <> 0 then raise Exception.Create('Invalid HexStr length');
  SetLength(Buffer, Length(HexStr) div 2);
  for I := 1 to Length(Buffer) do begin
    Hi := Pos(LowerCase(HexStr[(I - 1) * 2 + 1]), HexDigits) - 1;
    Lo := Pos(LowerCase(HexStr[(I - 1) * 2 + 2]), HexDigits) - 1;
    if (Hi < 0) or (Lo < 0) then raise Exception.Create('Invalid character in HexStr');
    ByteValue := (Hi shl 4) or Lo;
    Buffer[I - 1] := ByteValue;
  end;
  Fstream.Write(Buffer[0], Length(Buffer));
end;