Arsip: Cara generate isi table ke script

more 13 years ago
Random
Gimana ya caranya generate isi table sehingga nanti jadi kayak :
insert into table (a, b , c) values (1, 2, 3) -> per record
Kan saya pake dua database. Yang pertama yang asli, yang kedua itu buat coba-coba. Nah, data di database yang asli itu kan nambah terus. Maksudnya kalo tiap ada yang baru itu mau saya tambahin ke database yang buat coba-coba...

more 13 years ago
eksant
function TEmployeePayroll.GenerateSQL(aRepeatCount : Integer = 1): TStrings;
var
lQ: TIBQuery;
sSQL: string;
ssSQL: Tstrings;
begin
result := TStringList.create;
if State = csNone then
Begin
raise Exception.create('Tidak bisa generate dalam Mode csNone')
end;
ssSQL := CustomSQLTaskPrior;
if ssSQL <> nil then
Begin
result.AddStrings(ssSQL);
end;
ssSQL := Nil;
sSQL := 'select ' + GetFieldNameFor_ID + ' ' +
'from ' + CustomTableName + ' ' +
'where ' + GetFieldNameFor_ID + ' = ' + IntToStr(ID);
lQ := cOpenQuery(sSQL);
try
If ((ID <= 0) or (lQ.Fields[0].AsFloat = null)) then
begin
//Generate Insert SQL
ID := cGetMaxValue(CustomTableName, GetFieldNameFor_ID) + 1 + (aRepeatCount - 1) ;
sSQL := 'insert into ' + CustomTableName + ' ('
+ GetFieldNameFor_ID + ', '
+ GetFieldNameFor_Bulan + ', '
+ GetFieldNameFor_Tahun + ', '
+ GetFieldNameFor_Employee + ', '
+ GetFieldNameFor_Pajak + ', ' + ' stamp) values ('
+ IntToStr(ID) + ', '
+ IntToStr(Bulan) + ', '
+ IntToStr(Tahun) + ', '
+ InttoStr(Employee.ID) + ', '
+ FormatFloat('0', GetPajakBulanIni) + ', '
+ 'Null);'
end else
begin
//generate Update SQL
sSQL := 'Update ' + CustomTableName + ' set Stamp = Null '
+ ', ' + GetFieldNameFor_Bulan + ' = ' + IntToStr( Bulan)
+ ', ' + GetFieldNameFor_Tahun + ' = ' + IntToStr( Tahun)
+ ', ' + GetFieldNameFor_Employee + ' = ' + IntToStr(Employee.ID)
+ ', ' + GetFieldNameFor_Pajak + ' = ' + FormatFloat('0', GetPajakBulanIni)
+ ' Where ' + GetFieldNameFor_ID + ' = ' + IntToStr(ID) + ';';
end;
finally
FreeAndNil(lQ);
end;
result.append(sSQL);
//Result.SaveToFile('c:\irregular.txt');
//generating Collections SQL
ssSQL := CustomSQLTask;
if ssSQL <> nil then
Begin
result.AddStrings(ssSQL);
end;
end;

more 13 years ago
danieljun
Kebetulan saya pernah bikin, dan bisa untuk tabel apa saja yang penting bisa connect ke pakai ADO
program getsql;
uses
Messages,
SysUtils,
Variants,
Classes,
ADODB,
Dialogs,
OleCtrls,
ADOInt;
{$R .res}
function getNextParam(var current:integer):string;
begin
if current < ParamCount then
begin
Result := ParamStr(current+1);
Inc(current);
end
else
Result := '';
end;
var i,j: integer;
fieldValues, Values, QueryString, outfile, fields, tablename, columns, order, limits, s, dbsetup : string;
f : Text;
Con : TADOConnection;
Que : TADOQuery;
c : char;
vt : TOleEnum;
// addQuote:boolean;
fieldQuotes : array of boolean;
begin
if ParamCount < 1 then
begin
ShowMessage('Usage '+ sLineBreak +
'getInsert tablename [$ "order"] [limit] [! "outputfile"]'+ slinebreak +
'requires db.txt in executable path contains database connection string');
exit;
end;
columns := ''; /// all columns as default
order := ''; /// unsorted
tablename := '';
limits := ''; /// unlimited
/// get parameters ...
i := 1;
while i <= ParamCount do
begin
s := ParamStr(i);
if length(tablename) < 1 then /// assume first parameter is tablename
tablename := s
else
begin
c := s[1];
case c of
'$' : // order by
order := getNextParam(i);
'#' : // limit
begin
limits := getNextParam(i);
if not TryStrToInt(limits, j) then // must be valid integer
limits := '';
end;
'!' : // output
begin
outfile := getNextParam(i);
end;
else
// columns add
begin
if length(columns) > 0 then
columns := columns + ',';
columns := columns + s;
end;
end; // case
end; // while
inc(i);
end;
s := '';
con := nil;
Que := nil;
if length(columns) < 1 then columns := ' ';
if length(outfile) < 1 then outfile := 'out.txt';
dbSetup := ExtractFileDir(ParamStr(0)) + PathDelim + 'db.txt';
try
if fileExists(dbsetup) then
begin
AssignFile(f, dbsetup);
Reset(f);
ReadLn(f, s);
CloseFile(f);
end
else
raise Exception.Create(dbSetup + ' not exist');
try
Con := TAdoConnection.Create(nil);
Que := TAdoQuery.Create(nil);
Con.ConnectionString := s;
Con.Open;
if con.Connected then
begin
Que.Connection := Con;
With Que.SQL do
begin
Append('SELECT ');
if length(limits) > 0 then
Append('TOP ' + limits);
Append(columns);
Append(' FROM ' + tablename);
if length(order) > 0 then
Append('ORDER BY ' +order);
end;
Que.Open;
j := Que.Fields.Count;
SetLength(fieldQuotes, j);
fields := '';
for i := 0 to j - 1 do
begin
fields := fields + Que.Fields[i].FieldName;
if i < (j -1) then fields := fields + ',';
vt := Que.Recordset.Fields[i].Type_;
fieldQuotes[i] := true;
case vt of
adEmpty,adTinyInt, adSmallInt, adInteger, adBigInt,
adUnsignedTinyInt, adUnsignedSmallInt, adUnsignedInt,
adUnsignedBigInt, adSingle, adDouble, adCurrency,
adDecimal, adNumeric, adBoolean :
fieldQuotes[i] := false;
adVariant, adError, adUserDefined, adIDispatch, adIUnknown,
adGUID, adDate, adDBDate, adDBTime, adDBTimeStamp, adBSTR,
adChar, adVarChar, adLongVarChar, adWChar, adVarWChar,
adLongVarWChar, adBinary, adVarBinary, adLongVarBinary,
adChapter, adFileTime, adDBFileTime, adPropVariant, adVarNumeric :
fieldQuotes[i] := true;
end;
end;
QueryString := 'INSERT INTO ' + tablename + ' (' + fields + ') VALUES ';
AssignFile(f, outfile);
Rewrite(f);
Que.Recordset.MoveFirst;
while not Que.Recordset.EOF do
begin
fieldValues := '(';
for i := 0 to j - 1 do
begin
if Que.Recordset.Fields[i].Value = Null then
Values := 'NULL'
else
begin
Values := VarToStr(Que.Recordset.Fields[i].Value);
if fieldQuotes[i] then
Values := '''' + Values + '''';
end;
if i < j - 1 then
fieldValues := fieldValues + Values + ','
else
fieldValues := fieldValues + Values;
end;
fieldValues := fieldValues + ');';
Writeln(f, QueryString + fieldValues);
Que.Recordset.MoveNext;
end;
CloseFile(f);
end
else
raise Exception.Create('Database is not connected');
finally
con.Free;
Que.Free;
end;
except on e: exception do ShowMessage(e.Message);
end;
end.
cara pakainya lewat command line :
setelah di compile lewat dcc32.exe
pastikan ada file db.txt di folder yang sama dengan getSQL.exe hasil compile berisi ADO Connection String yang valid
getSQL tablename [fields] $ sortby ! outputfile
dimana tablename adalah tabel yang ada dalam spesifikasi connection string di db.txt yg lokasinya sama dengan program,
lalu fields adalah daftar field untuk di insert
outputfile adalah file hasil kalau tidak ada artinya out.txt
tiga parameter terakhir optional
contoh pemakaian :
getSQL tableA kolom1 kolom2 kolom3 $ kolom1! out.sql
akan dump tableA dengan kolom1 kolom2 kolom3 dalam statement insert diurutkan berdasarkan kolom1 ke file out.sqlmore ...
- Pages:
- 1
reply |
Report Obsolete
Last Articles
- Project Group dalam Lazarus
- FastPlaz Database Explorer
- Release: FastPlaz Super Mom v0.12.22
- PascalClass #3: Web Development with Free Pascal
- Makna Pascal di Pascal Indonesia
- Kulgram : Instalasi Lazarus di Perangkat Berbasis ARM
- PascalClass #1: Analisa Database dan Machine Learning
- PascalTalk #6: (Podcast) Kuliah IT di luar negeri, susah gak sih?
- Mengenal OXYGENE – Pascal For .NET
- PascalTalk #5: UX: Research, Design and Engineer
Last Topic
- PascalTalk #6: (Podcast) Kuliah IT di luar negeri, susah gak sih?
by LuriDarmawan in Tutorial & Community Project more 3 months ago - PascalTalk #5: UX: Research, Design and Engineer
by LuriDarmawan in Tutorial & Community Project more 3 months ago - PascalTalk #4: Obrolan Ringan Seputar IT
by LuriDarmawan in Tutorial & Community Project more 4 months ago - PascalTalk #2: Membuat Sendiri SMART HOME
by LuriDarmawan in Tutorial & Community Project more 4 months ago - PascalTalk #3: RADically Fast and Easy Mobile Apps Development with Delphi
by LuriDarmawan in Tutorial & Community Project more 4 months ago - PascalTalk #1: Pemanfaatan Artificial Intelligence di Masa Covid-19
by LuriDarmawan in Tutorial & Community Project more 4 months ago - Tempat Latihan Posting
by LuriDarmawan in OOT more 1 years ago - Archive
- Looping lagi...
by idhiel in Hal umum tentang Pascal Indonesia more 8 years ago - [ask] koneksi ke ODBC user Dsn saat runtime dengan ado
by halimanh in FireBird more 8 years ago - Validasi menggunakan data tanggal
by mas_kofa in Hal umum tentang Pascal Indonesia more 8 years ago
Random Topic
- About Printer
by krishy in Tip n Trik Pemrograman more 13 years ago - install DevExpress_installer
by Tiwul in Hal umum tentang Pascal Indonesia more 11 years ago - MUTEX/Threading.............
by rykemit in MySQL more 14 years ago - Mempertahankan Tampilan Form Walau pindah monitor
by nholik1205 in Hal umum tentang Pascal Indonesia more 12 years ago - Chart seperti Windows Task Manager
by luckynvic in Multimedia & Graphic Enhancement more 13 years ago - arithmetic coding
by aptho in Tip n Trik Pemrograman more 14 years ago - device driver
by odomsangjelemagoblog in Tip n Trik Pemrograman more 13 years ago - Connection Ke server dengan menggunakan indy
by canoey in Network, Files, I/O & System more 14 years ago - Beda BDE dan ADO
by hendrikwjy in MySQL more 15 years ago - Simpan dan Baca rumus Matematika
by kacungdelphi24 in Bedah Kasus more 13 years ago