unit uImportDMySQL;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, sqldb, dbf, DB, mysql56conn, FileUtil, Forms, Controls,
Graphics, Dialogs, StdCtrls, IniFiles, LConvEncoding;
type
{ TFormImport }
TFormImport = class(TForm)
BtnImportVyr: TButton;
DataSource1: TDataSource;
Dbf1: TDbf;
edtHeslo: TEdit;
Label1: TLabel;
lblHlaseni: TLabel;
MySQLConnection1: TMySQL56Connection;
Query: TSQLQuery;
SQLTransaction1: TSQLTransaction;
procedure BtnImportVyrClick(Sender: TObject);
private
{ private declarations }
public
{ public declarations }
end;
const
C_DB_SECTION = 'DB-INFO';
var
FormImport: TFormImport;
INI: TINIFile;
DBuser, DBpassword, DBlocation: string;
implementation
{$R *.lfm}
{ TFormImport }
//F U N K C E
function FieldValue(x: integer): string;
begin
case FormImport.Dbf1.Fields[x].Value = Null of
True: Result := '';
False: Result := FormImport.Dbf1.Fields[x].Value
end;
end;
procedure TFormImport.BtnImportVyrClick(Sender: TObject);
begin
lblHlaseni.caption:='Import in progress';
Application.ProcessMessages;
INI := TINIFile.Create('DMySQL.ini');
// Put reading the INI file inside a try/finally block to prevent memory leaks
try
// Demonstrates reading values from the INI file.
DBuser := INI.ReadString(C_DB_SECTION, 'DBuser', '');
//DBpassword := INI.ReadString(C_DB_SECTION, 'DBpassword', '');
DBlocation := INI.ReadString(C_DB_SECTION, 'DBlocation', '');
finally
// After the ini file was used it must be freed to prevent memory leaks.
INI.Free;
end;
DBpassword:=edtHeslo.Caption;
MySQLConnection1.HostName := DBlocation;
MySQLConnection1.UserName := DBuser;
MySQLConnection1.Password := DBpassword;
MySQLConnection1.Transaction := SQLTransaction1;
MySQLConnection1.DatabaseName := 'D2';
MySQLConnection1.Open;
if MySQLConnection1.Connected then
begin
Query.Database := MySQLConnection1;
SQLTransaction1.Database := MySQLConnection1;
Query.ParseSQL := True;
Query.ReadOnly := False;
Query.SQL.Text := 'SET CHARACTER SET `utf8`';
Query.ExecSQL;
Query.SQL.Text := 'SET NAMES `utf8`';
Query.ExecSQL;
Query.SQL.Text :=
'create table if not exists vyr1new(ID MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,KONTROLA char(1) null, ZAKAZKA char(10) null, NZAK varchar(254) null, VYKRES varchar(20) null,' + ' KODOPER char(6) null,' + ' OPERACE varchar(30) null, KODSTROJ char(4) null, STROJ varchar(30) null, OPERATOR varchar(40) null, KOD char(4) null,' + ' ZAKPOZN varchar(254) null, DOBA float null, ZACATEK CHAR(1), TIMEZAC varchar(20) null, KONEC CHAR(1), TIMEKON varchar(20) null,KS_DOBRY integer null,' + ' KS_ZMETEK integer null, UKONCENO char(1) null, POZNAMKA varchar(128) null)';
Query.ExecSQL;
Query.SQL.Text := 'ALTER TABLE vyr1new AUTO_INCREMENT=1;';
Query.ExecSQL;
Dbf1.FilePathFull := 'd:\J.C\Visual Foxpro Projects\D2\dta';
Dbf1.FilePathFull := 'd:\J.C\Visual Foxpro Projects\D2\dta\';
Dbf1.TableName := 'vyr11.dbf';
//DataSource1
Dbf1.Active := True;
Query.SQL.Text :=
'insert into vyr1new(ID,KONTROLA,ZAKAZKA,NZAK,VYKRES,KODOPER,OPERACE,KODSTROJ,STROJ,OPERATOR,KOD,'
+ 'ZAKPOZN,DOBA,ZACATEK,TIMEZAC,KONEC,TIMEKON,KS_DOBRY,KS_ZMETEK,UKONCENO,POZNAMKA) '
+ ' values((:ID),(:KONTROLA),(:ZAKAZKA),(:NZAK),(:VYKRES),(:KODOPER),(:OPERACE),(:KODSTROJ),(:STROJ),(:OPERATOR),(:KOD),'
+ '(:ZAKPOZN),(:DOBA),(:ZACATEK),(:TIMEZAC),(:KONEC),(:TIMEKON),(:KS_DOBRY),(:KS_ZMETEK),(:UKONCENO),(:POZNAMKA))';
//corresponding to foxpro table vyr11.dbf
lblHlaseni.caption:='Import in progress';
while not Dbf1.EOF do
begin
// lblHlaseni.visible:= not(lblHlaseni.visible);
// lblHlaseni.Paint;
Query.Params.ParamByName('ID').AsString := '0';
Query.Params.ParamByName('KONTROLA').AsString := FieldValue(18);
Query.Params.ParamByName('ZAKAZKA').AsString := FieldValue(2);
Query.Params.ParamByName('NZAK').AsString := CP1250ToUTF8(FieldValue(3));
Query.Params.ParamByName('VYKRES').AsString := CP1250ToUTF8(FieldValue(4));
Query.Params.ParamByName('KODOPER').AsString := FieldValue(6);
Query.Params.ParamByName('OPERACE').AsString := CP1250ToUTF8(FieldValue(7));
Query.Params.ParamByName('KODSTROJ').AsString := FieldValue(8);
Query.Params.ParamByName('STROJ').AsString := CP1250ToUTF8(FieldValue(9));
Query.Params.ParamByName('OPERATOR').AsString := CP1250ToUTF8(FieldValue(0));
Query.Params.ParamByName('KOD').AsString := FieldValue(1);
Query.Params.ParamByName('ZAKPOZN').AsString := CP1250ToUTF8(FieldValue(5));
Query.Params.ParamByName('DOBA').AsString := FieldValue(16);
Query.Params.ParamByName('ZACATEK').AsString := FieldValue(10);
Query.Params.ParamByName('TIMEZAC').AsString := FieldValue(11);
Query.Params.ParamByName('KONEC').AsString := FieldValue(12);
Query.Params.ParamByName('TIMEKON').AsString := FieldValue(13);
Query.Params.ParamByName('KS_DOBRY').AsString := FieldValue(14);
Query.Params.ParamByName('KS_ZMETEK').AsString := FieldValue(15);
Query.Params.ParamByName('UKONCENO').AsString := FieldValue(17);
Query.Params.ParamByName('POZNAMKA').AsString := CP1250ToUTF8(FieldValue(19));
//fields numbering dbf begins by zero
Query.ExecSQL;
Dbf1.Next;
//SQLTransaction1.commitretaining;
end;
lblHlaseni.caption:='Čekám..';
lblHlaseni.visible := true;
ShowMessage('Výroba byla naimportována.');
//*
Query.SQL.Text := 'DROP TABLE IF EXISTS VYR1old';
Query.ExecSQL;
SQLTransaction1.Active := True;
Query.SQL.Text := 'RENAME TABLE vyr1 TO vyr1old, vyr1new TO vyr1';
Query.ExecSQL;
Query.Close;
ShowMessage('Soubory přejmenovány.');
end;
end;
end.
Recent
