Dumping Mariadb table data with zeos.

[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  [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.

[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  [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

[BSaidus]

@marsupilami79
thnks