How to convert MySQL Blob field to string for insert statement

Question

I'm creating a "Backup/Restore" function for my application and I'm having problem converting blob to a sql statement. I have this code :

function GetDatasetValues(dataSet: TDataSet): string;
var
  i: Integer;
  ms: TStream;
  ss: TStringStream;
  sTemp : string;
begin
  Result := '';

  for i := 0 to Pred(dataSet.FieldCount) do
  begin

    if not dataSet.Fields[i].IsNull then
    begin

      case dataSet.Fields[i].DataType of
        TFieldType.ftGraphic, TFieldType.ftBlob, TFieldType.ftStream:
        begin
          ms := TMemoryStream.Create;
          try
            TBlobField(dataSet.Fields[I]).SaveToStream(ms);
            ms.Position := 0;
            ss := TStringStream.Create;
            try
              TNetEncoding.Base64.Encode(ms, ss);

              Result := Result + ss.DataString + ',';
            finally
              ss.Free;
            end;
          finally
            ms.Free;
          end;
        end;
// more codes...

the result is :

insert into `tb1`(f_int,f_varchar,f_blob) values 
(1,'1',e1xydGYxXGFuc2lcZGVmZjB7XGZvbnR0Ymx7XGYwXGZuaWwgXCdjZVwnYTJcJ2M4XCdlZFwnZDFc
J2M1XCdiYVwnZGE7fXtcZjFcZm5pbFxmY2hhcnNldDEzNCBcJ2NlXCdhMlwnYzhcJ2VkXCdkMVwn
YzVcJ2JhXCdkYTt9fQ0Ke1xjb2xvcnRibCA7XHJlZDMyXGdyZWVuMzFcYmx1ZTUzO30NCntcKlxn
ZW5lcmF0b3IgTXNmdGVkaXQgNS40MS4yMS4yNTEwO31cdmlld2tpbmQ0XHVjMVxwYXJkXGNmMVxs
YW5nMjA1MlxmMFxmczE4IFQwMzE3MCB4eSBTaW1vbiBcZjFcJ2I2XCdhM1wnYjVcJ2IxXCdjM1wn
YTggXCdiN1wnYzVcJ2NhXCdkNlwnYzhcJ2E1XCdkN1wnZjZcJ2EzXCdhY1wnYzFcJ2VkXCdjZFwn
ZTJcJ2QwXCdjMlwnY2FcJ2MwXCdiZFwnZTdcJ2IyXCdiYlwnZDNcJ2MzXCdiNVwnYzhcJ2I1XCdi
ZFwnYzhcJ2FiXCdiMlwnYmZcJ2JiXCdkOFwnYjFcJ2JlXCdkNFwnZDlcJ2I3XCdkNlwnYmFcJ2Vj
XHBhcg0KXHBhcg0KVDAzMTcwIFwnYzFcJ2JkXCdiOFwnZjZcJ2NhXCdjMlwnYzdcJ2U5XHBhcg0K
XHBhcg0KMVwnYTFcJ2EyXCdkMFwnYzJcJ2NhXCdjMFwnYmRcJ2U3XCdiMlwnYmJcJ2QzXCdjM1wn
YjVcJ2M4XCdiNVwnYmRcJ2M4XCdhYlwnYjJcJ2JmXCdiYlwnZDhcJ2IxXCdiZVwnZDRcJ2Q5XCdi
N1wnZDZcJ2JhXCdlY1wnYjBcJ2M5XCdhM1wnYWNcJ2JmXCdjOVwnZDJcJ2Q0XCdiZlwnYWFcJ2Nh
XCdiY1wnYzJcJ2JkXCdkMFwnZjhcJ2M0XCdjM1wnYjNcJ2Y2XCdkMlwnYmJcJ2IyXCdiZlwnYjdc
J2Q2XCdkN1wnZjZcJ2I3XCdkNlwnYmFcJ2VjXCdhM1wnYWNcJ2QyXCdiYlwnYjJcJ2JmXCdiN1wn
ZDZcJ2JiXCdiOVwnY2RcJ2I2XCdkN1wnY2FcJ2JmXCdlZVwnYTNcJ2FjXCdjNFwnZTNcJ2M4XCdh
NVwnY2ZcJ2ViXCdjZlwnZWJcJ2I3XCdiZFwnYjBcJ2I4XCdhMVwnYTNccGFyDQpccGFyDQoyXCdh
MVwnYTJ4eVwnY2ZcJ2VlXCdjNFwnYmZcJ2EzXCdhY1wnYjdcJ2M1XCdjYVwnZDZcJ2JkXCdiYlwn
YjhcJ2Y4XCdjNFwnZTNcJ2JhXCdjZFwnYjZcJ2EzXCdiNVwnYjFcJ2MzXCdhOFwnYzhcJ2E1XCdk
N1wnZjZcJ2EzXCdhY1wnYmZcJ2M5XCdkMlwnZDRcJ2I0XCdmM1wnZDZcJ2MyXCdkMFwnYjRcJ2I4
XCdmNlwnYjdcJ2EyXCdkNVwnYjlcJ2I3XCdiZFwnYjBcJ2I4XCdiOFwnZjhcJ2NlXCdkMlwnYTFc
J2EzXHBhcg0KXGYwXHBhcg0KfQ0KAA==);

the converted blob field failed to be executed in mysql browser. it says syntax error. when tried to backup the database using heidsql backup function, the sql it produced is :

insert INTO `tb1` (`f_int`, `f_varchar`, `f_blob`) VALUES
    (1, '1', _binary 0x7B5C727466315C616E73695C64656666307B5C666F6E7474626C7B5C66305C666E696C205C2763655C2761325C2763385C2765645C2764315C2763355C2762615C2764613B7D7B5C66315C666E696C5C6663686172736574313334205C2763655C2761325C2763385C2765645C2764315C2763355C2762615C2764613B7D7D0D0A7B5C636F6C6F7274626C203B5C72656433325C677265656E33315C626C756535333B7D0D0A7B5C2A5C67656E657261746F72204D7366746564697420352E34312E32312E323531303B7D5C766965776B696E64345C7563315C706172645C6366315C6C616E67323035325C66305C66733138205430333137302078792053696D6F6E205C66315C2762365C2761335C2762355C2762315C2763335C276138205C2762375C2763355C2763615C2764365C2763385C2761355C2764375C2766365C2761335C2761635C2763315C2765645C2763645C2765325C2764305C2763325C2763615C2763305C2762645C2765375C2762325C2762625C2764335C2763335C2762355C2763385C2762355C2762645C2763385C2761625C2762325C2762665C2762625C2764385C2762315C2762655C2764345C2764395C2762375C2764365C2762615C2765635C7061720D0A5C7061720D0A543033313730205C2763315C2762645C2762385C2766365C2763615C2763325C2763375C2765395C7061720D0A5C7061720D0A315C2761315C2761325C2764305C2763325C2763615C2763305C2762645C2765375C2762325C2762625C2764335C2763335C2762355C2763385C2762355C2762645C2763385C2761625C2762325C2762665C2762625C2764385C2762315C2762655C2764345C2764395C2762375C2764365C2762615C2765635C2762305C2763395C2761335C2761635C2762665C2763395C2764325C2764345C2762665C2761615C2763615C2762635C2763325C2762645C2764305C2766385C2763345C2763335C2762335C2766365C2764325C2762625C2762325C2762665C2762375C2764365C2764375C2766365C2762375C2764365C2762615C2765635C2761335C2761635C2764325C2762625C2762325C2762665C2762375C2764365C2762625C2762395C2763645C2762365C2764375C2763615C2762665C2765655C2761335C2761635C2763345C2765335C2763385C2761355C2763665C2765625C2763665C2765625C2762375C2762645C2762305C2762385C2761315C2761335C7061720D0A5C7061720D0A325C2761315C27613278795C2763665C2765655C2763345C2762665C2761335C2761635C2762375C2763355C2763615C2764365C2762645C2762625C2762385C2766385C2763345C2765335C2762615C2763645C2762365C2761335C2762355C2762315C2763335C2761385C2763385C2761355C2764375C2766365C2761335C2761635C2762665C2763395C2764325C2764345C2762345C2766335C2764365C2763325C2764305C2762345C2762385C2766365C2762375C2761325C2764355C2762395C2762375C2762645C2762305C2762385C2762385C2766385C2763655C2764325C2761315C2761335C7061720D0A5C66305C7061720D0A7D0D0A00);

and can be executed in mysql broser. So how can I convert BLOB to an executable sql string in mysql browser? or how can I achieve heidisql way's converting blob to string?

Geoff · Accepted Answer · 2019-06-29 11:57:38Z

3

This should give you a good start. The encoding is not base64, but simply converting the bytes to a hex string.

function BinToHex(Buffer: array of BYTE; BufSize: Integer):string;
var
  I: Integer;
  const Digits:array[0..15] of char='0123456789abcdef';
begin
  for I := 0 to BufSize - 1 do
    begin
      Result := Result + digits[Buffer[i] shr 4]+digits[Buffer[i] and $0F];
   end;
end;


function GetDatasetValues(dataSet: TDataSet): ansistring;
var
  i: Integer;
  ms: TStream;
  ss: TStringStream;
  sTemp : string;
  bytes : TBYTES;
begin
  Result := '';

  for i := 0 to Pred(dataSet.FieldCount) do
  begin

    if not dataSet.Fields[i].IsNull then
    begin

      case dataSet.Fields[i].DataType of
        TFieldType.ftGraphic, TFieldType.ftBlob, TFieldType.ftStream:
        begin
          ms := TMemoryStream.Create;
          try
            TBlobField(dataSet.Fields[I]).SaveToStream(ms);
            ms.Position := 0;
            ss := TStringStream.Create;
            try
              ms.Read(bytes,ms.Size);
              Result := BinToHex(bytes,ms.Size);
            finally
              ss.Free;
            end;
          finally
            ms.Free;
          end;
        end;
      end;
    end;
  end;
end;

answered Jun 29, 2019 at 11:57

Geoff

411 bronze badge

Sign up to request clarification or add additional context in comments.

3 Comments

Ago Over a year ago

just tried and I'm getting access violation in this line ms.Read(bytes,ms.Size);

Ago Over a year ago

i was able to make it work, I added this line and there is no longer access violation SetLength(bytes, ms.Size);

Ago Over a year ago

i was very happy when I was able to convert blob to an executable sql script. but when tested to the production database, blob data are not successfully restored. I'm not yet sure where did it go wrong. I will be updating my question once I found how to replicate the issue

Ago · Accepted Answer · 2019-07-01 07:36:19Z

i checked heidisql's source code and mixed Geoff's initial code and came up with this

function GetDatasetValues(dataSet: TDataSet): string;
var
  i: Integer;
  ms: TStream;
  sTemp : string;
  bytes : TBytes;

  function HexValue(var ByteData: TBytes): String;
  (*
   referenced source : heidisql
   unit : dbconnection
   function : TDBQuery.HexValue(var ByteData: TBytes):
  *)
  var
    BinLen: Integer;
    Ansi: AnsiString;
  begin
    BinLen := Length(ByteData);
    SetString(Ansi, PAnsiChar(ByteData), BinLen);

    if BinLen = 0 then
    begin
      Result := 'null';
    end
    else
    begin
      SetLength(Result, BinLen*2);
      BinToHex(PAnsiChar(Ansi), PChar(Result), BinLen);
      Result := '_binary 0x' + Result;
    end;

  end;
begin
  Result := '';

  for i := 0 to Pred(dataSet.FieldCount) do
  begin

    if not dataSet.Fields[i].IsNull then
    begin

      case dataSet.Fields[i].DataType of
        TFieldType.ftGraphic, TFieldType.ftBlob, TFieldType.ftStream:
        begin

          ms := TMemoryStream.Create;
          try
            TBlobField(dataSet.Fields[I]).SaveToStream(ms);
            ms.Position := 0;

            SetLength(bytes, ms.Size);
            ms.Read(bytes, ms.Size);

            Result := Result + HexValue(bytes) + ',';

          finally
            ms.Free;
          end;
        end;
// more codes

tested in production database and worked..

Collectives™ on Stack Overflow

How to convert MySQL Blob field to string for insert statement

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related