Dumping Mariadb table data with zeos.
lazarus / 16-Databases

----------------------------------------------------------------------
BSaidus · 2021-12-29 22:53

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT*
FROM tablename
here is the code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2021-12-31 09:40

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2021-12-31 18:02

@marsupilami79
thnks