Forum > Databases

Dumping Mariadb table data with zeos.

(1/1)

BSaidus:
Hello.
I want to generate SQL insert for a mariadb table using zeos component.
Table contains Numbers, Dates, DateTimes, Booleans ( tinyint), Memo's & Blob for images.
I have a Form with SynEdit to display SQL insert for Every record returned with query

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT* FROM tablename
here is the code:

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure TForm1.Button3Click(Sender: TObject);var  I, j, fCount: Integer;  LsValue: String;begin    with ZQ_nrec do    begin      Close;      SQL.Text := SysUtils.Format(C_Nsql, ['uc_ptnts_test']);      Open;       if RecordCount = 0 then                              // Not records      begin        Exit;      end;       fCount := FieldCount - 1;      First ;                                             // First      while not Eof do                                    // loop until the end      begin        LsValue := '';        for j := 0 to fCount do        begin          Application.ProcessMessages;          if j<>fCount then          begin            if Fields[j].DataType = ftBlob then            begin              if not Fields[j].IsNull then                LsValue := LsValue + QuotedStr(( Fields[j].AsString )) + ' ,'              else LsValue := LsValue + ' NULL ,' ;            end            else            if Fields[j].DataType in [ftInteger, ftLargeint, ftBoolean] then            begin              if not Fields[j].IsNull then                LsValue := LsValue +  IntToStr( Fields[j].AsLargeInt ) + ' ,'              else LsValue := LsValue + ' NULL ,';            end            else            if Fields[j].DataType in [ftDate, ftTime, ftDateTime] then            begin              if not Fields[j].IsNull then                LsValue := LsValue +  QuotedStr( FormatDateTime( 'yyyy-mm-dd hh:nn:ss', Fields[j].AsDateTime ) )+ ' ,'              else LsValue := LsValue + ' NULL ,';            end            else            begin              if not Fields[j].IsNull then                LsValue := LsValue + QuotedStr( Fields[j].AsString ) + ' ,'              else LsValue := LsValue + ' NULL ,';            end;          end          else          begin            if not Fields[j].IsNull then              LsValue := LsValue + QuotedStr( Fields[j].AsString )            else LsValue := LsValue + ' NULL ';          end;        end;         LsValue := SysUtils.Format( C_Isql, ['uc_ptnts_test']) + LsValue + ');';        SynEdit1.Append(LsValue);        Application.ProcessMessages;        Next;      end;    end;    SynEdit1.Append(''); end;  
The problem is with Blobs ( graphics ).
I tried using this , to encode bytes as string ( IdEncoderMIME1 from Indy package)

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---LsValue := LsValue + QuotedStr( IdEncoderMIME1.EncodeBytes( Fields[j].AsString ))) + ' ,'   
It do the job but, when I try to execute one row from SQL result, the image effectivelly do not inserts as stream of bytes but encoded in MIME64.
So,
Is there any solution to do not use the Encoders, and to get it work .

Thanks.

marsupilami79:
Hello,

I found a post on SO that MySQL and MariaDB support inserting binary data if it is encoded as hexadecimal: http://stackoverflow.com/questions/10139521/ddg#10283197
So I guess you need to convert to hex encoded strings:
--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---INSERT INTO my_table VALUES (1,0x19c0300dc90e7cedf64703ed8ae8683b,2);Somebody else over there pointed out that this is documented in §9.1.4 of the MySQL Reference Manual.

Unfortunately Zeos cannot help ypu with the HEX conversion but I seem to remember that there were some hex conversion functions in FPC.

Best regards,

Jan

BSaidus:
@marsupilami79
thnks

Navigation

[0] Message Index