examples/fbsql/fbsql.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) 2014 Tony Whyman, MWA Software
 *  (http://www.mwasoftware.co.uk).
 *
 *  All Rights Reserved.
 *
 *  Contributor(s): ______________________________________.
 *
*)
program fbsql;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  {$IFDEF WINDOWS} Windows, {$ENDIF}
  Classes, SysUtils, CustApp
  { you can add units after this }
  ,IBDatabase, ibxscript, IBExtract, DB, IBVersion,
  IBDataOutput, RegExpr
  {$IFDEF UNIX} ,TermIO, IOStream {$ENDIF}

  ;

const
  FExceptionTrapped: boolean = false;

type
  TInteractiveSQLProcessor = class;

  { TFBSQL }

  TFBSQL = class(TCustomApplication)
  private
    FIBDatabase: TIBDatabase;
    FIBTransaction: TIBTransaction;
    FIBXScript: TIBXScript;
    FISQLProcessor: TInteractiveSQLProcessor;
    FExtract: TIBExtract;
    FOutputFile: TStream;
    FDataOutputFormatter: TDataOutputFormatter;
    FPromptedForPassword: string;
    procedure LogHandler(Sender: TObject; Msg: string);
    procedure ErrorLogHandler(Sender: TObject; Msg: string);
    procedure loginPrompt(Database: TIBDatabase; LoginParams: TStrings);
  protected
    procedure DoRun; override;
    procedure ShowException(E: Exception); override;
  public
    constructor Create(TheOwner: TComponent); override;
    procedure WriteHelp; virtual;
  end;

  { TInteractiveSQLProcessor }

  {This is a TCustomIBXScript descendent that uses the console for input/output.
   It additionally supports QUIT/EXIT Commands. The log file can either be redirected
   to the console or sent to a separate file.}

  TInteractiveSQLProcessor = class(TCustomIBXScript)
  private
    FUseLogFile: boolean;
  protected
    procedure Add2Log(const Msg: string; IsError: boolean=true); override;
    function ProcessStatement(stmt: string): boolean; override;
  public
    constructor Create(aOwner: TComponent); override;
    procedure Run;
    property UseLogFile: boolean read FUseLogFile write FUseLogFile;
  end;

{$IFDEF UNIX}
function getpassword: string;
var oldattr, newattr: termios;
    stdinStream: TIOStream;
    c: char;
begin
  Result := '';
  stdinStream := TIOStream.Create(iosInput);
  try
    TCGetAttr(stdinStream.Handle, oldattr);
    newattr := oldattr;
    newattr.c_lflag := newattr.c_lflag and not (ICANON or ECHO);
    TCSetAttr( stdinStream.Handle, TCSANOW, newattr );
    try
      repeat
        read(c);
        if c = #10 then break;
        write('*');
        Result += c;
      until false;
      writeln;
    finally
      TCSetAttr( stdinStream.Handle, TCSANOW, oldattr );
    end;
  finally
    stdinStream.Free;
  end;
end;
{$ENDIF}
{$IFDEF WINDOWS}
function getpassword: string;
var oldmode, newmode: DWORD;
    c: char;
begin
  Result := '';
  GetConsoleMode(GetStdHandle(STD_INPUT_HANDLE), oldmode);
  newmode := oldmode - ENABLE_ECHO_INPUT - ENABLE_LINE_INPUT;
  SetConsoleMode(GetStdHandle(STD_INPUT_HANDLE),newmode);
  try
    repeat
      read(c);
      if c = #13 then break;
      write('*');
      Result += c;
    until false;
    writeln;
  finally
    SetConsoleMode(GetStdHandle(STD_INPUT_HANDLE),oldmode);
  end
end;
{$ENDIF}

{ TInteractiveSQLProcessor }

procedure TInteractiveSQLProcessor.Add2Log(const Msg: string; IsError: boolean);
begin
  if UseLogFile then
    inherited Add2Log(Msg,IsError)
  else
  if IsError then
    writeln(stderr,msg)
  else
    writeln(msg);
end;

function TInteractiveSQLProcessor.ProcessStatement(stmt: string): boolean;
var  RegexObj: TRegExpr;
     Terminator: char;
     ucStmt: string;
begin
  Result := inherited ProcessStatement(stmt);
  if not Result then
  begin
    Terminator := SQLStatementReader.Terminator;
    ucStmt := AnsiUpperCase(stmt);
    RegexObj := TRegExpr.Create;
    try
      RegexObj.Expression := '^ *(QUIT|EXIT) *(\' + Terminator + '|)';
      if RegexObj.Exec(ucStmt) then
      begin
         TInteractiveSQLStatementReader(SQLStatementReader).Terminated := true;
         Result := true;
      end;
    finally
      RegexObj.Free;
    end;
  end;
end;

constructor TInteractiveSQLProcessor.Create(aOwner: TComponent);
begin
  inherited Create(aOwner);
  SetSQLStatementReader(TInteractiveSQLStatementReader.Create);
  Echo := true;
end;

procedure TInteractiveSQLProcessor.Run;
begin
  ProcessStream;
end;

{ TFBSQL }

procedure TFBSQL.LogHandler(Sender: TObject; Msg: string);
begin
  if FOutputFile <> nil then
    FOutputFile.WriteAnsiString(Msg + LineEnding)
  else
    writeln( Msg);
end;

procedure TFBSQL.ErrorLogHandler(Sender: TObject; Msg: string);
begin
  writeln(stderr, Msg);
end;

procedure TFBSQL.loginPrompt(Database: TIBDatabase; LoginParams: TStrings);
var password: string;
begin
  if LoginParams.Values['password'] <> '' then Exit;
  write(LoginParams.Values['user_name'] + '''s Password:');
  password := getpassword;
  if password <> '' then
    LoginParams.Values['password'] := password;
end;

procedure TFBSQL.DoRun;
var
  ErrorMsg: String;
  SQLFileName: string;
  DoExtract: boolean;
  OutputFileName: string;
  i: integer;
  ExtractTypes: TExtractTypes;
  Opts,NonOpts: TStrings;
  OutputFormat: string;
  SQLStatement: string;
begin
  writeln(stderr,'fbsql: an SQL interpreter for Firebird');
  writeln(stderr,'Built using IBX ' + IBX_VERSION);
  writeln(stderr,'Copyright (c) MWA Software ' + system.copy({$I %DATE%},1,4));

  // quick check parameters
  Opts := TStringList.Create;
  NonOpts := TStringList.Create;
  try
    ErrorMsg := CheckOptions('aAhbegu:i:o:p:r:s:t:c:',['help','user','pass','role'],Opts,NonOpts);
    {Database name is last parameter if given and not an option}
    if (NonOpts.Count > 0) and ((Opts.Count = 0) or
             ((Opts.ValueFromIndex[Opts.Count-1] <> NonOpts[NonOpts.Count-1])) or
             (ParamCount = 1) or (ParamStr(ParamCount-1)[2] in ['!','A','h','b','e','g']))then
      FIBDatabase.DatabaseName := ParamStr(ParamCount);
  finally
    Opts.Free;
    NonOpts.Free;
  end;
  if ErrorMsg<>'' then begin
    ShowException(Exception.Create(ErrorMsg));
    Terminate;
    Exit;
  end;

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

  SQLFileName := '';
  OutputFileName := '';
  DoExtract := false;
  ExtractTypes := [];
  FDataOutputFormatter := TIBBlockFormatOut;
  SQLStatement := '';

  {Initialise user_name and password from environment if available}

  if GetEnvironmentVariable('ISC_USER') <> '' then
    FIBDatabase.Params.Add('user_name=' + GetEnvironmentVariable('ISC_USER'));

  if GetEnvironmentVariable('ISC_PASSWORD') <> '' then
    FIBDatabase.Params.Add('password=' + GetEnvironmentVariable('ISC_PASSWORD'));

  {Process Command line options}

  if HasOption('a') then
  begin
    DoExtract := true;
  end;

  if HasOption('A') then
  begin
    DoExtract := true;
    ExtractTypes := [etData];
  end;

  if not HasOption('b') then
  begin
    FIBXScript.StopOnFirstError := false;
    FISQLProcessor.StopOnFirstError := false;
  end;

  if not HasOption('e') then
    FIBXScript.Echo := false;

  if HasOption('i') then
    SQLFileName := GetOptionValue('i');

  if HasOption('g')then
    ExtractTypes += [etGrantsToUser];

  if HasOption('o') then
  begin
    OutputFileName := GetOptionValue('o');
    FISQLProcessor.UseLogFile := true;
  end;

  if HasOption('p','pass') then
    FIBDatabase.Params.Values['password'] := GetOptionValue('p','pass');

  if HasOption('r','role') then
    FIBDatabase.Params.Values['sql_role_name'] := GetOptionValue('r','role');

  if HasOption('s') then
    SQLStatement := GetOptionValue('s');

  if HasOption('t') then
  begin
    OutputFormat := GetOptionValue('t');
    if OutputFormat = 'CSV' then
      FDataOutputFormatter := TIBCSVDataOut
    else
    if OutputFormat = 'INS' then
      FDataOutputFormatter := TIBInsertStmtsOut
    else
    if OutputFormat = 'BLK' then
      FDataOutputFormatter := TIBBlockFormatOut
    else
      raise Exception.CreateFmt('Unrecognised data output format "%s"',[OutputFormat]);
  end;

  if HasOption('u','user') then
    FIBDatabase.Params.Values['user_name'] := GetOptionValue('u','user');

  if HasOption('c','charset') then
    FIBDatabase.Params.Values['lc_ctype'] := GetOptionValue('c','charset');

  {Validation}

  FIBDatabase.LoginPrompt := (FIBDatabase.Params.IndexOfName('user_name') <> -1) and
                              (FIBDatabase.Params.Values['password'] = '');

  if not DoExtract then
  begin
    if (SQLStatement <> '') and (SQLFileName <> '') then
       raise Exception.Create('An SQL Script File and text cannot be simultaneously requested');

    if (SQLStatement = '') and (SQLFileName <> '')  and not FileExists(SQLFileName) then
      raise Exception.CreateFmt('SQL File "%s" not found!',[SQLFileName]);

  end;

  if DoExtract and ((SQLFileName <> '') or (SQLStatement <> '')) then
    raise Exception.Create('Extract and script execution cannot be simulateously requested');

  {This is where it all happens}

  FIBXScript.DataOutputFormatter := FDataOutputFormatter.Create(self);
  FISQLProcessor.DataOutputFormatter := FDataOutputFormatter.Create(self);

  if OutputFileName <> '' then
    FOutputFile := TFileStream.Create(OutputFileName,fmCreate);

  FIBDatabase.Connected := FIBDatabase.DatabaseName <> '';
  try
    if DoExtract then
    begin
      FExtract.ExtractObject(eoDatabase,'',ExtractTypes);
      if FOutputFile <> nil then
        FExtract.Items.SaveToStream(FOutputFile)
      else
      for i := 0 to FExtract.Items.Count - 1 do
        writeln(FExtract.Items[i]);
    end
    else
    if SQLFileName <> '' then
      FIBXScript.RunScript(SQLFileName)
    else
    if SQLStatement <> '' then
      FIBXScript.ExecSQLScript(SQLStatement)
    else
      FISQLProcessor.Run;
  finally
    FIBDatabase.Connected := false;
    if FOutputFile <> nil then
      FOutputFile.Free;
  end;


  // stop program loop
  Terminate;
end;

procedure TFBSQL.ShowException(E: Exception);
begin
  FExceptionTrapped := true;
  writeln(stderr,'Error: ' + E.Message);
end;

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

  { Create Components }
  FIBDatabase := TIBDatabase.Create(self);
  FIBDatabase.OnLogin := @loginPrompt;
  FIBDatabase.Params.Clear;
  FIBDatabase.Params.Values['lc_ctype'] := 'UTF8';
  FIBTransaction := TIBTransaction.Create(self);
  FIBTransaction.DefaultDatabase := FIBDatabase;
  FIBXScript := TIBXScript.Create(self);
  FIBXScript.Database := FIBDatabase;
  FIBXScript.Transaction := FIBTransaction;
  FIBXScript.OnOutputLog := @LogHandler;
  FIBXScript.OnErrorLog := @ErrorLogHandler;
  FISQLProcessor := TInteractiveSQLProcessor.Create(self);
  FISQLProcessor.Database := FIBDatabase;
  FISQLProcessor.Transaction := FIBTransaction;
  FISQLProcessor.OnOutputLog := @LogHandler;
  FISQLProcessor.OnErrorLog := @ErrorLogHandler;
  FExtract := TIBExtract.Create(self);
  FExtract.Database := FIBDatabase;
  FExtract.Transaction := FIBTransaction;

  FIBTransaction.Params.Add('concurrency');
  FIBTransaction.Params.Add('wait');

end;

procedure TFBSQL.WriteHelp;
begin
  writeln(stderr,'Usage: ',ExtractFileName(ExeName),' <options> <database name>');
  writeln(stderr,'Options:');
  writeln(stderr,'-a            write database metadata to stdout');
  writeln(stderr,'-A            write database metadata and table data to stdout');
  writeln(stderr,'-b            stop on first error');
  writeln(stderr,'-e            echo sql statements to stdout');
  writeln(stderr,'-g            include grants to normal users in database metadata');
  writeln(stderr,'-i <filename> execute SQL script from file');
  writeln(stderr,'-h            show this information');
  writeln(stderr,'-o <filename> output to this file instead of stdout');
  writeln(stderr,'-p <password> provide password on command line (insecure)');
  writeln(stderr,'-r <rolename> open database with this rolename');
  writeln(stderr,'-s <sql>      Execute SQL text');
  writeln(stderr,'-t            specify output format for SQL Statements');
  writeln(stderr,'              BLK (default) for block format');
  writeln(stderr,'              CSV for CSV format');
  writeln(stderr,'              INS for Insert Statement format');
  writeln(stderr,'-u <username> open database with this username (defaults to SYSDBA)');
  writeln(stderr,'-c <character set name> connection charset');
  writeln;
  writeln(stderr,'Environment Variables:');
  writeln(stderr,'ISC_USER      Login user Name');
  writeln(stderr,'ISC_PASSWORD  Login password');
end;

var
  Application: TFBSQL;
begin
  Application:=TFBSQL.Create(nil);
  Application.Run;
  Application.Free;
  if FExceptionTrapped then
    Halt(1);
end.

Revision:	362
Committed:	Tue Dec 7 13:27:39 2021 UTC (3 years ago) by tony
File size:	13367 byte(s)
Log Message:	initiate test release
#	Content
1	(*
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) 2014 Tony Whyman, MWA Software
19	* (http://www.mwasoftware.co.uk).
20	*
21	* All Rights Reserved.
22	*
23	* Contributor(s): ______________________________________.
24	*
25	*)
26	program fbsql;
27
28	{$mode objfpc}{$H+}
29
30	uses
31	{$IFDEF UNIX}{$IFDEF UseCThreads}
32	cthreads,
33	{$ENDIF}{$ENDIF}
34	{$IFDEF WINDOWS} Windows, {$ENDIF}
35	Classes, SysUtils, CustApp
36	{ you can add units after this }
37	,IBDatabase, ibxscript, IBExtract, DB, IBVersion,
38	IBDataOutput, RegExpr
39	{$IFDEF UNIX} ,TermIO, IOStream {$ENDIF}
40
41	;
42
43	const
44	FExceptionTrapped: boolean = false;
45
46	type
47	TInteractiveSQLProcessor = class;
48
49	{ TFBSQL }
50
51	TFBSQL = class(TCustomApplication)
52	private
53	FIBDatabase: TIBDatabase;
54	FIBTransaction: TIBTransaction;
55	FIBXScript: TIBXScript;
56	FISQLProcessor: TInteractiveSQLProcessor;
57	FExtract: TIBExtract;
58	FOutputFile: TStream;
59	FDataOutputFormatter: TDataOutputFormatter;
60	FPromptedForPassword: string;
61	procedure LogHandler(Sender: TObject; Msg: string);
62	procedure ErrorLogHandler(Sender: TObject; Msg: string);
63	procedure loginPrompt(Database: TIBDatabase; LoginParams: TStrings);
64	protected
65	procedure DoRun; override;
66	procedure ShowException(E: Exception); override;
67	public
68	constructor Create(TheOwner: TComponent); override;
69	procedure WriteHelp; virtual;
70	end;
71
72	{ TInteractiveSQLProcessor }
73
74	{This is a TCustomIBXScript descendent that uses the console for input/output.
75	It additionally supports QUIT/EXIT Commands. The log file can either be redirected
76	to the console or sent to a separate file.}
77
78	TInteractiveSQLProcessor = class(TCustomIBXScript)
79	private
80	FUseLogFile: boolean;
81	protected
82	procedure Add2Log(const Msg: string; IsError: boolean=true); override;
83	function ProcessStatement(stmt: string): boolean; override;
84	public
85	constructor Create(aOwner: TComponent); override;
86	procedure Run;
87	property UseLogFile: boolean read FUseLogFile write FUseLogFile;
88	end;
89
90	{$IFDEF UNIX}
91	function getpassword: string;
92	var oldattr, newattr: termios;
93	stdinStream: TIOStream;
94	c: char;
95	begin
96	Result := '';
97	stdinStream := TIOStream.Create(iosInput);
98	try
99	TCGetAttr(stdinStream.Handle, oldattr);
100	newattr := oldattr;
101	newattr.c_lflag := newattr.c_lflag and not (ICANON or ECHO);
102	TCSetAttr( stdinStream.Handle, TCSANOW, newattr );
103	try
104	repeat
105	read(c);
106	if c = #10 then break;
107	write('*');
108	Result += c;
109	until false;
110	writeln;
111	finally
112	TCSetAttr( stdinStream.Handle, TCSANOW, oldattr );
113	end;
114	finally
115	stdinStream.Free;
116	end;
117	end;
118	{$ENDIF}
119	{$IFDEF WINDOWS}
120	function getpassword: string;
121	var oldmode, newmode: DWORD;
122	c: char;
123	begin
124	Result := '';
125	GetConsoleMode(GetStdHandle(STD_INPUT_HANDLE), oldmode);
126	newmode := oldmode - ENABLE_ECHO_INPUT - ENABLE_LINE_INPUT;
127	SetConsoleMode(GetStdHandle(STD_INPUT_HANDLE),newmode);
128	try
129	repeat
130	read(c);
131	if c = #13 then break;
132	write('*');
133	Result += c;
134	until false;
135	writeln;
136	finally
137	SetConsoleMode(GetStdHandle(STD_INPUT_HANDLE),oldmode);
138	end
139	end;
140	{$ENDIF}
141
142	{ TInteractiveSQLProcessor }
143
144	procedure TInteractiveSQLProcessor.Add2Log(const Msg: string; IsError: boolean);
145	begin
146	if UseLogFile then
147	inherited Add2Log(Msg,IsError)
148	else
149	if IsError then
150	writeln(stderr,msg)
151	else
152	writeln(msg);
153	end;
154
155	function TInteractiveSQLProcessor.ProcessStatement(stmt: string): boolean;
156	var RegexObj: TRegExpr;
157	Terminator: char;
158	ucStmt: string;
159	begin
160	Result := inherited ProcessStatement(stmt);
161	if not Result then
162	begin
163	Terminator := SQLStatementReader.Terminator;
164	ucStmt := AnsiUpperCase(stmt);
165	RegexObj := TRegExpr.Create;
166	try
167	RegexObj.Expression := '^ (QUIT\|EXIT) (\' + Terminator + '\|)';
168	if RegexObj.Exec(ucStmt) then
169	begin
170	TInteractiveSQLStatementReader(SQLStatementReader).Terminated := true;
171	Result := true;
172	end;
173	finally
174	RegexObj.Free;
175	end;
176	end;
177	end;
178
179	constructor TInteractiveSQLProcessor.Create(aOwner: TComponent);
180	begin
181	inherited Create(aOwner);
182	SetSQLStatementReader(TInteractiveSQLStatementReader.Create);
183	Echo := true;
184	end;
185
186	procedure TInteractiveSQLProcessor.Run;
187	begin
188	ProcessStream;
189	end;
190
191	{ TFBSQL }
192
193	procedure TFBSQL.LogHandler(Sender: TObject; Msg: string);
194	begin
195	if FOutputFile <> nil then
196	FOutputFile.WriteAnsiString(Msg + LineEnding)
197	else
198	writeln( Msg);
199	end;
200
201	procedure TFBSQL.ErrorLogHandler(Sender: TObject; Msg: string);
202	begin
203	writeln(stderr, Msg);
204	end;
205
206	procedure TFBSQL.loginPrompt(Database: TIBDatabase; LoginParams: TStrings);
207	var password: string;
208	begin
209	if LoginParams.Values['password'] <> '' then Exit;
210	write(LoginParams.Values['user_name'] + '''s Password:');
211	password := getpassword;
212	if password <> '' then
213	LoginParams.Values['password'] := password;
214	end;
215
216	procedure TFBSQL.DoRun;
217	var
218	ErrorMsg: String;
219	SQLFileName: string;
220	DoExtract: boolean;
221	OutputFileName: string;
222	i: integer;
223	ExtractTypes: TExtractTypes;
224	Opts,NonOpts: TStrings;
225	OutputFormat: string;
226	SQLStatement: string;
227	begin
228	writeln(stderr,'fbsql: an SQL interpreter for Firebird');
229	writeln(stderr,'Built using IBX ' + IBX_VERSION);
230	writeln(stderr,'Copyright (c) MWA Software ' + system.copy({$I %DATE%},1,4));
231
232	// quick check parameters
233	Opts := TStringList.Create;
234	NonOpts := TStringList.Create;
235	try
236	ErrorMsg := CheckOptions('aAhbegu:i:o:p:r:s:t:c:',['help','user','pass','role'],Opts,NonOpts);
237	{Database name is last parameter if given and not an option}
238	if (NonOpts.Count > 0) and ((Opts.Count = 0) or
239	((Opts.ValueFromIndex[Opts.Count-1] <> NonOpts[NonOpts.Count-1])) or
240	(ParamCount = 1) or (ParamStr(ParamCount-1)[2] in ['!','A','h','b','e','g']))then
241	FIBDatabase.DatabaseName := ParamStr(ParamCount);
242	finally
243	Opts.Free;
244	NonOpts.Free;
245	end;
246	if ErrorMsg<>'' then begin
247	ShowException(Exception.Create(ErrorMsg));
248	Terminate;
249	Exit;
250	end;
251
252	// parse parameters
253	if HasOption('h','help') then
254	begin
255	WriteHelp;
256	Terminate;
257	Exit;
258	end;
259
260	SQLFileName := '';
261	OutputFileName := '';
262	DoExtract := false;
263	ExtractTypes := [];
264	FDataOutputFormatter := TIBBlockFormatOut;
265	SQLStatement := '';
266
267	{Initialise user_name and password from environment if available}
268
269	if GetEnvironmentVariable('ISC_USER') <> '' then
270	FIBDatabase.Params.Add('user_name=' + GetEnvironmentVariable('ISC_USER'));
271
272	if GetEnvironmentVariable('ISC_PASSWORD') <> '' then
273	FIBDatabase.Params.Add('password=' + GetEnvironmentVariable('ISC_PASSWORD'));
274
275	{Process Command line options}
276
277	if HasOption('a') then
278	begin
279	DoExtract := true;
280	end;
281
282	if HasOption('A') then
283	begin
284	DoExtract := true;
285	ExtractTypes := [etData];
286	end;
287
288	if not HasOption('b') then
289	begin
290	FIBXScript.StopOnFirstError := false;
291	FISQLProcessor.StopOnFirstError := false;
292	end;
293
294	if not HasOption('e') then
295	FIBXScript.Echo := false;
296
297	if HasOption('i') then
298	SQLFileName := GetOptionValue('i');
299
300	if HasOption('g')then
301	ExtractTypes += [etGrantsToUser];
302
303	if HasOption('o') then
304	begin
305	OutputFileName := GetOptionValue('o');
306	FISQLProcessor.UseLogFile := true;
307	end;
308
309	if HasOption('p','pass') then
310	FIBDatabase.Params.Values['password'] := GetOptionValue('p','pass');
311
312	if HasOption('r','role') then
313	FIBDatabase.Params.Values['sql_role_name'] := GetOptionValue('r','role');
314
315	if HasOption('s') then
316	SQLStatement := GetOptionValue('s');
317
318	if HasOption('t') then
319	begin
320	OutputFormat := GetOptionValue('t');
321	if OutputFormat = 'CSV' then
322	FDataOutputFormatter := TIBCSVDataOut
323	else
324	if OutputFormat = 'INS' then
325	FDataOutputFormatter := TIBInsertStmtsOut
326	else
327	if OutputFormat = 'BLK' then
328	FDataOutputFormatter := TIBBlockFormatOut
329	else
330	raise Exception.CreateFmt('Unrecognised data output format "%s"',[OutputFormat]);
331	end;
332
333	if HasOption('u','user') then
334	FIBDatabase.Params.Values['user_name'] := GetOptionValue('u','user');
335
336	if HasOption('c','charset') then
337	FIBDatabase.Params.Values['lc_ctype'] := GetOptionValue('c','charset');
338
339	{Validation}
340
341	FIBDatabase.LoginPrompt := (FIBDatabase.Params.IndexOfName('user_name') <> -1) and
342	(FIBDatabase.Params.Values['password'] = '');
343
344	if not DoExtract then
345	begin
346	if (SQLStatement <> '') and (SQLFileName <> '') then
347	raise Exception.Create('An SQL Script File and text cannot be simultaneously requested');
348
349	if (SQLStatement = '') and (SQLFileName <> '') and not FileExists(SQLFileName) then
350	raise Exception.CreateFmt('SQL File "%s" not found!',[SQLFileName]);
351
352	end;
353
354	if DoExtract and ((SQLFileName <> '') or (SQLStatement <> '')) then
355	raise Exception.Create('Extract and script execution cannot be simulateously requested');
356
357	{This is where it all happens}
358
359	FIBXScript.DataOutputFormatter := FDataOutputFormatter.Create(self);
360	FISQLProcessor.DataOutputFormatter := FDataOutputFormatter.Create(self);
361
362	if OutputFileName <> '' then
363	FOutputFile := TFileStream.Create(OutputFileName,fmCreate);
364
365	FIBDatabase.Connected := FIBDatabase.DatabaseName <> '';
366	try
367	if DoExtract then
368	begin
369	FExtract.ExtractObject(eoDatabase,'',ExtractTypes);
370	if FOutputFile <> nil then
371	FExtract.Items.SaveToStream(FOutputFile)
372	else
373	for i := 0 to FExtract.Items.Count - 1 do
374	writeln(FExtract.Items[i]);
375	end
376	else
377	if SQLFileName <> '' then
378	FIBXScript.RunScript(SQLFileName)
379	else
380	if SQLStatement <> '' then
381	FIBXScript.ExecSQLScript(SQLStatement)
382	else
383	FISQLProcessor.Run;
384	finally
385	FIBDatabase.Connected := false;
386	if FOutputFile <> nil then
387	FOutputFile.Free;
388	end;
389
390
391	// stop program loop
392	Terminate;
393	end;
394
395	procedure TFBSQL.ShowException(E: Exception);
396	begin
397	FExceptionTrapped := true;
398	writeln(stderr,'Error: ' + E.Message);
399	end;
400
401	constructor TFBSQL.Create(TheOwner: TComponent);
402	begin
403	inherited Create(TheOwner);
404	StopOnException:=True;
405
406	{ Create Components }
407	FIBDatabase := TIBDatabase.Create(self);
408	FIBDatabase.OnLogin := @loginPrompt;
409	FIBDatabase.Params.Clear;
410	FIBDatabase.Params.Values['lc_ctype'] := 'UTF8';
411	FIBTransaction := TIBTransaction.Create(self);
412	FIBTransaction.DefaultDatabase := FIBDatabase;
413	FIBXScript := TIBXScript.Create(self);
414	FIBXScript.Database := FIBDatabase;
415	FIBXScript.Transaction := FIBTransaction;
416	FIBXScript.OnOutputLog := @LogHandler;
417	FIBXScript.OnErrorLog := @ErrorLogHandler;
418	FISQLProcessor := TInteractiveSQLProcessor.Create(self);
419	FISQLProcessor.Database := FIBDatabase;
420	FISQLProcessor.Transaction := FIBTransaction;
421	FISQLProcessor.OnOutputLog := @LogHandler;
422	FISQLProcessor.OnErrorLog := @ErrorLogHandler;
423	FExtract := TIBExtract.Create(self);
424	FExtract.Database := FIBDatabase;
425	FExtract.Transaction := FIBTransaction;
426
427	FIBTransaction.Params.Add('concurrency');
428	FIBTransaction.Params.Add('wait');
429
430	end;
431
432	procedure TFBSQL.WriteHelp;
433	begin
434	writeln(stderr,'Usage: ',ExtractFileName(ExeName),' <options> <database name>');
435	writeln(stderr,'Options:');
436	writeln(stderr,'-a write database metadata to stdout');
437	writeln(stderr,'-A write database metadata and table data to stdout');
438	writeln(stderr,'-b stop on first error');
439	writeln(stderr,'-e echo sql statements to stdout');
440	writeln(stderr,'-g include grants to normal users in database metadata');
441	writeln(stderr,'-i <filename> execute SQL script from file');
442	writeln(stderr,'-h show this information');
443	writeln(stderr,'-o <filename> output to this file instead of stdout');
444	writeln(stderr,'-p <password> provide password on command line (insecure)');
445	writeln(stderr,'-r <rolename> open database with this rolename');
446	writeln(stderr,'-s <sql> Execute SQL text');
447	writeln(stderr,'-t specify output format for SQL Statements');
448	writeln(stderr,' BLK (default) for block format');
449	writeln(stderr,' CSV for CSV format');
450	writeln(stderr,' INS for Insert Statement format');
451	writeln(stderr,'-u <username> open database with this username (defaults to SYSDBA)');
452	writeln(stderr,'-c <character set name> connection charset');
453	writeln;
454	writeln(stderr,'Environment Variables:');
455	writeln(stderr,'ISC_USER Login user Name');
456	writeln(stderr,'ISC_PASSWORD Login password');
457	end;
458
459	var
460	Application: TFBSQL;
461	begin
462	Application:=TFBSQL.Create(nil);
463	Application.Run;
464	Application.Free;
465	if FExceptionTrapped then
466	Halt(1);
467	end.
468