Format date (from a txt file) to Insert in MySql

Question

Format date (from a txt file) to Insert in MySql

Navigation

#1 by (4 votes)
#2 by (0 votes)

2

Follow the code:

    //informações necessarias para inserir no DB.
     coo := copy(lTemp,53,6);
                 ccf := copy(lTemp,47,6);
                 ecf := copy(lTemp,4,20);
     //Data sendo formatada para ser inserida
                 dtc1:= StrToDate(copy(lTemp,65,2)+'/'+copy(lTemp,63,2)+'/'+copy(
                   lTemp,59,4));
    //Query para inserção de linha no DB.
    DModuleGrid.ZQuery2.Close;
                 DModuleGrid.ZQuery2.SQL.Clear;
                 DModuleGrid.ZQuery2.SQL.Add('INSERT INTO tabc460(dtcompra, impcaixa, numcupom, ccf, valor) VALUES ( "'+DateTimeToStr(dtc1)+'", "'+ecf+'", "'+coo+'", "'+ccf+'", "'+FloatToStr(valortxt)+'")');
                 DModuleGrid.ZQuery2.ExecSQL;
                 DModuleGrid.ZQuery2.Close;
                 DModuleGrid.ZQuery2.SQL.Clear;
                 DModuleGrid.ZQuery2.SQL.Add('SELECT * FROM tabc460 ORDER BY dtcompra LIMIT 0,'+valor);
                 DModuleGrid.ZQuery2.Open;

Well, what I need is just to hit the format of the date, because when I format it above, it appears very well in the Show Message, the two formats are ok dd/mm/yyyy , but when I go to the database it everything is scrambled 20/03/2023 instead of 17/03/2014 .

What could be wrong?

mysql delphi sql-insert

asked by anonymous 15.05.2014 / 20:23

2 answers

0

Follow the code I wrote based on the @Caputo code.

with DModuleGrid.ZQuery2 do
           begin
           //comparação de divergências (Identifica se sao mesmo diferentes e grava os valores no banco de dados)
             coo := copy(lTemp,53,6);
             ccf := copy(lTemp,47,6);
             ecf := copy(lTemp,4,20);

             dtc1:= StrToDateTime(copy(lTemp,65,2)+'/'+copy(lTemp,63,2)+'/'+
             copy(lTemp,59,4));
             //FORMATANDO A DATA!!!!!!!! <<<------------------||||
             dtct := FormatDateTime('yyyy/mm/dd', dtc1);
             valortxt := StrToFloat(copy(lTemp, 109, 14)) / 100;

             DModuleGrid.ZQuery2.Close;
             DModuleGrid.ZQuery2.SQL.Clear;
             DModuleGrid.ZQuery2.SQL.Add('INSERT INTO tabc460(dtcompra, impcaixa, numcupom, ccf, valor) VALUES ( :dtcompra1, :impcaixa2, :numcupom3, :ccf4, :valor5)');
             DModuleGrid.ZQuery2.ParamByName('dtcompra1').AsDateTime := dtct;
             DModuleGrid.ZQuery2.ParamByName('impcaixa2').AsString:= ecf;
             DModuleGrid.ZQuery2.ParamByName('numcupom3').AsString := coo;
             DModuleGrid.ZQuery2.ParamByName('ccf4').AsString := ccf;
             DModuleGrid.ZQuery2.ParamByName('valor5').AsFloat:= valortxt;
             DModuleGrid.ZQuery2.ExecSQL;
             //ShowMessage(DModuleGrid.ZQuery2.FieldByName('dtcompra').AsString);
           end;

16.05.2014 / 04:08

Separation of fields in array Open word file through .jar

score 4 · Accepted Answer

In order to solve this problem it is ideal to use typed parameters because they protect the wrong conversion data, as well as protect your code against SQLInjection, therefore:

DModuleGrid.ZQuery2.SQL.Clear;
DModuleGrid.ZQuery2.SQL.Add('INSERT INTO tabc460(dtcompra, impcaixa, numcupom, ccf, valor) VALUES (:dtcompra, :impcaixa, :numcupom, :ccf, :valor)');

DModuleGrid.ZQuery2.ParamByName('dtcompra').AsDateTime := dtc1;
DModuleGrid.ZQuery2.ParamByName('impcaixa').AsString:= ecf;
DModuleGrid.ZQuery2.ParamByName('numcupom').AsString := coo;
DModuleGrid.ZQuery2.ParamByName('ccf').AsString := ccf;
DModuleGrid.ZQuery2.ParamByName('valor').AsFloat:= valortxt;

DModuleGrid.ZQuery2.ExecSQL;