Create tables on MS SQL Server
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
procedure TLocal.CreateTables(WindowsSecurity: Boolean; Username, Password: String); var ConnectionString: String; begin if WindowsSecurity then ConnectionString := 'Provider=SQLOLEDB.1;' + 'Integrated Security=SSPI;' + 'Persist Security Info=False;' + 'Initial Catalog=test' else ConnectionString := 'Provider=SQLOLEDB.1;' + 'Password=' + Password + ';' + 'Persist Security Info=True;' + 'User ID=' + Username + ';' + 'Initial Catalog=test'; try try ADOConnection.ConnectionString := ConnectionString; ADOConnection.LoginPrompt := False; ADOConnection.Connected := True; ADOQuery.Connection := ADOConnection; ADOQuery.SQL.Clear; with ADOQuery.SQL do begin Add('create table Klijent('); Add('JMBG char(13) not null,'); Add('Ime char(30) not null,'); Add('Adresa char(30) not null,'); Add('Telefon char(15) not null,'); Add('Primanja numeric(6,2) not null,'); Add('primary key (JMBG))'); end; ADOQuery.ExecSQL; ADOQuery.SQL.Clear; with ADOQuery.SQL do begin Add('create table Kredit('); Add('Sifra numeric not null,'); Add('Tip char(15) unique not null,'); Add('Kamata numeric not null,'); Add('primary key (Sifra))'); end; ADOQuery.ExecSQL; ADOQuery.SQL.Clear; with ADOQuery.SQL do begin Add('create table Operator('); Add('JMBG char(13) unique not null,'); Add('Ime char(30) not null,'); Add('Sifra char(30) not null,'); Add('Adresa char(30) not null,'); Add('Telefon char(15) not null,'); Add('Prioritet smallint not null check (Prioritet>0),'); Add('primary key (JMBG))'); end; ADOQuery.ExecSQL; ADOQuery.SQL.Clear; with ADOQuery.SQL do begin Add('create table Kreditiranja ('); Add('Sifra numeric not null,'); Add('Sifra_kredita numeric not null,'); Add('Datum datetime,'); Add('Iznos_kredita numeric(10,2) check (Iznos_kredita>0),'); Add('Broj_rata numeric,'); Add('JMBG_klijenta char(13),'); Add('JMBG_operatora char(13),'); Add('primary key(Sifra),'); Add('foreign key(Sifra_kredita) references Kredit(Sifra) on delete cascade on update cascade,'); Add('foreign key(JMBG_klijenta) references Klijent(JMBG) on delete cascade on update cascade,'); Add('foreign key(JMBG_operatora) references Operator(JMBG) on delete cascade on update cascade)'); end; ADOQuery.ExecSQL; ADOQuery.SQL.Clear; with ADOQuery.SQL do begin Add('create table Rata ('); Add('Broj_rate numeric not null,'); Add('Broj_sifre numeric not null,'); Add('Datum datetime,'); Add('Iznos_rate numeric(10,2) check (Iznos_rate>0),'); Add('primary key (Broj_rate),'); Add('foreign key (Broj_sifre) references Kreditiranja(Sifra) on delete cascade on update cascade)'); end; ADOQuery.ExecSQL; MessageDlg('Tabele su uspjesno kreirane.', mtInformation, [mbOK], 0); except on E: Exception do MessageDlg(E.Message, mtWarning, [mbOK], 0); end; finally ADOConnection.Connected := False; end; end; |