examples/consolemode/project1.lpr

(*
 *  IBX For Lazarus (Firebird Express)
 *
 *  The contents of this file are subject to the Initial Developer's
 *  Public License Version 1.0 (the "License"); you may not use this
 *  file except in compliance with the License. You may obtain a copy
 *  of the License here:
 *
 *    http://www.firebirdsql.org/index.php?op=doc&id=idpl
 *
 *  Software distributed under the License is distributed on an "AS
 *  IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
 *  implied. See the License for the specific language governing rights
 *  and limitations under the License.
 *
 *  The Initial Developer of the Original Code is Tony Whyman.
 *
 *  The Original Code is (C) 2015 Tony Whyman, MWA Software
 *  (http://www.mwasoftware.co.uk).
 *
 *  All Rights Reserved.
 *
 *  Contributor(s): ______________________________________.
 *
*) 
            
program project1;

{$mode objfpc}{$H+}

{ CONSOLE MODE EXAMPLE

  This is a simple use of IBX to access the employee database in console mode.
  The program opens the database, runs a query and writes the result to stdout.

  Key points to note:

  1. In console mode, you have to create the Database and Transaction objects
     explicitly and link them to each other.

  2. The Database properties have to be set explicitly. This includes username
     and password. In the example, these are set from literals. You could update
     this to (e.g.) parse the command line arguments from "ParamStr".
     However, this is left as an exercise for the implementor.

  3. It's a good idea to have the application own the IBDatabase. This ensures that
     exceptions are routed through your application object's exception handler.
}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes, SysUtils, CustApp, IBDatabase, IBQuery, IB
  { you can add units after this };

const
  sqlExample =
'with recursive Depts As (   '+
'Select DEPT_NO, DEPARTMENT, HEAD_DEPT, cast(DEPARTMENT  as VarChar(256)) as DEPT_PATH,'+
'cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH '+
'From DEPARTMENT Where HEAD_DEPT is NULL '+
'UNION ALL '+
'Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH ||  '' / '' || D.DEPARTMENT as DEPT_PATH,'+
'Depts.DEPT_KEY_PATH || '';'' || D.DEPT_NO as DEPT_KEY_PATH '+
'From DEPARTMENT D '+
'JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO '+
')'+

'Select A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE,'+
'A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH '+
'From EMPLOYEE A '+
'JOIN Depts D On D.DEPT_NO = A.DEPT_NO';

type

  { TMyApplication }

  TMyApplication = class(TCustomApplication)
  private
    FIBDatabase: TIBDatabase;
    FIBTransaction: TIBTransaction;
    procedure DoQuery;
  protected
    procedure DoRun; override;
  public
    constructor Create(TheOwner: TComponent); override;
    destructor Destroy; override;
    procedure WriteHelp; virtual;
  end;

{ TMyApplication }


procedure TMyApplication.DoQuery;
var i, rowno: integer;
    stats: TPerfCounters;
    SelectCount, InsertCount, UpdateCount, DeleteCount: integer;
    LargeCompFormat: string;
    ThreeSigPlacesFormat: string;
begin
  LargeCompFormat := '#' + DefaultFormatSettings.ThousandSeparator + '##0';
  ThreeSigPlacesFormat := '#0' + DefaultFormatSettings.DecimalSeparator + '000';
  with TIBQuery.Create(self) do
  try
     AllowAutoActivateTransaction := true;
     Database := FIBDatabase;
     SQL.Text := sqlExample;
     EnableStatistics := true;
     Active := true;
     rowno := 1;
     while not EOF do
     begin
       writeln('Record No. ',rowno);
       Inc(rowno);
       writeln;
       for i := 0 to FieldCount - 1 do
       begin
         writeln(Fields[i].FieldName + ': ',Fields[i].AsString);
       end;
       writeln;
       next;
     end;
     if GetPerfStatistics(stats) then
     begin
       writeln('Current memory = ', FormatFloat(LargeCompFormat,stats[psCurrentMemory]));
       writeln('Delta memory = ', FormatFloat(LargeCompFormat,stats[psDeltaMemory]));
       writeln('Max memory = ', FormatFloat(LargeCompFormat,stats[psMaxMemory]));
       writeln('Elapsed time= ', FormatFloat(ThreeSigPlacesFormat,stats[psRealTime]/1000),' sec');
       writeln('Cpu = ', FormatFloat(ThreeSigPlacesFormat,stats[psUserTime]/1000),' sec');
       writeln('Buffers = ', FormatFloat('#0',stats[psBuffers]));
       writeln('Reads = ', FormatFloat('#0',stats[psReads]));
       writeln('Writes = ', FormatFloat('#0',stats[psWrites]));
       writeln('Fetches = ', FormatFloat('#0',stats[psFetches]));
     end;
     if StmtHandle.GetRowsAffected(SelectCount, InsertCount, UpdateCount, DeleteCount) then
     begin
       writeln('Selects = ',SelectCount);
       writeln('Inserts = ',InsertCount);
       writeln('Updates = ',UpdateCount);
       writeln('Deletes = ',DeleteCount);
     end;
  finally
    Free;
  end;
end;

procedure TMyApplication.DoRun;
var
  ErrorMsg: String;
begin
  // quick check parameters
  ErrorMsg:=CheckOptions('h','help');
  if ErrorMsg<>'' then begin
    ShowException(Exception.Create(ErrorMsg));
    Terminate;
    Exit;
  end;

  // parse parameters
  if HasOption('h','help') then begin
    WriteHelp;
    Terminate;
    Exit;
  end;

  { In console Mode the application should own the database
    - ensures centralised exception handling }
  FIBDatabase := TIBDatabase.Create(self);
  FIBTransaction := TIBTransaction.Create(self);
  FIBDatabase.DatabaseName := 'employee';
  FIBDatabase.Params.Add('user_name=SYSDBA'); {You may have to modify this!}
  FIBDatabase.Params.Add('password=masterkey');  {You may have to modify this!}
  FIBDatabase.Params.Add('lc_ctype=UTF8');
  FIBTransaction.DefaultDatabase := FIBDatabase;
  DoQuery;

  // stop program loop
  Terminate;
end;

constructor TMyApplication.Create(TheOwner: TComponent);
begin
  inherited Create(TheOwner);
  StopOnException:=True;
end;

destructor TMyApplication.Destroy;
begin
  inherited Destroy;
end;

procedure TMyApplication.WriteHelp;
begin
  { add your help code here }
  writeln('Usage: ',ExeName,' -h');
end;

var
  Application: TMyApplication;
begin
  Application:=TMyApplication.Create(nil);
  Application.Title:='IBX In Console Mode';
  Application.Run;
  {$IFDEF WINDOWS}
  Readln; {Gives a chance to see the program output}
  {$ENDIF}
  Application.Free;
end.

Revision:	209
Committed:	Wed Mar 14 12:48:51 2018 UTC (6 years, 1 month ago) by tony
File size:	6354 byte(s)
Log Message:	Fixes Merged
#	User	Rev	Content
1	tony	143	(*
2			* IBX For Lazarus (Firebird Express)
3			*
4			* The contents of this file are subject to the Initial Developer's
5			* Public License Version 1.0 (the "License"); you may not use this
6			* file except in compliance with the License. You may obtain a copy
7			* of the License here:
8			*
9			* http://www.firebirdsql.org/index.php?op=doc&id=idpl
10			*
11			* Software distributed under the License is distributed on an "AS
12			* IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
13			* implied. See the License for the specific language governing rights
14			* and limitations under the License.
15			*
16			* The Initial Developer of the Original Code is Tony Whyman.
17			*
18			* The Original Code is (C) 2015 Tony Whyman, MWA Software
19			* (http://www.mwasoftware.co.uk).
20			*
21			* All Rights Reserved.
22			*
23			* Contributor(s): ______________________________________.
24			*
25			*)
26
27	tony	31	program project1;
28
29			{$mode objfpc}{$H+}
30
31			{ CONSOLE MODE EXAMPLE
32
33			This is a simple use of IBX to access the employee database in console mode.
34			The program opens the database, runs a query and writes the result to stdout.
35
36			Key points to note:
37
38			1. In console mode, you have to create the Database and Transaction objects
39			explicitly and link them to each other.
40
41			2. The Database properties have to be set explicitly. This includes username
42			and password. In the example, these are set from literals. You could update
43			this to (e.g.) parse the command line arguments from "ParamStr".
44			However, this is left as an exercise for the implementor.
45
46			3. It's a good idea to have the application own the IBDatabase. This ensures that
47			exceptions are routed through your application object's exception handler.
48			}
49
50			uses
51			{$IFDEF UNIX}{$IFDEF UseCThreads}
52			cthreads,
53			{$ENDIF}{$ENDIF}
54	tony	209	Classes, SysUtils, CustApp, IBDatabase, IBQuery, IB
55	tony	31	{ you can add units after this };
56
57			const
58			sqlExample =
59			'with recursive Depts As ( '+
60			'Select DEPT_NO, DEPARTMENT, HEAD_DEPT, cast(DEPARTMENT as VarChar(256)) as DEPT_PATH,'+
61			'cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH '+
62			'From DEPARTMENT Where HEAD_DEPT is NULL '+
63			'UNION ALL '+
64	tony	45	'Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH \|\| '' / '' \|\| D.DEPARTMENT as DEPT_PATH,'+
65			'Depts.DEPT_KEY_PATH \|\| '';'' \|\| D.DEPT_NO as DEPT_KEY_PATH '+
66			'From DEPARTMENT D '+
67			'JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO '+
68	tony	31	')'+
69
70			'Select A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE,'+
71			'A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH '+
72			'From EMPLOYEE A '+
73			'JOIN Depts D On D.DEPT_NO = A.DEPT_NO';
74
75			type
76
77			{ TMyApplication }
78
79			TMyApplication = class(TCustomApplication)
80			private
81			FIBDatabase: TIBDatabase;
82			FIBTransaction: TIBTransaction;
83			procedure DoQuery;
84			protected
85			procedure DoRun; override;
86			public
87			constructor Create(TheOwner: TComponent); override;
88			destructor Destroy; override;
89			procedure WriteHelp; virtual;
90			end;
91
92			{ TMyApplication }
93
94	tony	209
95	tony	31	procedure TMyApplication.DoQuery;
96			var i, rowno: integer;
97	tony	209	stats: TPerfCounters;
98			SelectCount, InsertCount, UpdateCount, DeleteCount: integer;
99			LargeCompFormat: string;
100			ThreeSigPlacesFormat: string;
101	tony	31	begin
102	tony	209	LargeCompFormat := '#' + DefaultFormatSettings.ThousandSeparator + '##0';
103			ThreeSigPlacesFormat := '#0' + DefaultFormatSettings.DecimalSeparator + '000';
104	tony	31	with TIBQuery.Create(self) do
105			try
106	tony	45	AllowAutoActivateTransaction := true;
107	tony	31	Database := FIBDatabase;
108			SQL.Text := sqlExample;
109	tony	209	EnableStatistics := true;
110	tony	31	Active := true;
111			rowno := 1;
112			while not EOF do
113			begin
114			writeln('Record No. ',rowno);
115			Inc(rowno);
116			writeln;
117			for i := 0 to FieldCount - 1 do
118			begin
119			writeln(Fields[i].FieldName + ': ',Fields[i].AsString);
120			end;
121			writeln;
122			next;
123			end;
124	tony	209	if GetPerfStatistics(stats) then
125			begin
126			writeln('Current memory = ', FormatFloat(LargeCompFormat,stats[psCurrentMemory]));
127			writeln('Delta memory = ', FormatFloat(LargeCompFormat,stats[psDeltaMemory]));
128			writeln('Max memory = ', FormatFloat(LargeCompFormat,stats[psMaxMemory]));
129			writeln('Elapsed time= ', FormatFloat(ThreeSigPlacesFormat,stats[psRealTime]/1000),' sec');
130			writeln('Cpu = ', FormatFloat(ThreeSigPlacesFormat,stats[psUserTime]/1000),' sec');
131			writeln('Buffers = ', FormatFloat('#0',stats[psBuffers]));
132			writeln('Reads = ', FormatFloat('#0',stats[psReads]));
133			writeln('Writes = ', FormatFloat('#0',stats[psWrites]));
134			writeln('Fetches = ', FormatFloat('#0',stats[psFetches]));
135			end;
136			if StmtHandle.GetRowsAffected(SelectCount, InsertCount, UpdateCount, DeleteCount) then
137			begin
138			writeln('Selects = ',SelectCount);
139			writeln('Inserts = ',InsertCount);
140			writeln('Updates = ',UpdateCount);
141			writeln('Deletes = ',DeleteCount);
142			end;
143	tony	31	finally
144			Free;
145			end;
146			end;
147
148			procedure TMyApplication.DoRun;
149			var
150			ErrorMsg: String;
151			begin
152			// quick check parameters
153			ErrorMsg:=CheckOptions('h','help');
154			if ErrorMsg<>'' then begin
155			ShowException(Exception.Create(ErrorMsg));
156			Terminate;
157			Exit;
158			end;
159
160			// parse parameters
161			if HasOption('h','help') then begin
162			WriteHelp;
163			Terminate;
164			Exit;
165			end;
166
167			{ In console Mode the application should own the database
168			- ensures centralised exception handling }
169			FIBDatabase := TIBDatabase.Create(self);
170			FIBTransaction := TIBTransaction.Create(self);
171	tony	37	FIBDatabase.DatabaseName := 'employee';
172	tony	31	FIBDatabase.Params.Add('user_name=SYSDBA'); {You may have to modify this!}
173			FIBDatabase.Params.Add('password=masterkey'); {You may have to modify this!}
174	tony	33	FIBDatabase.Params.Add('lc_ctype=UTF8');
175	tony	31	FIBTransaction.DefaultDatabase := FIBDatabase;
176			DoQuery;
177
178			// stop program loop
179			Terminate;
180			end;
181
182			constructor TMyApplication.Create(TheOwner: TComponent);
183			begin
184			inherited Create(TheOwner);
185			StopOnException:=True;
186			end;
187
188			destructor TMyApplication.Destroy;
189			begin
190			inherited Destroy;
191			end;
192
193			procedure TMyApplication.WriteHelp;
194			begin
195			{ add your help code here }
196			writeln('Usage: ',ExeName,' -h');
197			end;
198
199			var
200			Application: TMyApplication;
201			begin
202			Application:=TMyApplication.Create(nil);
203			Application.Title:='IBX In Console Mode';
204			Application.Run;
205	tony	33	{$IFDEF WINDOWS}
206	tony	45	Readln; {Gives a chance to see the program output}
207	tony	33	{$ENDIF}
208	tony	31	Application.Free;
209			end.
210