Differences

This shows you the differences between two versions of the page.

--- sqlblobtofile [2025/03/08 09:05] – jwan
+++ sqlblobtofile [2025/10/10 10:15] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-Create a progressbar called PB
+** a progressbar can be created and use filepos as its position
-Create mysql connection prior to reading the file
+** Create mysql connection prior to reading the file
-<code>
-procedure TForm1.btn1Click(Sender: TObject);
-var
-  BinStream: TFileStream;
-  Buffer: array[0..8191] of Byte; // 8KB buffer
-  BytesRead, TotalBytesRead, BlobSize: Int64;
-  BlobField: TBlobField;
-  BlobStream: TStream;
-begin
-  query.sql.clear;
-  query.sql.text := 'select filesubname,receiptfile from pic where fileid=1';
-  query.active   := true;
-  BlobField      := TBlobField(query.FieldByName('receiptfile'));
-  BlobSize       := BlobField.BlobSize;
-  BinStream      := tfilestream.create('z:\test.jpg',fmcreate);
-  PB.Max         := BlobSize;
-  PB.Position    := 0;
-  TotalBytesRead := 0;
-  BlobStream     := query.CreateBlobStream(BlobField, bmRead);
-  try
-     while TotalBytesRead < BlobSize do begin
-         BytesRead := BlobStream.Read(Buffer, SizeOf(Buffer));
-         if BytesRead = 0 then Break;
-         BinStream.Write(Buffer, BytesRead);
-         TotalBytesRead := TotalBytesRead + BytesRead;
-         Pb.Position := TotalBytesRead;
-         Application.ProcessMessages; // Update the progress bar
-    end;
-  finally
-  end;
-  BlobStream.Free;
-  BinStream.Free;
-end;
-</code>
 Getting Size of BLOB (ie: file size):
 <code>
-SELECT OCTET_LENGTH(filecontent) AS filesize FROM picfile
+SELECT OCTET_LENGTH(filecontent) AS filesize FROM picfile;
 </code>
@@ Line 49: / Line 12: @@
 SELECT SUBSTRING("SQL Tutorial", -5, 5) AS ExtractString;  <<= orial
 SELECT SUBSTRING("SQL Tutorial", -8, 2) AS ExtractString;  <<= Tu
+</code>
+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:
+<code>
+SELECT UNHEX(SUBSTRING(HEX(filecontent), 0, 16000*2)) AS partialchunk FROM picfile;
+</code>
+Assign fstream as a global variable
+<code>
+var
+  Fstream: TFileStream;
+</code>
+To get the Blob in chunks:
+<code>
+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;
+</code>
+<code>
+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;
 </code>