ufrmpubimport.pas

数据库通用工具

unit UfrmPubImport;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, UfrmModel, ExtCtrls, ActnList, DB, DBTables, ComCtrls, StdCtrls,
  Grids, DBGrids, Registry, Buttons, ADODB, Spin;

type
  TfrmPubImport = class(TfrmModel)
    Panel1: TPanel;
    Panel5: TPanel;
    DBGrid1: TDBGrid;
    pnl1: TPanel;
    Label2: TLabel;
    Label5: TLabel;
    cbxAtb: TComboBox;
    Panel6: TPanel;
    pnl2: TPanel;
    DBGrid2: TDBGrid;
    cbxDBType1: TComboBox;
    Label1: TLabel;
    mo: TMemo;
    cbxAdb: TComboBox;
    Label4: TLabel;
    edtAuser: TEdit;
    Label6: TLabel;
    edtApass: TEdit;
    Label3: TLabel;
    Label7: TLabel;
    cbxBtb: TComboBox;
    cbxDBType2: TComboBox;
    Label8: TLabel;
    cbxBdb: TComboBox;
    Label9: TLabel;
    edtBuser: TEdit;
    Label10: TLabel;
    edtBpass: TEdit;
    Label11: TLabel;
    edtAServer: TEdit;
    Label12: TLabel;
    edtBServer: TEdit;
    dlgOpenMDB: TOpenDialog;
    bbtUnLink1: TBitBtn;
    bbtUnLink2: TBitBtn;
    bbtLink1: TBitBtn;
    bbtLink2: TBitBtn;
    Panel2: TPanel;
    lv1: TListView;
    Panel3: TPanel;
    labJL1: TLabel;
    labZD1: TLabel;
    adoQList1: TADOQuery;
    DataSource1: TDataSource;
    qryPubSQL: TQuery;
    qryPubDB2: TQuery;
    CheckBox2: TCheckBox;
    qryPubMySQL: TQuery;
    CheckBox1: TCheckBox;
    Splitter1: TSplitter;
    Splitter4: TSplitter;
    Splitter5: TSplitter;
    Panel4: TPanel;
    Panel7: TPanel;
    Label13: TLabel;
    Label14: TLabel;
    CheckBox3: TCheckBox;
    lv2: TListView;
    SpinEdit1: TSpinEdit;
    SpinEdit2: TSpinEdit;
    procedure cbxDBType1Change(Sender: TObject);
    procedure bbtLink1Click(Sender: TObject);
    procedure ChangeStateA( b : Boolean );
    procedure bbtUnLink1Click(Sender: TObject);
    procedure cbxAtbChange(Sender: TObject);
    procedure addMo( ss : string );
    procedure FormShow(Sender: TObject);
    procedure CheckBox2Click(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmPubImport: TfrmPubImport;

implementation

uses Udbm;

{$R *.dfm}
{
  case cbxDBType1.ItemIndex of
    0:  begin//SQL Server [BDE]
         
        end;
    1:  begin//MySQL [ODBC]

        end;
    2:  begin//DB2 [BDE]

        end;
    3:  begin//Access [ADO]
          
        end;
  else Exit;
  end;
}

procedure TfrmPubImport.addMo( ss : string );
begin
  //
  mo.Lines.Add( ss );
end;

procedure TfrmPubImport.cbxDBType1Change(Sender: TObject);
begin
  inherited;
  cbxAdb.Items.Clear;
  cbxAtb.Items.Clear;

  edtAServer.Text := '';
  cbxAdb.Text := '';
  edtAuser.Text := '';
  edtApass.Text := '';
  cbxAtb.Text := '';
  Label11.Caption := '服务器';

  case cbxDBType1.ItemIndex of
    0:  begin//SQL Server [BDE]
          edtAServer.Text := '127.0.0.1';
          edtAuser.Text := 'sa';
          cbxAtb.Text := 'pubs';
        end;
    1:  begin//MySQL [ODBC]
          dbm.listODBCs( cbxAdb.Items );
          edtAuser.Text := 'root';
          cbxAtb.Text := 'mysql';
          Label11.Caption := '库名';
          edtAServer.Text := 'mysql';
        end;
    2:  begin//DB2 [BDE]
          edtAServer.Text := '127.0.0.1';
          edtAuser.Text := 'db2admin';
          cbxAtb.Text := ''; 
        end;
    3:  begin//Access [ADO]
          
        end;
  else Exit;
  end;
  
end;

procedure TfrmPubImport.ChangeStateA( b : Boolean );
begin
  //b = true 连上数据库
  bbtLink1.Enabled := not b;
  bbtUnLink1.Enabled := b;

  cbxDBType1.Enabled := not b;
  edtAServer.Enabled := not b;
  cbxAdb.Enabled := not b;
  edtAuser.Enabled := not b;
  edtApass.Enabled := not b;

end;
  
procedure TfrmPubImport.bbtLink1Click(Sender: TObject);
var
  slt:TStringList;
  i,j : Integer;
  ss,sb : string;
begin
  inherited;
  {
  SQL Server [BDE]
  MySQL [ODBC]
  DB2 [BDE]
  Access [ADO]
  }
  case cbxDBType1.ItemIndex of
    0:  begin//SQL Server [BDE]
          with dbm.dbSQLServer do
          begin
            if Connected then
              Connected := false;
              
            Params.Clear();
            //DatabaseName :=  edita2.Text ;
//            DatabaseName :=  'A'+cbxAdb.Text ;
            Params.Values['DATABASE NAME']:= cbxAdb.Text;//'master'
            Params.Values['SERVER NAME']  := edtAServer.Text ;
            Params.Values['USER NAME']    := edtAuser.Text ;
            Params.Values['PASSWORD']     := edtApass.Text ;
            Params.Values['BLOB SIZE']    := IntToStr( 1024*8 );

            try
              Connected := true;
            except
              ShowMSG('数据库链接错误，请重新设置');
              exit;
            end;

            if Connected = true then
            begin
              ChangeStateA( True );
              
              slt := TStringList.Create();
              GetTableNames(slt,false);
//              Query1.DatabaseName := Database1.DatabaseName ;

              //排序
              for i := 0 to slt.Count-2 do
              begin
                ss := slt.Strings[i];
                for j := i to slt.Count-1 do
                begin
                  sb := slt.Strings[j];
                  if LowerCase(sb) < LowerCase(ss) then
                  begin
                    ss := slt.Strings[j];
                    slt.Strings[j] := slt.Strings[i];
                    slt.Strings[i] := ss;
                  end;

                end;

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

              if cbxAtb.Items.Count>0 then cbxAtb.ItemIndex := 0;
              
              slt.Free;
              //}
              
              {//列出所有数据库
              with qryPubSQL do
              begin
                SQL.Clear;
                //SQL.Add('exec sp_databases');
                //SQL.Add('exec sp_helpdb');
                SQL.Text := 'use master select * from sysdatabases order by name';

                try
                  Open;
                except 
                end;

                cbxAtb.Items.Clear;

                while not eof do
                begin
                  //cmbList.Items.Add( Query2.FieldByName('DATABASE_NAME').AsString );
                  cbxAtb.Items.Add( FieldByName('name').AsString );
                  //Format('%.2f MB',[Query.FieldByName('DATABASE_SIZE').AsFloat/1024]);
                  Next;
                end;

                close;
              end;
              //}
            end;
          end;

        end;
    1:  begin//MySQL [ODBC]
          with dbm.dbODBCMySQL do
          begin
            if Connected then  Connected := false;
            Params.Values['ODBC DSN']   :=  cbxAdb.Text;
            Params.Values['DATABASE NAME'] :=  edtAServer.Text;
            Params.Values['USER NAME']  :=  edtAuser.Text;
            Params.Values['PASSWORD']   :=  edtApass.Text;

            try
              Connected := true;
            except
              ShowMSG('数据库链接错误，请重新设置');
              Exit;
            end;

            if Connected = true then
            begin
              ChangeStateA( True );

              slt := TStringList.Create();
              GetTableNames(slt,false);
//              Query1.DatabaseName := Database1.DatabaseName ;
              
              //排序
              for i := 0 to slt.Count-2 do
              begin
                ss := slt.Strings[i];
                for j := i to slt.Count-1 do
                begin
                  sb := slt.Strings[j];
                  if LowerCase(sb) < LowerCase(ss) then
                  begin
                    ss := slt.Strings[j];
                    slt.Strings[j] := slt.Strings[i];
                    slt.Strings[i] := ss;
                  end;

                end;

              end;
              //}

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

              if cbxAtb.Items.Count>0 then cbxAtb.ItemIndex := 0;
              
              slt.Free;
              //}  
            end;
          end;

        end;
    2:  begin//DB2 [BDE]