Recent

Author Topic: Dumping Mariadb table data with zeos.  (Read 3576 times)

BSaidus

  • Sr. Member
  • ****
  • Posts: 411
  • lazarus 1.8.4 Win8.1 / cross FreeBSD
Dumping Mariadb table data with zeos.
« on: December 29, 2021, 11:53:34 pm »
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  [Select][+][-]
  1. SELECT* FROM tablename

here is the code:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button3Click(Sender: TObject);
  2. var
  3.   I, j, fCount: Integer;
  4.   LsValue: String;
  5. begin
  6.     with ZQ_nrec do
  7.     begin
  8.       Close;
  9.       SQL.Text := SysUtils.Format(C_Nsql, ['uc_ptnts_test']);
  10.       Open;
  11.  
  12.       if RecordCount = 0 then                              // Not records
  13.       begin
  14.         Exit;
  15.       end;
  16.  
  17.       fCount := FieldCount - 1;
  18.       First ;                                             // First
  19.       while not Eof do                                    // loop until the end
  20.       begin
  21.         LsValue := '';
  22.         for j := 0 to fCount do
  23.         begin
  24.           Application.ProcessMessages;
  25.           if j<>fCount then
  26.           begin
  27.             if Fields[j].DataType = ftBlob then
  28.             begin
  29.               if not Fields[j].IsNull then
  30.                 LsValue := LsValue + QuotedStr(( Fields[j].AsString )) + ' ,'
  31.               else LsValue := LsValue + ' NULL ,' ;
  32.             end
  33.             else
  34.             if Fields[j].DataType in [ftInteger, ftLargeint, ftBoolean] then
  35.             begin
  36.               if not Fields[j].IsNull then
  37.                 LsValue := LsValue +  IntToStr( Fields[j].AsLargeInt ) + ' ,'
  38.               else LsValue := LsValue + ' NULL ,';
  39.             end
  40.             else
  41.             if Fields[j].DataType in [ftDate, ftTime, ftDateTime] then
  42.             begin
  43.               if not Fields[j].IsNull then
  44.                 LsValue := LsValue +  QuotedStr( FormatDateTime( 'yyyy-mm-dd hh:nn:ss', Fields[j].AsDateTime ) )+ ' ,'
  45.               else LsValue := LsValue + ' NULL ,';
  46.             end
  47.             else
  48.             begin
  49.               if not Fields[j].IsNull then
  50.                 LsValue := LsValue + QuotedStr( Fields[j].AsString ) + ' ,'
  51.               else LsValue := LsValue + ' NULL ,';
  52.             end;
  53.           end
  54.           else
  55.           begin
  56.             if not Fields[j].IsNull then
  57.               LsValue := LsValue + QuotedStr( Fields[j].AsString )
  58.             else LsValue := LsValue + ' NULL ';
  59.           end;
  60.         end;
  61.  
  62.         LsValue := SysUtils.Format( C_Isql, ['uc_ptnts_test']) + LsValue + ');';
  63.         SynEdit1.Append(LsValue);
  64.         Application.ProcessMessages;
  65.         Next;
  66.       end;
  67.     end;
  68.     SynEdit1.Append('');
  69.  
  70. end;
  71.  

The problem is with Blobs ( graphics ).
I tried using this , to encode bytes as string ( IdEncoderMIME1 from Indy package)
Code: Pascal  [Select][+][-]
  1. LsValue := LsValue + QuotedStr( IdEncoderMIME1.EncodeBytes( Fields[j].AsString ))) + ' ,'  
  2.  

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.

« Last Edit: December 30, 2021, 09:09:16 am by BSaidus »
lazarus 1.8.4 Win8.1 / cross FreeBSD
dhukmucmur vernadh!

marsupilami79

  • New Member
  • *
  • Posts: 26
Re: Dumping Mariadb table data with zeos.
« Reply #1 on: December 31, 2021, 10:40:47 am »
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  [Select][+][-]
  1. 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
« Last Edit: December 31, 2021, 10:57:54 am by marsupilami79 »
Zeos developer

BSaidus

  • Sr. Member
  • ****
  • Posts: 411
  • lazarus 1.8.4 Win8.1 / cross FreeBSD
Re: Dumping Mariadb table data with zeos.
« Reply #2 on: December 31, 2021, 07:02:22 pm »
@marsupilami79
thnks
lazarus 1.8.4 Win8.1 / cross FreeBSD
dhukmucmur vernadh!

 

TinyPortal © 2005-2018