ufrmdb2.pas

数据库通用工具

unit UfrmDB2;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, Grids, DBGrids, StdCtrls, Buttons, ExtCtrls,
  CheckLst;

type
  TfrmDB2 = class(TForm)
    Database1: TDatabase;
    DataSource1: TDataSource;
    Query1: TQuery;
    Table1: TTable;
    pnlMsg: TPanel;
    Panel1: TPanel;
    BitBtn1: TBitBtn;
    btnUnLink: TBitBtn;
    BitBtn3: TBitBtn;
    Panel2: TPanel;
    DBGrid1: TDBGrid;
    Panel3: TPanel;
    mo: TMemo;
    ListBox1: TListBox;
    Panel4: TPanel;
    Panel8: TPanel;
    CheckListBox1: TCheckListBox;
    Panel9: TPanel;
    mo2: TMemo;
    Splitter1: TSplitter;
    BitBtn10: TBitBtn;
    Panel5: TPanel;
    btnRun: TBitBtn;
    CheckBox4: TCheckBox;
    CheckBox1: TCheckBox;
    labMsg: TLabel;
    Query2: TQuery;
    OpenDialog1: TOpenDialog;
    Database2: TDatabase;
    Query3: TQuery;
    Splitter2: TSplitter;
    Splitter3: TSplitter;
    CheckBox2: TCheckBox;
    bbtGetVal: TBitBtn;
    edtDB: TEdit;
    edtUser: TEdit;
    edtPass: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure btnUnLinkClick(Sender: TObject);
    procedure Database1AfterConnect(Sender: TObject);
    procedure Database1AfterDisconnect(Sender: TObject);
    procedure btnRunClick(Sender: TObject);
    procedure ListBox1Click(Sender: TObject);
    procedure BitBtn10Click(Sender: TObject);
    procedure CheckBox2Click(Sender: TObject);
    procedure DBGrid1MouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure bbtGetValClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmDB2: TfrmDB2;

implementation

uses UfrmImportDB2;

{$R *.dfm}

procedure TfrmDB2.BitBtn3Click(Sender: TObject);
begin
  btnUnLink.Click;
  Close;
end;

procedure TfrmDB2.BitBtn1Click(Sender: TObject);
var
  slt:TStringList;
  i:integer;
  ss:string;
begin
  {
  USER NAME=db2admin
  DB2 DSN=EPL
  OPEN MODE=READ/WRITE
  SCHEMA CACHE SIZE=8
  LANGDRIVER=
  SQLQRYMODE=
  SQLPASSTHRU MODE=SHARED AUTOCOMMIT
  SCHEMA CACHE TIME=-1
  MAX ROWS=-1
  BATCH COUNT=200
  ENABLE SCHEMA CACHE=FALSE
  SCHEMA CACHE DIR=
  ENABLE BCD=FALSE
  ROWSET SIZE=20
  BLOBS TO CACHE=64
  BLOB SIZE=32
  PASSWORD=yym
  }
  with Database1 do
  begin
    if Connected then  Connected := false;
//    Params.Values['SERVER NAME'] :=  ServerName;
    Params.Values['DB2 DSN'] :=  edtDB.Text;//'EPL';
    Params.Values['USER NAME'] := edtUser.Text;//'db2admin';
    Params.Values['PASSWORD'] := edtPass.Text;//'yym';
    Params.Values['BLOB SIZE'] := IntToStr( 1024*8 );
    Connected := true;
  end;

  if Database1.Connected = true then
  begin
    {
    slt := TStringList.Create();
    //Database1.Session.GetTableNames(Database1.DatabaseName,'',False,False,slt);//A
//    Database1.Session.GetTableNames(Database1.DatabaseName,'',true,true,slt);//A
    Database1.GetTableNames(slt,false);//B

    listbox1.Items.Clear;
    for i := 0 to slt.Count-1 do
    begin
      ss := slt.Strings[i];
//      ss := copy(ss,5,length(ss)-4);
      listbox1.Items.Add( ss );
    end;

    slt.Free;
    //}
    listbox1.Items.Clear;
    with Query1 do
    begin
      Close;
//      SQL.Text := 'SELECT * FROM SYSIBM.SYSCONSTDEP order by DTBCREATOR,dtbname';
      SQL.Text := 'SELECT NAME,CREATOR,TBSPACE,DEFINER FROM SYSIBM.SYSTABLES where TBSPACE=''USERSPACE1'' order by CREATOR,NAME';

      try
        Open;
      except
      end;

      labMsg.Caption := '表数: '+ IntToStr(RecordCount);
      
      while not Eof do
      begin            
//        ss := FieldByName('DTBCREATOR').AsString+'.'+FieldByName('dtbname').AsString;
        //"NAME",CREATOR
        ss := '【'+FieldByName('CREATOR').AsString+'】'+FieldByName('NAME').AsString;
        listbox1.Items.Add(ss);
        Next;
      end;
      labMsg.Hint := IntToStr( listbox1.Items.Count );
    end;
    
  end;

end;

procedure TfrmDB2.btnUnLinkClick(Sender: TObject);
begin
  Database1.Connected := False;
end;

procedure TfrmDB2.Database1AfterConnect(Sender: TObject);
begin
  pnlMsg.Caption := '已连接';
end;

procedure TfrmDB2.Database1AfterDisconnect(Sender: TObject);
begin
  pnlMsg.Caption := '已断开连接!!!';
end;

function getFieldType( sDPName : string ) : string;
begin
  sDPName := Trim(sDPName);
  Result := '';
  if sDPName='TIntegerField' then
  begin
    Result := 'int';
  end
  else if sDPName='TStringField' then
  begin
    Result := 'varchar';
  end
  else if sDPName='TWideStringField' then//TStringField
  begin
    Result := 'varchar';
  end
  else if sDPName='TFloatField' then
  begin
    Result := 'float';
  end
  else if sDPName='TCurrencyField' then
  begin
    Result := 'money';
  end
  else if sDPName='TDateTimeField' then
  begin
    Result := 'datetime';
  end
  else if sDPName='TBooleanField' then
  begin
    Result := 'bit';
  end
  else if sDPName='TBlobField' then
  begin
    Result := 'image';
  end
  else if sDPName='TAutoIncField' then
  begin
    Result := 'int (自增)';
  end
  else if sDPName='TSmallintField' then
  begin
    Result := 'smallint';
  end
  else if sDPName='TMemoField' then
  begin
    Result := 'text';
  end
  else//'TDateField'
  begin
    Result := '-【'+sDPName+'】-';
  end;

end;

procedure TfrmDB2.btnRunClick(Sender: TObject);
var
  ss,sb,st : string;
  i,a,j,k:integer;
begin
  if not Database1.Connected then
  begin
    pnlMsg.Caption := '没有连接库';
    Exit;
  end;

  if Trim(mo.Text)='' then
  begin
    Exit;
  end;

  //SELECT * FROM DB2ADMIN.WS_VOTEQUEST;--民意调查问题表
  ss := mo.Text;
//  ss := StringReplace( ss,'【','',[rfReplaceAll]);
//  ss := StringReplace( ss,'】','.',[rfReplaceAll]);
  //delete from INT_DATA_INFO
  if Pos('delete',LowerCase(ss) )>0 then
  begin
    with Query1 do
    begin
      Close;
      SQL.Text := ss;
      try
        ExecSQL;
      except
        mo2.Lines.Add('执行失败！');
        Exit;
      end;
      mo2.Lines.Add('执行成功。');
      Exit;
    end;

  end;

  if Pos('select',LowerCase(ss) )<=0 then
  begin
    ss := 'select * from '+ss;
  end;

  with Query1 do
  begin
    Close;
    SQL.Text := ss;

    try
      Open;
    except
      mo2.Lines.Add('执行失败！');
      Exit;
    end;

    mo2.Lines.Add('执行成功。');
    
    //统一列宽
    if CheckBox2.Checked then
    begin
      for i := 0 to DBGrid1.Columns.Count-1 do
      begin
        DBGrid1.Columns.Items[i].Width := 100;
      end;
    end;

    ss := '';
    {
    SELECT * FROM ADMINISTRATOR.QIM_DUTYINFO; 
    SELECT * FROM ADMINISTRATOR.QIM_NOTE;
    SELECT * FROM DB2ADMIN.QIM_ANNEX;
    SELECT * FROM DB2ADMIN.QIM_AUTOADDIDVALUE;
    }
    if ListBox1.ItemIndex>=0 then
      ss := ListBox1.Items.Strings[ListBox1.ItemIndex];

    ss := StringReplace( ss,'【','',[rfReplaceAll]);
    ss := StringReplace( ss,'】','.',[rfReplaceAll]);

    j := Pos('.',ss);
//    k := Pos(';',ss);
    sb := Copy(ss,j+1,MaxInt);

    a := DBGrid1.FieldCount;
    CheckListBox1.Items.Clear;
    mo2.Lines.Clear;
    mo2.Lines.Add( 'CREATE TABLE [' +sb+ '] (' );

    for i := 0 to a-1 do
    begin
      ss := DBGrid1.Columns.Items[i].FieldName ;  
//      cbxZD.Items.Add(ss);
      st := DBGrid1.Columns.Items[i].Field.ClassName;
      CheckListBox1.Items.Add( ss +' | '+getFieldType( DBGrid1.Columns.Items[i].Field.ClassName )
                                  +' 【'+IntToStr( DBGrid1.Columns.Items[i].Field.Size )+'】'
                                  + st );

      CheckListBox1.Checked[i] := true;
      //mo2.Lines.Add( ss+ ',' );
      if CheckBox4.Checked then//逗号前置 &Z
      begin
        if i>=1 then  
          ss := ','+ss;// + ' | ' + DBGrid1.Columns.Items[i].Field.ClassName;
      end
      else
      begin
        if i< a-1 then
          ss := ss+',';
      end;

      //mo2.Lines.Add( ss );
//      ss := ' varchar (20) DEFAULT (getdate())';
      if SameText( getFieldType(st),'varchar' ) then
      begin
        ss := ss + ' '+ getFieldType(st)+ '('+IntToStr( DBGrid1.Columns.Items[i].Field.Size )+')';
      end
      else
      begin
        ss := ss + ' '+ getFieldType(st);
      end;
      mo2.Lines.Add( ss );
      
    end;

    mo2.Lines.Add( ')' );
    //Panel9.Caption := IntToStr(CheckListBox1.Items.Count)+' / '+IntToStr( a );
    Panel9.Caption := '字段数 : '+IntToStr( a );//+' / '+IntToStr( Query1.FieldCount );;

  end;


end;

procedure TfrmDB2.ListBox1Click(Sender: TObject);
var
  ss : string;
begin
  if ListBox1.ItemIndex<0 then Exit;
  {
  SELECT * FROM ADMINISTRATOR.QIM_DUTYINFO; 
  SELECT * FROM ADMINISTRATOR.QIM_NOTE;
  SELECT * FROM DB2ADMIN.QIM_ANNEX;
  SELECT * FROM DB2ADMIN.QIM_AUTOADDIDVALUE;
  SELECT * FROM DB2ADMIN.QIM_DEPARTMENTINFO;
  SELECT * FROM DB2ADMIN.QIM_DUTYINFO;
  SELECT * FROM DB2ADMIN.QIM_ORGANINFO;
  SELECT * FROM DB2ADMIN.QIM_RESOURCEINFO;
  SELECT * FROM DB2ADMIN.QIM_ROLEINFO;
  SELECT * FROM DB2ADMIN.QIM_ROLERESOURCEINFO;
  SELECT * FROM DB2ADMIN.QIM_ROLEUSERINFO;
  SELECT * FROM DB2ADMIN.QIM_SYSCODE;
  SELECT * FROM DB2ADMIN.QIM_SYSTEMLOG;
  SELECT * FROM DB2ADMIN.QIM_USERINFO;
  SELECT * FROM DB2ADMIN.T20080315_134936;
  SELECT * FROM DB2ADMIN.T20080331_114715;
  SELECT * FROM DB2ADMIN.T20080331_114715_EXCEPTION;
  SELECT * FROM DB2ADMIN.THX_TABLES;
  SELECT * FROM DB2ADMIN.WS_APPLYOPEN;
  SELECT * FROM DB2ADMIN.WS_CERTIFICATE;
  SELECT * FROM DB2ADMIN.WS_COMMEND;
  SELECT * FROM DB2ADMIN.WS_COMMENT;
  SELECT * FROM DB2ADMIN.WS_CONFIG;
  SELECT * FROM DB2ADMIN.WS_DIALOGLIST;
  SELECT * FROM DB2ADMIN.WS_INDEXINFOCONFIG;
  SELECT * FROM DB2ADMIN.WS_INFORMATION;
  SELECT * FROM DB2ADMIN.WS_ITEM;
  SELECT * FROM DB2ADMIN.WS_LAYOUT;
  SELECT * FROM DB2ADMIN.WS_ONLINEMAIL;
  SELECT * FROM DB2ADMIN.WS_ONLINEVISITINFO;
  SELECT * FROM DB2ADMIN.WS_ONLINEVISITQUEST;
  SELECT * FROM DB2ADMIN.WS_QUESTIONNAIRE;
  SELECT * FROM DB2ADMIN.WS_QUESTIONNAIREKEY;
  SELECT * FROM DB2ADMIN.WS_REVIEW;
  SELECT * FROM DB2ADMIN.WS_SOLVECODE;
  SELECT * FROM DB2ADMIN.WS_THEME;
  SELECT * FROM DB2ADMIN.WS_USERINFO;
  SELECT * FROM DB2ADMIN.WS_VOTEQUEST;--民意调查问题表
  SELECT * FROM DB2ADMIN.WS_VOTERESP;--调查响应表
  SELECT * FROM DB2ADMIN.结果;  
  }

  ss := ListBox1.Items.Strings[ListBox1.ItemIndex];
  ss := StringReplace( ss,'【','',[rfReplaceAll]);
  ss := StringReplace( ss,'】','.',[rfReplaceAll]);

  mo.Text := ss;
  if CheckBox1.Checked then
  begin
    btnRun.Click;
  end;

end;

procedure TfrmDB2.BitBtn10Click(Sender: TObject);
begin
  if not Database1.Connected then Exit;
  Application.CreateForm(TfrmImportDB2, frmImportDB2);
  frmImportDB2.edita5.Items.Assign(ListBox1.Items);  
  frmImportDB2.ShowModal;
  frmImportDB2.Free;
end;

procedure TfrmDB2.CheckBox2Click(Sender: TObject);
var
  i : Integer;
begin
  //统一列宽
  if CheckBox2.Checked then
  begin
    for i := 0 to DBGrid1.Columns.Count-1 do
    begin
      DBGrid1.Columns.Items[i].Width := 100;  
    end;
  end;
end;

procedure TfrmDB2.DBGrid1MouseDown(Sender: TObject; Button: TMouseButton;
  Shift: TShiftState; X, Y: Integer);
begin
  if Button = mbRight then
  begin
    bbtGetVal.Click;
  end;

end;

procedure TfrmDB2.bbtGetValClick(Sender: TObject);
var
  ss : string;
begin
  if Query1.IsEmpty then Exit;
  
  ss := getFieldType(DBGrid1.SelectedField.ClassName);
  if SameText( ss, 'varchar')
    or SameText( ss, 'text') then
  begin
    mo2.Text := Query1.FieldByName( DBGrid1.SelectedField.FieldName ).AsString;
  end;
end;

end.