Author: Yoganand Aiyadurai
Interbase Object for executing all the Interbase commands at Run time
Problem/Question/Abstract:
How can I create Interbase database at run time? How can I change Interbase
database password/user without using Interbase utilities?
Answer:
If an application runs on an interbase database, the database and all the required
objects such as functions, stored procedures etc. has to be created before running
the application.
And some of the commands such as changing the Administrators name and password has
to be done either using Server manager of Interbase or by the command line
utilities supplied by Interbase.
By including this unit in the project, You can execute all the required commands
such as creating a database, changing the administrators password, creating
shadows, functions, procedures etc.
Make sure that this object is created first in your application. In your project
source file the unit "Object_Interbase" must be the first unit to follow after the
standard units used by the application.
Include the Object_Interbase unit in your unit's uses cluase from which you are
going to use the object. You will be able to get the variable named "ThisDataBase"
of Class TMyIbDataBase which we will be using to executing the Interbase commands.
1
2 //*** THE UNIT STARTS HERE
3
4 unit Object_Interbase;
5
6 interface
7
8 uses Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
9 Dialogs, Registry, IBDataBase, IBQuery, FileCtrl;
10
11 type
12 TShadow = (stManual, stAuto, stConditional);
13
14 TMyIbDataBase = class(TObject)
15 private
16 //User Defined Type Variable
17
18 FShdType: TShadow;
19
20 //Components Variables
21 FDataBase: TIBDatabase;
22 FTransaction: TIBTransaction;
23 FQuery: TIBQuery;
24
25 //Boolean Variables
26 FUseDefaultFiles: Boolean;
27 FConnected: Boolean;
28 FShadow: Boolean;
29
30 //String Variables
31 FIBServerPath: string;
32 FDataBasePath, FShadowPath: string;
33 FUser, FPassword: string;
34 FDatabaseName: string;
35
36 //Procedures
37 procedure CheckDirPath(var Value: string);
38 procedure ChangetoIBDir;
39 procedure CreateComponents;
40 procedure InitilizeVariables;
41 procedure IBLoadpathfromRegistry;
42 procedure SetDataBasePath(Value: string);
43 procedure SetShadowPath(Value: string);
44 procedure SetAdminName(Value: string);
45 procedure SetAdminPassword(Value: string);
46 procedure SetDatabaseName(Value: string);
47 procedure SetShadow(Value: Boolean);
48 procedure SetShadowType(Value: TShadow);
49 protected
50 public
51 constructor Create;
52
53 //Procedures
54 procedure IBCreateDatabase;
55 procedure IBConnectToDatabase;
56 procedure IBDisConnecFromDatabase;
57 procedure IBCreateShadow;
58 procedure IBQueryAssisnSQL(Value: string; CloseAfterExecution: Boolean);
59 procedure IBChangeAdminPassword(Value: string);
60
61 //Component Properties
62 property IBAppDatabase: TIBDatabase read FDataBase;
63 property IBAppTransaction: TIBTransaction read FTransaction;
64 property IBAppQuery: TIBQuery read FQuery;
65
66 //User Defined Type Properties
67 property IBShadowType: TShadow read FShdType write SetShadowType;
68
69 //Boolean value Properties
70 property IBConnected: Boolean read FConnected default False;
71 property IBExists: Boolean read FUseDefaultFiles default False;
72 property IBShadow: Boolean read FShadow write SetShadow default False;
73
74 //String Value Properties
75 property IBServerPath: string read FIBServerPath;
76 property IBUserName: string read FUser write SetAdminName;
77 property IBPassword: string read FPassword write SetAdminPassword;
78 property IBDatabasePath: string read FDataBasePath write SetDataBasePath;
79 property IBShadowPath: string read FShadowPath write SetShadowPath;
80 property IBDatabaseName: string read FDatabaseName write SetDatabaseName;
81 end;
82
83 var
84 ThisDataBase: TMyIbDataBase;
85
86 implementation
87
88 { TIbDataBase }
89
90 procedure TMyIbDataBase.CheckDirPath(var Value: string);
91 begin
92 if Value[Length(Value)] <> '\' then
93 Value := Value + '\';
94 if not DirectoryExists(Value) then
95 begin
96 CreateDir(Value);
97 end;
98 end;
99
100 procedure TMyIbDataBase.IBChangeAdminPassword(Value: string);
101 var
102 I: Integer;
103 begin
104 ThisDataBase.ChangetoIBDir;
105 I := WinExec(pchar('gsec -user ' + ThisDataBase.IBUserName +
106 ' -password ' + ThisDataBase.IBPassword +
107 ' -mo ' + ThisDataBase.IBUserName + ' -pw ' +
108 Value), 0);
109 ThisDataBase.IBPassword := Value;
110 end;
111
112 procedure TMyIbDataBase.ChangetoIBDir;
113 begin
114 if ThisDataBase.IBExists then
115 ChDir(ThisDataBase.IBServerPath);
116 end;
117
118 procedure TMyIbDataBase.IBConnectToDatabase;
119 begin
120 if not ThisDataBase.IBConnected then
121 begin
122 FDataBase.Close;
123 FDataBase.SQLDialect := 1;
124 FTransaction.Active := False;
125 FQuery.Close;
126 FDataBase.LoginPrompt := False;
127 FDataBase.Params.Clear;
128 FDataBase.Params.Add('USER_NAME=' + ThisDataBase.IBUserName);
129 FDataBase.Params.Add('PASSWORD=' + ThisDataBase.IBPassword);
130 FDataBase.DatabaseName := ThisDataBase.IBDatabasePath + IBDatabaseName;
131 try
132 FDataBase.Connected := True;
133 FTransaction.DefaultDatabase := FDataBase;
134 except
135 end;
136 FConnected := FDataBase.Connected;
137 FQuery.Transaction := FTransaction;
138 FQuery.Database := FDataBase;
139 FDataBase.DefaultTransaction := FTransaction;
140 if FConnected then
141 begin
142 FTransaction.Active := True;
143 end;
144 end;
145 end;
146
147 constructor TMyIbDataBase.Create;
148 begin
149 CreateComponents;
150 InitilizeVariables;
151 IBLoadpathfromRegistry;
152 end;
153
154 procedure TMyIbDataBase.CreateComponents;
155 begin
156 FDataBase := TIBDatabase.Create(Application);
157 FTransaction := TIBTransaction.Create(Application);
158 FDataBase.DefaultTransaction := FTransaction;
159 FTransaction.DefaultDatabase := FDataBase;
160 FQuery := TIBQuery.Create(Application);
161 FQuery.Database := FDataBase;
162 FQuery.Transaction := FTransaction;
163 FQuery.ParamCheck := False;
164 end;
165
166 procedure TMyIbDataBase.IBCreateDatabase;
167 var
168 vmem: TStringList;
169 S: string;
170 begin
171 S := ExtractFilePath(Application.ExeName);
172 vmem := TStringList.Create;
173 vmem.Add('Create database "' + ThisDataBase.IBDatabasePath +
174 ThisDataBase.IBDatabaseName +
175 '" user "' + ThisDataBase.IBUserName + '" password "' +
176 ThisDataBase.IBPassword + '" page_size=2048 Length=50;');
177 vmem.Add('Commit work;');
178 vmem.Add('gfix -w "sync" -user "' + ThisDataBase.IBUserName + '" -pa ' +
179 ThisDataBase.IBPassword + '" "' + ThisDataBase.IBDatabasePath +
180 ThisDataBase.IBDatabaseName + '"');
181 S := S + 'Sql03EASY05.Sql';
182 vmem.SaveToFile(S);
183 vmem.Free;
184 ThisDataBase.ChangetoIBDir;
185 S := 'isql -input ' + S;
186 winexec(pchar(S), 0);
187 DeleteFile(S);
188 S := ThisDataBase.IBDatabasePath + ThisDataBase.IBDatabaseName;
189 while not FileExists(S) do
190 ;
191 ThisDataBase.IBConnectToDatabase;
192 FConnected := FDataBase.Connected;
193 end;
194
195 procedure TMyIbDataBase.IBCreateShadow;
196 var
197 S, vFname: string;
198 begin
199 if ThisDataBase.IBConnected then
200 begin
201 case FShdType of
202 stAuto: S := 'Auto';
203 stManual: S := 'Manual';
204 stConditional: S := 'Conditional';
205 end;
206 vFname := Copy(FDatabaseName, 1, pos('.', FDatabaseName)) + 'Shd';
207 FQuery.Close;
208 FQuery.SQL.Clear;
209 FQuery.SQL.Text := 'Create Shadow 1 ' + S + ' "' + FShadowPath +
210 vFname + '" Length = 10000';
211 FQuery.ExecSQL;
212 Application.ProcessMessages;
213 end;
214 end;
215
216 procedure TMyIbDataBase.InitilizeVariables;
217 begin
218 FDataBasePath := '';
219 FShadowPath := '';
220 FIBServerPath := '';
221 FUser := '';
222 FPassword := '';
223 FDatabaseName := '';
224 FShdType := stConditional;
225 FConnected := False;
226 end;
227
228 procedure TMyIbDataBase.IBLoadpathfromRegistry;
229 var
230 vReg: TRegistry;
231 begin
232 vReg := TRegistry.Create;
233 vReg.RootKey := HKEY_LOCAL_MACHINE;
234 if vReg.OpenKey('\Software\InterBase Corp\InterBase\CurrentVersion', False) then
235 begin
236 FIBServerPath := vreg.ReadString('ServerDirectory');
237 FUseDefaultFiles := True;
238 end
239 else
240 begin
241 FIBServerPath := ExtractFilePath(Application.ExeName);
242 FUseDefaultFiles := False;
243 end;
244 vReg.CloseKey;
245 vReg.Free;
246 end;
247
248 procedure TMyIbDataBase.SetAdminName(Value: string);
249 begin
250 if (Value <> FUser) then
251 FUser := Value;
252 end;
253
254 procedure TMyIbDataBase.SetAdminPassword(Value: string);
255 begin
256 if (Value <> FPassword) then
257 FPassword := Value;
258 end;
259
260 procedure TMyIbDataBase.SetDatabaseName(Value: string);
261 begin
262 if (Value <> FDatabaseName) then
263 FDatabaseName := Value;
264 end;
265
266 procedure TMyIbDataBase.SetDataBasePath(Value: string);
267 begin
268 if (Value <> FDataBasePath) then
269 begin
270 FDataBasePath := Value;
271 CheckDirPath(FDataBasePath);
272 end;
273 end;
274
275 procedure TMyIbDataBase.SetShadow(Value: Boolean);
276 begin
277 if (Value <> FShadow) then
278 FShadow := Value;
279 end;
280
281 procedure TMyIbDataBase.SetShadowPath(Value: string);
282 begin
283 if (Value <> FShadowPath) then
284 begin
285 FShadowPath := Value;
286 CheckDirPath(FShadowPath);
287 end;
288 end;
289
290 procedure TMyIbDataBase.SetShadowType(Value: TShadow);
291 begin
292 if (Value <> FShdType) then
293 FShdType := Value;
294 end;
295
296 procedure TMyIbDataBase.IBQueryAssisnSQL(Value: string; CloseAfterExecution:
297 Boolean);
298 begin
299
300 FQuery.Close;
301 FQuery.SQL.Clear;
302 FQuery.SQL.Text := Value;
303 try
304 FQuery.ExecSQL;
305 except
306 end;
307 if CloseAfterExecution then
308 begin
309 FQuery.Close;
310 FQuery.SQL.Clear;
311 FQuery.SQL.Text := 'Commit';
312 FQuery.ExecSQL;
313 FQuery.Close;
314 end;
315 end;
316
317 procedure TMyIbDataBase.IBDisConnecFromDatabase;
318 begin
319 FDataBase.CloseDataSets;
320 FDataBase.ForceClose;
321 FConnected := FDataBase.Connected;
322 end;
323
324 initialization
325 if (ThisDataBase = nil) then
326 ThisDataBase := TMyIbDataBase.Create;
327 finalization
328 if (ThisDataBase <> nil) then
329 begin
330 ThisDataBase.Free;
331 ThisDataBase := nil;
332 end;
333 end.
334
335 //** THE UNIT ENDS HERE
Examples:
1. Creating a Database
If you want to create a database called "Sample.Gdb" in the directory called
"c:\test\" with the administrator named
"LION" with the password "king". Just by using the properties and methods of this
simple object we can create the database.
336
337 ThisDataBase.IBUserName := 'LION';
338 ThisDataBase.IBPassword := 'king';
339 ThisDataBase.IBDatabasePath := 'c:\test\';
340 ThisDataBase.IBDatabaseName := 'Sample.Gdb';
341 ThisDataBase.IBCreateDatabase;
The properties IBUserName, IBPassword, IBDatabaseName has to be assigned only once.
2. Creating Shadow
ThisDataBase.IBConnectToDatabase;
ThisDatabse.IBShadowType := stAuto;
ThisDatabse.IBCreateShadow;
3. Changing Database Password
ThisDataBase.IBChangeAdminPassword('NewPassword');
4. Creating a Table
ThisDatabase.IBQueryAssisnSQL('CREATE TABLE USERS( ' +
'USERCODE VARCHAR(6) NOT NULL , ' +
'USERNAME VARCHAR(20) NOT NULL , ' +
'USERACTIVE VARCHAR(1) DEFAULT "Y", ', True);
In the same way you can assign the scripts for creating the stored procedures,
function and the scripts for creating all the objects using this method.
5. The properties IBAppDatabase, IBAppTransaction and IBAppQuery can be used to
assign to the properties of the IBTable component if you are go to work with the
IBTable component.
If in case you are not going to work with the Interbase components this object can be modifed to work with the simple database components.
|
