Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
-Delphi/Pascal
-CBuilder/C++
-C#Builder/C#
-JBuilder/Java
-Kylix
Member Area
-Home
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Login/Logout
-Become a Member
-Why sign up!
-Newsletter
-Chat Online!
-Indexes NEW!!
Employment
-Build your resume
-Find a job
-Post a job
-Resume Search
Contacts
-Contacts
-Feedbacks
-Link to us
-Privacy/Disclaimer
Embarcadero
Visit Embarcadero
Embarcadero Community
JEDI
Links
How to use Interbase Object for executing all the Interbase commands at Run time Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
Interbase Object for executing all the Interbase commands a 14-Aug-03
Category
Database Others
Language
Delphi 5.x
Views
211
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			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. 

			
Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10

 

Advertisement
Share this page
Advertisement
Download from Google

Copyright © Mendozi Enterprises LLC