Arsip: Cara generate isi table ke script

 
user image
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...
user image
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;
user image
more 13 years ago

eksant

hehe..cuman contoh lho!!
user image
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.sql
more ...
  • Pages:
  • 1
Share to

Random Topic

Local Business Directory, Search Engine Submission & SEO Tools FreeWebSubmission.com SonicRun.com